Re: Why is this query slow?

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 12 Mar 2010 22:22:01 +0100
Message-ID: <4B9AB079.1040707_at_gmail.com>



On 12.03.2010 21:54, Charles Hooper wrote:

> Plan hash value: 1245405710
>
> -------------------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-Rows
> | A-Time | Buffers | OMem | 1Mem | Used-Mem |
> -------------------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | | 0 |
> 00:00:00.01 | 12 | | | |
> |* 1 | FILTER | | 1 | | 0 |
> 00:00:00.01 | 12 | | | |
> |* 2 | HASH JOIN ANTI NA | | 1 | 2 | 0 |
> 00:00:00.01 | 12 | 1079K| 1079K| 553K (0)|
> |* 3 | TABLE ACCESS FULL| TRANSACTIONS | 1 | 3 | 3 |
> 00:00:00.01 | 7 | | | |
> |* 4 | TABLE ACCESS FULL| TRANSACTION | 1 | 3 | 3 |
> 00:00:00.01 | 5 | | | |
> -------------------------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 1 - filter(SYSDATE_at_!-1<=SYSDATE@!+1)
> 2 - access("ID"="ID")
> 3 - filter(("TX_DATE">=SYSDATE_at_!-1 AND "TX_DATE"<=SYSDATE@!+1 AND
> "STATUS"='AAA'))
> 4 - filter(("TX_DATE">=SYSDATE_at_!-1 AND "TX_DATE"<=SYSDATE@!+1 AND
> "STATUS"='AAA'))
>

> One item that stands out in the last of the above execution plans is
> the null aware hash join anti operation, which was introduced in
> Oracle 11.1.0.6. This makes should make a high performance increase
> when for large data sets that permit NULL values.
>

Excellent point, almost forgot it, thank you for reminder.

Best regards

Maxim Received on Fri Mar 12 2010 - 15:22:01 CST

Original text of this message