Re: Oracle Myths

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3ceb5bba$0$8514$ed9e5944_at_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.
>

What I was referring to are some typical senarios many people have seen, such as product_id, product_type. You can do  create index prod_idx01 on product(product_id,product_type); or,
 create index prod_idx01 on product(product_type,product_id) compress;

Which is better? It depends on what kind of queries you run or expect to run in the future. For these types of columns I would do this as well,  alter table product add constraint prod_pk primary key(product_id);

Now the question becomes, why don't you create a PK index on product_id alone if you expect many of your queries to look like '...where product_id=...'. One of the reasons is so that some queries won't need to access the table. I'd be interested to hear other pros and cons, not just for select, but also for insert/update/delete statements.

> SQL> select count(distinct client) from agltransact;
>
> COUNT(DISTINCTCLIENT)
> ---------------------
> 3
>
> SQL> select count(distinct cur_amount) from agltransact;
>
> COUNT(DISTINCTCUR_AMOUNT)
> -------------------------
> 436018
>
> 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 - 10:35:01 CDT