query optimizing issues

[parijat67]

Hi ,

I am giving below a single query which is pretty big in size and what all it does is returns not more than 1 record at a time.

PLS REFER THE QUERY TO UNDERSTAND THE FOLLOWING LINES:-

The Properties_objects table has only one record against the objectid = 0 and objecttypeid = 10214 AND propertyid = 548. As such PROPOBJ has only one record in it at any point of time whereas RS1,RS2,RS3....RS10 can have multiple records in them.

THE PROBLEM FOLLOWS:-

The query mentioned below is fully functional and optimized and gives the result within less than a second. But if we change the where statement of the given query and make it like this :

WHERE propobj.valueobjectid = rs1.objectid
AND propobj.valueobjectid = rs2.objectid
AND propobj.valueobjectid = rs3.objectid
AND propobj.valueobjectid = rs4.objectid
AND propobj.valueobjectid = rs5.objectid
AND propobj.valueobjectid = rs6.objectid
AND propobj.valueobjectid = rs7.objectid
AND propobj.valueobjectid = rs8.objectid
AND propobj.valueobjectid = rs9.objectid
AND propobj.valueobjectid = rs10.objectid

then the query takes more than half an hours time to execute. Why it's happening like that?? Could anyone pls explain...

thanks a lot!

Parijat
-----------------the query follows--------------------------

(SELECT objectid, valueobjectid,
securitymatchcodeid5, securitymatchcodeid4,
securitymatchcodeid3, securitymatchcodeid2,
securitymatchcodeid1, holdingsmatchcodeid5,
holdingsmatchcodeid4, holdingsmatchcodeid3,
holdingsmatchcodeid2, holdingsmatchcodeid1
FROM
(SELECT propobj.objectid , propobj.valueobjectid ,
rs1.securitymatchcodeid5 ,rs2.securitymatchcodeid4 ,
rs3.securitymatchcodeid3 , rs4.securitymatchcodeid2 ,
rs5.securitymatchcodeid1 , rs6.holdingsmatchcodeid5 ,
rs7.holdingsmatchcodeid4 , rs8.holdingsmatchcodeid3 ,
rs9.holdingsmatchcodeid2 , rs10.holdingsmatchcodeid1
FROM (SELECT valueobjectid, SEQUENCE objectid
FROM properties_objects
WHERE objecttypeid = 10214
AND objectid = 0
AND propertyid = 548) propobj,
(SELECT valueobjecttypeid,
valueobjectid securitymatchcodeid5, objectid
FROM properties_objects
WHERE objecttypeid = 45014
AND propertyid = 520) rs1,
(SELECT valueobjecttypeid,
valueobjectid securitymatchcodeid4, objectid
FROM properties_objects
WHERE objecttypeid = 45014
AND propertyid = 519) rs2,
(SELECT valueobjecttypeid,
valueobjectid securitymatchcodeid3, objectid
FROM properties_objects
WHERE objecttypeid = 45014
AND propertyid = 518) rs3,
(SELECT valueobjecttypeid,
valueobjectid securitymatchcodeid2, objectid
FROM properties_objects
WHERE objecttypeid = 45014
AND propertyid = 517) rs4,
(SELECT valueobjecttypeid,
valueobjectid securitymatchcodeid1, objectid
FROM properties_objects
WHERE objecttypeid = 45014
AND propertyid = 516) rs5,
(SELECT valueobjecttypeid,
valueobjectid holdingsmatchcodeid5, objectid
FROM properties_objects
WHERE objecttypeid = 45014
AND propertyid = 525) rs6,
(SELECT valueobjecttypeid,
valueobjectid holdingsmatchcodeid4, objectid
FROM properties_objects
WHERE objecttypeid = 45014
AND propertyid = 524) rs7,
(SELECT valueobjecttypeid,
valueobjectid holdingsmatchcodeid3, objectid
FROM properties_objects
WHERE objecttypeid = 45014
AND propertyid = 523) rs8,
(SELECT valueobjecttypeid,
valueobjectid holdingsmatchcodeid2, objectid
FROM properties_objects
WHERE objecttypeid = 45014
AND propertyid = 522) rs9,
(SELECT valueobjecttypeid,
valueobjectid holdingsmatchcodeid1, objectid
FROM properties_objects
WHERE objecttypeid = 45014
AND propertyid = 521) rs10
WHERE propobj.valueobjectid = rs1.objectid
AND rs1.objectid = rs2.objectid
AND rs2.objectid = rs3.objectid
AND rs3.objectid = rs4.objectid
AND rs4.objectid = rs5.objectid
AND rs5.objectid = rs6.objectid
AND rs6.objectid = rs7.objectid
AND rs7.objectid = rs8.objectid
AND rs8.objectid = rs9.objectid
AND rs9.objectid = rs10.objectid))

[aquintar]

Why don't you find out what the exection plan is of both queries by setting the autotrace option on the sql prompt and executing both queries. Make sure you have a plan table.
If needed create on with ~/rdbms/admin/utlxpln.sql

sql> set autotrace traceonly explain

regards