Re: Object Ownership v/s Parsed Query

It would help if you could read the Concept manual. There's no ambiguity when parsing the first two SQLs. For the 3rd one, Oracle tries to find table MyTab in C's schema (suppose ALTER SESSION SET CURRENT_SCHEMA is not done). It's there! So a full table scan is performed on it. If it's not found, it may be a view; failing that, it may be a private synonym; failing that, public synonym; failing that, you get ORA-942.

When another session executes "Select * from MyTab" for the 1st time, it finds this SQL in library cache. It performs authentication (it always has to do this) and sees if it's his own table, view, private synonym...

It's always a good idea to specify owner's name if the table is not your own than using a synonym. Steve Adams has done some benchmark in his recent newsletter.

Yong Huang
yong321_at_yahoo.com

learning_still_at_hotmail.com (Newbie) wrote in message news:<6093c29e.0108010924.2db6103d_at_posting.google.com>...
> If a table, say MyTab is created by users A, B and C within their
> respective default tablespaces, A.MyTab and C.MyTab having same
> structures but b.MyTab have different structure. Now if user C issues
> following statements -
>
> Select * from A.MyTab
> Select * from B.MyTab
> Select * from MyTab
>
> - How are the queries parsed?
> - How is their execution plan identified?
> - Will exec plan be different for all?
> - If it is different for all, isn't it always a better practice to
> qualify the table name with owner name within front-end code?
> - What about cost of query and reusability of the query once it is
> parsed? In what cases same parsed one could be used?
Received on Wed Aug 01 2001 - 23:29:08 CDT