Re: This is a resubmission on an earlier enquiry
Date: Tue, 16 Nov 2010 14:14:43 +0000 (UTC)
Message-ID: <pan.2010.11.16.14.14.42_at_gmail.com>
On Mon, 15 Nov 2010 20:18:48 -0800, Mick wrote:
> I welcome any suggestions on how I can improve the performance of this
> statement.
> Please note that I have not control of the SQL code because it is vendor
> generated.
So what made you post it here, if you don't have a control over it? Off hand, the first two things to do would be to replace "not in" with "not exist" and create a functional index on
OBJECTIVE.PHY_ALL_OBJECTS(upper(name))
That is the condition which requires resolution by the full table scan. Depending on the selectivity of the condition "date_delete is null", I would consider replacing NULL dates with 1.1.4001 or creating a functional index on NVL(date_delete, to_date('01/01/4001','MM/DD/YYYY')). The best "tuning method" for bad SQL is to rewrite it. If you can't do that, your options are limited. Very limited.
-- http://mgogala.byethost5.comReceived on Tue Nov 16 2010 - 08:14:43 CST