大表查询优化问题

显示全部楼层 · 2013-2-25 14:51:24

本帖最后由 kevindbbi 于 2013-1-10 10:07 编辑
有一个大表，20亿数据，按天做了分区，该表存储了3个月数据，每个分区平均2000万数据。TIME_ID是分区键，BS_ID是经常检索的，唯一值只有4万多个，都建了位图分区索引，
P_HZ_TOTAL 表结构：
名称
类型
--------------------
SUB_AREAVARCHAR2(20)
MSISDNVARCHAR2(11)
TIME_ID NUMBER
BS_ID NUMBER
CITYVARCHAR2(4)
查询语句：
SELECT/*+ PARALLEL */
SUB_AREA,bs_id,'2012110100 - 2012110723',CITY,COUNT(DISTINCT MSISDN)
FROMP_HZ_TOTAL
WHERE TIME_ID BETWEEN 2012110100 AND 2012110723
ANDBS_ID IN
(92472,
130385,
104500,
116199,
92746,
122008,
116076,
95698,
91430,
107230,
93153,
92102,
98432,
95861,
94476,
124668,
103880,
93058,
87175,
118884
)
GROUP BY SUB_AREA,Bs_id,CITY

执行计划：
Plan hash value: 3176810213
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes | Cost (%CPU)| Time | Pstart| Pstop |TQ|IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 205K|7225K|8287(41)| 00:01:40 | |

千问 · 2013-2-25 14:51:24

|
| 1 |PX COORDINATOR
|
| | |
|
| |

千问 · 2013-2-25 14:51:24

|
| 2 | PX SEND QC (RANDOM)
| :TQ10002
| 205K|7225K|8287(41)| 00:01:40 | | |Q1,02 | P->S | QC (RAND)|
| 3 |HASH GROUP BY
|
| 205K|7225K|8287(41)| 00:01:40 | | |Q1,02 | PCWP |
|
| 4 | PX RECEIVE
|
| 205K|7225K|8287(41)| 00:01:40 | | |Q1,02 | PCWP |
|
| 5 |PX SEND HASH
| :TQ10001
| 205K|7225K|8287(41)| 00:01:40 | | |Q1,01 | P->P | HASH |
| 6 | HASH GROUP BY
|
| 205K|7225K|8287(41)| 00:01:40 | | |Q1,01 | PCWP |
|
| 7 |VIEW
| VM_NWVW_1
| 205K|7225K|8287(41)| 00:01:40 | | |Q1,01 | PCWP |
|
| 8 | HASH GROUP BY
|
| 205K|9835K|8287(41)| 00:01:40 | | |Q1,01 | PCWP |
|
| 9 |
PX RECEIVE
|
| 205K|9835K|8287(41)| 00:01:40 | | |Q1,01 | PCWP |
|
|10 | PX SEND HASH
| :TQ10000
| 205K|9835K|8287(41)| 00:01:40 | | |Q1,00 | P->P | HASH |
|11 |
HASH GROUP BY
|
| 205K|9835K|8287(41)| 00:01:40 | | |Q1,00 | PCWP |
|
|12 |
PX PARTITION RANGE ITERATOR |
| 205K|9835K|8284(41)| 00:01:40 |33 |39 |Q1,00 | PCWC |
|
|13 |
TABLE ACCESS BY LOCAL INDEX ROWID| P_HZ_TOTAL | 205K|9835K|8284(41)| 00:01:40 |33 |39 |Q1,00 | PCWP |
|
|14 |
BITMAP CONVERSION TO ROWIDS |
| | |
|
| | |Q1,00 | PCWP |
|
|15 |
BITMAP AND
|
| | |
|
| | |Q1,00 | PCWP |
|
|16 |
BITMAP OR
|
| | |
|
| | |Q1,00 | PCWP |
|
|* 17 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 18 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 19 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 20 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 21 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 22 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 23 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 24 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 25 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 26 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 27 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 28 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 29 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 30 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 31 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 32 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 33 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 34 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 35 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|* 36 |
BITMAP INDEX SINGLE VALUE| B_P_HZ_TOTAL_BS_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
|37 |
BITMAP MERGE
|
| | |
|
| | |Q1,00 | PCWP |
|
|* 38 |
BITMAP INDEX RANGE SCAN| B_P_HZ_TOTAL_TIME_ID | | |
|
|33 |39 |Q1,00 | PCWP |
|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
17 - access("BS_ID"=87175)
18 - access("BS_ID"=91430)
19 - access("BS_ID"=92102)
20 - access("BS_ID"=92472)
21 - access("BS_ID"=92746)
22 - access("BS_ID"=93058)
23 - access("BS_ID"=93153)
24 - access("BS_ID"=94476)
25 - access("BS_ID"=95698)
26 - access("BS_ID"=95861)
27 - access("BS_ID"=98432)
28 - access("BS_ID"=103880)
29 - access("BS_ID"=104500)
30 - access("BS_ID"=107230)
31 - access("BS_ID"=116076)
32 - access("BS_ID"=116199)
33 - access("BS_ID"=118884)
34 - access("BS_ID"=122008)
35 - access("BS_ID"=124668)
36 - access("BS_ID"=130385)
38 - access("TIME_ID">=2012110100 AND "TIME_ID"<=2012110723)
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: skipped because of IO calibrate statistics are missing

说明：BS_ID是会新增的，而且查询的最大个数有可能是几百个，现在个数少速度还可以，但是多了话就不行了。
现在只做了时间范围分区，按天分区，粒度大吗？本来想把BS_ID也分区，但没想好如何分区，列表的话太多了，而且还有新增的；范围的话也不合适，因为会有离散的。
请大家看看还没有提高性能的空间，谢谢！

千问 · 2013-2-25 14:51:24

我觉得，对于你这个查询，这个位图索引就没什么用。因为group by里还包含了city。
直接建btree索引就可以了。另外对你这个查询，分区索引还不如全局索引。

千问 · 2013-2-25 14:51:24

udfrog 发表于 2013-1-10 10:16
我觉得，对于你这个查询，这个位图索引就没什么用。因为group by里还包含了city。
直接建btree索引就可以了 ...
在那个字段上建btree索引呢？city字段用处理吗？

千问 · 2013-2-25 14:51:24

就直接bsid上建btree。

千问 · 2013-2-25 14:51:24

udfrog 发表于 2013-1-10 10:44
就直接bsid上建btree。
不是很理解啊，btree索引不是用于主键吗?这样才能构造一颗平衡二叉树，而我这BS_ID重复率很大，4万个分布在20亿行里，似乎更适合位图索引吧？

千问 · 2013-2-25 14:51:24

支持2楼的，直接建btree索引就可以了。

千问 · 2013-2-25 14:51:24

不是很理解啊，btree索引不是用于主键吗?这样才能构造一颗平衡二叉树，而我这BS_ID重复率很大，4万个分布在20亿行里，似乎更适合位图索引吧？--求解释

千问 · 2013-2-25 14:51:24

能单个条件分开计算，再合并不？如果可以试试物化视图。