Re: Oracle Myths

In article <3cecaa23$0$237$ed9e5944_at_reading.news.pipex.net>, you said (and I quote):
>
> I get the same plan though even with status = 'E' where only 10 rows satisfy
> the query.

You'd need to have the columns reversed, with status coming first. Then it should be picked up. But you'd need to do something optimizer-wise: I suspect the default settings in 8i will cause it to ignore the index and go FS. Darn thing relies on FS too often for my taste...

> >
> > 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)

Cool, looks like 8173 is a lot more intelligent. Have you tried the same with reversed columns as in LEASTSEL? I'd be interested in seeing the "Card" and "Bytes" results. In theory would be the same for this particular statement(group by status,count)?

> 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.

hmmm, dangerous to use these numbers? Caching,etcetc. Anyways, let me see what I can come up with in my small 8i db.

>
> 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.
>

Exactly. And thanks a lot for doing this and having the patience to verify it. It's super to have great feedback from a reasonably large db in 8i. All my large ones are 8.0...

-- 
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam