Home » SQL & PL/SQL » SQL & PL/SQL » query performance
query performance [message #220766] Wed, 21 February 2007 23:55 Go to next message
oracle_coorgi
Messages: 188
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
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
where
p.FK_OU_ID_REGION=v.pk_ou_id

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

Hi,

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

its almost stopped

Not clear to me. please clarify more.


regards
Taj
Re: query performance [message #220769 is a reply to message #220766] Thu, 22 February 2007 00:23 Go to previous message
flyboy
Messages: 1903
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: INSERT BLOB IN ORACLE 10G
Goto Forum:
  


Current Time: Thu Mar 28 19:56:28 CDT 2024