Database Reference
In-Depth Information
SQL> truncate table test1.t1;
Table truncated.
SQL> insert into test1.t1 select mod(level,50), level*10, level*100, level*1000, level*10000,
level*100000 from dual connect by level <= 10000 order by 1;
10000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('TEST1', 'T1')
PL/SQL procedure successfully completed.
SQL> select blocks from dba_tab_statistics where owner='TEST1' and table_name='T1';
BLOCKS
----------
43
SQL> select num_rows, clustering_factor from dba_ind_statistics where owner='TEST1' and
index_name='I_T1';
NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
10000 50
SQL> select /*+ index(t1 i_t1) */ count(b) from test1.t1 where a is not null;
COUNT(B)
----------
10000
SQL> select plan_table_output from table (sys.dbms_xplan.display_cursor('','','ALLSTATS
LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
------------------
SQL_ID 1qu2dvu86558x, child number 0
-------------------------------------
select /*+ index(t1 i_t1) */ count(b) from test1.t1 where a is not null
Plan hash value: 2575626123
----------------------------------------------------------------------------------------------
-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers
|
----------------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.10 | 71
|
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.10 | 71
|
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10000 | 10000 |00:00:00.08 | 71
|
|* 3 | INDEX FULL SCAN | I_T1 | 1 | 10000 | 10000 |00:00:00.03 | 21
|
----------------------------------------------------------------------------------------------
-
P.: „
Die Tabelle kann aber mehrere Indices mit einem hohen CF haben. Was macht man in
diesem Fall?
“
L.: „
Es kann auch sein, dass die Tabellendaten sehr dynamisch sind und häufig geändert
werden. Der CF kann sich im Laufe der Zeit verschlechtern, auch wenn er am Anfang sehr gut
war. Ja, diese Methode ist nicht für alle Fälle gut geeignet. Wenn das jeweilige Performanz-
