Re: Oracle Myths

Niall Litchfield wrote:
>
> "Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message
> news:3ceb7f0c$0$15146$afc38c87_at_news.optusnet.com.au...
> > In article <3ceb76f1$0$8509$ed9e5944_at_reading.news.pipex.net>, you said
> > (and I quote):
> > > This is 8173.
> >
> > > SQL> create index mostsel2 on agltransact(cur_amount,status);
> > >
> > > Index created.
> > >
> > > SQL> set autot on explain
> > > SQL> select att_1_id,dc_flag
> > > 2 from agltransact
> > > 3 where status = 'X'
> > > 4 and cur_amount = 1000;
> >
> > This should be:
> >
> > select att_1_id,dc_flag
> > from agltransact
> > where status = 'X';
> >
> > Note the absence of the most selective column in the predicates.
>
> You are correct the index doesn't get picked up here.
>
> SQL> select att_1_id,dc_flag
> 2 from agltransact
> 3 where status = 'X'
> 4 and rownum < 10
> 5 ;
>
> AT DC_FLAG
> -- ----------
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
>
> 9 rows selected.
>
> Elapsed: 00:00:00.08
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8340 Card=1094370 By
> tes=6566220)
>
> 1 0 COUNT (STOPKEY)
> 2 1 TABLE ACCESS (FULL) OF 'AGLTRANSACT' (Cost=8340 Card=109
> 4370 Bytes=6566220)
>
> <rownum used because of the large number of rows that would be returned>
>
> I get the same plan though even with status = 'E' where only 10 rows satisfy
> the query.
>
> >
> > Obviously, another type of query where this index would not work would
> > be:
> >
> > select status,sum(cur_amount)
> > from agltransact
> > group by status;
> >
> > a very common type of query in these situations. Hence me referring to
> > aggregate queries not being able to use the index. This should now be
> > obvious why not, due to the very definition of how concatenated indexes
> > work.
>
> SQL> select status,count(*)
> 2 from agltransact
> 3 group by status;
>
> S COUNT(*)
> - ----------
> 4926960
> B 157902
> D 2635
> E 10
> N 11213
> O 32110
> X 343503
>
> 7 rows selected.
>
> Elapsed: 00:00:09.01
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12657 Card=5 Bytes=5
> )
>
> 1 0 SORT (GROUP BY) (Cost=12657 Card=5 Bytes=5)
> 2 1 INDEX (FAST FULL SCAN) OF 'MOSTSEL2' (NON-UNIQUE) (Cost=
> 2 Card=5471848 Bytes=5471848)
>
> >
> >
> > Now, the second issue would be:
> >
> > if you compare the TOTAL I/O for "mostsel2" and "leastsel2" when using
> > YOUR original statements, which one would result in less I/O?
> > I believe it would be "leastsel2", particularly when compression is used.
>
> My original post included
>
> 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
>
> for both mostsel and leastsel which (unless I'm having a brain fit) show
> that IO is (at least in this case) identical.
>
> >
> > I also believe that given the statements as you coded them in the
> > example, it wouldn't matter significantly if you omitted "status" from
> > the "mostsel2" index all together. Ie, the gain in I/O is not enough to
> > warrant the extra overhead of keeping this column. And since the extra
> > column is never used for any aggregation when coded as in "mostsel2", it
> > is next to useless. Much better to just have the single most selective
> > column indexed and be done with it.
>
> I agree.
>
> >
> > The only instance where there might be a need for the two columns is if
> > the concatenation is needed for other purposes, such as RI/PK.
> >
> > Hope this is more clear now.
>
> At least we're having a discussion and testing out ideas, which was part of
> the point of posting the 'Myths' in the first place. I believe them all to
> be myths but belief does not a proof make.
>
> --
> 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
>
> ******************************************

<shameless advertising mode on>

For those people who:

1. enjoy Oracle Myths
2. live in the UK
3. like having a laugh

you can come see two presentations (Myths 1-10 at the Unix SIG group meeting June 11, Myths 11-20 at the DBMS SIG meeting July 2)

</shameless advertising mode off>

Cheers
Connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."