Path: news.f.de.plusline.net!news-fra1.dfn.de!news-mue1.dfn.de!news-stu1.dfn.de!news.germany.com!postnews.google.com!o5g2000hsb.googlegroups.com!not-for-mail From: "Kevin Kirkpatrick" Newsgroups: comp.databases.oracle.misc Subject: Number 1 gripe about CBO: 0 NNTP-Posting-Host: 144.160.98.31 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1175697633 22794 127.0.0.1 (4 Apr 2007 14:40:33 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 4 Apr 2007 14:40:33 +0000 (UTC) User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; SBC; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: o5g2000hsb.googlegroups.com; posting-host=144.160.98.31; posting-account=mW7hBA0AAABTqVYqprbLgSusB9AyVeEZ Xref: news.f.de.plusline.net comp.databases.oracle.misc:78647 Clipped from a subtree of the execution plan (Oracle 10g R2) 8 MERGE JOIN CARTESIAN Cardinality: 2,442 5 TABLE ACCESS BY INDEX ROWID SCHEMA1.T1 Cardinality: 1 4 INDEX RANGE SCAN SCHEMA1.T1_I1 Cardinality: 1 7 BUFFER SORT Cardinality: 2,323,987 6 TABLE ACCESS FULL SCHEMA1.T2 Cardinality: 2,323,987 Since when does 2.3 Million times "1" equal 2,442??? Yes, I know - behind the scenes, the CBO has decided that the filter criteria of #4 and #5 results in a cardinality of: Cardinality #5 = 20,000 x .05 x .01 x .01 x.01 = 0.0001 rows My beef is with the fact that the CBO does not immediately round this intermediate value up to "1" before applying it to future calculations. CARDINALITIES CAN NEVER EQUAL 0.0001 ROWS. They are either 1 or more. So how can it make sense to EVER evaluate a execution plan on the premise that one of intermediate steps will yield 0.0001 rows, a logical impossibility? The above snippet is a plan in a query that's been running in our production system for 12 hours now. step #4 / #5 actually yields 4 rows, not .0001 rows; so the CBO is doing a series of index nested loops using > 10 million rows, not 2,400 rows. The point is not that the stats on T1 and conditional criteria on T1 yielded a lower-than-actual estimate; the point is that no matter how low this estimate was, the CBO should have known that a cartesian join of T1 and T2 would yield at least the cardinality of T2.