Optimization Help

[ulicki]

Hi have the following two queries.
QUERY 1
SELECT
count(*)
FROM
OUTST_VIEW outst_view,
CUS_WEIGHTED_FAIRVALUE CUS_WEIGHTED_FAIRVALUE
WHERE
outst_view.NUF_GRANT_NUMBER = CUS_WEIGHTED_FAIRVALUE.FK_GRANT_NUMBER(+)
ORDER BY
outst_view.GRANT_TYPE_BASE ASC,
outst_view.OPTION_PRICE ASC,
outst_view.GRANT_DATE ASC,
CUS_WEIGHTED_FAIRVALUE.WEIGHTED_FAIR_VALUE ASC,
outst_view.GRANT_TYPE ASC

QUERY 2
SELECT
COUNT(*)
FROM
OUTST_VIEW outst_view,
CUS_WEIGHTED_FAIRVALUE CUS_WEIGHTED_FAIRVALUE
WHERE
outst_view.NUF_GRANT_NUMBER = CUS_WEIGHTED_FAIRVALUE.FK_GRANT_NUMBER(+) AND
outst_view.NUF_SAR_TYPE_IF_ALLOWED <> 2 AND
outst_view.NUF_GRANT_TYPE <> 2
ORDER BY
outst_view.GRANT_TYPE_BASE ASC,
outst_view.OPTION_PRICE ASC,
outst_view.GRANT_DATE ASC,
CUS_WEIGHTED_FAIRVALUE.WEIGHTED_FAIR_VALUE ASC,
outst_view.GRANT_TYPE ASC

I am using the count function because I have over 100,000 records in my output.

Both objects in the from clause are views. The OUTST_VIEW selects data from many tables and the US_WEIGHTED_FAIRVALUE view selects data from one table. The outst_view.NUF_GRANT_NUMBER field is indexed in its underlying table and is the primary key of that table. It is also unique in the view. The CUS_WEIGHTED_FAIRVALUE.FK_GRANT_NUMBER field is indexed in its underlying table but is not the primary key.

So when I run both queries in SQL Worksheet the first query runs in about 15 seconds. However I give up on the second query after about two minutes. BTW, in my actual application I am using Crystal Reports, which sends these
queries to the database (COUNT(*) is replaced by the fields used in the report). I really want to stick with the second query.

I thought is was the left outer join but when I run the second query without it, same problem.

Why is this happening?

I hope I have explained my problem correctly.

 

[rrrkrishnan]

Hi Ulicki

In the first query, I guess the optimizer is doing a full table scan on CUS_WEIGHTED_FAIRVALUE and gets the matching records from OUTST_VIEW.
In the second query, the optimzer does full table (view) scan on OUTST_VIEW and applies the inequality conditions and then gets the matching rows from CUS_WEIGHTED_FAIRVALUE. Now though there is an index on CUS_WEIGHTED_FAIRVALUE.FK_GRANT_NUMBER, this query might be slow because of the no. of rows involved in the OUTST view. If there are less rows in CUS_WEIGHTED_FAIRVALUE, try to force a full table scan on the underlying table of CUS_WEIGHTED_FAIRVALUE view using HINTS. Collecting statistics will also help if the optimizer is cost-based.

Hope this helps.

 

[jimbopalmer]

AND
    outst_view.NUF_SAR_TYPE_IF_ALLOWED <> 2 AND
    outst_view.NUF_GRANT_TYPE <> 2
if either of these are indexed, you could be doing a really rotton index, we can trick theminto not being indexed by

AND
    outst_view.NUF_SAR_TYPE_IF_ALLOWED+0 <> 2 AND
    outst_view.NUF_GRANT_TYPE+0 <> 2
I tried to remain child-like, all I acheived was childish.

 

[ulicki]

Thanks for the feedback. One of my hurdles is that Crystal Reports generates my queries. And it was Crystal Reports that put the other two lines of my where clause in the query automatically. However I used Jimbopalmer's + 0 trick in my code that selects records and this removed it from the query that gets sent to ORACLE; so now Crystal Reports will filter out the records. I can definitely accept the performance hit for the extra rows being returned to my report.

Thanks so much again!!!!!