Re: Oracle Myths

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 23 May 2002 09:36:51 +0100
Message-ID: <3cecaa23$0$237$ed9e5944@reading.news.pipex.net>

"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

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

Received on Thu May 23 2002 - 03:36:51 CDT