[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