CODEWITHSUNDEEP
sqloracle-database
meta
meta

Reputation: 153

Oracle undo analyze table

I'm doing an assignment on indexing and optimizing queries and rushed a little too far with analyzing my table. I'd like to get some data of the state and costs of before performing this command: 

analyze table TAB1 compute statistics;

Is there a way to "unanalyze" a table?

In case it helps: I'm using oracle 11.2G and SQL*Developer 4.1

Upvotes: 1

Views: 508

Answers (1)

Jon Heller
Jon Heller

Reputation: 36807

The DBMS_STATS package allows you to save and re-apply old statistics.

Create the initial table and data

Create a simple table with 100K rows.

--drop table tab1;
create table tab1(a number);
insert into tab1 select level from dual connect by level <= 100000;
begin
    dbms_stats.gather_table_stats(user, 'TAB1');
end;
/

View original stats

Note that the estimated number of rows is set to 100K in the Rows column.

explain plan for select * from tab1;
select * from table(dbms_xplan.display);


Plan hash value: 2211052296

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|   488K|    69   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TAB1 |   100K|   488K|    69   (2)| 00:00:01 |
--------------------------------------------------------------------------

Save the original statistics

begin
    dbms_stats.create_stat_table(user, 'TAB1_OLD_STATS');
    dbms_stats.export_table_stats(user, 'TAB1', stattab => 'TAB1_OLD_STATS');
end;
/

Change the statistics

After changing the statistics Rows is set to 99M.

begin
    dbms_stats.set_table_stats(user, 'TAB1', numrows => 99999999);
end;
/

explain plan for select * from tab1;
select * from table(dbms_xplan.display);


Plan hash value: 2211052296

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    99M|   476M|   793  (92)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TAB1 |    99M|   476M|   793  (92)| 00:00:01 |
--------------------------------------------------------------------------

Revert to the old statistics

Using DBMS_STATS.IMPORT_TABLE_STATS the statistics are reverted to the old values and Rows is back to 100K.

begin
    dbms_stats.import_table_stats(user, 'TAB1', stattab => 'TAB1_OLD_STATS');
end;
/


explain plan for select * from tab1;
select * from table(dbms_xplan.display);

Plan hash value: 2211052296

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|   488K|    69   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TAB1 |   100K|   488K|    69   (2)| 00:00:01 |
--------------------------------------------------------------------------

Upvotes: 1

Related Questions