December 6, 2002 at 9:36 am
People: I have a table that establish a relationship between other two. The PK has two columns, the queries over this table has a double-sense. In one sense the order is similar to the PK order (col1, col2). In the other order, the sense is inverse (col2, col1). It's neccesary to define a index over this inverse order (col2, col1)? How can i check if there is a table scan over there?
TIA,
December 6, 2002 at 9:52 am
Set up a profile trace that includes index ID, monitor it for index id's of 0, which are table scans. This also gives you the exact statement causing the table scan if you collect the sqltext.
Edited by - scorpion_66 on 12/06/2002 09:52:01 AM
December 7, 2002 at 9:32 pm
SO you are saying that it references col2 first then col1 or exactly what. If you PK is defined as Col1, Col2 and there is an index for that then any reference to Col2 should utilize that index as longs as it the statistics make it the best canidate. The way to ensure that is to make sure the first column in the index order is the most unique item. The reason is stats are based on the first column of a composite index.
For ex. If Col1 contains 4 unique values and 2 contains 500000. But if you reference Col3 which has 10 unique items and has an index. Col3s index will be used.
Also, the query manager may decide based on a query with reference to col2 that the index density does make it worth using and instead you have a Table Scan performed. Just make sure you have a high number of unique values in your index, the highest being the first column in a composite index.
You should not need another index in reverse order.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply