OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » optimize queries (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

optimize queries [message #334909]

Fri, 18 July 2008 10:59

acarella
Messages: 21
Registered: July 2008
Location: Latham, NY

Junior Member

Can anyone help me to rewrite/optimize these queries

Query #1
SELECT DISTINCT
v.F_PRODUCT,
(SELECT F_PRODUCT_NAME FROM T_PRODUCTS WHERE F_PRODUCT = v.F_PRODUCT) AS F_PRODUCT_NAME,
'' AS F_CAS_NUMBER,
'' AS F_COMPONENT_ID,
'' AS F_CHEM_NAME,
v.F_TYPE,
'' AS F_PERCENT,
'' AS F_REACH_FUCNTION,
(SELECT
CAST(SUM(F_QUANTITY) AS INT)
FROM
T_REACH_VOLUMES v1
WHERE
v1.F_LEO_ID = v.F_LEO_ID
AND v1.F_PRODUCT = v.F_PRODUCT
AND v1.F_TYPE = 1
AND F_START_DATE >= '01-Jan-2005'
AND F_STOP_DATE <= '31-Dec-2005' ) Y1IMP,
(SELECT
CAST(SUM(F_QUANTITY) AS INT)
FROM
T_REACH_VOLUMES v1
WHERE
v1.F_LEO_ID = v.F_LEO_ID
AND v1.F_PRODUCT = v.F_PRODUCT
AND v1.F_TYPE = 2
AND F_START_DATE >= '01-Jan-2005'
AND F_STOP_DATE <= '31-Dec-2005' ) Y1PROD,
(SELECT
CAST(SUM(F_QUANTITY) AS INT)
FROM
T_REACH_VOLUMES v1
WHERE
v1.F_LEO_ID = v.F_LEO_ID
AND v1.F_PRODUCT = v.F_PRODUCT
AND v1.F_TYPE = 1
AND F_START_DATE >= '01-Jan-2006'
AND F_STOP_DATE <= '31-Dec-2006' ) Y2IMP,
(SELECT
CAST(SUM(F_QUANTITY) AS INT)
FROM
T_REACH_VOLUMES v1
WHERE
v1.F_LEO_ID = v.F_LEO_ID
AND v1.F_PRODUCT = v.F_PRODUCT
AND v1.F_TYPE = 2
AND F_START_DATE >= '01-Jan-2006'
AND F_STOP_DATE <= '31-Dec-2006') Y2PROD,
(SELECT
CAST(SUM(F_QUANTITY) AS INT)
FROM
T_REACH_VOLUMES v1
WHERE
v1.F_LEO_ID = v.F_LEO_ID
AND v1.F_PRODUCT = v.F_PRODUCT
AND v1.F_TYPE = 1
AND F_START_DATE >= '01-Jan-2007'
AND F_STOP_DATE <= '31-Dec-2007') Y3IMP,
(SELECT
CAST(SUM(F_QUANTITY) AS INT)
FROM
T_REACH_VOLUMES v1, T_REACH_VENDORS vd
WHERE
v1.F_LEO_ID = v.F_LEO_ID
AND v1.F_PRODUCT = v.F_PRODUCT
AND v1.F_TYPE = 2
AND F_START_DATE >= '01-Jan-2007'
AND F_STOP_DATE <= '31-Dec-2007') Y3PROD , '' AVG3IMP, '' AVG3PROD, '' AVG3TOTAL
FROM
T_REACH_VOLUMES v, T_REACH_VENDORS vd
WHERE
F_LEO_ID = 1
AND v.F_VENDOR_ID = vd.F_VENDOR_ID

Query #2

SELECT v.F_PRODUCT,
p.F_PRODUCT_NAME,
'' AS F_CAS_NUMBER,
'' AS F_COMPONENT_ID,
'' AS F_CHEM_NAME,

v.F_TYPE,
'' AS F_PERCENT,
'' AS F_REACH_FUCNTION,

sum(decode(greatest(v.F_START_DATE,to_date('20050101','YYYYMMDD')),v.F_START_DATE,
decode(least(v.F_STOP_DATE,to_date('20051231','YYYYMMDD')),v.F_STOP_DATE,
decode(v.F_TYPE,1,v.f_quantity,0), 0), 0) ) y1imp_new,

sum(decode(greatest(v.F_START_DATE,to_date('20050101','YYYYMMDD')),v.F_START_DATE,
decode(least(v.F_STOP_DATE,to_date('20051231','YYYYMMDD')),v.F_STOP_DATE,
decode(v.F_TYPE,2,v.f_quantity,0),
0), 0) ) y1prod_new,

sum(decode(greatest(v.F_START_DATE,to_date('20060101','YYYYMMDD')),v.F_START_DATE,
decode(least(v.F_STOP_DATE,to_date('20061231','YYYYMMDD')),v.F_STOP_DATE,
decode(v.F_TYPE,1,v.f_quantity,0), 0), 0)
) y2imp_new,

sum(decode(greatest(v.F_START_DATE,to_date('20060101','YYYYMMDD')),v.F_START_DATE,
decode(least(v.F_STOP_DATE,to_date('20061231','YYYYMMDD')),v.F_STOP_DATE,
decode(v.F_TYPE,2,v.f_quantity,0), 0), 0) ) y2prod_new,

sum(decode(greatest(v.F_START_DATE,to_date('20070101','YYYYMMDD')),v.F_START_DATE,
decode(least(v.F_STOP_DATE,to_date('20071231','YYYYMMDD')),v.F_STOP_DATE,
decode(v.F_TYPE,1,v.f_quantity,0), 0), 0) ) y2imp_new,

sum(decode(greatest(v.F_START_DATE,to_date('20070101','YYYYMMDD')),v.F_START_DATE,
decode(least(v.F_STOP_DATE,to_date('20071231','YYYYMMDD')),v.F_STOP_DATE,
decode(v.F_TYPE,2,v.f_quantity,0), 0), 0)) y3prod_new,
'' AVG3IMP,
'' AVG3PROD,
'' AVG3TOTAL
FROM T_REACH_VOLUMES v, T_REACH_VENDORS vd, T_PRODUCTS p
WHERE v.F_LEO_ID = 1
AND v.F_VENDOR_ID = vd.F_VENDOR_ID
and p.F_PRODUCT = v.F_PRODUCT
group by v.F_PRODUCT,
p.F_PRODUCT_NAME,
v.F_TYPE

Query #3
SELECT DISTINCT v.F_PRODUCT,
(SELECT F_PRODUCT_NAME FROM T_PRODUCTS WHERE F_PRODUCT = v.F_PRODUCT) AS F_PRODUCT_NAME,
'' AS F_CAS_NUMBER, '' AS F_COMPONENT_ID, '' AS F_CHEM_NAME, v.F_TYPE, '' AS F_PERCENT,
'' AS F_REACH_FUCNTION,

(SELECT CAST(SUM(F_QUANTITY) AS INT) FROM T_REACH_VOLUMES v1,
T_REACH_VENDORS rvd
WHERE v1.F_LEO_ID = v.F_LEO_ID AND v1.F_VENDOR_ID = rvd.F_VENDOR_ID
AND rvd.F_IS_EU = 1
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '03')
AND v1.F_PRODUCT = v.F_PRODUCT AND v1.F_TYPE = 1 AND F_START_DATE >= ? AND F_STOP_DATE <= ? ) Y1IMP,

(SELECT CAST(SUM(F_QUANTITY) AS INT) FROM T_REACH_VOLUMES v1, T_REACH_VENDORS rvd
WHERE v1.F_LEO_ID = v.F_LEO_ID AND v1.F_VENDOR_ID = rvd.F_VENDOR_ID
AND rvd.F_IS_EU = 1 AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '03')
AND v1.F_PRODUCT = v.F_PRODUCT AND v1.F_TYPE = 2 AND F_START_DATE >= ? AND F_STOP_DATE <= ? ) Y1PROD,

(SELECT CAST(SUM(F_QUANTITY) AS INT) FROM T_REACH_VOLUMES v1, T_REACH_VENDORS rvd
WHERE v1.F_LEO_ID = v.F_LEO_ID
AND v1.F_VENDOR_ID = rvd.F_VENDOR_ID AND rvd.F_IS_EU = 1
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '03')
AND v1.F_PRODUCT = v.F_PRODUCT AND v1.F_TYPE = 1 AND F_START_DATE >= ? AND F_STOP_DATE <= ? ) Y2IMP,

(SELECT CAST(SUM(F_QUANTITY) AS INT) FROM T_REACH_VOLUMES v1, T_REACH_VENDORS rvd
WHERE v1.F_LEO_ID = v.F_LEO_ID AND v1.F_VENDOR_ID = rvd.F_VENDOR_ID AND rvd.F_IS_EU = 1
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '03')
AND v1.F_PRODUCT = v.F_PRODUCT AND v1.F_TYPE = 2 AND F_START_DATE >= ? AND F_STOP_DATE <= ?) Y2PROD,

(SELECT CAST(SUM(F_QUANTITY) AS INT) FROM T_REACH_VOLUMES v1, T_REACH_VENDORS rvd
WHERE v1.F_LEO_ID = v.F_LEO_ID AND v1.F_VENDOR_ID = rvd.F_VENDOR_ID AND rvd.F_IS_EU = 1
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '03')
AND v1.F_PRODUCT = v.F_PRODUCT AND v1.F_TYPE = 1 AND F_START_DATE >= ? AND F_STOP_DATE <= ?) Y3IMP,

(SELECT CAST(SUM(F_QUANTITY) AS INT) FROM T_REACH_VOLUMES v1, T_REACH_VENDORS rvd , T_REACH_VENDORS vd
WHERE v1.F_LEO_ID = v.F_LEO_ID AND v1.F_VENDOR_ID = rvd.F_VENDOR_ID AND rvd.F_IS_EU = 1
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '03')
AND v1.F_PRODUCT = v.F_PRODUCT AND v1.F_TYPE = 2 AND F_START_DATE >= ? AND F_STOP_DATE <= ?) Y3PROD ,

'' AVG3IMP, '' AVG3PROD, '' AVG3TOTAL
FROM T_REACH_VOLUMES v, T_REACH_VENDORS vd
WHERE F_LEO_ID = ? AND v.F_VENDOR_ID = vd.F_VENDOR_ID
AND v.F_VENDOR_ID = vd.F_VENDOR_ID
AND vd.F_IS_EU = 1 AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '03')

GROUP BY v.F_LEO_ID, v.F_TYPE, v.F_PRODUCT

Query #4
SELECT DISTINCT v.F_CAS_NUMBER,
(SELECT MIN(F_CHEM_NAME) FROM T_COMPONENTS WHERE F_CAS_NUMBER = v.F_CAS_NUMBER ) F_CHEM_NAME,

(SELECT SUM(v1.F_QUANTITY) FROM T_REACH_VOLUMES v1, T_REACH_FUNCTIONS f WHERE v1.F_LEO_ID = v.F_LEO_ID
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '01')
AND v1.F_CAS_NUMBER = v.F_CAS_NUMBER AND v1.F_TYPE = 1 AND v1.F_START_DATE >= ? AND v1.F_STOP_DATE <= ?
AND v1.F_REACH_FUNCTION = f.F_RECORD_ID AND f.F_REGISTER_IMP <> 0 ) Y1IMP,

(SELECT SUM(v1.F_QUANTITY) FROM T_REACH_VOLUMES v1, T_REACH_FUNCTIONS f WHERE v1.F_LEO_ID = v.F_LEO_ID
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '01')
AND v1.F_CAS_NUMBER = v.F_CAS_NUMBER AND v1.F_TYPE = 2 AND F_START_DATE >= ? AND F_STOP_DATE <= ?
AND v1.F_REACH_FUNCTION = f.F_RECORD_ID AND f.F_REGISTER_PROD <> 0 ) Y1PROD,

(SELECT SUM(v1.F_QUANTITY) FROM T_REACH_VOLUMES v1 , T_REACH_FUNCTIONS f WHERE v1.F_LEO_ID = v.F_LEO_ID
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '01')
AND v1.F_CAS_NUMBER = v.F_CAS_NUMBER AND v1.F_TYPE = 1 AND v1.F_START_DATE >= ? AND v1.F_STOP_DATE <= ?
AND v1.F_REACH_FUNCTION = f.F_RECORD_ID AND f.F_REGISTER_IMP <> 0 ) Y2IMP,

(SELECT SUM(F_QUANTITY) FROM T_REACH_VOLUMES v1 , T_REACH_FUNCTIONS f WHERE v1.F_LEO_ID = v.F_LEO_ID
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '01')
AND v1.F_CAS_NUMBER = v.F_CAS_NUMBER AND v1.F_TYPE = 2 AND v1.F_START_DATE >= ? AND v1.F_STOP_DATE <= ?
AND v1.F_REACH_FUNCTION = f.F_RECORD_ID AND f.F_REGISTER_PROD <> 0 ) Y2PROD,

(SELECT SUM(v1.F_QUANTITY) FROM T_REACH_VOLUMES v1, T_REACH_FUNCTIONS f WHERE v1.F_LEO_ID = v.F_LEO_ID AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '01') AND v1.F_CAS_NUMBER = v.F_CAS_NUMBER AND v1.F_TYPE = 1 AND v1.F_START_DATE >= ? AND v1.F_STOP_DATE <= ? AND v1.F_REACH_FUNCTION = f.F_RECORD_ID AND f.F_REGISTER_IMP <> 0 ) Y3IMP, (SELECT SUM(v1.F_QUANTITY) FROM T_REACH_VOLUMES v1, T_REACH_FUNCTIONS f WHERE v1.F_LEO_ID = v.F_LEO_ID AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '01') AND v1.F_CAS_NUMBER = v.F_CAS_NUMBER AND v1.F_TYPE = 2 AND v1.F_START_DATE >= ? AND v1.F_STOP_DATE <= ? AND v1.F_REACH_FUNCTION = f.F_RECORD_ID AND f.F_REGISTER_PROD <> 0 ) Y3PROD, '' AVG3IMP, '' AVG3PROD, '' AVG3TOTAL

FROM T_REACH_VOLUMES v, T_COMPONENTS c, T_REACH_VENDORS vd WHERE
F_LEO_ID = ? AND v.F_VENDOR_ID = vd.F_VENDOR_ID
AND v.F_CAS_NUMBER = c.F_CAS_NUMBER
AND v.F_VENDOR_ID = vd.F_VENDOR_ID
AND vd.F_IS_EU = 1
AND v.F_Product IN (SELECT F_PRODUCT FROM T_PROD_DATA WHERE f_Data_code = 'SUPINT' AND F_DATA = '01')
AND F_LOCATION_ID = ? GROUP BY v.F_LEO_ID, v.F_TYPE, v.F_CAS_NUMBER,v.F_Product

Where ‘MIN’(ChemName) is taken because of possibility to have many Component Names with the same CAS#

Thank you.
AC

Report message to a moderator

Re: optimize queries [message #334911 is a reply to message #334909]

Fri, 18 July 2008 11:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

[Updated on: Fri, 18 July 2008 11:02] by Moderator

Report message to a moderator

Re: optimize queries [message #334982 is a reply to message #334909]

Sat, 19 July 2008 01:50

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Many times, the solution to a better performing query is to re-write it into something else.

The DISTINCT should be your first clue that this query will give you problems. Distinct is a valid operation, but nine times out of ten, developers use it when they don't need it.

The second clue that there may be a problem is the t_Reach_Vendors vd table reference. You join to it, but then you never select anything from it or use it to control any of your scalar sub-selects. One must ask therefore, why is it included. Certainly it could be performing an existential check. But it might also simply be one extra join not needed. Indeed, it might even be adding to your "duplicate rows" problem if there is a one-to-many between volumns and vendors.

Lastly scalar sub-selects are handy, but not always efficient. Many developers use them as any easy way to write code. This is not bad, easier to understand code is a good thing, but scalar sub-selects are a finicky coding construct which can make your code faster, or slower depending upon what the alternative formulations are. Some good uses of a scalar sub-select can be to avoid otherwise doing an outerjoin, or avoid using a table function. None-the-less, it is prudent to evaluate a query's performance with them and without them in order to assess their cost. Then at least you are making an informed decision. Your query neither avoids outerjoin, nor avoids table functions, by use of its sub-selects; thus they are immediately suspect as to their value.

So, one should try to re-write your query without distinct, and without scalar sub-selects, and without unnecessary joins (if they exist).

Consider this alternative formulation of your query. It I think does the same thing but much more efficiently. You need to think about how the rows in your tables will be accessed. Try to understand how this query works in terms of how it accesses rows. Then do the same for your query. You will grasp why this might be much better. It only passes the data once. Yours passes the data at least twice.

with
     some_dates (
                  select to_date('01-Jan-2005','dd-mon-rrrr') start_date 
                        ,to_date('31-dec-2005 23:59:59','dd-mon-rrrr hh24:mi:ss') end_date
                  from dual union all
                  select to_date('01-Jan-2006','dd-mon-rrrr') start_date
                        ,to_date('31-dec-2006 23:59:59','dd-mon-rrrr hh24:mi:ss') end_date
                  from dual union all
                  select to_date('01-Jan-2007','dd-mon-rrrr') start_date
                        ,to_date('31-dec-2007 23:59:59','dd-mon-rrrr hh24:mi:ss') end_date
                  from dual
                )
   , product_type_sum as (
                           select v.f_product,t.f_product_name,v.f_type,sd.start_date,sd.end_date,sum(v.f_quantity) f_quantity
                           from t_Reach_Volumes v
                               ,some_dates sd
                               ,t_products t
                           where v.f_type in (1,2)
                           and v.f_start_date >= sd.start_date
                           and v.f_stop_date <= sd.end_date
                           and v.f_product = t.f_product
                           group by v.f_product,t.f_product_name,v.f_type,sd.start_date,sd.end_date
                         )
select *
from product_type_sum
/

Or, for those not yet familiar with the WITH CLAUSE:

select v.f_product,t.f_product_name,v.f_type,sd.start_date,sd.end_date,sum(v.f_quantity) f_quantity
from t_Reach_Volumes v
    ,(
       select to_date('01-Jan-2005','dd-mon-rrrr') start_date 
             ,to_date('31-dec-2005 23:59:59','dd-mon-rrrr hh24:mi:ss') end_date
       from dual union all
       select to_date('01-Jan-2006','dd-mon-rrrr') start_date
             ,to_date('31-dec-2006 23:59:59','dd-mon-rrrr hh24:mi:ss') end_date
       from dual union all
       select to_date('01-Jan-2007','dd-mon-rrrr') start_date
             ,to_date('31-dec-2007 23:59:59','dd-mon-rrrr hh24:mi:ss') end_date
       from dual
     ) sd
where v.f_type in (1,2)
and v.f_start_date >= sd.start_date
and v.f_stop_date <= sd.end_date
group by v.f_product,t.f_product_name,v.f_type,sd.start_date,sd.end_date
/

I have not tested any of this code (how can I, I do not have your table scripts), so there are likely several syntax errors, but you can find them and make this code run. You should do this to validate if this query alternative(s) is actually sematically equivelant to your original.

Good luck, Kevin

Report message to a moderator

Re: optimize queries [message #335204 is a reply to message #334982]

Mon, 21 July 2008 07:30

acarella
Messages: 21
Registered: July 2008
Location: Latham, NY

Junior Member

thank you so much Kevin.
I am not familiar with the WITH CLAUSE (as I am just learning -- taking courses vs. actual hands on is a biggey). I will learn this. Thank you.

Arlene

Report message to a moderator

Re: optimize queries [message #335535 is a reply to message #334909]

Tue, 22 July 2008 08:30

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

query #1:

look at in-line select:

(SELECT 
CAST(SUM(F_QUANTITY) AS INT)
FROM
T_REACH_VOLUMES v1, T_REACH_VENDORS vd
WHERE 
v1.F_LEO_ID = v.F_LEO_ID
AND v1.F_PRODUCT = v.F_PRODUCT 
AND v1.F_TYPE = 2 
AND F_START_DATE >= '01-Jan-2007'
AND F_STOP_DATE <= '31-Dec-2007') Y3PROD

I don't see any JOIN conditions to T_REACH_VENDORS vd table. Do you?

HTH.
Michael

Report message to a moderator

Previous Topic:	How many times Queries/DML statement run over a week.
Next Topic:	Better way to handle this query?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 11:47:22 CDT 2024