Post by Emrynso » Sat, 06 Dec 1997 04:00:00

Hi Krishnanand,

I will try as best as I can to answer your questions:

Quote:>>  (2) Why rows for "table access full obj$" is just 1 when the table  has

many rows ?

It is telling you how many rows are returned after your specified action not
how many rows are scanned.

Quote:>>  (3) Can someone give the EXACT order of execution steps ? I haven't been

able to deduce one by  reading the manual.

The output is read from inside to out and top to bottom. So start with the
lines that are most indented and go from top to bottom on that indentation
level. For you plan the execution steps will be:

1. Unique index scan of i_user1 and read the user table data using rowid.
2. Do a nested loop join using full scan of table objs$
3. Do a nested loop join on i_objauth1 index. It does not have to go to the
objauth table as it gets all required data from the index
4. Do a nested loop join on i_table_privilege_map index. Does not need to go to
map table

 >>>   BUT I SEE ALL THE COLUMNS of objauth$ table.
You are doing a select *. This will show all columns of all tables in your from
clause. You are looking at the explain plan for a different sql statement
above. There you are doing a select a.name so it does'nt have to go to the
objauth$ table. Run an explain plan on the "select * ..." query. It will
produce a different execution plan

Quote:>>>  Why leaf blocks have NULL cost ?

  Not sure what you mean

Hope that helps,


1. Show/Explain Query Plan

Sometimes, I wonder if I should trust the plan returned from the query
optimizer.  For example,

create table aaa (a int)

select * from aaa
where 1=2

Some plans show table scan for the above query.  Is it normal?

2. Problems talking to ODS Procsrv example

3. Explain plan

4. Universe locking up

5. Explain query estimation plan

6. Oracle Sales Analyzer

7. Explain Plan

8. Stored Procedures on Source Safe.

9. Oracle and Explain Plan

10. explain plan counter part

11. Planned small change in EXPLAIN behavior

12. More question about plans & explain (long)

13. A plan returned by explain doesn't make sense to me