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

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

33recursive 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

2SQL*Net roundtrips to/from client

3sorts (memory)

0sorts (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

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

1recursive 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

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (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

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

8recursive calls

0 dbblock gets

224 consistent gets

0physical reads

0 redosize

544 bytes sent via SQL*Net to client

551 bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

2sorts (memory)

0sorts (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

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

12recursive 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

2SQL*Net roundtrips to/from client

2sorts (memory)

0sorts (disk)

1 rowsprocessed

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!