Oracle Performance Tuning Question

Hello,

I have an Oracle optimization question. I have a table which contains time-sequenced data from which I would like to extract the latest information from a set of information sources. Below are the two tables in the query, though only FD_BROKER_ESTIMATE is needed to demonstrate the behavior:

describe FD_BROKER_ESTIMATE;

Column Name                    Null?    Type


------------------------------ -------- ----


RECSEQ                         NOT NULL NUMBER(16)
VENDOR_ID                      NOT NULL NUMBER(4)
SYMBOL                         NOT NULL VARCHAR2(9)
BROKER_CODE                    NOT NULL VARCHAR2(2)
FISCAL_YEAR                    NOT NULL NUMBER(4)
FISCAL_YEAR_END_MONTH          NOT NULL NUMBER(2)
CURR_EST_DATE                  NOT NULL DATE
PREV_EST_DATE                           DATE
CURR_ANNUAL_ACT_FLAG                    CHAR(1)
CURR_ANNUAL_EST                         NUMBER(9,2)
CURR_Q1                                 NUMBER(9,2)
CURR_Q1_ACT_FLAG                        CHAR(1)
CURR_Q2                                 NUMBER(9,2)
CURR_Q2_ACT_FLAG                        CHAR(1)
CURR_Q3                                 NUMBER(9,2)
CURR_Q3_ACT_FLAG                        CHAR(1)
CURR_Q4                                 NUMBER(9,2)
CURR_Q4_ACT_FLAG                        CHAR(1)
PREV_ANNUAL_EST                         NUMBER(9,2)
PREV_Q1                                 NUMBER(9,2)
PREV_Q2                                 NUMBER(9,2)
PREV_Q3                                 NUMBER(9,2)
PREV_Q4                                 NUMBER(9,2)

describe LU_FC_BROKER_EARNINGS;
Column Name                    Null?    Type


------------------------------ -------- ----


BROKER_ID                      NOT NULL NUMBER(4)
BROKER_CODE                    NOT NULL VARCHAR2(2)
BROKER_NAME                    NOT NULL VARCHAR2(40)
VENDOR_ID                      NOT NULL NUMBER(4)

Below is the query as originally formulated:

select V0.CURR_Q1, V0.CURR_Q2, V0.CURR_Q3, V0.CURR_Q4, V0.BROKER_CODE, V0.SYMBOL, V0.FISCAL_YEAR, max(V0.CURR_EST_DATE), V1.BROKER_NAME from FD_BROKER_ESTIMATE V0, LU_FC_BROKER_EARNINGS V1 where V0.CURR_Q2 is not null and V0.CURR_Q3 is not null and V0.CURR_Q4 is not null and V0.SYMBOL in ('BAY', 'CARY', 'CBTSY', 'CCL', 'CCOW') and V0.VENDOR_ID = V1.VENDOR_ID and V0.BROKER_CODE = V1.BROKER_CODE group by

V0.CURR_Q1, V0.CURR_Q2, V0.CURR_Q3, V0.CURR_Q4, V0.BROKER_CODE,
V0.SYMBOL, V0.FISCAL_YEAR, V1.BROKER_NAME order by V0.SYMBOL,
V0.FISCAL_YEAR, V1.BROKER_NAME;

On a relatively small data set on my local copy of Oracle, this takes roughly 13 seconds to return the correct data set. For non-trivial data sets, query performance will be completely unaacceptable. However, if I recode the query as a union of five queries, each for a single symbol, as below:

select V0.CURR_Q1, V0.CURR_Q2, V0.CURR_Q3, V0.CURR_Q4, V0.BROKER_CODE, V0.SYMBOL, V0.FISCAL_YEAR, max(V0.CURR_EST_DATE), V1.BROKER_NAME from FD_BROKER_ESTIMATE V0, LU_FC_BROKER_EARNINGS V1 where V0.CURR_Q2 is not null and V0.CURR_Q3 is not null and V0.CURR_Q4 is not null and V0.SYMBOL = 'BAY' and V0.VENDOR_ID = V1.VENDOR_ID and V0.BROKER_CODE = V1.BROKER_CODE group by V0.CURR_Q1, V0.CURR_Q2, V0.CURR_Q3, V0.CURR_Q4, V0.BROKER_CODE, V0.SYMBOL, V0.FISCAL_YEAR, V1.BROKER_NAME union
select V0.CURR_Q1, V0.CURR_Q2, V0.CURR_Q3, V0.CURR_Q4, V0.BROKER_CODE, V0.SYMBOL, V0.FISCAL_YEAR, max(V0.CURR_EST_DATE), V1.BROKER_NAME from FD_BROKER_ESTIMATE V0, LU_FC_BROKER_EARNINGS V1 where V0.CURR_Q2 is not null and V0.CURR_Q3 is not null and V0.CURR_Q4 is not null and V0.SYMBOL = 'CBTSY' and V0.VENDOR_ID = V1.VENDOR_ID and V0.BROKER_CODE = V1.BROKER_CODE group by V0.CURR_Q1, V0.CURR_Q2, V0.CURR_Q3, V0.CURR_Q4, V0.BROKER_CODE, V0.SYMBOL, V0.FISCAL_YEAR, V1.BROKER_NAME union
select V0.CURR_Q1, V0.CURR_Q2, V0.CURR_Q3, V0.CURR_Q4, V0.BROKER_CODE, V0.SYMBOL, V0.FISCAL_YEAR, max(V0.CURR_EST_DATE), V1.BROKER_NAME from FD_BROKER_ESTIMATE V0, LU_FC_BROKER_EARNINGS V1 where V0.CURR_Q2 is not null and V0.CURR_Q3 is not null and V0.CURR_Q4 is not null and V0.SYMBOL = 'CCL' and V0.VENDOR_ID = V1.VENDOR_ID and V0.BROKER_CODE = V1.BROKER_CODE group by V0.CURR_Q1, V0.CURR_Q2, V0.CURR_Q3, V0.CURR_Q4, V0.BROKER_CODE, V0.SYMBOL, V0.FISCAL_YEAR, V1.BROKER_NAME union
select V0.CURR_Q1, V0.CURR_Q2, V0.CURR_Q3, V0.CURR_Q4, V0.BROKER_CODE, V0.SYMBOL, V0.FISCAL_YEAR, max(V0.CURR_EST_DATE), V1.BROKER_NAME from FD_BROKER_ESTIMATE V0, LU_FC_BROKER_EARNINGS V1 where V0.CURR_Q2 is not null and V0.CURR_Q3 is not null and V0.CURR_Q4 is not null and V0.SYMBOL = 'CCOW' and V0.VENDOR_ID = V1.VENDOR_ID and V0.BROKER_CODE = V1.BROKER_CODE group by V0.CURR_Q1, V0.CURR_Q2, V0.CURR_Q3, V0.CURR_Q4, V0.BROKER_CODE, V0.SYMBOL, V0.FISCAL_YEAR, V1.BROKER_NAME union
select V0.CURR_Q1, V0.CURR_Q2, V0.CURR_Q3, V0.CURR_Q4, V0.BROKER_CODE, V0.SYMBOL, V0.FISCAL_YEAR, max(V0.CURR_EST_DATE), V1.BROKER_NAME from FD_BROKER_ESTIMATE V0, LU_FC_BROKER_EARNINGS V1 where V0.CURR_Q2 is not null and V0.CURR_Q3 is not null and V0.CURR_Q4 is not null and V0.SYMBOL = 'CARY' and V0.VENDOR_ID = V1.VENDOR_ID and V0.BROKER_CODE = V1.BROKER_CODE group by V0.CURR_Q1, V0.CURR_Q2, V0.CURR_Q3, V0.CURR_Q4, V0.BROKER_CODE, V0.SYMBOL, V0.FISCAL_YEAR, V1.BROKER_NAME order by SYMBOL,
FISCAL_YEAR; the query comes back in roughly one second, perhaps less. Performance is quite good even with good sized data sets on a good-sized Sun server. Can anyone explain why there is over a factor of ten difference in performance between the two queries?

Please respond via email as I do not read this newsgroup regularly.

Sincerely,
Mark W. Eaton
Senior Engineering Manager, Application Server Development Harvest Technology, Inc.
164 Townsend
Suite 2
San Francisco, CA, 94107
415.908.6804 (phone)
415.777.4456 (FAX)
eaton_at_harvestinc.com

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Sep 09 1998 - 12:21:34 CDT