Re: How to calculate cardinality for inequality in WHERE

Here is what I tried in 11.2.0.1

• created table with 1.5 million records create table TEST_TAB as with TBL as (select 1 from DUAL connect by level <= 1500) select 'test' name,rownum QTY1, rownum QTY2 from tbl,tbl where rownum <= 1500000;

exec dbms_stats.gather_table_stats(USER,'TEST_TAB');

---update 3 records to set qty lesss than qty2 update TEST_TAB set qty1=qty2-1 where qty1 in (34,10000,999);

explain plan for
select * from TEST_TABwhere QTY1 < QTY2;

On Thu, Jul 11, 2013 at 6:57 PM, Martin Klier <Martin.Klier_at_klug-is.de>wrote:

> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l