Re: How to calculate cardinality for inequality in WHERE

From: Tim Gorman <tim_at_evdbt.com>
Date: Thu, 11 Jul 2013 09:07:16 -0600
Message-ID: <51DECA24.5030209_at_evdbt.com>

Martin,
Not sure if you are permitted to edit the SQL, but what if the SQL was written instead as...

    select * from TABLE
    where (QTY1 - QTY2) < 0

...so that then a function-based index on the expression "qty1-qty2" would yield the intended result without histograms or extended statistics.

If you're interested, I did a test case to prove this, so I can forward the DDL to you if it helps, but here are the results...

    SQL> select * from test where qty1 < qty2;

    ...

    3 rows selected.

    Execution Plan

---

    Plan hash value: 217508114

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
    Time |

---

| 0 | SELECT STATEMENT | | 4 | 1516 | 65 (2)| 00:00:01 |
    |* 1 | TABLE ACCESS FULL| TEST | 4 | 1516 | 65 (2)| 00:00:01 |

---

...and then the rewritten query with the function-based index...

    SQL> select * from test where qty1 - qty2 < 0;

    ...

    3 rows selected.

    Execution Plan

---

    Plan hash value: 1399782417

---

| Id | Operation | Name | Rows | Bytes |
    Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | | 530 | 202K|
    6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 530 | 202K|
    6 (0)| 00:00:01 |
    |* 2 | INDEX RANGE SCAN | TEST_FBI01 | 95 | |     2 (0)| 00:00:01 |

---

Hope this helps...

-Tim

On 7/11/2013 7:27 AM, Martin Klier 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

Received on Thu Jul 11 2013 - 17:07:16 CEST