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(*)之后也是使用走索引。
