July 20, 2011 at 12:11 pm
Hello All,
I have table ACCT with composite primary key as_of_date and Acct_id.
and I have foreign key tran_id1,trans2 and tran3.
Total no. of records are 50,000 and more than that.
Now I am using query
1. select * from acct
output :clustered index scan
2. select * from acct
where as_of_date= '2011-06-30'
Output: clustered index seek
3. select * from acct
where as_of_date= '2011-06-30' and acct_id = 100
Output : clustered index scan
4. select * from acct
where acct_id =100
Output:select-nested loop-index scan
-Key look up
and saying that missing index :create non clustered index on acct.acct_id
5. I already have non clustered index on tran1 ,tran2 and tran3.but
select * from acct
where tran1=225
Output: select- nested loop-index seek(0%)
-key look up(100%)
and saying that missing index : create non clustered index on tran1 includes(as_of_date,acct_id,tran1_tran2,tran3)
(means it's saying that create non clustered index for all the columns)
Question:
(a.)No.1 is right or not?
(b.)why NO. 4 saying that create non clustered index on acct_id? I already have composite primary key on as_of_date and acct_id (see result No.2 and No.3)
(c.) Why No.5 saying that create non clustered index on tran1 includes(as_of_date,acct_id,tran1_tran2,tran3)but i already have non clustered index on tran1.
Please let me know...
Thanks
L
July 20, 2011 at 12:18 pm
Without seeing your DDL, I can't be sure, but it sounds like your non-clustered index doesn't use tran1 in the first column.
I heard a lecture at a SQL Saturday that composite indexes evaluate in the order they are placed in the index. So Create Nonclustered Index MyIndex ON MyTable (Col3, Col2, Col1) will not be used if Col1 is the only column in the ON clause. And it might not be used if you have multiple columns in the ON clause but in the wrong order from what the index has (I could be wrong about this last one).
July 20, 2011 at 12:23 pm
For the missing index warnings I remember reading about a bug that even after you create the index, the warning can remain in place. I forget the exact circumstances but this is defenitely a possible culprit. The only safe way is to read the actual execution plan so you raelly know what's going on.
Please read this : http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
July 20, 2011 at 12:25 pm
Just add one more to what Brandie said, You are using Select *. The optimizer might think that having an Index with Include is faster for case 5.
For number 1, since you do not have a where clause, it will do a CL scan. (If you did not have a CL, it would have done a table scan)
Just my 2 cents
-Roy
July 20, 2011 at 12:25 pm
Hello Brandie,
But i did not understand...
Please explain in Detail.
I have created indexes today so i want to make sure I am right or not ?
Thanks
July 20, 2011 at 12:28 pm
Thanks Roy.
What about (b) and (c).?
Thanks
L
July 20, 2011 at 12:31 pm
i am sorry ...
Please ignore previous no.3
this is the right one.
3. select * from acct
where as_of_date= '2011-06-30' and acct_id = 100
Output : clustered index seek
Thanks
L
July 20, 2011 at 12:31 pm
patla4u (7/20/2011)
Hello Brandie,But i did not understand...
Please explain in Detail.
I have created indexes today so i want to make sure I am right or not ?
Thanks
Plz read the full article in my link. It will answer all your questions.
If it's still unclear then come back and post a more detailed question.
July 20, 2011 at 12:33 pm
Ok ..NInja..
Thanks..If I will not get it then I will post again .
Thanks
L:-)
July 20, 2011 at 12:34 pm
We do not have enough information to fully answer your questions. We would need create table statements, create index statements, and sample data insert statements in order to test ourselves. You have also not attached an execution plan to this article. So, at the moment, all we can do is guess at your problem.
If you have an index table with 20+ columns (Col1, Col2, Col3, Col4 ... Col20) and your index definition has the columns listed as Col3, Col2, Col1, then the index will use those columns in that order.
Say your code is:
Select Col1, Col2, Col3, Col4, Col5
From MyTable
Where Col1 = 'abc'
This code will most likely NOT use the index because the index only works if your WHERE clause has Col3 listed and if it references Col3 first. So
Select Col1, Col2, Col3, Col4, Col5
From MyTable
Where Col3 = 'abc'
will likely use the index. The same if you have Col3, Col2 or Col3, Col2, and Col1 in your WHERE clause. But they have to be in that order. Having the columns in this order: Col1, Col2, Col3 will likely not use the index either. The same is true of the ON clause.
July 20, 2011 at 12:35 pm
Now that I think of it, that articles series is a good read for you as well.
July 21, 2011 at 7:45 am
Brandie Tarvin (7/20/2011)
This code will most likely NOT use the index because the index only works if your WHERE clause has Col3 listed and if it references Col3 first. So
It's more than "most likely NOT" - it WILL NOT, and can not.
Patla4u - understanding how a composite index is used is quite straightforward really once you get it. The classic example is to compare it to a telephone directory. This is sorted alphabetically by surname, and then alaphabetically by firstname. Think of it as a composite index.
If someone asks you to look through a phone directory and find all the people whose FIRST name starts with "Pau", could you do it? No - not really. You've not been given the information needed to know where to start looking in the directory (i.e. the SURNAME), and so you'd have to look through the entire book from start to finish (the same as a table oro clustered index scan).
If someone asks you to look through the phone directory and find all people whose FIRST name starts with "Pau" AND whose SURNAME = "Smith", then you could do that easily. Your Query #4 is missing the "SURNAME" information (the as_of_date), so it cannot use the index.
Query #5 is using your non-clustered index on (tran1, tran2, tran3) but because you say SELECT *, the database engine will also have to go to the main table as well as the index in order to get the extra columns. This means that the index is not as efficient as it could be, and the "missing index" message you are seeing is telling you that if you put those extra columns into the index itself, they will be there ready and waiting to be selected without the need to go to the main table as well. This is called a COVERED INDEX, and they can be very efficient if used sensibly. Try to resist creating lots of indexes with lots of additional covering columns though - you'll just use lots of database space unnecessarily and won't always improve your query performance.
July 21, 2011 at 8:00 am
Philip Yale-193937 (7/21/2011)
Brandie Tarvin (7/20/2011)
This code will most likely NOT use the index because the index only works if your WHERE clause has Col3 listed and if it references Col3 first. SoIt's more than "most likely NOT" - it WILL NOT, and can not.
That's pretty much what I thought, but I wasn't sure enough to say "won't". @=)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply