1)composite index
1@@@@null
When a sql statement would use the index?
1)The b-tree index do not record the null values. If a sql statement compare with null,
it would miss the index. In Oracle 10g, compare with not null would use the index.
2)that column which join two tables.
3)use where statement.
@@@
@@@create two tables compare with each other in different situation.
[oracle@station78 admin]$ sqlplus / as sysdba;
SQL> @?/sqlplus/admin/plustrce.sql
SQL> grant plustrace to hr;
SQL> create table hr.tt1 as select * from dba_objects;
SQL> create table hr.tt1_ref as select * from dba_objects;
SQL> create tablespace indextbs01 datafile size 50M;
2@@@@general index
SQL> conn hr/hr
SQL> set autot traceonly;
SQL> create table t1(id number(2), name varchar2(2));
SQL> create index it1 on t1(name) tablespace indextbs01@@@default is nonunique index.
SQL> insert into t1 values(1,'a');
SQL> insert into t1 values(2,'A');
SQL> commit;
SQL> set linesize 200
SQL> select * from t1;
ID NAME
---------- ------
1 a
2 A
SQL> exec dbms_stats.gather_table_stats('HR','T1');
@@@
@@@general index
@@@group2-1: condition statement like where.
SQL> select * from t1 where name='A';
...
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 5 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IT1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"='A')
@@@
SQL> select * from t1 where id=1;
...
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 5 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
@@@
@@@function index
@@@group2-1: function would skip index. need create independent index on it.
SQL> select * from t1 where upper(name)='A';
...
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 5 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("NAME")='A')
@@@
SQL> create index it1_func on t1(upper(name)) tablespace indextbs01;
Index created.
@@@
SQL> select * from t1 where upper(name)='A';
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 5 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IT1_FUNC | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("NAME")='A')
3@@@@composite index
1)Reduce the need of storage. Index is the content for a table.
What we care is which situation the composite index do not work!!!
2)group3-1: INDEX RANGE SCAN
group3-2: INDEX SKIP SCAN use the second column in the composite index.(not)
group3-3: first column, fewness return would work.
group3-4: direct check the leaf of index
3)experience:
When the return set is fewness, the composite index would work.
More column including composite index in where statement, it is easy to work.
The selected column is included by the composite index.
If the values of column only two values, the index would different from others.
@@@more detail referencing the oracle online library.
@@@if you want compare with the physical gets, you should be flush cache first.
@@@
[oracle@station78 ~]$ sqlplus / as sysdba;
SQL> alter system flush buffer_cache;
@@@
SQL> set linesize 80
SQL> desc tt1
Name Null? Type
----------------------------------------- -------- ----------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> create index itt1_composite on tt1(status,owner,object_type,object_id)
tablespace indextbs01;
SQL> exec dbms_stats.gather_table_stats('HR','TT1');
SQL> set autotrace traceon
SQL> select * from tt1;
...
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50388 | 4576K| 160 (2)| 00:00:02 |
| 1 | TABLE ACCESS FULL| TT1 | 50388 | 4576K| 160 (2)| 00:00:02 |
--------------------------------------------------------------------------
4010 consistent gets
693 physical reads
@@@
@@@compare for composite. "INDEX RANGE SCAN"
@@@group3-1: object1 tt1, object tt1_test without index.
@@@use the composite index at the same time;
SQL> select * from tt1 where owner='SYS' and status='VALID'
and object_type='TABLE' and object_id=2;
...
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TT1 | 1 | 93 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ITT1_COMPOSITE | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='VALID' AND "OWNER"='SYS' AND "OBJECT_TYPE"='TABLE' AND
"OBJECT_ID"=2)
...
3 consistent gets
24 physical reads
@@@
SQL> select * from tt1_ref where owner='SYS' and status='VALID'
and object_type='TABLE' and object_id=2;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 177 | 160 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TT1_REF | 1 | 177 | 160 (2)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS' AND "STATUS"='VALID' AND
"OBJECT_TYPE"='TABLE' AND "OBJECT_ID"=2)
...
817 consistent gets
707 physical reads
@@@
@@@INDEX SKIP SCAN
@@@group3-2: why? use the same column, but return different way of index scan.
SQL> select * from tt1 where owner='HR';
68 rows selected.
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 3627 | 43 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TT1 | 39 | 3627 | 43 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | ITT1_COMPOSITE | 39 | | 41 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='HR')
filter("OWNER"='HR')
...
35 consistent gets
40 physical reads
@@@
SQL> select * from tt1 where owner='SYS';
22950 rows selected.
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23041 | 2092K| 159 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TT1 | 23041 | 2092K| 159 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
...
2203 consistent gets
677 physical reads
@@@
@@@first column
@@@group3-3: when the return is fewness, index would work?
SQL> select * from tt1 where status='VALID';
50369 rows selected. @@@<==it is the point
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50373 | 4574K| 160 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TT1 | 50373 | 4574K| 160 (2)| 00:00:02 |
--------------------------------------------------------------------------
1 - filter("STATUS"='VALID')
Statistics
----------------------------------------------------------
192 recursive calls @@@<==
0 db block gets
4043 consistent gets
702 physical reads
0 redo size
2596528 bytes sent via SQL*Net to client
37396 bytes received via SQL*Net from client
3359 SQL*Net roundtrips to/from client
5 sorts (memory) @@@<==
0 sorts (disk)
50369 rows processed
@@@
SQL> select * from tt1 where status='INVALID';
19 rows selected. @@@<==
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 930 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TT1 | 10 | 930 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ITT1_COMPOSITE | 10 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
2 - access("STATUS"='INVALID')
Statistics
----------------------------------------------------------
1 recursive calls @@@<==
0 db block gets
9 consistent gets
24 physical reads
0 redo size
3410 bytes sent via SQL*Net to client
480 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory) @@@<==
0 sorts (disk)
19 rows processed
@@@
@@@group3-4: direct check the leaf of index.
@@@what the meaning of recursive.
SQL> select status from tt1 where status='INVALID';
19 rows selected.
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 70 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| ITT1_COMPOSITE | 10 | 70 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
1 - access("STATUS"='INVALID')
Statistics
----------------------------------------------------------
0 recursive calls @@@<== it is direct invoke
0 db block gets
5 consistent gets
16 physical reads
0 redo size
838 bytes sent via SQL*Net to client
480 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19 rows processed
@@@
SQL> select status,owner,object_type,object_id from tt1 where status='INVALID';
19 rows selected.
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 270 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| ITT1_COMPOSITE | 10 | 270 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
1 - access("STATUS"='INVALID')
Statistics
----------------------------------------------------------
1 recursive calls @@@<==
0 db block gets
5 consistent gets
16 physical reads
0 redo size
1128 bytes sent via SQL*Net to client
480 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19 rows processed
@@@
SQL> select owner from tt1 where status='INVALID';
....
Statistics
----------------------------------------------------------
1 recursive calls @@@<==
0 db block gets
5 consistent gets
0 physical reads
0 redo size
转载于:https://blog.51cto.com/majesty/896194