select count的优化-凯发app官方网站

执行计划
--------------------------------------------------
| id | operation          | name | rows | cost (%cpu)| time     |
-------------------------------------------------------------------
|   0 | select statement   |      |     1 |   124 (4)| 00:00:02 |
|   1 | sort aggregate    |      |     1 |          |          |
|   2 |   table access full| t1   |   116k|   124 (4)| 00:00:02 |
-----------------------------------------------------
代价为124，运行的计划为全表扫描。
sql> delete t1 where deptno=10;

已删除24576行。

sql> commit;

提交完成。

sql> execute dbms_stats.gather_table_stats('scott','t1');

pl/sql 过程已成功完成。

sql> select count(*) from t1;

执行计划
-----------------------------------------------------
| id | operation          | name | rows | cost (%cpu)| time     |
-------------------------------------------------------------------
|   0 | select statement   |      |     1 |   123 (3)| 00:00:02 |
|   1 | sort aggregate    |      |     1 |          |          |
|   2 |   table access full| t1   | 90286 | 123 (3)| 00:00:02 |
-----------------------------------------------------
sql> --1.降低高水位
sql> alter table t1 move tablespace users;

表已更改。

sql> execute dbms_stats.gather_table_stats('scott','t1');

pl/sql 过程已成功完成。

sql> select count(*) from t1;

执行计划
-----------------------------------------------------
| id | operation          | name | rows |cost (%cpu)| time   |
-------------------------------------------------------------------
|   0 | select statement   |      |     1 |   102 (3)| 00:00:02 |
|   1 | sort aggregate    |      |     1 |          |          |
|   2 |   table access full| t1   | 90667 |   102 (3)| 00:00:02 |
-----------------------------------------------------
代价为102，降低了

sql> --2.修改pctfree
sql> alter table t1 pctfree 0;

表已更改。

sql> alter table t1 move tablespace users;

表已更改。

sql> execute dbms_stats.gather_table_stats('scott','t1');

pl/sql 过程已成功完成。

sql> select count(*) from t1;

执行计划
----------------------------------------------------------
plan hash value: 3724264953

-------------------------------------------------------------------
| id | operation          | name | rows | cost (%cpu)| time     |
-------------------------------------------------------------------
|   0 | select statement   |      |     1 |    92   (4)| 00:00:02 |
|   1 | sort aggregate    |      |     1 |            |          |
|   2 |   table access full| t1   | 91791 |    92   (4)| 00:00:02 |
-------------------------------------------------------------------
代价为92，降低了10%

sql> --3.参数db_file_multiblock_read_count=64
sql> --4.建立b*tree类型的索引
sql> create index i1 on t1(empno);

索引已创建。

sql> execute dbms_stats.gather_index_stats('scott','i1');

pl/sql 过程已成功完成。

sql> select count(*) from t1;

sql> alter table t1 modify(empno not null);

表已更改。

sql> select count(*) from t1;

执行计划
----------------------------------------------------------
plan hash value: 129980005

----------------------------------------------------------------------
| id | operation             | name | rows | cost (%cpu)| time     |
----------------------------------------------------------------------
|   0 | select statement      |      |     1 |    36   (6)| 00:00:01 |
|   1 | sort aggregate       |      |     1 |            |          |
|   2 |   index fast full scan| i1   | 91791 |    36   (6)| 00:00:01 |
----------------------------------------------------------------------
我们的索引起到了很大的作用！

sql> --5.使用并行查询的特性

强制全表扫描，屏蔽索引

sql> select /* full(t1) parallel(t1 2) */ count(*) from t1;

执行计划
----------------------------------------------------------------------------------------
| id | operation    | name     | rows | cost (%cpu)| time     |    tq |in-out| pq distrib |
----------------------------------------------------------------------------------------------
|   0 | select statement   |    |     1 |    51   (4)| 00:00:01 |    |      |    |
|   1 | sort aggregate        |          |     1 |    |    |        |      |    |
|   2 |   px coordinator       |   |       |            |          |        |    |    |
|   3 |    px send qc (random) | :tq10000 |     1 |    |   | q1,00 | p->s | qc(rand) |
|   4 |     sort aggregate     |          |     1 |    | | q1,00 | pcwp | |
|   5 |      px block iterator |          | 91791 |    51   (4)| 00:00:01 | q1,00 | pcwc| |
|   6 |       table access full| t1       | 91791 |    51   (4)| 00:00:01 | q1,00 | pcwp | |
-----------------------------------------------------------------------------------------------
并行度越高，代价越低

sql> alter table t1 parallel 4;

表已更改。
也可以通过使用表的属性来定义并行度，但是影响比较大，不如语句级别限制并行！

sql> select count(*) from t1;

执行计划
-----------------------------------------------------------------------------------------
| id | operation      | name     | rows | cost (%cpu)| time   |    tq |in-out| pqdistrib |
---------------------------------------------------------------------------------------------
|   0 | select statement   |      |     1 |    25   (0)| 00:00:01 |    |    |    |
|   1 | sort aggregate        |       |     1 |      |          |        |      |     |
|   2 |   px coordinator       |          |       |        |      |        |      |    |
|   3 |    px send qc (random) | :tq10000 |     1 |       |   | q1,00 | p->s | qc(rand) |
|   4 |     sort aggregate     |          |     1 |      |      | q1,00 | pcwp |    |
|   5 |      px block iterator |          | 91791 |    25   (0)| 00:00:01 | q1,00 | pcwc |   |
|   6 |       table access full| t1       | 91791 |    25   (0)| 00:00:01 | q1,00 | pcwp |   |
---------------------------------------------------------------------------------------------
代价为25，代价比两个的又少一半！

sql> --6.建立位图索引来避免全表扫描
sql> create bitmap index i2 on t1(deptno);

索引已创建。

sql> execute dbms_stats.gather_index_stats('scott','i2');

pl/sql 过程已成功完成。

sql> select count(*) from t1;

执行计划
----------------------------------------------------------
plan hash value: 3738977131

------------------------------------------------------------------------------
| id | operation                     | name | rows | cost (%cpu)| time     |
------------------------------------------------------------------------------
|   0 | select statement              |      |     1 |     4   (0)| 00:00:01 |
|   1 | sort aggregate               |      |     1 |            |          |
|   2 |   bitmap conversion count     |      | 91791 |     4   (0)| 00:00:01 |
|   3 |    bitmap index fast full scan| i2   |       |            |          |
------------------------------------------------------------------------------

sql> alter index i2 parallel 4;

索引已更改。

sql> select count(*) from t1;
执行计划
----------------------------------------------------------------------------------------
| id | operation       | name   | rows | cost (%cpu)| time   |   tq |in-out| pq distrib |
----------------------------------------------------------------------------------------------
|   0 | select statement     |      |     1 |     2   (0)| 00:00:01 |      | |       |
|   1 | sort aggregate   |   |     1 |            |          |        | |      |
|   2 |   px coordinator   |      |       |       |          |        | |    |
|   3 |    px send qc (random) | :tq10000 |     1 |    |          | q1,00 | p->s | qc (rand) |
|   4 |     sort aggregate |    |     1 |   |      | q1,00 | pcwp |        |
|   5 |      px block iterator | | 91791 |     2   (0)| 00:00:01 | q1,00 |pcwc |     |
|   6 |       bitmap conversion count |   | 91791 | 2   (0)| 00:00:01 | q1,00 |pcwp |    |
|   7 |        bitmap index fast full scan| i2    |   |     |     | q1,00 | pcwp |    |
--------------------------------------------------------------------------------------------
代价为2，原来为124，优化无止境呀！
只有你把握原理，一切尽在掌握！

阅读(1017) | 评论(0) | 转发(0) |

上一篇：oracle 查询记录最多的表

下一篇：日志记录模式(logging 、force logging 、nologging)

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们