performance tuning

[learning_plsql]

hi all,

sorry this question may be too easy for gurus in here, I read some Oracle docs and I wasn't able to understand what they mean. Below is the exactly words from the docs:

EXPLAIN PLAN SET statement_id = 'example_plan2' FOR
SELECT full_name FROM per_all_people_f
WHERE full_name LIKE 'Pe%' ;

Plan
---------------------------------------------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID PER_ALL_PEOPLE_F
INDEX RANGE SCAN PER_PEOPLE_F_N54


This plan shows execution of a SELECT statement.

Index per_people_f_n54 is used in a range scan operation.
The table per_all_people_f is accessed through ROWID. ROWIDs are obtained from the index in the previous step for keys that meet the WHERE clause criteria. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated.


WHAT DOES ORACLE MEAN WHEN THEY SAY " When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated."

Another word, I am trying to understand of how to read the explain plan

thanks all

[saundman2000]

Understanding an explain plan is something every Oracle Developer and DBA should be familiar with, however, it is not a short study. Whole books have been written on the subject. To understand it for one's self I think you have to do some reading. If you have documentation, but it is convoluded, then check this link out. Tom Kyte always has a way of demystifying topics:

http://asktom.oracle.com/pls/ask/f?p...:231814117467,

[saundman2000]

Having said that, let me try to interpret that specific line from the documentation:

When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated.

I think they mean that the Oracle optimizer is smart enough to only look in the index if "the columns you are looking for" and "the complete way to find the data" is contained there. In the case that the columns of the index are not enough to solve the whole lookup, then Oracle has to look at the main table associated with the index. Oracle will get what it can from the index and, if necessary, refer to the main table to verify which rows actually match the data.

I hope my explanation makes more sense than the original documentation.

They are not saying anything earth-shattering here. Only that it is more efficient if a lookup can find all its data and verify it's constraints completely within an index. That way it has no need to refer to a table.

[learning_plsql]

Saundman2000,

thanks so much for your advise and am trying to read and learn explain plan now. Any way, let me try to rephrase of how to read the explain plan and please correct me if I am wrong:

EXPLAIN PLAN SET statement_id = 'example_plan2' FOR
SELECT full_name FROM per_all_people_f
WHERE full_name LIKE 'Pe%' ;

Plan
---------------------------------------------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID PER_ALL_PEOPLE_F
INDEX RANGE SCAN PER_PEOPLE_F_N54


This plan shows execution of a SELECT statement.

Index per_people_f_n54 is used in a range scan operation.

The table per_all_people_f is accessed through ROWID. ROWIDs are obtained from the index in the previous step for keys that meet the WHERE clause criteria.


BY LOOKING AT THE EXPLAIN PLAN, IS THAT RIGHT WE ALWAYS ALWAYS READ FROM BOTTOM TO TOP??? Another word, based on the explain plan, and Oracle read the range scan FIRST and then table access by ROWID. Since Oracle used the RANGE SCAN OPERATION, the table accessed trough ROWID are obtained from the index in the previous step for keys that meet the WHERE CLAUSE.

[slimdave]

Try using the following methodology ...

set autotrace traceonly explain

SELECT full_name FROM per_all_people_f
WHERE full_name LIKE 'Pe%' ;

... and post the response. you willsee the numbering of the lines of the plan, and the lines parent lines which tell you what order to read the plan in.

Your interpretation of this simple plan is correct though.