Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Myths

Re: Oracle Myths

From: Nuno Souto <nsouto_at_optushome.com.au.nospam>
Date: Wed, 22 May 2002 21:15:46 +1000
Message-ID: <3ceb7f0c$0$15146$afc38c87@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?

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

-- 
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam
Received on Wed May 22 2002 - 06:15:46 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US