Home » SQL & PL/SQL » SQL & PL/SQL » query performance
query performance [message #220766] Wed, 21 February 2007 23:55 Go to next message
Messages: 188
Registered: September 2006
Location: INDIA-karnataka
Senior Member
i have a query
select DISTINCT p.PK_proj_id,v.pk_ou_id,v.name,v.entity_type
from proj p,VIEW_M_BUSINESS_ENTITY v,proj_alt pa,proj_alt_cf PC

vw...... table has = 687 records
proj table has = 671 records
proj_alt has 1314 records
proj_alt_cf has 289873 records
total 292545 records

query is very slow its almost stopped
any advice

Re: query performance [message #220767 is a reply to message #220766] Thu, 22 February 2007 00:03 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member


query is very slow
what is explain plan for this query ?

its almost stopped

Not clear to me. please clarify more.

Re: query performance [message #220769 is a reply to message #220766] Thu, 22 February 2007 00:23 Go to previous message
Messages: 1848
Registered: November 2006
Senior Member
Although you select from four tables, you join only two of them. It results in a cartesian product of (VIEW + proj), proj_alt and proj_alt_cf so it contains 671*1314*289873 rows.
Add join conditions for proj_alt and proj_alt_cf tables.
If you do not suppose to use any column from them later (as you do not use it now), remove them from the FROM clause.
Previous Topic: sql help
Next Topic: difference between Performance Tuning & Optimization
Goto Forum:

Current Time: Wed Aug 23 07:04:53 CDT 2017

Total time taken to generate the page: 0.01955 seconds