RE: How to calculate cardinality for inequality in WHERE
Date: Thu, 11 Jul 2013 09:54:23 -0400
Message-ID: <9287D00721407A4CBDC9925C789DBB6702CED03AF4_at_nam-wil-exc-l03.newsamerica.com>
You can create a virtual column DIFF defined as (QTY1 - QTY2). You should gather stats on the table, including the new DIFF column. Add histograms to the DIFF column if needed. Then you should rewrite the statement like this : select * from TABLE
where DIFF < 0;
The CBO should have come with better cardinality numbers.
Iordan Iotzov
http://iiotzov.wordpress.com/
Check out SmartSource Xpress, our new iPad app! Follow us on Twitter | Like us on Facebook
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Klier
Sent: Thursday, July 11, 2013 9:28 AM
To: oracle-l_at_freelists.org
Subject: How to calculate cardinality for inequality in WHERE
Hello listers,
today again, a question merely for understanding than for solving a current problem.
DB-Version 11.2.0.3.6 SE on Linux x86_64
select * from TABLE
where QTY1 < QTY2;
There are approx. 1.5 million rows in this table. QTY1 < QTY2 means 0, 1 or 2 rows at max
Both columns have unpredictable numbers of values, so we see a Height Balanced Histogram for both. No NOT NULL constraints on QTY1/QTY2, but de-facto no NULLs.
Creating extended statistics for QTY1, QTY2 does not change anything, it seems the CBO cannot combine them for an inequality (maybe not for an equality, either).
So what's the best way to help Oracle to calculate the correct cardinality for WHERE QTY1 < QTY2?
Thanks in advance and best reagrds
Martin Klier
PS:
(For the practical folks:
In my real-life case, it was possible to change the SQL, so I solved the issue with:
select * from TABLE
where QTY1 - QTY2 <0;
and a function based index (QTY1 - QTY2) that creates a virtual column where we can create statistics for.)
--
Mit freundlichem Gruß
Best regards
Martin Klier
Senior Oracle Database Administrator
--
http://www.freelists.org/webpage/oracle-l
This message and its attachments may contain legally privileged or confidential information. It is intended solely for the named addressee. If you are not the addressee indicated in this message (or responsible for delivery of the message to the addressee), you may not copy or deliver this message or its attachments to anyone. Rather, you should permanently delete this message and its attachments and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of News America Incorporated or its subsidiaries must be taken not to have been sent or endorsed by any of them. No warranty is made that the e-mail or attachment(s) are free from computer virus or other defect.
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jul 11 2013 - 15:54:23 CEST