Oracle PL/SQL/Index/Index STATISTICS
Содержание
Autotrace a table with two indexes
SQL>
SQL>
SQL> set echo on
SQL>
SQL> create table t as
2 select * from all_objects;
Table created.
SQL>
SQL> create index t_idx1 on t(object_name);
Index created.
SQL>
SQL> create index t_idx2 on t(object_type);
Index created.
SQL>
SQL> set autotrace traceonly
SQL> select * from t t1 where object_type in( "FUNCTION","PROCEDURE", "TRIGGER" );
520 rows selected.
Execution Plan
--------------------------------------------------
Plan hash value: 831817202
--------------------------------------------------
-------------------------------------
| Id | Operation | Name | Ro
ws | Bytes | Cost (%CPU)| Time |
--------------------------------------------------
-------------------------------------
| 0 | SELECT STATEMENT | |
596 | 76288 | 7 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | |
| | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T |
596 | 76288 | 7 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX2 |
52 | | 3 (0)| 00:00:01 |
--------------------------------------------------
-------------------------------------
Predicate Information (identified by operation id)
:
--------------------------------------------------
-
3 - access("OBJECT_TYPE"="FUNCTION" OR "OBJECT_
TYPE"="PROCEDURE" OR
"OBJECT_TYPE"="TRIGGER")
Note
-----
- dynamic sampling used for this statement
Statistics
--------------------------------------------------
248 recursive calls
0 db block gets
243 consistent gets
3 physical reads
0 redo size
28155 bytes sent via SQL*Net to client
754 bytes received via SQL*Net from clien
t
36 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
520 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --
Compute Index statistics
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> -- Collect statistics upon creation of the index as follows:
SQL>
SQL> CREATE INDEX employee_idx
2 ON employee (first_name, city)
3 COMPUTE STATISTICS;
Index created.
SQL>
SQL> drop index employee_idx;
Index dropped.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Create index and check the Execution Plan
SQL>
SQL> create table indextest as select * from dba_objects
2 where owner in ("OUTLN","PUBLIC","SCOTT","SYS","SYSTEM");
Table created.
SQL>
SQL> analyze table indextest compute statistics;
Table analyzed.
SQL>
SQL> set autotrace trace explain
SQL> select owner, object_name from indextest
2 where object_name = "DBA_INDEXES";
Execution Plan
----------------------------------------------------------
Plan hash value: 2792531790
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 36 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| INDEXTEST | 1 | 21 | 36 (3)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"="DBA_INDEXES")
SQL>
SQL> create index indxtest_objname_idx
2 on indextest (object_name);
Index created.
SQL>
SQL> select owner, object_name from indextest
2 where object_name = "DBA_INDEXES";
Execution Plan
----------------------------------------------------------
Plan hash value: 1012932391
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| INDEXTEST | 1 | 21 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDXTEST_OBJNAME_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"="DBA_INDEXES")
SQL>
SQL>
SQL> drop table indextest;
Table dropped.
SQL>
SQL>
Discovering When an Index Is Useful
SQL>
SQL>
SQL> set autotrace off
SQL>
SQL> create table indextest as select * from dba_objects
2 where owner in ("OUTLN","PUBLIC","SCOTT","SYS","SYSTEM");
Table created.
SQL>
SQL>
SQL> select owner, count(*) from indextest group by owner;
OWNER COUNT(*)
------------------------------ ----------
PUBLIC 2767
OUTLN 8
SYSTEM 449
SYS 6683
SQL>
SQL> create index indxtest_owner_idx on indextest (owner);
Index created.
SQL>
SQL> set autotrace trace explain
SQL>
SQL> select owner, object_name from indextest where owner="SYS";
Execution Plan
----------------------------------------------------------
Plan hash value: 2792531790
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7763 | 629K| 36 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| INDEXTEST | 7763 | 629K| 36 (3)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"="SYS")
Note
-----
- dynamic sampling used for this statement
SQL>
SQL> select owner, object_name from indextest where owner="SCOTT";
Execution Plan
----------------------------------------------------------
Plan hash value: 3406603611
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 83 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| INDEXTEST | 1 | 83 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDXTEST_OWNER_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"="SCOTT")
Note
-----
- dynamic sampling used for this statement
SQL>
SQL>
SQL> analyze table indextest compute statistics for columns owner;
Table analyzed.
SQL>
SQL> select owner, object_name from indextest where owner="SYS";
Execution Plan
----------------------------------------------------------
Plan hash value: 2792531790
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7763 | 530K| 36 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| INDEXTEST | 7763 | 530K| 36 (3)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"="SYS")
Note
-----
- dynamic sampling used for this statement
SQL>
SQL> select owner, object_name from indextest where owner="SCOTT";
Execution Plan
----------------------------------------------------------
Plan hash value: 3406603611
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 70 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| INDEXTEST | 1 | 70 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDXTEST_OWNER_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"="SCOTT")
Note
-----
- dynamic sampling used for this statement
SQL>
SQL> set autotrace off
SQL> drop table indextest;
Table dropped.
SQL>
SQL>