Re: Performance issue on Oracle 9i

It seems a little odd that you're allowed to post the SQL but not the execution plan - it's usually the other way round!

You didn't answer many of the questions - in particular the one about the index definition you used. It's not possible for an index just on (send_date) to be used for the predicate "send_date is null", unless it's a bitmap index, which might be okay for a test system but may cause problems in a production system with concurrent small updates to the column.

You've said that you have about 388K rows in the table and 29K of them match the subquery - so that part of the operation is not going to be particularly quick (bear in mind that if the optimizer unnests the subquery it will also be including a "select distinct" on that 29K rows) . How many rows are returned by the whole query - does the client code want all of them, or does it just process the first few in any one call to the query?

There are basically two execution plans: a) use the main query block to identify rows where the send_date is null, then execute the subquery as a correlated "not exists", b) use the subquery to generate a list of unique tx_ref values that become the build (i.e. first) table in a hash anti-join, then find all the rows in the table where send_date is null and probe the build table.

Option (b) is likely to be the better strategy if your client wants all the rows, option (a) MAY be the better query if the client processes a few rows at a time and isn't worried about the order.

For the "not like" predicate, an alternative formulation that doesn't require you to derive one of the values for the two-part comparison is:

   substr(t.exp_cmnt,1,2) > 'PR'
or substr(t.exp_cmnt,1,2) < 'PR'

Regards
Jonathan Lewis

>
>

--
http://www.freelists.org/webpage/oracle-l