Home » RDBMS Server » Performance Tuning » dbms_stats.gather_index_stats vs dbms_stats.gather_table_stats (11.2.0.1)
dbms_stats.gather_index_stats vs dbms_stats.gather_table_stats [message #566941] Fri, 21 September 2012 03:16 Go to next message
c_stenersen
Messages: 254
Registered: August 2007
Senior Member
I have a small question, more from curiosity than from a real life problem. (At least at this time. I'm taking an optimization course, and this was something we had problems finding an explanation for.)
I saw one issue when creating a table and where gathering the index stats gave an explain plan which was different from the explain plan I got after gathering the table stats. Normally this would be completely natural, but in this query all the columns involved are in this index. My question then is, at what case is the gathering of the index statistics rather than the table statistics useful? (since it seemed like it ignored the distribution of data in the index, although it's the same as in the table)

Test case:
create table t(c number, d number);

--insert some test data:
begin 
  for i in 1..10000 loop 
    insert into t values(1,i); 
  end loop; 
end;

create index t_index on t(c,d);


Before gathering the stats. Full table scan given.
SQL> select count(*) from t where d=1;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    13 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("D"=1)


Then gathering the stats on the index doesn't help. I don't quite understand this since all of the columns it should need are in the index.
SQL> execute dbms_stats.gather_index_stats('sh', 't_index')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> select count(*) from t where d=1;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    13 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("D"=1)


But if I gather the table stats instead, then the index is used.
SQL> execute dbms_stats.gather_table_stats('sh', 't')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.28
SQL> select count(*) from t where d=1;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1269404236

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     1 |     4 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |         |     1 |     4 |            |          |
|*  2 |   INDEX SKIP SCAN| T_INDEX |     1 |     4 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"=1)
       filter("D"=1)

[Updated on: Fri, 21 September 2012 03:40]

Report message to a moderator

Re: dbms_stats.gather_index_stats vs dbms_stats.gather_table_stats [message #566943 is a reply to message #566941] Fri, 21 September 2012 05:13 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10621
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Lack of column level statistics Statistics. And Index stats are automatically collected during index creation.

SQL> create table t(c number, d number);

Table created.

SQL> begin 
  for i in 1..10000 loop 
    insert into t values(1,i); 
  end loop; 
end;
  2    3    4    5    6  
  7  /

PL/SQL procedure successfully completed.

SQL> create index t_index on t(c,d);

Index created.

SQL> get colStats;
  1* select table_name,column_name,num_distinct from user_tab_cols where table_name='T'
SQL> /

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------------------------ ------------
T                              C
T                              D


SQL> get indexStats;
  1* select index_name,distinct_keys,num_rows from user_indexes where table_name='T'
SQL> /

INDEX_NAME                     DISTINCT_KEYS   NUM_ROWS
------------------------------ ------------- ----------
T_INDEX                                10000      10000

SQL> execute dbms_stats.gather_table_stats('DBADMIN','T');

PL/SQL procedure successfully completed.

SQL> @colStats;

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------------------------ ------------
T                              C                                         1
T                              D                                     10000




And Index skip scan works if the leading column is not used and if the leading column has  a few distinct values.

[Updated on: Fri, 21 September 2012 05:15]

Report message to a moderator

Re: dbms_stats.gather_index_stats vs dbms_stats.gather_table_stats [message #566947 is a reply to message #566943] Fri, 21 September 2012 06:28 Go to previous message
c_stenersen
Messages: 254
Registered: August 2007
Senior Member
Ah, now I see what you mean (I think). with only the index statistics it knows that there is a lot of distinct values in the index, but it has no idea about that it's the second field in the index which gives the high number, and that the first field isn't very selective.

Thank you Smile
Previous Topic: HIBERNATE query runs slow but ok in SQLPLUS
Next Topic: Analytics and Predicate Pushing
Goto Forum:
  


Current Time: Tue Sep 02 12:53:13 CDT 2014

Total time taken to generate the page: 0.11468 seconds