Re: Concatenated Index

Steve Johnson wrote:

>If I have a concatenated index on tablea columns (a, b, c) and a query:
>
>SELECT count(*)
>FROM tablea,
>tableb
>WHERE tablea.a like 'AB%'
> AND tablea.b=tableb.b
>AND tablea.c=tableb.c
>
>Will columns b and c of the index on tablea be used in the query? It uses
>the index like I wanted but I don't get the speed I think I should which
>leads me to believe that it doesn't.
>
>Any help would be appreciated.
>
>Thanks...
>Steve
>
>

Hello Steve,

from your question, I guess that you expect that "tableb" is driving, which is not necessarely true. If it is, according to my knowledge, and if the join-method is nested loops, than for each row of tableb, tabla is searched, with start value 'AB', and the values for columns b and c. The end value shoud be < 'AC' (and columns b and c). In the range 'AB', * filtering within the index entries * occurs to satisfy the column values b and c.

So, column values b and c are used in this case - but not very efficiently.

A probably much faster way for this case is to use a hash join (with hash_area_size set to 2 Mbyte or more), and a index fast-full scan for tablea.

Note that in 9iR2, the plan table contains 2 new columns, access_predicates and filter_predicates, which show this. Before 9.2, one should use (awkward) event 10060 to get the same info.

Kind Regards,

Herman de Boer
sr consultant
IT Consultancy Group bv
h.de.boer_at_itcg.nl Received on Wed Jun 05 2002 - 04:24:12 CDT