0
votes
2answers
54 views

Which should I use on my DB? - Clustered Index or Non Clustered Index or Both

I just need advice on how I am going to optimize my database. Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN I have that format of tables on my database. Each ...
1
vote
0answers
2k views

How to speed up query on table with millions of rows

The Issue: I'm working on a big table that consists about 37mln rows. Data include measurements of many devices made in certain time e.g. '2013-09-24 10:45:50'. Each day all of those devices are ...
3
votes
1answer
71 views

Locating row in index page

In clustered index, we have root, intermediate and leaf level pages. Every page has few records where indicating range of pages from level below. How SQL check whether some index key belong to ...
0
votes
1answer
119 views

clustered index - data warehouse

My source data comes from multiple OLTP databases where the primary key is an identitiy column. We anticipate adding data centers and/or splitting the databases by product. Currently i have one system ...
5
votes
2answers
741 views

Large Fact table and partitioning key dilemma

I have fairly large fact table (2 billion records, approx 120 GB). This table is not partitioned and the queries are very slow to respond. I am planning to partition the table and indexes. The table ...
4
votes
2answers
429 views

Does a cluster index provide more benefits than pre-sorting the load file and creating non-cluster index?

Informix 11.70.TC4DE: CREATE TABLE cluster_tbl ( fk_id INT, data CHAR(2048) ); LOAD FROM "presorted.ld" INSERT INTO cluster_tbl; CREATE UNIQUE CLUSTER INDEX cl_idx ON cluster_tbl(fk_id); ...
30
votes
3answers
1k views

Is the concept of a clustered index in a DB design sensical when using SSDs?

When designing a SQL server data schema and the subsequent queries, sprocs, views, etc. does the notion of a clustered index and order of data on disk make any sense to consider for DB designs made ...