RE: That crazy CBO.....

Glad you liked it!

In theory, yes, it should be as efficient as an index range scan. In logical I/Os, it should be, too, unless perhaps there is a single-I/O extra cost if your range starts in one leaf block, but finds its first "hit" in the next leaf block, which should happen rarely (less than 1% of the time), but *might* happen occasionally, depending on details of the index branch-block implementation. In *practice*, there might be non-I/O costs very slightly higher than for the UNIQUE scan, cost that I don't see, depending on deep implementation details I don't know about, but, practically speaking, yes, it should be as good as an index UNIQUE scan. (When the query is this efficient, the main cost is generally the round trip to the database, not the I/O-related execution costs.)

Yours,

Dan Tow
650-858-1557
www.singingsql.com

Quoting "Bobak, Mark" <Mark.Bobak_at_il.proquest.com>:

> Dan,
>
> Wow! This is great! And thanks for the detailed explanation of *why*
> and *how* it works!
>
> I had an index on (ip_addr_start,ip_addr_end,ausr_id).
> I recreated it as (ip_addr_end,ip_addr_start,ausr_id),
> and tried your technique. It worked great!
>
> FYI:
> Old Query: 67 buffer gets
> New Query: 5 buffer gets!
>
> Great improvement!
>
> So, if I read the explanation correctly, it makes this
> range scan as efficient as an index unique scan, is that
> right?
>
> Thanks again,
>
> -Mark

--
http://www.freelists.org/webpage/oracle-l