Oracle PL/SQL/Index/Index STATISTICS
Содержание
Autotrace a table with two indexes
<source lang="sql">
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> --
</source>
Compute Index statistics
<source lang="sql">
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>
</source>
Create index and check the Execution Plan
<source lang="sql">
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>
</source>
Discovering When an Index Is Useful
<source lang="sql">
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>
</source>
