Re: Full table scan

In article <02026da7.bd2ebd70_at_usw-ex0105-035.remarq.com>,   craibuc <craibucNOcrSPAM_at_hotmail.com.invalid> wrote:
> How do I read an explain plan? I understand that full table
> scans are bad, but how do I know what WHERE clause line is at
> fault? What's the best way to eliminate a full table scan? An
> index?
>

How you read an explain plan depends partly on the sql you use to retrive the plan. If you are using sql that indents the output, then the first indented line that has a following line that starts at the same or to the left of its starting position is generally the first action being taken.

When you see a line that says table fetch by rowid followed by an indented line that refers to an index, the index was read and the rowid found in the index was used to access the table in the preceeding line.

The parent_id column of plan table identifies the id, step, that the results of a step are returned to.

Full table scans are not always bad. They can be the best way to read a table where all or the majority of rows will be retrived. They are also more efficient than index retrievals against very small, less than multiblock_read_count number of blocks in size, tables.

In general for the optimizer to use an index in retriving from the driving table the leading column or columns of the index must be referenced in the where clause condition. Then for Oracle to use an index to perform a join, the join columns referenced in the where clause must be indexed.

For CBO you should code your tables in the from clause in the join order that you want to drive on and reference them in the same order in the where clause. This is partly for style and also to aid the use of the ordered hint to force the CBO to drive in the desired, correct, order where it makes a questionable choice. In theory the order of where clause references should not effect the CBO but it does because the CBO only evaluates so many plans before picking the low cost one and the where clause ordering affects where the otimizer starts looking so I try to always reference the table I want as the driver first.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.