asked on

Oracle Join Query (Slow Response.....)

Hi,

Enclosed Join Query takes longer execution time in our Client Server VB/ADO/Oracle 9i application. Please suggest how we can improve execution times.

Currently it takes about 70 - 80 Seconds and this is unacceptable in Production environment.

******************* QUERY *********************************************

SELECT DISTINCT S.product_id, S.prod_yr, S.prod_issue, D.short_name, I.start_date, V.ebd
FROM rates_dir_info D, rates_ver_specific V, rates_prod_scheme S, directory_issue I
WHERE D.fk_product_code = s.product_id
AND D.fk_product_code = V.fk_product_code
AND V.access_dir_code = I.DIRECTORY_CODE
AND (V.access_issue_num = I.dir_issue_num OR (V.access_issue_num IS NULL))
AND S.PROD_YR= '2004'
AND TRIM(V.fk_product_code) = TRIM(S.product_id)
AND V.product_year = S.prod_yr
AND V.product_issue = S.prod_issue
AND (D.delete_flag IS NULL OR D.delete_flag = '')
AND (V.delete_flag IS NULL OR V.delete_flag = '')
AND (S.delete_flag IS NULL OR S.delete_flag = '')
ORDER BY S.product_id;

******************* QUERY *********************************************

Tables Used
rates_prod_scheme ( 4.5 Million Records)
PK Columns ( product_id, prod_yr, prod_issue, udac_7 )

rates_dir_info ( 1190 records)
PK Columns ( fk_product_code)

directory_issue (7727 records)
PK Columns ( directory_code, dir_issue_num )

rates_ver_specific (10597)
PK Columns ( fk_product_code, product_year, access_issue_num, product_issue )

Regards
Pramod

ASKER CERTIFIED SOLUTION

seazodiac

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

andrewst

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

geotiger

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ora_Techie

And please also post execution plan of query.

pramodsrao

ASKER

Hello everybody,

First of all thank you all for wonderful suggestions. Its working and has improved the response time from 4 mins to 2 mins in Production DB.

I have made the following changes.

1) Removed TRIM across the whole Query
2) Modified statments
AND (D.delete_flag IS NULL OR D.delete_flag = '')
AND (V.delete_flag IS NULL OR V.delete_flag = '')
AND (S.delete_flag IS NULL OR S.delete_flag = '')
to
AND (D.delete_flag IS NULL OR D.delete_flag = '')
AND (V.delete_flag IS NULL OR V.delete_flag = '')
AND (S.delete_flag IS NULL OR S.delete_flag = '')

AND D.delete_flag IS NULL
AND V.delete_flag IS NULL
AND S.delete_flag IS NULL

3) All DBA related suggestions have been passed on to our DB team and I haven't got any response from them. I shall also discuss with them following DBA related suggestions as you all have described.

a) Run Update Statistics
b) I am not sure about WORKAREA_SIZE_POLICY and PGA_AGGREGATE_SIZE in the init.ora
b) Partition rates_prod_scheme table

4) PROD_YR is defined as CHAR(4) in DB Schema and not Numeric.

Regards
Pramod

Ora_Techie

from ur changes

AND (D.delete_flag IS NULL OR D.delete_flag = '')
AND (V.delete_flag IS NULL OR V.delete_flag = '')
AND (S.delete_flag IS NULL OR S.delete_flag = '')

AND D.delete_flag IS NULL
AND V.delete_flag IS NULL
AND S.delete_flag IS NULL

I will STRONGLY OPPOSE THIS APPROACH. You are telling optimizer:

give me the rows where d.delete_flag is null
and on the other hand,
you are asking for rows where d.delete_flag is null OR d.delete_flag='' (contains some value implies not null)

So please please please elaborate your requirement first. You should either use:

AND D.delete_flag IS NULL
AND V.delete_flag IS NULL
AND S.delete_flag IS NULL

OR

AND trim(D.delete_flag) IS NULL (logically equivalent to d.delete_flag=null or d.delete_flag='')
AND trim(V.delete_flag) IS NULL
AND trim(S.delete_flag) IS NULL

PLEASE DON'T USE TWO CONTRADICTORY STATEMENTS TOGETHER (I WILL STOP SHOUTING NOW).

SOLUTION

Ora_Techie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ora_Techie

Also do (always) post the explain plan for your queries...it will help a lot in understanding how the query is executed.