Re: Oracle Myths

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.
>
> Ooops, sorry Nial. My apologies, complete lack of attention from me.
>
> The problem is not that this index should not be picked up. As you coded
> the queries (all of them) the index most definitely WILL be picked up,
> regardless of what order the columns are in.
>
> Let me elaborate a bit more.
>
> The problem is not that the index will not be picked up if ALL its
> columns are specified in the predicates against constants.
>
> The problem is that a "reversed column selectivity" index will NOT be
> picked up if your query only includes a predicate for the least selective
> column.
> I've jotted down below what I mean, against one of your examples.
>
> There are people claiming it would be picked up. Not to my knowledge in
> V8 or less. Only in 9i is this possible, IIRC.
>
> Another thing is that some people will claim the index will be more
> efficient if using reverse order of concatenated columns. Ie, resulting
> in less index I/O to locate the rows. This won't be true in any case,
> since at least V8 and IIRC was never true. Can anyone confirm or deny a
> different experience?
>

I believe the statement on I/O being equal is a correct one unless index is compressed. But there could be a slight difference in the number of CPU cycles needed to locate the rowid. If we look at an extreme situation where, using your example, cur_amount is unique and status is identical for all rows, two different search paths are, 1, if cur_amount <> key_value_1 then skip -- always executed

   else if status <> key_value_2 then skip -- executed only once vs
2, if status <> key_value_2 then skip -- always executed

   else if cur_amount <> key_value_2 then skip -- always executed

Could be very insignificant unless in a CPU-starved mode. With index compression tipping the I/O balance this is even less of an issue now.

> >
> > 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.
>
> Obviously, another type of query where this index would not work would
> be:
>
> select status,sum(cur_amount)
> from agltransact
> group by status;
>

A good example, allows Oracle to use FFS when you have a concatenated index. This type of quries should be anticipated. Seen too many of them.

> 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.
>
>
> 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.
>
> 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.
>
> 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.
Received on Wed May 22 2002 - 15:07:46 CDT