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