A type of index mostly used in SQL-Server, which aligns the data of a table with the index.
1
vote
2answers
84 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 ...
-2
votes
0answers
43 views
Unexpected index scan against partitioned table [closed]
I have a set of partitioned tables with CIX on (ID, DATE) and partitioned on DATE.
There is another identical set of non-partitioned tables with CIX on ID.
Case A: T1 and T2 are non-partitioned ...
0
votes
0answers
10 views
Would Mongo's ObjectID work well in InnoDB's clustered index?
Mongo's ObjectID is defined like this:
ObjectId is a 12-byte BSON type, constructed using:
a 4-byte value representing the seconds since the Unix epoch,
a 3-byte machine identifier,
...
1
vote
4answers
146 views
Speed up large clustered index creation?
I have a large table, the row count of the table is more than 3 billion, the data space for this table is about 120 GB.
And Intel Xeon CPU E5645 @2.4GHz(2 processors), 24 CPUs, 64G memory, 64bit ...
15
votes
4answers
1k views
Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?
Our databases consist of lots of tables, most of them using an integer surrogate key as a primary key. About half of these primary keys are on identity columns.
The database development started in ...
2
votes
2answers
82 views
Cannot rebuild index, but there's no reason why not?
I've created a process whereby I am able to only rebuild indexes that need rebuilding(the process takes an hour and a half if I rebuild them all), and while it works beautifully, it gets stuck on one ...
0
votes
0answers
41 views
is it good to use combined primary key for MySQL InnoDB clustered index?
I'm trying to build a aggregated reader website of multiple forums.
Because most queries are likely to be within same time periods for written_time column,
I'm thinking about taking advantage of ...
5
votes
2answers
146 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
votes
0answers
62 views
What is meant by Clustering index and Multilevel indices? [closed]
What is meant by Clustering Index and Multilevel Indices ?
I could not find much when I googled it.
1
vote
2answers
121 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 ...
2
votes
1answer
57 views
Updating column in underlying table causes massive transaction log growth
I have an underlying table ~14k records and an indexed view that contains ~11 million records. When updating a column in the small underlying table this seems to cause a massive transaction log entry ...
4
votes
1answer
68 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
79 views
How to solve index missing problem in one table, while other table of same property does not produce any error?
Two databases with same structure at least for my eye. One of them was used as training/testing, where I can add list of products in Art table via a program's extension FDT but not in Art table of ...
2
votes
1answer
64 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 ...
2
votes
1answer
46 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
132 views
How do you avoid a deadlock on the primary key of a table? [closed]
So I have a deadlock that looks like this. Both sides of the deadlock complain about the the primary key clustered index.
<deadlock-list>
<deadlock victim="processec49b8">
...
11
votes
2answers
813 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?
0
votes
1answer
95 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 ...
3
votes
2answers
251 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 ...
0
votes
1answer
1k 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
157 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 ...
11
votes
1answer
174 views
What are valid usage scenarios for HEAP tables?
I am currently doing some data imports into a legacy system and discovered that this system does not use a single clustered index. A quick Google search introduced me to the concept of HEAP tables and ...
6
votes
2answers
268 views
Adding a Clustered index to a HEAP table on someone else's app
We're using a proprietary application based on SQL Server 2005, which has many HEAP based tables (that is, no Clustered index). Over the years, these tables have grown badly fragmented (e.g. 99% ...
2
votes
1answer
212 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 ...
7
votes
1answer
386 views
What factors go into an Indexed View's Clustered Index being selected?
Breifly
What factors go into they query optimizer's selection of an indexed view's index?
For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this ...
3
votes
1answer
160 views
How do I automate converting heaps into clustered indexes?
I have around 40 tables in one of our production databases that, for varying reasons, where not created with a clustered index.
What is the best automated method for converting these heaps?
Since ...
4
votes
3answers
348 views
Lookup table for a one-to-many relationship
I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source.
tickets
- id
- sutff
lookup
- ...
1
vote
1answer
78 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 ...
3
votes
0answers
232 views
SQL Server 2008 R2 clustered index issue
Last week, we came up with a strange issue on Clustered index. We are using SQL Server 2008 R2 in our organization.
A stored procedure is written for our application which contains a cursor in it. ...
2
votes
1answer
139 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
2answers
130 views
Need for reaching data through clustered index with a non-clustered index
I have found that when a table has both clustered and non-clustered indexes (on different columns), the leaf level non-clustered pages, instead of pointing to the data row, point to the node of the ...
4
votes
1answer
184 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 ...
23
votes
5answers
2k views
Why do sequential GUID keys perform faster than sequential INT keys in my test case?
After asking this question comparing sequential and non-sequential GUIDs, I tried to compare the INSERT performance on 1) a table with a GUID primary key initialized sequentially with ...
4
votes
2answers
264 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);
...
3
votes
0answers
206 views
Why are runtimes different for a table including a primary key versus a table with a clustered unique index added after population
When I create a temporary table with a PK defined from the outset like this I get great performance in subsequent joins on that key:
create table #temp (
field1 int not null
field2 int ...
4
votes
2answers
591 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 ...
4
votes
1answer
270 views
Storage order vs Result order
This is a spin-off question from Sort order specified in primary key, yet sorting is executed on SELECT.
@Catcall says this on the subject of storage order (clustered index) and the output order
...
9
votes
2answers
5k views
What is a Clustered Index?
I need a short explaining of clustered index.
What is a clustered index?
What are best practices for using the clustered index?
5
votes
2answers
799 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 ...
2
votes
1answer
101 views
After dropping the clustered index, it is still attempting to do a clustered insert
I am trying to convert data from one database system to another. One of the tables I need to transfer and format contains over 10 million rows.
I am running the following script to do it:
USE ...
5
votes
2answers
122 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 ...
1
vote
1answer
240 views
Update and select concurrently running on same set of rows but different indexes are referred?
In multithreaded application where update and select can go simultaneously assuming update is used within transaction. If table has non clustered index and clustered index, what are the chances of ...
6
votes
3answers
335 views
What happens to non clustered index when included column is updated using update statement?
Question on non clustered index with included columns (DB - MS SQL Server).
I read blog Optimized Non-clustered Index Maintenance which gives information on query plans when update statements is ...
22
votes
3answers
1k views
Performance of Non Clustered Indexes on Heaps vs Clustered Indexes
This 2007 White Paper compares the performance for individual select/insert/delete/update and range select statements on a table organized as a clustered index vs that on a table organized as a heap ...
3
votes
1answer
492 views
Clustered Index Update on Nonclustered Columns
Using: SQL Server 2008 R2
I am currently stepping through a query execution plan, and have come across an instance of a clustered index update on a table. The issue here is that the columns that are ...
0
votes
2answers
893 views
Clustered index always better than Non-Clustered index?
When I have a clustered index on 'A', is it better to use this clustered index even if I have
selection of 'A=constant'? If not, why?
Thank you!
3
votes
1answer
99 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 ...
3
votes
3answers
612 views
Does ORDER BY on a clustered key effect performance?
Say I have a table clustered on PrimaryKey, and in all cases I want my results to be ordered by PrimaryKey so I additionally always ORDER BY PrimaryKey in all queries.
Does this ORDER BY affect ...
2
votes
2answers
170 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
160 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 ...
