predicate 列存储索引扫描_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(*)之后也是使用走索引。