A type of index mostly used in SQL-Server, which aligns the data of a table with the index.
2
votes
2answers
77 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
34 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
135 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
4answers
108 views
How to speed up creating clustered index on large table on SQL Server 2008 R2?
I have a large SQL Server table, the row count of the table is more than 3 billion, the data space for this table is about 120G.
And Intel Xeon CPU E5645 @2.4GHz(2 processors), 24 CPUs, 64G memory, ...
-1
votes
0answers
60 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
111 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
51 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
58 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
77 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
62 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
129 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">
...
2
votes
1answer
45 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
89 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
939 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
155 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 ...
3
votes
2answers
243 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 ...
11
votes
1answer
172 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
253 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
198 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
365 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
155 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
332 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
- ...
3
votes
0answers
222 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. ...
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 ...
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
1answer
181 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
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
2answers
257 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
204 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
583 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 ...
