CBO fails where RBO works!

[parijat67]

Hi All,

The following query returns no rows when run under CBO. However the RS2 query returns the correct data when run individually. Similarly, the query for RS3 also runs well if run alone. The output that we get from running these two queries separately shows matching programids. But when the entire query is run then we do not get any output. Somehow the join condition i.e. (WHERE RS3.programid = RS2.programid is becoming false, wheras this should not be the case as both RS2 and RS3 have matching programids.

This same query works well if we put the hint /*+RULE*/ in the select stmt. Our DBA team has spent some good time figuring out the possibilities about this behaviour of CBO. Still we are far from any convincing reasoning. Pls share your valueable thoughts regarding this.

Thanking you all.
Parijat Paul

Note: The db is oracle Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
--------------------------THE QUERY FOLLOWS---------------------------

SELECT 
  RS2.programid,
  RS3.ruleid
FROM 
(   SELECT 
      lg_programrules.programid, 
      lg_programrules.ruleid 
    FROM lg_programrules,
	(SELECT ruleid,count(ruleid) noofassertions 
         FROM lg_rulesassertion 
         WHERE assertionid in 
             (SELECT objectid FROM cache_0_10573 
              WHERE objectid not in
                    (SELECT assertionid 
                     FROM lg_assertionresults 
                     WHERE searchid = 0 and result=0
                    )
             )
	 GROUP BY ruleid
        )RS01  
    WHERE 
        lg_programrules.ruleid = RS01.ruleid and 
        lg_programrules.numberofassertions = RS01.noofassertions and 
        lg_programrules.propertyid = 1783 
) RS3,  
(   SELECT lg_programproperties.programid
    FROM lg_programproperties,
        (SELECT programid, count(distinct(propertyid)) noofprops  
         FROM lg_programrules,
             (SELECT ruleid,count(ruleid) noofassertions 
              FROM lg_rulesassertion 
              WHERE assertionid in 
                   (SELECT objectid
                    FROM cache_0_10573 
                    WHERE objectid not in 
                         (SELECT assertionid 
                          FROM lg_assertionresults 
                          WHERE searchid = 0 
                          and result=0
                         )
                    )
              GROUP BY ruleid 
             ) RS02 
         WHERE 
           lg_programrules.ruleid = RS02.ruleid and 
           lg_programrules.numberofassertions 
                             =  RS02.noofassertions   
         GROUP BY programid
         ) RS1 
     WHERE RS1.programid = lg_programproperties.PROGRAMID and    
     RS1.noofprops >= lg_programproperties.NUMBEROFPROPERTIES 
) RS2   
WHERE RS3.programid = RS2.programid;

----------------------------------------------------------------------

[davey23uk]

repost it using the code tage properly so it is readable. Then post the explain plan. Imaging some full table scans so have you generated proper statistics

[parijat67]

Sorry, I don't know how to use the code to format the query. As such I am sending a gif file which has the query properly formatted for viewing purpose.

The explain plan shows the rows returned from rs3 as 1 wheras the rows returned from RS2 as 244. Now if we switch the position of rs3 and rs2 then the number of rows returned from RS2 shows as 1 wheras the rows returned for RS3 becomes 244. But if we run the rs3 separately then the rows returned are 244 and same for RS2.The problem arises when we join rs2 and rs3 based on matching programid and that too only when the optimizer is CBO.

thanks
Paul

[waitecj]

I just opened that attachment and its FAR from readable!!

Re-post the code on this website using the following tags:

[ code ]my code[ / code ]

without the spaces inside the square brackets.

[parijat67]

Tx! formatted the query successfully.

Paul

[abhaysk]

Originally posted by parijat67
The explain plan shows the rows returned from rs3 as 1 wheras the rows returned from RS2 as 244. Now if we switch the position of rs3 and rs2 then the number of rows returned from RS2 shows as 1 wheras the rows returned for RS3 becomes 244. But if we run the rs3 separately then the rows returned are 244 and same for RS2.The problem arises when we join rs2 and rs3 based on matching programid and that too only when the optimizer is CBO.

Did you ever run the query?? or is that your just guessing by what does CBO and RULE plan give u?

Abhay.

[parijat67]

yes we have run this query in CBO as well as in RBO mode. The CBO mode returns "no row found" whereas RBO returns the correct output.

Paul

[abhaysk]

Originally posted by parijat67
yes we have run this query in CBO as well as in RBO mode. The CBO mode returns "no row found" whereas RBO returns the correct output.

Paul

If so, find the lenght of distinct programid ( along with programid ) and see if they match using CBO/RBO ( for each of your sub queries when run seperately )

[parijat67]

yes they have lots of matching programids. In CBO mode the RS2 & RS3 queries return correct set of data if run independently. If I copy & paste the RS2 query in the sql editor of TOAD then it returns correct data. Similarly, the RS3 query also returns correct set of data.They have common matching programids. The problem arises when rs2 & rs3 are run together as inline views as shown in the main query.


Paul

[abhaysk]

did u check the length of the programid?? ( with cbo/rbo ).. i suspect if there are some spaces..