oracle 左关联索引,sql - Oracle SQL查询优化（左连接，索引） - SO中文参考 - www.soinside.com...

在左连接集中添加表时，以下查询会卡住。如果排除最后一个连接，则查询将平滑运行。但如果包含最后一次加入，则会卡住我也尝试了ALL_ROWS，FIRST_ROWS优化器提示。对于'20190404'，cps_taskhis有大约5500个预期行。

笔记：

表cpsmgt.cps_orderhis.orderid是索引列。 {screenshot 1}。

表cpsmgt.cps_taskhis.orderid也是一个索引列，它在最后一个左连接中连接到cpsmgt.cps_orderhis.orderid。 {screenshot 2}

截屏1：

截屏2： SQL查询

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并在浏览器中打开)

重要说明：我正在尝试将输出插入临时表。相反，如果我使用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.