索引特性之存储列值及ROWID

最新推荐文章于 2024-06-02 14:54:06 发布
最新推荐文章于 2024-06-02 14:54:06 发布
阅读量122 收藏
点赞数
文章标签： 数据库

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/429786/viewspace-776251/

cuiwu7783
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle中的rowid
02-24
ROWID是ORACLE中的一个重要的概念。用于定位数据库中一条记录的一个相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且...第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引
SQL Server数据库中伪列及伪列的含义详解
01-21
对于非聚集索引表，索引存储的是索引键值+RowId，这应该是一个常识，对此不作具体详细阐述。 这里主要是提到的RowId引起了一点思考。 那么，这个RowId是个什么玩意？能不能更加直观一点来看看RowId的信息？代表...
Index key值(索引列上的值)以及rowid
cuixie2370的博客
09-05 112
A Btree index sorts the keys into a binary tree and stores these keys together with the table’s ROWID...
MySQL索引
weixin_44237240的博客
10-14 102
索引（index）：帮助MySQL高校获取数据的数据结构，可以理解为索引就是排好序的快速查找的数据结构。 记录超过三百万数量级的查询性能就开始下降，也需要开始建立索引优化。 一、以下情况需要创建索引 主键自动建立唯一索引 频繁作为查询条件的字段应该创建索引 查询中与其他表关联的字段，外键关系建立索引 where条件里用不到的字段不创建索引 在高并发下倾向创建组合索引 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度 查询中统计或者分组字段 二、以下情况不需要创建索引 频繁更新的字段不
17.读书笔记收获不止Oracle之 索引存储列值
badman250的专栏
11-05 793
17.读书笔记收获不止Oracle之 索引存储列值 下面来看下索引的第二个特点，索引存储列值及rowid的特性。 1.  简单示例 SQL>create table t as select * from dba_objects; Create index idx1_object_id on t (object_id); Select count(*) from t;
聊聊oracle rowid与索引结构
岳麓丹枫
02-12 1864
ROWID定义 ROWID：数据库中行的全局唯一地址 对于数据中的每一行，rowid伪列返回行的地址。rowid值主要包含以下信息： 对象的数据对象编号 该行所在的数据文件中的数据块 该行中数据块的位置（第一行是0） 数据行所在的数据文件（第一个文件是1）。该文件编号是相对于表空间。 扩展ROWID 从Oracle 8i开始使用扩展rowid标识行物理地址 扩展rowid使...
oracle TABLE ACCESS BY INDEX ROWID 你不知道的索引回表-开发系列（三）
热门推荐
xiaohai798的专栏
11-11 3万+
TABLE ACCESS BY INDEX ROWID 你不知道的索引回表带来的低效率
Oracle查询语句中rownum与rowid的不同之处分析
09-10
主要介绍了Oracle查询语句中rownum与rowid的不同之处分析,需要的朋友可以参考下
列存储索引1：初识列存储索引
weixin_33717117的博客
11-17 169
     2012以后提供了一种不同于传统B树结构的索引类型，就是内存列存储索引。这种索引应用了一种基于列的存储模式，也是一种新的查询执行的批处理模式，并且为特定的负载提供了巨大的性能提升。它是如何构建？如何工作？又是为什么能对性能有如此大的提升，接下来我们用简明的描述和详尽的示例来解释说明。      那么列存储索引究竟是什么？大多数时候，列存储索引被描述作为一种数据仓库和数据报表的功能。事实...
详述ROWID的原理及其使用
Mike_ToUp
06-19 8244
ROWID基础概念在Oracle内部，每个数据表都有一个伪列ROWID，用于存放被称为ROWID的二进制值。每个ROWID代表了一行数据的存储地址。物理ROWID能够标识普通数据表中的一行信息。其中ROWID类型只能存储物理内容，而UROWID(universal rowid)类型可以存储物理，逻辑或外来(non-oracle)ROWID。物理ROWID(PhysicalRowid)可以让我们快速的
深入理解Oracle索引(3)：唯一索引和ROWID
数据库
02-03 400
索引都会包含rowid来定位到具体的位置，但是唯一索引不会用rowid来作为“索引键”的一部分 比如在列col1上建立非唯一索引，则其索引键是col1| rowid,这样索引键就唯一了，但唯一索引不会在列上加上rowid构成索引键 测试： SQL&gt; conn / as sysdba 已连接。 SQL&gt; create table t as select * from db...
全字段排序 VS rowid 排序
Thxxxxxx
05-09 1115
CREATE TABLE `t` ( `id` int(11) NOT NULL, `city` int(11) NOT NULL, `name` varchar(16) NOT NULL, `age` int(11) NOT NULL, `addr` varchar(128) DEFAULT NULL, PRIMARY KEY (`id`), KEY `city` (`city`...
oracle学习总结(一)---ROWID
hbcui1984的专栏
11-20 5360
oracle学习总结(一)---ROWID       搞oracle都会经常碰到rowid，本文是笔者根据网上各位大虾的文章，加上自己学习中的体会，总结而成。一.rowid简介        rowid就是唯一标志记录物理位置的一个id，在oracle 8版本以前，rowid由file#+block#+row#组成，占用6个bytes的空间，10 bit 的 file# ，22b
MySQL MRR 索引检索优化之 key/rowid 排序
误入歧途的蜗牛
09-21 433
DS-MRR mrr function calls Key-Ordered Retrieval 关键思想：如果 MRR 扫描类似于： tbl.key=value1 OR tbl.key=value2 OR ... OR tbl.key=valueN 那么将要查询的条件进行收集并且排序，例如： sort(value1, value2, .. valueN) 然后按照索引顺序进行索引检索，就会减少的索引页 page 的 fetch 操作。 Rowid-Ordered Retrieval
Oracle是否根据ROWID顺序读取记录|oracle sql方面
haiross的专栏
01-04 3926
oracle sql方面 (2013-04-28 14:58:21) 转载▼ 标签： sql oracle it 分类：oracle技能 SQL方面： 对字符串的“sum”——在Oracle中自定义聚集函数的例子：如果利用Oracle的自定义功能实现用户自定义的聚集函数，这个例子给出了一个实现字符串合并的例子。 MERGE语句奇怪的错误提
索引中是否包含ROWID
haiross的专栏
12-31 1766
veteransa 发表于:2007.10.29 10:08 ::分类: ( ORACLE学习笔记 ) ::阅读:(436次) :: 评论 (0) =========================================================== 索引中是否包含ROWID =====================================
chat4-Server端保存聊天消息到mysql
最新发布
gulanga5的博客
06-02 830
本文档描述了Server端接收到Client的消息并转发给所有客户端或私发给某个客户端同时将聊天消息保存到mysql服务端为当前客户端创建一个线程，此线程接收当前客户端的消息并转发给所有客户端或私发给某个客户端同时将聊天消息保存到mysql本文档主要总结了将聊天消息保存到mysql!!!),;import;import;import;/**用druid连接池来连接数据库的工具类*/DBUtil。
唯一索引模式和rowid模式
04-28
在唯一索引模式下，数据库会为每个索引键值创建一个唯一标识符，这个标识符可以作为查询时的索引值。 Rowid模式是一种物理存储结构，它为每个记录分配一个唯一的行标识符(rowid)，这个标识符可以作为表的主键，也...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值