Database Reference
In-Depth Information
SQL> create table t1 (a number, b number, c number, d number);
Table created.
SQL> insert into t1 select mod(level, 20), level, level, level from dual connect by level <=
30000;
30000 rows created.
SQL> commit;
Commit complete.
SQL> create index i_t1 on t1(a);
Index created.
SQL> exec dbms_stats.gather_table_stats(user, 'T1', method_opt=>'for all columns size 254')
PL/SQL procedure successfully completed.
SQL> alter session set statistics_level=all;
Session altered.
L.: „
Für den folgenden Select entscheidet der Optimizer absolut richtig, den Full Table Scan
einzusetzen, weil sehr viele Tabellensätze selektiert werden.
“
SQL> var b1 number
SQL> exec :b1:=5;
PL/SQL procedure successfully completed.
SQL> select count(b) from t1 where a >= :b1;
COUNT(B)
----------
22500
SQL> select plan_table_output from table (sys.dbms_xplan.display_cursor('','','ALLSTATS
LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
------------------
SQL_ID 4fxwhyjs9pqs6, child number 0
-------------------------------------
select count(b) from t1 where a >= :b1
Plan hash value: 3724264953
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.11 | 95 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.11 | 95 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 22537 | 22500 |00:00:00.06 | 95 |
-------------------------------------------------------------------------------------
L.: „
Fügen wir jetzt die neuen Sätze hinzu, deren Wert der Spalte
'
A
'
außerhalb der Histo-
gramme liegt, und führen denselben Select für die neuen Werte aus.
“
