0
votes
2answers
33 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 ...
2
votes
1answer
55 views

Clustered index additional storage

Reading some books I found that clustered index not require additional disk space for storing index data, because leaf level of clustered index are data itself. But, as clustered index is organized ...
-1
votes
1answer
80 views

Difference between Clustered Index and Unique Clustered Index? [closed]

I got difference between clustered index and unique index here. But what is the exact difference between clustered index and unique-clustered index? AFAIK creating clustered index can be done for a ...
1
vote
0answers
513 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 ...
1
vote
2answers
321 views

Avoiding a sort on an already clustered index for group by

On a table T with two fields, pid and did, the following query results in a seq. scan followed by a sort on pid: select count(did), pid from T group by pid Here is the query plan: ...
2
votes
3answers
435 views

Meaning of “nonclustered located on primary”

When I run sp_help (using ALT+F1) for a table, on the indexes, there is a column index_description. and the rows have for example: nonclustered located on PRIMARY (on foreign keys) nonclustered, ...
1
vote
2answers
769 views

SQL Server clustered index high fragmentation

I've got a Table with an Integer (4bytes) as primary key. it's defined as an identity. it is also the clustered index. Inserts are working perfectly fine. After inserting 2000 rows the fragmentation ...
6
votes
1answer
1k views

Why disabling a clustered index makes the table inaccessible?

When an index is disabled, the definition remains in the system catalog but is no longer used. SQL Server does not maintain the index (as data in the table changes), and the index cannot be used to ...
1
vote
2answers
200 views

Clustered vs Nonclustered Index

My database currently has a primary Key/Clustered index on the ID column for each table. However, the application that connects to the database is always looking at views defined as: SELECT * FROM ...
5
votes
1answer
266 views

How does PostgreSQL physically order new records on disk (after a cluster on primary key)?

Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
2
votes
1answer
57 views

How to efficiently hit an index in a query that uses IN (mysql)

I have a table like this where people can subscribe to certain feeds and the query fetches from the subscription. Here is a sample table: ...
0
votes
1answer
113 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 ...
0
votes
1answer
3k views

fundamental difference between Primary/Clustered and Secondary/Non-Clustered index

Are Primary Index and Clustered Index similar in concept Secondary Index and Non-Clustered Index similar in concept
2
votes
2answers
195 views

Defrag a HEAP by creating clustered index and immediately dropping it

I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...
2
votes
1answer
425 views

Index with multiple leaf levels

I have a three-column (int, smallint, smallint) composite clustered index with three leaf levels. My question is how and when does SQL Server create multiple leaf levels (index_level 0) for the same ...
1
vote
1answer
123 views

Finding exact row in Clustered index leaf page

When traversing through Clustered index in Sql Server, the engine will start from Root page and traverse over non leaf nodes until it reaches leaf level page (data page). Every data page has row ...
2
votes
1answer
155 views

Aren't two writes required to update a clustered index record

I was reading the article on indexes at simple-talk, where it is written that If a heap has a non-clustered index on it (as the primary key), and data is inserted into the table, two writes have ...
4
votes
1answer
371 views

How are indexes represented in an ERD?

What do indexes look like in an Entity Relationship Diagram? I've googled it and I'm unsure what the standard look of an index is in an ERD. And I'm talking about the crowsfoot diagram that includes ...
4
votes
2answers
398 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); ...
4
votes
2answers
845 views

Should a table have a clustered index even if it doesn't have appropriate fields for it?

If you have a table without a good candidate field or fields for a clustered index (stable, sequential), is it better to have a clustered index on a bad field or is it better to make all table indices ...
7
votes
2answers
3k views

Why index REBUILD does not reduce index fragmentatation?

I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It ...
5
votes
2answers
144 views

In SQL Server, is it possible to have a PRIMARY KEY on a table without either CLUSTERED or NONCLUSTERED indexes on the same key?

In Sql Server (2008), is it possible to have a PRIMARY KEY on a set of columns without either CLUSTERED or NONCLUSTERED indexes on the same set of columns? I am aware of the fact that PRIMARY KEY and ...
3
votes
1answer
118 views

should we replace some non-clustered indexes with clustered indexes

We have some data saved for reporting each of our tables currently has a unique id column that has an auto generated clustered index on the pk and a non-clustered index for the fk. I am thinking that ...
2
votes
2answers
208 views

Execution plan doesn't use clustered index!

I'm using "SQL*Plus: Release 10.2.0.1.0 - Production" and have a table foo with a field fooID. I have created a clustered B+tree index on the field foodID of my table foo. And I do the following: ...
5
votes
1answer
176 views

Composite or Single-Field Clustering Key

My DBA and I are having a disagreement on index structure. Consider data for a medical claim... We have the Header table with fields like: ClaimId (varchar(50)), PaidAmount, MemberID... and for ...
11
votes
2answers
944 views

Is there any tangible difference between a unique clustered index and a clustered primary key?

I understand that there may be a difference in meaning or intent between the two, but are there any behavioral or performance differences between a clustered primary key and a clustered unique index?