本文通过对比测试展示了在Oracle数据库中，如何通过创建并利用分区索引来显著提高查询效率。通过对表ods.ods_cust_owe_m创建本地分区索引，并进行表分析后，原本耗时181秒的查询缩短至仅0.38秒。
欠费表 ods.ods_cust_owe_m 账月一级分区，地市二级分区 ，每个分区2千万的数据
沃商务基础表 edw_m.edw_m_wo_busi_serv_m 账月一级分区，地市二级分区 ，分区月实际数据暂时不详，测试数据为40条
测试平台信息
服务器
数据库 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
SQL> show sga
Total System Global Area 2.0972E+10 bytes --19.531697G
Fixed Size 2121472 bytes
Variable Size 1.3237E+10 bytes --12.3279169G
Database Buffers 7717519360 bytes --7.1875G
Redo Buffers 14655488 bytes
测试语句：
SELECT COUNT(*) from edw_m.edw_m_wo_busi_serv_m A, ods.ods_cust_owe_m B
WHERE A.SERV_ID=B.SERV_ID
AND B.acct_month='201105' AND B.area_no='2101';
测试场景1
ods.ods_cust_owe_m 无分区索引
edw_m.edw_m_wo_busi_serv_m 无分区索引
准备1 查看数据库中buffer header数量
SQL> select count(*) from v$bh;
COUNT(*)
----------
899024
准备2 查看当前空闲buffer header数量
SQL> select count(*) from x$bh where state=0; --state=0 is free
COUNT(*)
----------
0
准备3 清空所有buffer header后空闲buffer header数量
SQL> alter system flush buffer_cache;
SQL> select count(*) from x$bh where state=0;
COUNT(*)
----------
898972
执行查询语句
SQL> SELECT COUNT(*) from edw_m.edw_m_wo_busi_serv_m A, ods.ods_cust_owe_m B
2 WHERE A.SERV_ID=B.SERV_ID
3 AND B.acct_month='201105' AND B.area_no='2101';
COUNT(*)
----------
65
已用时间: 00: 03: 01.34
执行计划
----------------------------------------------------------
--------------------------------------------------------------------------------
----------------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 5
(20)| | |
| 1 | SORT AGGREGATE | | 1 | 34 |
| | |
| 2 | HASH JOIN | | 1 | 34 | 5
(20)| | |
| 3 | PARTITION RANGE SINGLE| | 1 | 22 | 2
(0)| 37 | 37 |
| 4 | PARTITION LIST SINGLE| | 1 | 22 | 2
(0)| 2 | 2 |
| 5 | TABLE ACCESS FULL | ODS_CUST_OWE_M | 1 | 22 | 2
(0)| 542 | 542 |
| 6 | TABLE ACCESS FULL | EDW_M_WO_BUSI_SERV_M | 40 | 480 | 2
(0)| | |
--------------------------------------------------------------------------------
----------------------
Note
-----
- 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
121 recursive calls
0 db block gets
105861 consistent gets
108442 physical reads
0 redo size
225 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from x$bh where state=0;
COUNT(*)
----------
841487
测试场景2.1
ods.ods_cust_owe_m 有分区索引
edw_m.edw_m_wo_busi_serv_m 无分区索引
在表ods.ods_cust_owe_m 上创建本地分区索引
SQL> SELECT COUNT(*) from edw_m.edw_m_wo_busi_serv_m A, ods.ods_cust_owe_m B
2 WHERE A.SERV_ID=B.SERV_ID
3 AND B.acct_month='201105' AND B.area_no='2101';
COUNT(*)
----------
65
已用时间: 00: 01: 56.26
执行计划
----------------------------------------------------------
--------------------------------------------------------------------------------
----------------------
| Id | Operation | Name | Rows | Bytes | Cost (
%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 5
(20)| | |
| 1 | SORT AGGREGATE | | 1 | 34 |
| | |
| 2 | HASH JOIN | | 1 | 34 | 5
(20)| | |
| 3 | PARTITION RANGE SINGLE| | 1 | 22 | 2
(0)| 37 | 37 |
| 4 | PARTITION LIST SINGLE| | 1 | 22 | 2
(0)| 2 | 2 |
| 5 | TABLE ACCESS FULL | ODS_CUST_OWE_M | 1 | 22 | 2
(0)| 542 | 542 |
| 6 | TABLE ACCESS FULL | EDW_M_WO_BUSI_SERV_M | 40 | 480 | 2
(0)| | |
--------------------------------------------------------------------------------
----------------------
Note
-----
- 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
121 recursive calls
0 db block gets
105861 consistent gets
108439 physical reads
0 redo size
208 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
与场景1相比 执行计划完全没有变化没有走索引，应该是我创建索引后没有做表分析的原因。
测试场景2.2
ods.ods_cust_owe_m 有分区索引
edw_m.edw_m_wo_busi_serv_m 无分区索引
在表ods.ods_cust_owe_m 上创建本地分区索引
对表ods.ods_cust_owe_m收集统计信息
ANALYZE TABLE ods.ods_cust_owe_m COMPUTE STATISTICS;
对于分区表最好使用dbms_stats包进行统计信息收集，由于当前测试表只有一个分区有数据所以使用analyze也不影响统计数据的准确性。
SQL> ANALYZE TABLE ods.ods_cust_owe_m COMPUTE STATISTICS;
表已分析。
已用时间: 00: 17: 40.30
SQL> SELECT COUNT(*) from edw_m.edw_m_wo_busi_serv_m A, ods.ods_cust_owe_m B
2 WHERE A.SERV_ID=B.SERV_ID
3 AND B.acct_month='201105' AND B.area_no='2101';
COUNT(*)
----------
65
已用时间: 00: 00: 00.23
执行计划
----------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------
| Id | Operation | Name | Rows | Byte
s | Cost (%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------
--------------------------------
| 0 | SELECT STATEMENT | | 1 | 3
4 | 2 (0)| | |
| 1 | SORT AGGREGATE | | 1 | 3
4 | | | |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| ODS_CUST_OWE_M | 1 | 2
2 | 0 (0)| 542 | 542 |
| 3 | NESTED LOOPS | | 1 | 3
4 | 2 (0)| | |
| 4 | TABLE ACCESS FULL | EDW_M_WO_BUSI_SERV_M | 40 | 48
0 | 2 (0)| | |
| 5 | PARTITION RANGE SINGLE | | 1 |
| 0 (0)| 37 | 37 |
| 6 | PARTITION LIST SINGLE | | 1 |
| 0 (0)| 2 | 2 |
| 7 | INDEX RANGE SCAN | IDX_SERVID | 1 |
| 0 (0)| 542 | 542 |
--------------------------------------------------------------------------------
--------------------------------
Note
-----
- 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
154 consistent gets
38 physical reads
0 redo size
224 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
对比分析
| 无索引 | 有索引 | |
| 物理读数据块数量 | 108442 | 38 |
| 语句执行时间 | 00: 03: 01.34即 181.57s | 00: 00: 00.23 即0.38s |
| 本地索引占用空间 | 0 | 13722*32/1024＝428.8125M blevel=2 leaf_blocks=13722 |
加了索引查询效率增加的还是相当明显的
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
