索引特性之儲存列值及ROWID

SQL >select * from v$version whererownum=1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production

SQL >drop table obj;

SQL >create table obj as select * fromdba_objects;

SQL >update obj set object_id=rownum;

SQL >commit;

2.檢視執行計劃

SQL >set autotrace traceonly

1）未建立任何索引

SQL >select * from obj where object_id<100;

Execution Plan

----------------------------------------------------------

Plan hash value: 730912574

--------------------------------------------------------------------------

| Id | Operation         | Name |Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0| SELECT STATEMENT  |      |  190 | 39330 |   297   (1)| 00:00:04 |

|*  1|  TABLE ACCESS FULL| OBJ  |   190 | 39330 |   297  (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   1- filter("OBJECT_ID"<100)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

      1073  consistent gets

         0  physical reads

         0  redo size

      5542  bytes sent via SQL*Net toclient

       586  bytes received via SQL*Netfrom client

         8  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

        99  rows processed

SQL >select * from obj where object_id=100;

Execution Plan

----------------------------------------------------------

Plan hash value: 730912574

--------------------------------------------------------------------------

| Id | Operation         | Name |Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0| SELECT STATEMENT  |      |   17 |  3519 |   297  (1)| 00:00:04 |

|*  1|  TABLE ACCESS FULL| OBJ  |    17 | 3519 |   297   (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   1- filter("OBJECT_ID"=100)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

      1067  consistent gets

         0  physical reads

          0 redo size

      1611  bytes sent via SQL*Net toclient

       520  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

SQL >select count(*) from obj where object_id<100;

Execution Plan

----------------------------------------------------------

Plan hash value: 1612036541

---------------------------------------------------------------------------

| Id | Operation          | Name |Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0| SELECT STATEMENT   |      |    1 |    13 |   297  (1)| 00:00:04 |

|   1|  SORT AGGREGATE    |     |     1 |    13 |            |          |

|*  2|   TABLE ACCESS FULL| OBJ  |   190 | 2470 |   297   (1)| 00:00:04 |

---------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2- filter("OBJECT_ID"<100)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

      1066  consistent gets

         0  physical reads

         0  redo size

       526  bytes sent via SQL*Net toclient

       520  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

SQL >select count(*) from obj where object_id=100;

Execution Plan

----------------------------------------------------------

Plan hash value: 1612036541

---------------------------------------------------------------------------

| Id | Operation          | Name |Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0| SELECT STATEMENT   |      |    1 |    13 |   297  (1)| 00:00:04 |

|   1|  SORT AGGREGATE    |     |     1 |    13 |            |          |

|*  2|   TABLE ACCESSFULL| OBJ  |    17 |  221 |   297   (1)| 00:00:04 |

---------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2 -filter("OBJECT_ID"=100)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

      1066  consistent gets

         0  physical reads

         0  redo size

       526  bytes sent via SQL*Net toclient

       520  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

2）查詢條件建立索引

SQL >create index ind_obj onobj(object_id);

SQL >select * from obj where object_id<100;

Execution Plan

----------------------------------------------------------

Plan hash value: 342571587

-------------------------------------------------------------------------------------------------

| Id | Operation                  | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------

|   0| SELECT STATEMENT            |                   |    99 | 20493 |     4  (0)| 00:00:01 |

|   1|  TABLE ACCESS BY INDEX ROWID| OBJ              |   99 | 20493 |     4   (0)| 00:00:01 |

|*  2|   INDEX RANGE SCAN          | IND_OBJ         |    99 |      |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2- access("OBJECT_ID"<100)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

        18  consistent gets

         0  physical reads

         0  redo size

     10770  bytes sent via SQL*Net toclient

        586 bytes received via SQL*Net from client

         8  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

        99  rows processed

SQL >select *from obj where object_id=100;

Execution Plan

----------------------------------------------------------

Plan hash value: 342571587

-------------------------------------------------------------------------------------------------

| Id | Operation                   |Name              | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------

|   0| SELECT STATEMENT            |                   |     1 |  207 |     2   (0)| 00:00:01 |

|   1|  TABLE ACCESS BY INDEX ROWID| OBJ              |    1 |   207 |     2  (0)| 00:00:01 |

|*  2|   INDEX RANGE SCAN          | IND_OBJ         |    1 |       |     1  (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2- access("OBJECT_ID"=100)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

          0 db block gets

         4  consistent gets

         0  physical reads

         0  redo size

      1614  bytes sent via SQL*Net toclient

       520  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

SQL >select count(*) from obj where object_id<100;

Execution Plan

----------------------------------------------------------

Plan hash value: 3727096564

---------------------------------------------------------------------------------------

| Id | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0| SELECT STATEMENT  |                   |     1 |   13 |     2   (0)| 00:00:01 |

|   1|  SORT AGGREGATE   |                   |     1 |   13 |            |          |

|*  2|   INDEX RANGE SCAN| IND_OBJ     |    99|  1287 |     2  (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2- access("OBJECT_ID"<100)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

         2  consistent gets

         0  physical reads

         0  redo size

        526 bytes sent via SQL*Net to client

       520  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

SQL >select count(*) from obj where object_id=100;

Execution Plan

----------------------------------------------------------

Plan hash value: 3727096564

---------------------------------------------------------------------------------------

| Id | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0| SELECT STATEMENT  |                   |     1 |   13 |     1   (0)| 00:00:01 |

|   1|  SORT AGGREGATE   |                  |    1 |    13 |            |         |

|*  2|   INDEX RANGE SCAN| IND_OBJ         |     1|    13 |     1  (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2- access("OBJECT_ID"=100)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

         2  consistent gets

         0  physical reads

         0  redo size

       526  bytes sent via SQL*Net toclient

       520  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

SQL >select object_idfrom obj where object_id=100;

Execution Plan

----------------------------------------------------------

Plan hash value: 3193802408

----------------------------------------------------------------------------

| Id | Operation        | Name    | Rows | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0| SELECT STATEMENT |         |    99 | 1287 |     2   (0)| 00:00:01 |

|*  1|  INDEX RANGE SCAN| IND_OBJ|    99 | 1287 |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   1- access("OBJECT_ID"<100)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

         9  consistent gets

         0  physical reads

         0  redo size

      2141  bytes sent via SQL*Net toclient

       586  bytes received via SQL*Netfrom client

         8  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

        99  rows processed

--猜測索引使用範圍

--表記錄總數

SQL> select count(*) from obj;

COUNT(*)

----------

    74524

SQL> select *from obj where object_id<24248;

24247 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2584912024

---------------------------------------------------------------------------------------

| Id | Operation                   |Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0| SELECT STATEMENT            |         | 15856 |  3205K|  297   (1)| 00:00:04 |

|   1|  TABLE ACCESS BY INDEX ROWID| OBJ     | 15856 | 3205K|  297   (1)| 00:00:04 |

|*  2|   INDEX RANGE SCAN          | IND_OBJ | 15856|       |    44  (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2- access("OBJECT_ID"<24248)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         7  recursive calls

         0  db block gets

      3667  consistent gets

         0  physical reads

         0  redo size

   2702325  bytes sent via SQL*Net toclient

     18296  bytes received via SQL*Netfrom client

      1618  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

     24247  rows processed

SQL>select *from obj where object_id<24249;

24248 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 730912574

--------------------------------------------------------------------------

| Id | Operation         | Name |Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0| SELECT STATEMENT  |      | 15874 | 3208K|   297   (1)| 00:00:04 |

|*  1|  TABLE ACCESS FULL| OBJ  | 15874 |  3208K|  297   (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   1- filter("OBJECT_ID"<24249)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

      2662  consistent gets

         0  physical reads

         0  redo size

   1229061  bytes sent via SQL*Net toclient

     18296  bytes received via SQL*Netfrom client

      1618  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

     24248  rows processed

SQL> select round(24247/74524,6) fromdual;

ROUND(24247/74524,6)

--------------------

            .325358

SQL> select round(24248/74524,6) fromdual;

ROUND(24248/74524,6)

--------------------

            .325372

--求最大值（最小值）

SQL> select max(object_id) from obj;

Execution Plan

----------------------------------------------------------

Plan hash value: 1481376741

---------------------------------------------------------------------------------------

| Id | Operation                  |Name     | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0| SELECT STATEMENT           |          |    1 |    13 |     2  (0)| 00:00:01 |

|   1|  SORT AGGREGATE            |          |    1 |    13 |            |          |

|   2|   INDEX FULL SCAN (MIN/MAX)| IND_OBJ |     1|    13 |     2  (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

         2  consistent gets

         0  physical reads

         0  redo size

       534  bytes sent via SQL*Net toclient

       520  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

SQL> select min(object_id) from obj;

Execution Plan

----------------------------------------------------------

Plan hash value: 1481376741

---------------------------------------------------------------------------------------

| Id | Operation                  |Name     | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0| SELECT STATEMENT           |          |    1 |    13 |     2  (0)| 00:00:01 |

|   1|  SORT AGGREGATE            |          |    1 |    13 |            |          |

|   2|   INDEX FULL SCAN (MIN/MAX)| IND_OBJ |     1|    13 |     2  (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

         2  consistent gets

         0  physical reads

         0  redo size

       532  bytes sent via SQL*Net toclient

       520  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

3）建立複合索引

SQL >drop index ind_obj;

SQL >create index ind1_obj onobj(object_id,object_name);

SQL >select *from obj where object_id=100;

Execution Plan

----------------------------------------------------------

Plan hash value: 3929261980

----------------------------------------------------------------------------------------

| Id | Operation                   |Name     | Rows  | Bytes | Cost (%CPU)|Time     |

----------------------------------------------------------------------------------------

|   0| SELECT STATEMENT            |          |    1 |   207 |     3   (0)| 00:00:01 |

|   1|  TABLE ACCESS BY INDEX ROWID| OBJ      |     1 |  207 |     3   (0)| 00:00:01 |

|*  2|   INDEX RANGE SCAN          | IND1_OBJ|     1 |      |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2- access("OBJECT_ID"=100)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

         4  consistent gets

         0  physical reads

         0  redo size

      1614  bytes sent via SQL*Net toclient

       520  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

SQL >select object_id,object_name from obj where object_id<100;

Execution Plan

----------------------------------------------------------

Plan hash value: 3067097907

-----------------------------------------------------------------------------

| Id | Operation        | Name     | Rows | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0| SELECT STATEMENT |          |    99 | 7821 |     2   (0)| 00:00:01 |

|*  1|  INDEX RANGE SCAN| IND1_OBJ|    99 | 7821 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   1- access("OBJECT_ID"<100)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

         9  consistent gets

         0  physical reads

         0  redo size

      3051  bytes sent via SQL*Net toclient

       586  bytes received via SQL*Netfrom client

         8  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

        99  rows processed

SQL >select object_id,object_name from obj where object_id=100;

Execution Plan

----------------------------------------------------------

Plan hash value: 3067097907

-----------------------------------------------------------------------------

| Id | Operation        | Name     | Rows | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0| SELECT STATEMENT |          |     1 |   79 |     2   (0)| 00:00:01 |

|*  1|  INDEX RANGE SCAN| IND1_OBJ|     1 |    79 |    2   (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   1- access("OBJECT_ID"=100)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

         3  consistent gets

         0  physical reads

         0  redo size

       611  bytes sent via SQL*Net toclient

       520  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

SQL >select * from obj where object_name='OBJ';

Execution Plan

----------------------------------------------------------

Plan hash value: 730912574

--------------------------------------------------------------------------

| Id | Operation         | Name |Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0| SELECT STATEMENT  |      |   12 |  2484 |   297  (1)| 00:00:04 |

|*  1|  TABLE ACCESS FULL| OBJ  |    12 | 2484 |   297   (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   1- filter("OBJECT_NAME"='OBJ')

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

      1067  consistent gets

         0  physical reads

         0  redo size

      1738  bytes sent via SQL*Net toclient

        520 bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         2  rows processe

SQL >select object_id,object_name from obj where object_name='OBJ';

Execution Plan

----------------------------------------------------------

Plan hash value: 3807257211

---------------------------------------------------------------------------------

| Id | Operation            | Name     | Rows | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------

|   0| SELECT STATEMENT     |          |   12 |   948 |   121  (1)| 00:00:02 |

|*  1|  INDEX FAST FULL SCAN| IND1_OBJ |    12 |   948 |  121   (1)| 00:00:02 |

---------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   1- filter("OBJECT_NAME"='OBJ')

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

       429  consistent gets

         0  physical reads

         0  redo size

       665  bytes sent via SQL*Net toclient

       520  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         2  rows processed

4）複合索引的前後順序

SQL >drop index ind1_obj;

SQL >create index ind2_obj onobj(object_id,object_type);

SQL >create index ind3_obj on obj(object_type,object_id);

（1）等值查詢

SQL >select /*+index(obj,ind2_obj)*/* from obj where object_id=20 and object_type='TABLE';

Execution Plan

----------------------------------------------------------

Plan hash value: 25832809

----------------------------------------------------------------------------------------

| Id | Operation                   |Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0| SELECT STATEMENT            |          |    7 |  1449 |     2  (0)| 00:00:01 |

|   1|  TABLE ACCESS BY INDEX ROWID| OBJ      |    7 |  1449 |     2  (0)| 00:00:01 |

|*  2|   INDEX RANGE SCAN         | IND2_OBJ|     1 |       |    1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2- access("OBJECT_ID"=20 AND "OBJECT_TYPE"='TABLE')

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

         4  consistent gets

         0  physical reads

         0  redo size

      1607  bytes sent via SQL*Net toclient

       520  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

SQL >select /*+index(obj,ind3_obj)*/*from obj where object_id=20 and object_type='TABLE';

Execution Plan

----------------------------------------------------------

Plan hash value: 3023182639

----------------------------------------------------------------------------------------

| Id | Operation                   |Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0| SELECT STATEMENT            |          |    7 |  1449 |     2  (0)| 00:00:01 |

|   1|  TABLE ACCESS BY INDEX ROWID| OBJ      |    7 |  1449 |     2  (0)| 00:00:01 |

|*  2|   INDEX RANGE SCAN         | IND3_OBJ|     1 |       |    1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2- access("OBJECT_TYPE"='TABLE' AND "OBJECT_ID"=20)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

         4  consistent gets

         0  physical reads

         0  redo size

      1607  bytes sent via SQL*Net toclient

       520  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

SQL >select * from obj where object_id=20 and object_type='TABLE';

Execution Plan

----------------------------------------------------------

Plan hash value: 25832809

----------------------------------------------------------------------------------------

| Id | Operation                   |Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0| SELECT STATEMENT            |          |    7 |  1449 |     2  (0)| 00:00:01 |

|   1|  TABLE ACCESS BY INDEX ROWID| OBJ      |    7 |  1449 |     2  (0)| 00:00:01 |

|*  2|   INDEX RANGE SCAN         | IND2_OBJ|     1 |       |    1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2- access("OBJECT_ID"=20 AND "OBJECT_TYPE"='TABLE')

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

         4  consistent gets

         0  physical reads

         0  redo size

      1607  bytes sent via SQL*Net toclient

       520  bytes received via SQL*Netfrom client

         2  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

（2）範圍查詢（不等值）

A、複合索引的等值條件在後面

SQL >select /*+index(obj,ind2_obj)*/* from obj where object_id>=20000 and object_id<=50000 andobject_type='TABLE';

Execution Plan

----------------------------------------------------------

Plan hash value: 25832809

----------------------------------------------------------------------------------------

| Id | Operation                   |Name     | Rows  | Bytes | Cost (%CPU)|Time     |

----------------------------------------------------------------------------------------

|   0| SELECT STATEMENT            |          |    2 |   414 |    89   (0)| 00:00:02 |

|   1|  TABLE ACCESS BY INDEX ROWID| OBJ      |    2 |   414 |    89  (0)| 00:00:02 |

|*  2|   INDEX RANGE SCAN         | IND2_OBJ|    85 |       |   87   (0)| 00:00:02 |

----------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2- access("OBJECT_ID">=20000 AND "OBJECT_TYPE"='TABLE'AND

              "OBJECT_ID"<=50000)

      filter("OBJECT_TYPE"='TABLE')

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

       109  consistent gets

         0  physical reads

         0  redo size

      1343  bytes sent via SQL*Net toclient

       509  bytes received via SQL*Netfrom client

         1  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         0  rows processed

B、複合索引的等值條件在前面

SQL >select /*+index(obj,ind3_obj)*/* from obj where object_id>=20000 and object_id<=50000 andobject_type='TABLE';

Execution Plan

----------------------------------------------------------

Plan hash value: 3023182639

----------------------------------------------------------------------------------------

| Id | Operation                   |Name     | Rows  | Bytes | Cost (%CPU)|Time     |

----------------------------------------------------------------------------------------

|   0| SELECT STATEMENT            |          |    2 |   414 |     3   (0)| 00:00:01 |

|   1|  TABLE ACCESS BY INDEX ROWID| OBJ      |    2 |   414 |     3  (0)| 00:00:01 |

|*  2|   INDEX RANGE SCAN         | IND3_OBJ|     1 |       |    2   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2- access("OBJECT_TYPE"='TABLE' AND "OBJECT_ID">=20000AND

             "OBJECT_ID"<=50000)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

         2  consistent gets

         0  physical reads

         0  redo size

      1343  bytes sent via SQL*Net toclient

       509  bytes received via SQL*Netfrom client

         1  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

          0 sorts (disk)

         0  rows processed

C、自動選擇最優的複合索引

SQL >select * from obj where object_id>=20000 andobject_id<=50000 and object_type='TABLE';

Execution Plan

----------------------------------------------------------

Plan hash value: 3023182639

----------------------------------------------------------------------------------------

| Id | Operation                   |Name     | Rows  | Bytes | Cost (%CPU)|Time     |

----------------------------------------------------------------------------------------

|   0| SELECT STATEMENT            |          |    2 |   414 |     3   (0)| 00:00:01 |

|  1 | TABLE ACCESS BY INDEX ROWID| OBJ     |     2 |   414 |    3   (0)| 00:00:01 |

|*  2|   INDEX RANGE SCAN         | IND3_OBJ|     1 |       |    2   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

   2- access("OBJECT_TYPE"='TABLE' AND "OBJECT_ID">=20000AND

              "OBJECT_ID"<=50000)

Note

-----

   -dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

         2  consistent gets

         0  physical reads

         0  redo size

      1343  bytes sent via SQL*Net toclient

       509  bytes received via SQL*Netfrom client

         1  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

         0  rows processed

3.結論

1. ）表沒有索引時，不管是等值或範圍查詢都是全表掃描操作（TABLE ACCESS FULL），如做COUNT統計還會產生排序動作（SORT AGGREGATE）。
2. ）如果在查詢條件的列建立索引，等值或範圍都有可能使用索引範圍掃描操作（INDEX RANGE SCAN），注意是有可能，超過一定的範圍（猜測臨界值可能為35.24%左右），CBO認為全表掃描的COST成本更低時會選擇全表掃描，如果結果列不只是索引列時，還會伴隨著回表讀操作（TABLE ACCESS BY INDEX ROWID）。
3. ）求最大值或最小值的列如果是索引列，則使用索引全掃描（最小/最大）（INDEX FULL SCAN (MIN/MAX)），注意例外情況。
4. ）查詢條件為複合索引的第一列時，可能使用索引範圍掃描操作（INDEX RANGE SCAN），若結果列剛好為複合索引的列，將不會產生回表讀操作（TABLE ACCESS BY INDEX ROWID）；查詢條件為複合索引的第二列（或後面列）並且結果列也只有複合索引的列時，將使用索引快速全掃描操作（INDEX FAST FULL SCAN），否則則是全表掃描。
5. ）等值查詢的複合索引，不管索引列的前後順序都不影響查詢結果。
6. ）非等值（或範圍）查詢，查詢條件中的等值條件列放在前面，非等值條件列放在後面，否則效能差異很大。

注意：執行計劃中的Cost (%CPU) 及consistent gets的相關值。

以上實驗參考樑敬彬老師的《收穫不止ORALCE》一書。