25.读书笔记收获不止Oracle之 位图索引

来源:互联网 发布:淘宝客服找不到人 编辑:程序博客网 时间:2024/03/28 18:54

25.读书笔记收获不止Oracle之 位图索引

接下去来看下位图索引。

比较普通索引和位图索引的性能差异。

Drop table t purge;

Create table t as select * fromdba_objects;

SQL> Insert into t select * from t;

90945 rows created.

SQL> Insert into t select * from t;

181890 rows created.

SQL> Insert into t select * from t;

363780 rows created.

SQL> Insert into t select * from t;

727560 rows created.

SQL>commit;

Set autotrace on

Set linesize 1000

Select count(*) from t;

 COUNT(*)

----------

  1455120

 

 

Execution Plan

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

Plan hash value: 2966233522

 

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

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

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

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

|   1|  SORT AGGREGATE    |     |   1 |           |  |

|   2|   TABLE ACCESS FULL| T       | 90945 |   426  (1)| 00:00:01 |

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

 

 

Statistics

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

             2 recursive calls

             1  dbblock gets

     35408  consistent gets

             0 physical reads

    877864  redo size

           545  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

1   创建B树索引

进行了全表扫描。然后在object_id列建索引,并设置该列属性为非空,执行COUNT(*)执行计划及性能。

SQL> create index idx_t_obj on t(object_id);

Index created.

SQL> alter table T modify object_id notnull;

Table altered.

SQL> select count(*) from t;

 

 COUNT(*)

----------

  1455120

 

 

Execution Plan

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

Plan hash value: 2966233522

 

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

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

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

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

|   1|  SORT AGGREGATE    |     |   1 |           |  |

|   2|   TABLE ACCESS FULL| T       | 90945 |   426  (1)| 00:00:01 |

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

 

 

Statistics

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

             8 recursive calls

             0  dbblock gets

     24402  consistent gets

             0 physical reads

             0  redosize

           545  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)

2      rows processed

 

还是走的全表扫描,因为回表造的消耗要比走索引很多,所以都的是全表扫描。

强制索引看下如下:

SQL> select /*+INDEX(t,idx_t_obj)*/ *from t;

 

1455120 rows selected.

 

 

Execution Plan

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

Plan hash value: 3019848943

 

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

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

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

|   0| SELECT STATEMENT                    |                   |90945 |     9M|  1458K (1)| 00:00:57 |

|   1|  TABLE ACCESS BY INDEX ROWID BATCHED| T  | 90945 |     9M| 1458K  (1)| 00:00:57 |

|   2|   INDEX FULL SCAN                   | IDX_T_OBJ | 90945 |       | 3234   (1)| 00:00:01 |

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

 

 

Statistics

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

             1 recursive calls

             0  dbblock gets

   1555143  consistent gets

      3235  physical reads

             0  redosize

 194065366  bytes sent via SQL*Netto client

   1067628  bytes received viaSQL*Net from client

     97009  SQL*Net roundtrips to/fromclient

             0 sorts (memory)

             0 sorts (disk)

   1455120  rows processed

3   创建位图

SQL> create bitmap indexidx_bitm_t_status on t(status);

SQL> select count(*) from t;

 

 COUNT(*)

----------

  1455120

 

 

Execution Plan

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

Plan hash value: 4272013625

 

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

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

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

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

|   1|  SORT AGGREGATE          |               |   1 |           |  |

|   2|   BITMAP CONVERSION COUNT     |                 | 90945 |   32   (0)| 00:00:01 |

|   3|    BITMAP INDEX FAST FULL SCAN|IDX_BITM_T_STATUS |        |          |   |

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

 

 

Statistics

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

             1 recursive calls

             0  dbblock gets

            42  consistent gets

            36  physical reads

             0  redosize

           545  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

不管B树索引怎么样,位图索引代价小了近百倍。

COUNT(*)性能,比不同的B树索引高很多。

当然,只有最合适的技术没有最高级的技术。

 

阅读全文
0 0
原创粉丝点击