Re: How to calculate cardinality for inequality in WHERE
From: Rajiv Iyer <raju.rgi_at_gmail.com>
Date: Thu, 11 Jul 2013 21:15:33 +0530
Message-ID: <CADxvSwPVTbYZ-4GdJj2VZ6W_583N3CJye-cUwkYcfGEue-OdDA_at_mail.gmail.com>
|* 1 | TABLE ACCESS FULL| TEST_TAB | 750K| 10M| 1210 (3)| 00:00:15 |
|* 1 | TABLE ACCESS FULL| TEST_TAB | 39 | 585 | 1210 (3)| 00:00:15 |
> DYNAMIC_SAMPLING will give a much better estimate.
> 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:
Date: Thu, 11 Jul 2013 21:15:33 +0530
Message-ID: <CADxvSwPVTbYZ-4GdJj2VZ6W_583N3CJye-cUwkYcfGEue-OdDA_at_mail.gmail.com>
- 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);
- plan shows 50% estimate of the total rows
explain plan for
select * from TEST_TAB where QTY1 < QTY2;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 750K| 10M| 1210 (3)|00:00:15 |
|* 1 | TABLE ACCESS FULL| TEST_TAB | 750K| 10M| 1210 (3)| 00:00:15 |
----Dynamic sampling gives better estimate
explain plan for
select /*+ dynamic_sampling(TEST_TAB 2) */ * from TEST_TAB
where QTY1 < QTY2;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 39 | 585 | 1210 (3)|00:00:15 |
|* 1 | TABLE ACCESS FULL| TEST_TAB | 39 | 585 | 1210 (3)| 00:00:15 |
On Thu, Jul 11, 2013 at 9:10 PM, Rajiv Iyer <raju.rgi_at_gmail.com> wrote:
> Hi Martin.
>
> DYNAMIC_SAMPLING will give a much better estimate.
>
> 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-lReceived on Thu Jul 11 2013 - 17:45:33 CEST