• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Oracle subquery issue in 11.2.01

Hello Experts the following join worked perfectly in 11.1. and still does.
 
               FROM   b1 bm
               INNER JOIN en                    e
                    ON e.id_number = bm.bm_donor_id
               INNER JOIN ba tender
                    ON  bm.mg_number = tender.mg_number
                    AND bm.bm_batch_number = tender.bg_batch_number
                    AND tender.xsequence in (SELECT MIN(x.xsequence)
                                           FROM bt x
                                           WHERE x.mg_number = bm.mg_number
                                           AND x.bg_batch_number = bm.bm_batch_number)
               LEFT OUTER JOIN  tms         tct
               ON tct.currency_type_code = bm.foreign_type
         WHERE  bm.bm_batch_number = v_batch_number
        AND    bm.mg_number = i_mg_number

When tested on 11.2.01  I had to change the subquery to use the variables instead of the column names.

Can anyone tell me why?  Did it have anything at all to do with the upgrade?

               FROM   b1 bm
               INNER JOIN en                    e
                    ON e.id_number = bm.bm_donor_id
               INNER JOIN ba tender
                    ON  bm.mg_number = tender.mg_number
                    AND bm.bm_batch_number = tender.bg_batch_number
                    AND tender.xsequence in (SELECT MIN(x.xsequence)
                                           FROM bt x
                                           WHERE x.mg_number =  i_mg_number
                                           AND x.bg_batch_number =v_batch_number)
               LEFT OUTER JOIN  tms         tct
               ON tct.currency_type_code = bm.foreign_type
              WHERE  bm.bm_batch_number = v_batch_number
              AND    bm.mg_number = i_mg_number
0
bill_home
Asked:
bill_home
1 Solution
 
sdstuberCommented:
can't say why for sure about the exact internal operation changes, but yes, it's has to do with the upgrade,

  or optimizer parameter changes made in 11.2.0.1 that are different from 11.1


often, it can help to give the optimizer redundant information.

WHERE x.mg_number =  i_mg_number
 AND x.mg_number = bm.mg_number
 AND x.bg_batch_number =v_batch_number
 AND x.bg_batch_number = bm.bm_batch_number

if the optimizer doesn't need the information it should ignore the extra conditions away.
On the other hand, as it's iterating through permutations of join conditions it now has additional information to help it choose the best way.
Given infinite time and memory the optimizer should figure out on its own that the two queries are equivalent.
But,  the optimizer isn't guaranteed to do an exhaustive search,  so it can make a mistake by quitting too early.
Redundant information helps keep it looking in the right direction even if it misses some options.

Also, by being redundant you are saving the optimizer a few steps from being forced to figure out on its own that the conditions are equivalent.






0
 
bill_homeAuthor Commented:
Excellent explanation!! Thanks!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now