Re: xplan bug with Jonathan Lewis Computing Index?

This looks like a typical use of the 5% selectivity for a predicate involving an inequality with an unknown or in a join. See, for example the filter subquery selectivity at:

      http://jonathanlewis.wordpress.com/2006/11/08/subquery-selectivity/

Disclaimer - it's only a piece of SQL I created a long time ago (there's a little of the history here:

    http://www.jlcomp.demon.co.uk/hit_ratios.html

I didn't invent the name JLOCI.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

> From: "Henry Poras" <henry_at_itasoftware.com>
> Subject: xplan bug with Jonathan Lewis Computing Index?
> Date: Fri, 29 Jun 2007 16:43:40 -0400
>
> I was running the Jonathan Lewis Oracle Computing Index (
> http://www.miraclebenelux.nl/jloci.html ) which is just SQL high in CPU. It
> does connect-by using an IOT. When I looked at the execution plan using
> dbms_xplan.display_cursor (v. 10.2.0.3) something didn't look right. The IOT
> has 20,000 rows, I gathered stats on it, but the rows accessed, both in
> INDEX FULL SCAN and INDEX RANGE SCAN (access("N">PRIOR NULL) which should be
> everything) both showed 1000 rows.
>
> Tracing with 10046 has INDEX FULL SCAN with 1 row, and INDEX RANGE SCAN with
> 19998 rows.
>
> Since dbms_xplan should use real data, why the difference?
>
> Different interpretation of Rows? a bug? anybody else see this?
>
> Henry
>

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