ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引特性之存储列值及ROWID

索引特性之存储列值及ROWID

原创 Linux操作系统 作者:wailon 时间:2013-11-10 21:24:00 0 删除 编辑

1、数据准备

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》一书。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/429786/viewspace-776251/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-11-08

  • 博文量
    51
  • 访问量
    290811