| dbms_stats.gather_index_stats vs dbms_stats.gather_table_stats [message #566941] |
Fri, 21 September 2012 03:16  |
c_stenersen
Messages: 255
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  |
 |
Mahesh Rajendran
Messages: 10707
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
