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 beeverything) both showed 1000 rows.Tracing with 10046 has INDEX FULL SCAN with 1 row, and INDEX RANGE SCAN with19998 rows.Since dbms_xplan should use real data, why the difference?Different interpretation of Rows? a bug? anybody else see this?Henry
-- //www.freelists.org/webpage/oracle-l