| Tracy McKibben 2006-10-24, 6:30 pm |
| plokolp wrote:
> Thanks for responding Tibor.
>
> I think I've got it.... Are you saying then that some indexes are used
> for joining tables, and others are used for retrieving the eventual
> display data?
>
> The SQL I am analyzing selects from a series of nested views. View
> within views etc. Perhaps a NC index is good for joining these views
> together, but the CL index is for if and when the eventual data is
> displayed?
>
> ...am I barking up the wrong tree here?
>
> Many thanks,
>
> Rod.
>
> Tibor Karaszi wrote:
>
I agree with Tibor, you need to start simple, to understand how the
indexes are used... Here's an explanation that I posted earlier, see if
this helps get you started:
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|