[thelist] Nested SQL question
Rudy_Limeback at maritimelife.ca
Rudy_Limeback at maritimelife.ca
Tue Sep 12 15:09:55 CDT 2000
> You seem to be mentioning a lot of things that are in:
>
> Use Oracle's Explain Plan to Tune Your Queries
> http://evolt.org/index.cfm?menu=8&cid=2986
coincidence, i assure you ;o)
> In fact, I think adam even wrote this one. I wish the title
> was more general so that non-Oraclonians would read it...
> it's a really good article.
yes, it is
<tip type="physical database design">
a "covering" index is an index which contains all the columns necessary to
satisy a query, both in the select list and the where clause
why is this a good thing? because the database can resolve the query
using the covering index alone, and does not have to access the table at
all!
example:
create table foobar
( foo1 integer
, foo2 integer
, foo3 integer
, foo4 integer
, foo5 integer )
create index foobarindex on foobar
( foo1 , foo2 , foo3 )
the following query will *NOT* use foobarindex
SELECT foo1, foo2 FROM foobar
WHERE foo4 = 937
this query requires a column that is not in the index
the following query will (probably) *NOT* use foobarindex
SELECT foo1, foo2 FROM foobar
WHERE foo3 = 937
the database will typically use an index only if selectivity is provided
by the first column of the index
in this query, the selectivity is on a secondary index column, so the
database may just decide to use a table scan instead
the following query *WILL* use foobarindex
SELECT foo2, foo3 FROM foobar
WHERE foo1 = 937
all columns required for the query are within the index, and selectivity
is provided on the highest index column
</tip>
rudy
r937.com
More information about the thelist
mailing list