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@xxxxxxxxxxxxxxx>
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

Other related posts: