RE: Reverse Key Index Performance

>
> Sometimes it's a pity that a problem can be resolved
> without being understood, but that's the real world.

No kidding -- if some things "appear" to work, it would help to understand the details to make sure a valid conclusion is being drawn. The test of reverse vs. b-tree was simply performed because of the situation described earlier where the only two issues where on those with RKI's. I had the test going on in the background while focusing on more critical work that needed to be done. I didn't expect the big difference or else I would have tried to be a little more controlled to really pinpoint all possible factors. And so now I will still be wondering what other factors were involved. And it's hard to duplicate on my Win2k box, and not sure that you could draw a correlation anyway due to such massive differences in size, HW, and OS's.

>
> A couple of thoughts (for next time).
>
> It would be useful to see the execution plans (particularly
> to see the plan dumped in the trace files just in case
> the theoretical plan was not the same as the
> actual plan).

The plan in the raw trace file was the same as what explain plan was giving.

> Also the full EXPLAIN PLAN output to see
> if the estimated index access costs on the subquery varied.

No can do -- was lucky to be able to temporarily "borrow" the space. I hardly ever pay attention to the calculated cost when dealing with problem queries, but, it would have been a good idea here to note the differences in the calculated costs when using the reverse key vs. b-tree. Had this been my primary focus, I would have taken the time to do a 10053 trace as well just to see inside the CBO's head and how it might calculate things differently between the RKI and the b-tree. Maybe there would have been differences, but the bottom line is the same access path was used either way, so I don't know how much we would gain from seeing the numbers.

>
> One thought that could explain the discrepancy, which
> would be controlled by the type of query and the size of
> the table.
>
> If Oracle optiimises the query by doing the DISTINCT
> before doing the subquery (and this is nominally a valid
> optimisation, depending on scale and statistics) then
> the EMPNOs being checked would be in empno order.

Would this show up differently in the plan? I don't guess that I have seen that. Typically I have seen the sort phase for the distinct operation as the last step. Or, are you implying that even if the sort phase shows up last, that internally it could have selected the distinct values before doing the correlation? I could see where that could be a valid optimization -- reduce the number of correlated UK index lookups. On the other hand, if most were unique, and the correlated sub-query eliminated many rows, the cost of sorting could be much less when done *after* the correlation, at the expense of more unique index lookups. And which one benefits the most?
>
> With a standard index, you would get 100% buffering
> of index blocks when doing the subquery - with the
> reverse key, you COULD get 0% buffering on the
> leaf blocks. It tallies with the timing - does it tally
> with the execution path ?

And especially in my test case. My update was simply a "where rownum < 1000001" -- yeah, I should have done something random. So, with this being a "fresh" table just inserted into, I know there's a high probability that I was updating physically adjacent rows that would correspond very nicely with the index, minimizing the number of blocks to visit and then benefiting from the buffering. And with the FTS on the MLOGS$ table, and the way I did the updates, and the MLOG$ table being "new", I would stand a good chance of reading those updated values in order. So this was like a best case scenario. That's why I then used the MLOG$ table from the "real" table for additional testing. Those updates would have been random, and not updating 1 million rows residing in the fewest blocks possible, thus being a little bit better test than using my MLOG$ table and it's built in advantage due to the way I did the updates. On a table of this size, and if the updates were really random, though, you could conceivably see cases where the difference in the number of index blocks visited could be nearly the same between the two types of indexes. But things could flush out with the RKI requiring physically reading a block again. So, buffer size could play a role.

And I also wonder what type of overhead is needed when reversing the value to do the index lookup. Similar to compressed indexes -- we know there can be a big benefit, but we also hear from some people where, in specific cases, the decompression overhead offset any advantage of a smaller index.

Anyway, thanks for throwing some things out there to think about. At some point in time, I would like to dig deeper, but just can't justify it now.

>
>
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
> Next Seminar - Australia - July/August
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).