sql - Oracle SQL 查询优化(左连接、索引)
问题描述
在左连接集中添加表时,以下查询会卡住。如果排除最后一个连接,则查询运行顺利。但如果包含最后一个加入,则会卡住。我也尝试过 ALL_ROWS、FIRST_ROWS 优化器提示。cps_taskhis 对于“20190404”有大约 5500 个预期行。
笔记:
表 cpsmgt.cps_orderhis.orderid 是索引列。{屏幕截图 1}。
表 cpsmgt.cps_taskhis.orderid 也是一个索引列,它在最后一个左连接中被连接到 cpsmgt.cps_orderhis.orderid。{截图 2}
SELECT /*FIRST_ROWS*/
t.taskid task_no,
t.orderid order_id,
DECODE(t.priority,50,'Low',100,'Medium',200,'High','Other') task_priority,
t.taskname task_name,
TO_CHAR(t.createtime,'HH24') creation_hour,
t.createtime create_time,
t.completedtime completed_time,
( to_timestamp(t.completedtime,'yyyy-mm-dd hh24:mi:ss') - to_timestamp(t.createtime,'yyyy-mm-dd hh24:mi:ss') ) time_difference,
op1.user_name initiator_login,
rd1.name role_assigned_maker,
op2.user_name checker_login,
rd2.name role_assigned_checker,
t.taskstate task_state,
t.tasktype task_type,
t.description task_description,
ord_comment.commenttext, -- 2048 byte varchar2
sim.msisdn target_msisdn,
o.reason reason_of_task, -- 2048 byte varchar2
DECODE(t.bussinesscategory,'0','Business Operation','1','Transaction and Action','2','Financial','3','Manual Task','4','Bulk','5'
,'Configuration','Others:'
|| t.bussinesscategory) businesscategory_of_task
FROM
cpsmgt.cps_taskhis t --PARTITION ( SYS_P30212 ) t
LEFT JOIN cpsmgt.cps_operator op1 ON op1.operator_id = t.createid
LEFT JOIN cpsmgt.cps_operator op2 ON op2.operator_id = t.ownerid
LEFT JOIN cpsmgt.cps_role_operator ro1 ON op1.operator_id = ro1.operator_id
AND op1.status <> '06'
LEFT JOIN cpssys.cps_role_def rd1 ON rd1.role_id = ro1.role_id
AND rd1.status = '30'
LEFT JOIN cpsmgt.cps_role_operator ro2 ON op2.operator_id = ro2.operator_id
AND op2.status <> '06'
LEFT JOIN cpssys.cps_role_def rd2 ON rd2.role_id = ro2.role_id
AND rd2.status = '30'
LEFT JOIN cpsmgt.cps_order_comment ord_comment ON t.orderid = ord_comment.orderid
LEFT JOIN cpsmgt.cps_sim_device sim ON t.on_identity_id = sim.identity_id
LEFT JOIN cpsmgt.cps_orderhis o ON t.orderid = o.orderid
WHERE
t.createtime BETWEEN TO_DATE(TO_CHAR(20190404)
|| ' 00:00:00','yyyy-mm-dd hh24:mi:ss') AND TO_DATE(TO_CHAR(20190404)
|| ' 23:59:59','yyyy-mm-dd hh24:mi:ss')
AND t.procdefid IN (
'IC_EditP2PMSISDNWorkflow',
'IC_ResetCustomerPinWorkflow',
'TC_TransactionConfirmWorkflow',
'IC_ChangeCustomerProductWorkflow',
'IC_EditG2PMSISDNWorkflow',
'IC_ChangeCustomerIdentityStatusWorkflow',
'TC_CancelRemittanceWorkflow',
'IC_ChangeCustomerMSISDNWorkflow',
'IC_ResetOrgOperatorPINWorkflow',
'IC_MigrateCustTrustL1Workflow',
'IC_MigrateCustTrustL2Workflow',
'IC_ChangeCustomerIdentityKYCWorkflow',
'IC_UnblockPaymentTransactionWorkflow',
'IC_ResetOrgOperatorPasswordWorkflow'
)
AND rd1.name IN (
'Complaints (Maker)',
'MFS 1344 Help Line',
'MFS 4444 Helpline',
'Operator user(back end user) maker',
'Complaints Checker',
'Operator user(back end user) completer'
)
AND rd2.name IN (
'Complaints (Maker)',
'MFS 1344 Help Line',
'MFS 4444 Helpline',
'Operator user(back end user) maker',
'Complaints Checker',
'Operator user(back end user) completer'
);
查询解释计划(SQL Developer):(将文件保存为html并在浏览器中打开)
https://drive.google.com/open?id=1KBnhETTzgNLRUh7djyfCN-Wo5QxPZTfb
重要说明:我正在尝试将输出插入临时表。相反,如果我使用 create table as 将此数据插入到新表中。相同的查询工作正常:-(
-- previous columns
(
SELECT
o.reason
FROM
cpsmgt.cps_orderhis o
WHERE
o.orderid = t.orderid
) reason_of_task,
-- rest of the query.
解决方案
您是否尝试过以下提示?
/*+ INDEX(o IDX_CPS_ORDERHIS_3) USE_NL(o t)*/
可能 OPTIMIZER 没有选择正确的索引,而选择了 USE_HASH 作为执行计划。
推荐阅读
- javascript - 强制表格导航向下行然后跨到下一列
- c# - C# FireStore,从文档中获取值
- powerbi - 两个运行总查询之间的差异
- android - TextView 在旧文本之上覆盖新文本,而不是替换它。(科特林 - 安卓)
- google-sheets - 从不断增长的范围中检索月份(Google 表格)
- typescript - 如何使用 Jest 模拟 Es6 类?
- linux - 如何在 Linux 上使用 mmap 系统调用解决我在此 x86-64 汇编代码中遇到的 EINVAL 和 EPERM 错误
- apache-spark - 超出 GC 开销限制 - Databricks 内存不足
- ruby - 抓取缺少 url / ruby 的网站
- r - 如何存储 var(data) 的结果(方差函数)