RE: That crazy CBO.....
- From: Dan Tow <dantow@xxxxxxxxxxxxxx>
- To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- Date: Thu, 28 Oct 2004 10:46:09 -0500
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@xxxxxxxxxxxxxxx>:
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
