Friday, March 20, 2009

Create index with compute statistics

When you are (re)building an index on Peoplesoft with in Application designer, Peoplesoft is writing a sql script to (re)create the index on the database level.
The statement of index creation contains "compute statistics" option.
If in most of cases there is no issue in that, sometimes you could have some strange behaviour behind this simple statement.

Some times ago, on one of our 9.2 database, we had a query which was not fast as expected. Just to try, we decided to create a new index. It was still not fast enough, then drop this new index.
Strangely, this index creation makes Oracle optimizer change his behaviour comparing before the index (creation and drop) and start to use an other explain plan.
How comes ? We created and drop a new index (through Application Designer) and Oracle "decided" to change the execution plan, hmmm, strange.
After looking deeper, it appears the table statistics had been updated by the index creation containing "compute statistics".

Example :
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';

NUM_ROWS LAST_ANA
---------- --------


SQL> create index idx on emp(ename) compute statistics;

Index created.

SQL> drop index idx;

Index dropped.

SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';

NUM_ROWS LAST_ANA
---------- --------
14 20/03/09


But, in some cases, I wouldn't want the table statistics to be updated by my index creation. That's bad, that does not happen without this option "compute statistics" :
SQL> create index idx on emp(ename);

Index created.

SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';

NUM_ROWS LAST_ANA
---------- --------

Finally, found an event to work around and avoid that table statistics update :

SQL> alter session set events '8130 trace name context forever';

Session altered.

SQL> create index idx on emp(ename) compute statistics;

Index created.

SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';

NUM_ROWS LAST_ANA
---------- --------

Ah, now that's better, now if I drop this index, my explain plan won't change as before the index.

This case has been tested on 9.2.0.6 and 9.2.0.8. Seems not to be a problem anymore in the higher version.

Enjoy,

5 comments:

Laurent Schneider said...

strange, but the COMPUTE STATISTICS is used for backward compatibility

http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/changes.htm
COMPUTE STATISTICS Clause of CREATE INDEX
In earlier releases, the COMPUTE STATISTICS clause of CREATE INDEX could be used to start or stop the collection of statistics on an index. This clause has been deprecated

Nicolas Gasparotto said...

Thank you Laurent for pointing out this deprecated option in 11g. However, it is how Peoplesoft works when building indexes objects. Not sure how to deactivate it on the Peoplesoft developper tool, Application Designer.

Laurent Schneider said...

according to the doc, it will generate no error, but it will be ignored

9i:
drop table lsc_t;
create table lsc_t ( x number);
create index lsc_i on lsc_t(x) compute statistics;
select last_analyzed from user_indexes where index_name='LSC_I';
23-MRZ-09
drop index lsc_i;
create index lsc_i on lsc_t(x) ;
select last_analyzed from user_indexes where index_name='LSC_I';
NULL

10g:
drop table lsc_t;
create table lsc_t ( x number);
create index lsc_i on lsc_t(x) compute statistics;
select last_analyzed from user_indexes where index_name='LSC_I';
NULL
drop index lsc_i;
create index lsc_i on lsc_t(x) ;
select last_analyzed from user_indexes where index_name='LSC_I';
NULL

Nicolas Gasparotto said...

But on 11.1.0.7, it is disabled the statistics...
Personal Oracle Database 11g Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table lsc_t;
drop table lsc_t
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table lsc_t ( x number);

Table created.

SQL> create index lsc_i on lsc_t(x) compute statistics;

Index created.

SQL> select last_analyzed from user_indexes where index_name='LSC_I';

LAST_ANAL
---------


SQL> drop index lsc_i;

Index dropped.

SQL> create index lsc_i on lsc_t(x) ;

Index created.

SQL> select last_analyzed from user_indexes where index_name='LSC_I';

LAST_ANAL
---------
23-MAR-09

Nicolas Gasparotto said...

Laurent, your example on 10g show a bug. According to the doc you linked earlier : "Oracle Database 10g Release 1 (10.1) and later releases automatically collect statistics during index creation and rebuild"
Ok, fixed.
CREATE INDEX with COMPUTE STATISTICS won't generate statistics on empty table (as it should) but it does on non-empty table (bug #6670551, fixed in 11.2 and 10.2.0.5):
SQL> drop index lsc_i;

Index dropped.

SQL> insert into lsc_t values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> create index lsc_i on lsc_t(x) compute statistics;

Index created.

SQL> select last_analyzed from user_indexes where index_name='LSC_I';

LAST_ANAL
---------
23-MAR-09

SQL>