How to calculate cardinality for inequality in WHERE

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 Received on Thu Jul 11 2013 - 15:27:45 CEST