Fragen

INLIST ITERATOR
• erscheint im Execution Plan, wenn der
zugegriffene Index ´IN list`
implementiert
also bei Bedingungen wie
.. WHERE N.N_NAME IN (´PERU`, ´BRAZIL`, ´ARGENTINA`) ..
.. WHERE O.O_DATE IN (´01.03.95`, `02.03.95´) ..
 SELECT STATEMENT
INLIST ITERATOR
TABLE ACCESS BY ROWID NATION
INDEX RANGE SCAN IDX_NATION_CUSTNAME
COSTS
• COST
.. the value of this column does not have any particular unit of
measurement, it is merely a weighted value used to compare
costs of execution plans.
• CARDINALITY
the estimate by the cost-based approach of the number of
rows accessed by the operation.
• BYTES
the estimate by the cost-based approach of the number of
bytes accessed by the operation.
DBMS_STATS package
Von DBMS_STATS erstellte Statistiken umfassen:
– Table statistics
o Number of rows
o Number of blocks
o Average row length
ANALYZE table COMPUTE
STATISTICS;
– Column statistics
o Number of distinct values (NDV) in column
o Number of nulls in column
o Data distribution (histogram)
– Index statistics
o Number of leaf blocks
o Levels
o Clustering factor
– System statistics
o I/O performance and utilization
o CPU performance and utilization
Nicht optimierte Pläne
Execution Plan für Q1.1 ohne Materialized Views:
SELECT STATEMENT
Cost = 3481
HASH JOIN
HASH JOIN
TABLE ACCESS FULL NATION
TABLE ACCESS FULL CUSTOMER
TABLE ACCESS FULL ORDERS
Execution Plan für Q2.3 ohne Materialized Views:
SELECT STATEMENT
Cost = 771018
SORT ORDER BY
SORT GROUP BY
HASH JOIN
TABLE ACCESS FULL PART
HASH JOIN
TABLE ACCESS FULL SUPPLIER
HASH JOIN
INDEX FAST FULL SCAN SYS_C0012879
HASH JOIN
TABLE ACCESS FULL ORDERS
INDEX FAST FULL SCAN SYS_C0012882
Optimiertere Pläne
Execution Plan für Q1.1 mit Materialized Views:
SELECT STATEMENT
Cost = 132
HASH JOIN
TABLE ACCESS FULL MV1
INDEX FAST FULL SCAN OTPRICE_OSTAT_CKEY_OKEY;
Execution Plan für Q1.5 mit Materialized Views:
SELECT STATEMENT
Cost = 26
HASH JOIN
TABLE ACCESS FULL MV3
TABLE ACCESS FULL MV1
Table/Index Stats
Enter
Enter
Table
Table
Table
value for owner (RETURN for ALL) > gruppe1
value for table name (RETURN for ALL) > MV1
Stats: GRUPPE3.TEST_VIEW1 NUM_ROWS 449999
Stats: GRUPPE3.TEST_VIEW1 BLKS 5387
Stats: GRUPPE3.TEST_VIEW1 AVG_ROW_LEN 92
.. wir nehmen
NUM_ROWS *
AVG_ROW_LEN
[..]
Col
Col
Col
Col
Col
Col
Col
Col
Stats:
Stats:
Stats:
Stats:
Stats:
Stats:
Stats:
Stats:
TEST_VIEW1.O_TOTALPRICE DISTCNT 446631
TEST_VIEW1.O_TOTALPRICE DENSITY .0000022389847547528
TEST_VIEW1.O_TOTALPRICE NULLCNT 0
TEST_VIEW1.O_TOTALPRICE AVGCLEN 5
TEST_VIEW1.O_ORDERSTATUS DISTCNT 3
TEST_VIEW1.O_ORDERSTATUS DENSITY .333333333333333
TEST_VIEW1.O_ORDERSTATUS NULLCNT 0
TEST_VIEW1.O_ORDERSTATUS AVGCLEN 1
PL/SQL procedure successfully completed.