RE: How to calculate cardinality for inequality in WHERE
- From: "Iotzov, Iordan" <IIotzov@xxxxxxxxxxxxxxx>
- To: "Martin.Klier@xxxxxxxxxx" <Martin.Klier@xxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 11 Jul 2013 09:56:17 -0400
Sorry - I did not read the whole question....
Check out SmartSource Xpress, our new iPad app!
Follow us on Twitter | Like us on Facebook
-----Original Message-----
From: Iotzov, Iordan
Sent: Thursday, July 11, 2013 9:54 AM
To: 'Martin.Klier@xxxxxxxxxx'; oracle-l@xxxxxxxxxxxxx
Subject: RE: How to calculate cardinality for inequality in WHERE
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Martin Klier
Sent: Thursday, July 11, 2013 9:28 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
