Re: Oracle Myths

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Wed, 22 May 2002 09:50:01 +0100
Message-ID: <3ceb5bba$0$8514$ed9e5944@reading.news.pipex.net>

"D.Y." <dyou98_at_aol.com> wrote in message news:f369a0eb.0205211119.5b4b6b90_at_posting.google.com...
> nsouto_at_optushome.com.au (Nuno Souto) wrote in message
news:<dd5cc559.0205151535.cef9399_at_posting.google.com>...
> > "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in
message news:<3ce21b71$0$8510$ed9e5944_at_reading.news.pipex.net>...
> > > Suggested list to be added to, deleted from etc
> >
> > Oh yeah, almost forgot:
> >
> > - Put the most selective column first in a concatenated index.
> > (this one AFAIK was never true, but somehow it stuck)
> >

> I found myself on the opposite side again :-)

> There is one reason to do this: the most selective column is more likely
> to be part of the criteria of many queries. Therefore having the most
> selective column first will likely allow you to serve many queries with
> one index. As for performance, compressed index with the least selective
> column first may be better. So do what's best for your application.

where the query uses more than one column Oracle is bright enough to select the index even if the columns are in the 'wrong' order (the output below I think shows this). As you say indexes built in this way are also cadidates for key compression.

Where you have one column used in many queries you should be considering indexing that column alone.

SQL> select count(distinct client) from agltransact;

COUNT(DISTINCTCLIENT)

SQL> select count(distinct cur_amount) from agltransact;

COUNT(DISTINCTCUR_AMOUNT)

SQL> CREATE INDEX MOSTSEL ON AGLTRANSACT(CUR_AMOUNT,CLIENT); Index created.

SQL> analyze table agltransact estimate statistics;

Table analyzed.

SQL> set autotrace on explain statistics; SQL> select rowid,cur_amount,status
  2 from agltransact
  3 where client = 'DE'
  4 and cur_amount = 293.75;

ROWID CUR_AMOUNT S

------------------ ---------- -
AAAGbaAALAAACwQAAh     293.75
<snip>
AAAGbaAAOAAADf6AAh     293.75

50 rows selected.

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=366 Card=369 Bytes=5
          166)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'AGLTRANSACT' (Cost=366 C
          ard=369 Bytes=5166)

   2    1     INDEX (RANGE SCAN) OF 'MOSTSEL' (NON-UNIQUE) (Cost=3 Car
          d=369)

Statistics

          0  recursive calls
          0  db block gets
         36  consistent gets
          0  physical reads
          0  redo size
       3434  bytes sent via SQL*Net to client
        740  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

SQL> drop index mostsel;

Index dropped.

SQL> create index leastsel on agltransact(client,cur_amount);

Index created.

SQL> select rowid,cur_amount,status
  2 from agltransact
  3 where client = 'DE'
  4 and cur_amount = 293.75;

ROWID CUR_AMOUNT S

------------------ ---------- -
AAAGbaAALAAACwQAAh     293.75
<snip>
AAAGbaAAOAAADf6AAh     293.75

50 rows selected.

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=369 Bytes=516
          6)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'AGLTRANSACT' (Cost=2 Car
          d=369 Bytes=5166)

   2    1     INDEX (RANGE SCAN) OF 'LEASTSEL' (NON-UNIQUE) (Cost=1 Ca
          rd=369)

Statistics

          0  recursive calls
          0  db block gets
         36  consistent gets
          0  physical reads
          0  redo size
       3434  bytes sent via SQL*Net to client
        740  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

Received on Wed May 22 2002 - 03:50:01 CDT