Re: left outer join with full table scan

m.fangtao_at_genesis.co.nz a écrit :

> Hi,

Hello,

> I have a query with 3 full table scans (each table has 360000 rows). I
> tried to create indexes,add hints and removed the FTS,but cost was 100
> times more than FTS and ran slower. Could anybody help me to tune the
> statement? Thanks.

[skip big query]
[skip unformatted exec plan]

Knowing Oracle version and options, platform OS et hardware capabilities would help.
Anything else worth to be known :
Are these tables partitioned ?
Did you consider using mviews to precalculate your joins before applying WHERE clause predicates with literals ? Does this query run within a batch or is it issued by a client app ? Did you TKProf your query ? How much time does it take to execute ?

Tip on why accessing rows by index may be more costly than FTS : Getting a row from a table needs basically one logical i/o operation with FTS. With an index, it takes two, one to read the index, retrieving a rowid and one to access the row by rowid. If your index doesn't have a good selectivity regarding the criteria used, the entire index could be searched and, then, the table, increasing the overall cost of the operation.

HTH Regards

-- 
SL