Re: Why is this query slow?

From: Lothar Armbrüster <lothar.armbruester_at_t-online.de>
Date: Fri, 12 Mar 2010 17:40:20 +0100
Message-ID: <874oklo5mz.fsf_at_prometeus.nothing.none>

vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com> writes:

[...]
>
> If I save results of the subquery in a temporary table then such
> modified query
> completes in less than a second:
>
> select * from transactions
> where tx_date between (date1 and date2) and
> status = 'AAA'
> ID not in
> (select ID from temp_table)
>
> Surely Oracle should be able to hold 2,500 records from the subquery
> in
> memory and access it more efficiently than my temporary table? But it
> doesn't do it.
>

One way to speed up NOT IN queries is the HASH_AJ hint. The query would look like the following:

select * from transactions
where

   tx_date between (date1 and date2) and    status='AAA' and
   id is not null and
   id not in (select /*+ HASH_AJ */

                 id
              from
                 temp_table
              where
                 id is not null)

Maybe you can leave out one or even both of the not null restrictions. Have a look at the execution plan if a hash anti join is used. I think there is also an initialization parameter to automatically enable hash anti joins.

> I can re-write the query using MINUS and it will run much faster. I am
> just puzzled why is it so slow.
>
> This is plan from AUTOTRACE (9.2.0.8 on AIX):
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=45545 Card=81
> Bytes=3888)
> 1 0 FILTER
> 2 1 FILTER
> 3 2 ABLE ACCESS (BY INDEX ROWID) OF
> 'TRANSACTIONS' (Cost=1076 Card=81 Bytes=3888)
> 4 3 NDEX (RANGE SCAN) OF 'TRANSACTIONS_IND01' (NON-
> UNIQUE) (Cost=821 Card=105815)
> 5 1 FILTER
> 6 5 TABLE ACCESS (BY INDEX ROWID) OF
> 'TRANSACTIONS' (Cost=549 Card=4 Bytes=192)
> 7 6 INDEX (RANGE SCAN) OF 'TRANSACTIONS_IND01' (NON-
> UNIQUE) (Cost=419Card=53853)
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 2326009 consistent gets
> 0 physical reads
> 0 redo size
> 7609 bytes sent via SQL*Net to client
> 832 bytes received via SQL*Net from client
> 18 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 253 rows processed
>
>
>

HTH,
Lothar

-- 
Lothar Armbrüster  | lothar.armbruester_at_t-online.de
Hauptstr. 26       |
65346 Eltville     |

Received on Fri Mar 12 2010 - 10:40:20 CST