dbms_stats

Hi,  oracle 10.2.0.4

i have deleted the statistics on a table EMP

then i have gathered the stats with dbms_stats:

EXEC 
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HR',tabname=>'EMP',estimate_percent=>NULL);
-- it takes 498 sec

Then i have queried  the emp table

in that last_name column is not  indexed and salary column is  indexed .

Here is my test:

select count(*) from emp where last_name='KUMAR';

this  takes 40.717 sec to produce the output

select count(*) from emp where salary=2000;

this takes 0.187 sec to produce the output.

then with Analyze command:

EXEC DBMS_STATS.delete_schema_STATS('HR');

Analyze table emp  compute statistics; -- it takes 430 sec

Here is my test:

select count(*) from emp where last_name='KUMAR';

this  takes 12 sec to produce the output

select count(*) from emp where salary=2000;

this takes 0.0001 sec to produce the output.


so from my test , i show analyze is better than DBMS_STATS..... Any
other ideas plz
--
http://www.freelists.org/webpage/oracle-l

Other related posts: