Re: SQL not working in 10g Release 2

"Balamurali" <bmuralir_at_gmail.com> wrote in message news:1136975760.803226.270900_at_g44g2000cwa.googlegroups.com...
> Hi Jonathan Lewis,
>
> Thanks for the reply.
>
> Yes, col1 and column1 are VARCHAR2(10).
>
> I have tried using outlines since the Schema user is able to execute
> the SQL query. But the outline was not used by the othere user-id.
>
> I create the outline in the schema user using
>
> create or replace outline cu_1
> on SELECT....
>
> Then I logged in as a different user and executed this statement before
> running the same SQL query
>
> alter session set use_stored_outlines=true;
>
> Still when i run the query it is not using the outline. Do I have to
> grant any privilege?
>
> Should I use outline in this case?
>
> Is there anything else I should check?
>
> Please reply.
> Regards,
> R. Balamurali
>

As I said in my previous post

    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.

It MIGHT give me a clue about which 10.2 feature is causing the problem, which means it MIGHT make it possible for me to suggest a workaround.

Since you've created an outline that apparently works, you might also post the list from user_outline_hints as seen by the owning schema.

Does the actual statement involve bind variables ? Do the different users have logon triggers that change their environments.
Do the different users have different roles granted, or different privileges granted.
Are there any function-based indexes involved

-- 
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 10th Jan 2006