17.读书笔记收获不止Oracle之 索引存储列值

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;                                    

 

  COUNT(*)

----------

     91717

 

           表的情况和索引的情况的差别在于表把整行的记录依次放进BLOCK形成DATA的BLOCK，而索引是把所在列的记录排序后依次放进BLOCK里面形成INDEX_BLOCK。在没有索引的情况下，DATA BLOCK中可以统计出表记录数，INDEX BLOCK也可以的。

           不过INDEX BLOCK里存放的值是表特定的索引列，容纳空间要比存放整行也就是所有列的DATA BLOCK要少得多。用索引一定会高效。

2.  简单示例二

SQL>set autotrace on

SQL> set linesize 1000

Set timing on

Select count(*) from t;

 

 COUNT(*)

----------

    91717

 

Elapsed: 00:00:00.26

 

Execution Plan

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

Plan hash value: 2966233522

 

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

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

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

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

|   1|  SORT AGGREGATE    |     |   1 |           |  |

|   2|   TABLE ACCESS FULL| T       | 91717 |   429  (1)| 00:00:01 |

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

 

 

Statistics

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

            33 recursive calls

             0  dbblock gets

      1564  consistent gets

      1539  physical reads

             0  redosize

           544  bytes sent via SQL*Net to client

           551  bytes received via SQL*Net from client

             2 SQL*Net roundtrips to/from client

             3 sorts (memory)

             0 sorts (disk)

             1  rowsprocessed

 

发现还是使用了全表扫描，这是因为索引列有空的记录，不能准确统计表记录数。

继续查找：

SQL> select count(*) from t where object_id is not null;

 

 COUNT(*)

----------

    91717

 

Elapsed: 00:00:00.05

 

Execution Plan

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

Plan hash value: 1296839119

 

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

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

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

|   0| SELECT STATEMENT      |              |    1 |     5 |    57  (0)| 00:00:01 |

|   1|  SORT AGGREGATE       |            |    1 |     5 |          |               |

|*  2|   INDEX FAST FULL SCAN| IDX1_OBJECT_ID| 91717 |   447K|    57     (0)| 00:00:01 |

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

 

Predicate Information (identified byoperation id):

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

 

   2- filter("OBJECT_ID" IS NOT NULL)

 

 

Statistics

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

             1 recursive calls

             0  dbblock gets

           211  consistent gets

           203  physical reads

             0  redosize

           544  bytes sent via SQL*Net to client

           551  bytes received via SQL*Net from client

             2 SQL*Net roundtrips to/from client

             0 sorts (memory)

             0 sorts (disk)

             1  rowsprocessed

将此列为非空后，走的是索引。

也可以修改OBJECT_ID列的属性，修改为不允许为空

SQL> alter table t modify object_id not null;

继续查询：

select count(*) from t ;

 COUNT(*)

----------

    91717

 

Elapsed: 00:00:00.01

 

Execution Plan

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

Plan hash value: 1296839119

 

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

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

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

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

|   1|  SORT AGGREGATE       |            |    1 |        |                |

|   2|   INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 91717|    57    (0)| 00:00:01 |

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

 

 

Statistics

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

             8 recursive calls

             0  dbblock gets

           224  consistent gets

             0 physical reads

             0  redosize

           544  bytes sent via SQL*Net to client

           551  bytes received via SQL*Net from client

             2 SQL*Net roundtrips to/from client

             2 sorts (memory)

             0 sorts (disk)

             1  rowsprocessed

也是走索引了。

此外还可以修改该列为主键也可以同样实现走索引。

 

3.  简单示例三

SQL> drop table t purge;

SQL> alter table t add constraintpk1_object_id primary key (OBJECT_ID);

SQL> set autotrace on

SQL> set linesize 1000

SQL> set timing on

SQL> select count(*) from t;

 

 COUNT(*)

----------

    91717

 

Elapsed: 00:00:00.06

 

Execution Plan

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

Plan hash value: 1604907147

 

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

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

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

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

|   1|  SORT AGGREGATE       |           |    1 |        |                 |

|   2|   INDEX FAST FULL SCAN| PK1_OBJECT_ID | 91717|    53      (0)|00:00:01 |

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

 

 

Statistics

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

            12 recursive calls

             0  dbblock gets

           213  consistent gets

           191  physical reads

             0  redosize

           544  bytes sent via SQL*Net to client

           551  bytes received via SQL*Net from client

             2 SQL*Net roundtrips to/from client

             2 sorts (memory)

             0 sorts (disk)

             1  rowsprocessed

 

           在主键上建的索引，然后在使用count(*)之后也是使用走索引。