根据查询计划优化查询语句
来源:互联网 发布:怎么加入淘宝嘉年华 编辑:程序博客网 时间:2024/06/05 14:54
一般在oracle的查询语句中,如果碰到两个表进行关联,在使用基于规则的优化程序执行检索时,基本的一个思路是使用中间结果较少的表做driving table.但也有特例.
环境描述:
表PCR中有2w条数据记录,其中符合条件pcr.channel_id=’31’的记录条数不足2000,字段party_id, channel_id上建有联合索引.
表OI上的记录行数超过1000w条,其中符合条件ORDER_DATE_ID BETWEEN '20080801' AND '20080803'的记录条数约5W条,而oi表的distributor_id上不具有单据索引,且该列选择性较差,不适合建立索引.
考虑下面的查询.
SELECT OI.ORDER_DATE_ID,
OI.DISTRIBUTOR_ID,
OI.PRODUCT_ID,
SUM(OI.QUANTITY),
SUM(OI.AMOUNT)
FROM ODS.CR_PARTY_CHANNEL_RELATIONSHIP PCR,
ODS.CR_ORDER_INFO OI
WHERE OI.DISTRIBUTOR_ID = PCR.PARTY_ID
AND PCR.CHANNEL_ID = '31'
AND OI.ORDER_TYPE_ID IN ('SALES_ORDER', 'SALES_RETURN_ORDER')
AND OI.ORDER_DATE_ID BETWEEN '20080801' AND '20080803'
GROUP BY OI.ORDER_DATE_ID, OI.DISTRIBUTOR_ID, OI.PRODUCT_ID;
Oracle给出的查询执行计划如下:
SELECT STATEMENT, GOAL = CHOOSE 326 2 188
SORT GROUP BY 326 2 188
TABLE ACCESS BY INDEX ROWID ODS CR_ORDER_INFO 318 2 100
NESTED LOOPS 320 2 188
TABLE ACCESS FULL ODS CR_PARTY_CHANNEL_RELATIONSHIP 2 1 44
INDEX RANGE SCAN ODS IDX_CR_ORDER_INFO_X1 38 8371
可以看出,oracle自动选择结果较小的表pcr作为driving table.但这种情况下由于oi的distribution_id上不具有索引(即使具有索引,也不能使用,因为day_id的索引选择性更高且更有效).索引导致两个关联表做nest loops时会遍历表oi产生的中间结果集,降低了查询执行的效率.
结合表的特点和索引结构,我们认为,优化查询效率的关键步骤是使用oi表作为驱动表,这样在两个表进行关联的时候可以使用pcr上的索引.并且由于我们的最终结果中不包含pcr表的数据,所以可以只扫描pcr的索引数据,而避免对表pcr的实际记录内容进行查询.
最终我们给出的查询语句如下.
SELECT OI.ORDER_DATE_ID,
OI.DISTRIBUTOR_ID,
OI.PRODUCT_ID,
SUM(OI.QUANTITY),
SUM(OI.AMOUNT)
FROM ODS.CR_ORDER_INFO OI
WHERE EXISTS
(SELECT /*+index(PCR IDX_CR_P_C_RELATIONSHIP)*/
1
FROM ODS.CR_PARTY_CHANNEL_RELATIONSHIP PCR
WHERE OI.DISTRIBUTOR_ID = PCR.PARTY_ID
AND PCR.CHANNEL_ID = '31')
AND OI.ORDER_TYPE_ID IN ('SALES_ORDER', 'SALES_RETURN_ORDER')
AND OI.ORDER_DATE_ID BETWEEN '20080801' AND '20080803'
GROUP BY OI.ORDER_DATE_ID, OI.DISTRIBUTOR_ID, OI.PRODUCT_ID
执行计划为:
SELECT STATEMENT, GOAL = CHOOSE 353 2 188
SORT GROUP BY 353 2 188
HASH JOIN SEMI 347 2 188
TABLE ACCESS BY INDEX ROWID ODS CR_ORDER_INFO 319 3348 167400
INDEX RANGE SCAN ODS IDX_CR_ORDER_INFO_X1 39 8371
INDEX FULL SCAN ODS IDX_CR_P_C_RELATIONSHIP 26 1 44
本例中,优化前后数据的执行时间从60秒减少到3秒,取得了较好的效果.
- 根据查询计划优化查询语句
- 根据查询计划优化查询语句
- 优化SQL查询:如何写出高性能SQL语句1、首先要搞明白什么叫执行计划?执行计划是数据库根据SQL
- SQL语句执行计划查询
- SQL 查询语句优化
- mysql 语句优化查询
- sql查询语句优化
- SQL语句查询优化
- sql查询语句优化
- oracle查询语句优化
- SQL语句优化查询
- mysql查询语句优化
- MYSQL查询语句优化
- SQL查询语句优化
- sql查询语句优化
- MYSQL查询语句优化
- sql查询语句优化
- MYSQL查询语句优化
- 嵌入式 Open SSL 常用函数——签名与验证
- 嵌入式 OpenSSL 常用函数——Base64编码及解码和证书操作
- 嵌入式 mysql的交互原则
- 嵌入式 关于setsockopt函数的详解
- 嵌入式 mysql结果集的返回与处理
- 根据查询计划优化查询语句
- 嵌入式 Linux下mysql的基本使用方法
- 嵌入式 编译时候头文件和库的连接详解
- 嵌入式 如何更好的封装函数的接口问题
- [LeetCode]Binary Tree Postorder Traversal,解题报告
- 嵌入式 ubuntu12.04在线安装mysql服务器以及启动和停止MySQL服
- Java中线程安全的单例模式
- 嵌入式 GDB调试示例过程
- 基于visual Studio2013解决C语言竞赛题之0204实数求值