Re: SQL not working in 10g Release 2

"Balamurali" <bmuralir_at_gmail.com> wrote in message news:1136973873.431748.175480_at_g14g2000cwa.googlegroups.com...
> Hi Laurenz Albe,
>
> The "SQL query" I am having is quite similar to the one I have
> mentioned.
>
> select column1,.....,columnx from t,(select col1 from t2 where
> t2.xyz='1234') t2
> where t.column1 = t2.col1
>
> What was and is happening in Oracle 10g R1 is that, when I execute this
> query "t2" is executed only once because it returns only one row. And
> then it is joined with "t" and then 't' is retrieved once, so there are
> 2 SELECTs.
>
> But in Oracle 10g R2 't2' is matched with all rows in 't' !!
> i.e., for every row in 't' , 't2' is retrieved and from that set one
> row is returned as output.
>
> I hope you get the picture.
>
> Is there any parameter setting which I have to change in R2 to avoid
> this?
>
> I have gatherd the statistics for all objects.
>
> Thanks in advance.
> Regards,
> Balamurali
>

There are a number of changes from 10.1 to 10.2 that might have an effect on this - so it would help if you posted the two different execution plans.

As a first guess - it looks as if 10.2 has done a form of predicate pushing which has persuaded it to take a different access path into t2 - with a side effect that a particular scalar subquery optimisation is no longer possible. Are col1 and column1 character types ?

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 29th Nov 2005