Analyze table

[omegamark]

Hi all,
i have a simple query that returns records based on the values passed and the values passed are the primary key columns. when i get the explain plan for this query it gives a full table scan.after i delete the statistics of thta particular table then it gives aunique scan,
i didn't understant the concept of analyzing the tables and the way they are related to the optimization of queries(i mean unique scan and full table scan when we get the explain plans)
thanks a lot,

[sambavan]

What is your optimizer mode? If it was a cost based optimization, then it would mean that it would have been cheaper to do the full table scan, rather than unique scan based on the statistics. Did you also analyze your pk index. If not analyze that and try the same querry.

Sam

[omegamark]

my optimizer is a cost base doptimizer...
i have analyzed the index and tried but it still give the full table scan

[sambavan]

TRY setting the OPTIMIZER_MODE=FIRST_ROWS and if this doesn't bring out any improvement then it means that cost of scanning the entire table is less than that of using the index.

If you really want the index scan to look more effective for the optimizer then you can set the OPTIMIZER_INDEX_COST_ADJ to any value less than 100, that would tell the OPTIMIZER that using index would be more efficient when comparing the full table scans...

Hope this would help you.

Sam

[UNIX DBA]

Rebuild the index and then analyze.