Re: fix to heavy disk-read query?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 29 Jul 1999 15:52:50 +0100
Message-ID: <933260373.3607.0.nnrp-10.9e984b29@news.demon.co.uk>

It's always a bit tricky and long-winded doing this sort of thing by remote control: a couple of hours attached to the database is worth a couple of days of chatting back and fore, but:

As everyone 'knows' an EXISTS subquery is always much better than an IN subquery - except for the cases when its much worse ! (I'll be writing about that in my MYTHS page eventually).

Your REQUIREMENT looks as though it should drive off the ORDER_ITEM tables for a given account_id to determine all orders that have items against that account.

Your CODE looks as if it going to scan every single order, connect to ORDER_ITEMS, and then BALANCE twice before doing a correlated subquery to see if the order was one of the required orders.

Since subqueries are left to the end of the query by the optimiser, the existence test will be very expensive and a lot of redundant work will be done before you get there.

Obviously I would check the execution plan first to see if my guess was right, and check the stats of (typically) how many order items exist for each account but two possibly options:

Introduce the PUSH_SUBQ hint to tell Oracle to exercise the subquery at the earliest possible opportunity.
(which may be the best option, depending on data distribution stats). Change the subquery to an IN subquery,

IN (SELECT distinct -- actually redundant, since IN does a sort unique

            order_id
        FROM ORDER_ITEM
        WHERE ACCOUNT_ID = :1

)

I assume that there is an index on order_item.account_id, but even if there isn't, the IN subquery (if it becomes the driver) may be much quicker anyway.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

David Spaisman wrote in message <379FDDE6.6DD3E75_at_intercall.net>...
>Jonathan:
>
>You right on the mark. The query I originally posted did not have another
>balance join. Here is the correct full query.
>
>All of the where clause columns have either a clustered(primary key)index
or a
>unique index with the exception of the three'type' columns(which only have
very
>limited selectivity). Any suggestions how I can rewrite to avoid the disk
>joins?
Received on Thu Jul 29 1999 - 09:52:50 CDT