Database Reference
In-Depth Information
SQL> select count(*) from t1 where a=0 and b=0;
COUNT(*)
----------
54
SQL> select plan_table_output from table (sys.dbms_xplan.display_cursor('','','LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3mqr4p1pyns34, child number 0
-------------------------------------
select count(*) from t1 where a=0 and b=0
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | TABLE ACCESS FULL| T1 | 54 | 972 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Löschen wir die erweiterten Statistiken und erstellen die herkömmlichen Histogramme:
SQL> exec dbms_stats.drop_extended_stats(user, 'T1', '(a,b)')
PL/SQL procedure successfully completed.
SQL>
SQL> select extension_name, extension from user_stat_extensions where table_name = 'T1';
no rows selected
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'for all columns size 254',
no_invalidate=>false);
PL/SQL procedure successfully completed.
Mit den herkömmlichen Histogrammen ist die Schätzung der Kardinalität wesentlich
schlechter:
SQL> select count(*) from t1 where a=0 and b=0;
COUNT(*)
----------
54
SQL>
SQL> select plan_table_output from table (sys.dbms_xplan.display_cursor('','','LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3mqr4p1pyns34, child number 0
-------------------------------------
select count(*) from t1 where a=0 and b=0
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| T1 | 11 | 66 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Search WWH ::




Custom Search