Re: SQL not working in 10g Release 2


"Balamurali" <bmuralir@xxxxxxxxx> wrote in message
news:1136973873.431748.175480@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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


.

Relevant Pages

  • Re: SQL not working in 10g Release 2
    ... The "SQL query" I am having is quite similar to the one I have ... What was and is happening in Oracle 10g R1 is that, when I execute this ... Balamurali ...
    (comp.databases.oracle.server)
  • RE: How to Populate a Table based on Entries in Another
    ... You can execute a SQL query in your application to check if the keys being ... Best regards, ...
    (microsoft.public.access.queries)
  • [NEWS] Multiple Vulnerabilities in Oracle Database (Trigger, Extproc, Wrapped Procedures, PL/SQL Inj
    ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... Multiple vulnerabilities were discovered in the Oracle database server. ... Oracle Trigger Abuse ... written in PL/SQL and execute with the privileges of the definer/owner. ...
    (Securiteam)
  • Re: Debug ORA-03113 on Oracle XE
    ... a laptop that runs the Oracle 10g Express ... The query returns no rows with the current data set but it's pretty ... If Oracle XE generates further info I don't know where I ... Usually, when you install Oracle, the UTL_FILE package is installed, EXECUTE is granted to PUBLIC, and a public synonym is created. ...
    (comp.databases.oracle.server)
  • Best practise for distributed transactions and oracle procedures.
    ... Execute a Stored Procedure to return a recordset of IDs from a SQL2K DB ... Then call a oracle procedure that will validate these ids against its ... Insert these exceptions into a exception table in our SQL2K DB. ...
    (microsoft.public.sqlserver.dts)