Oracle fails to get the index result due to an error. oracle does not get the index.

Oracle fails to get the index result due to an error. oracle does not get the index.

There is a script that has been running for a long time and does not produce any results immediately after optimization. The original statement is as follows: SQL> explain plan for 2 select * 3 from crm_dg.tb_ba_channelstaff a, 4 crm_dg.tb_ba_subscription_hist B, 5 crm_dg.tb_cm_serv c 6 where. subs_id = B. subs_id 7 and B. serv_id = c. serv_id 8 and. create_date> = to_date ('20140901', 'yyyymmdd') 9 and c. acc_nbr = '000000'; Explained. elapsed: 00:00:00. 03SQL> @ getplan 'General, outline, starts 'enter value for plan type: PLAN_TABLE_OUTPUT ------------- Explain Plan hash value: 1257311340 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | average ------------------------------------------------------------------------------------ --------------------------- | 0 | select statement | 1 | 562 | 12 (0) | 00:00:01 | 1 | nested loops | 1 | 562 | 12 (0) | 00:00:01 | 2 | merge join cartesian | 2 | 716 | 8 (0) | 00:00:01 | 3 | table access by index rowid | PROD_INST | 1 | 273 | 4 (0) | 00:00:01 | * 4 | index range scan | IX_PROD_INST_NUM | 1 | 3 (0) | 00:00:01 | 5 | buffer sort | 2 | 170 | 4 (0) | 00:00:01 | 6 | TABLE ACCES S by index rowid | TB_BA_CHANNELSTAFF | 2 | 170 | 4 (0) | 00:00:01 | * 7 | index range scan | IDX_BA_CHANNELSTAFF_CRT_DATE | 2 | 2 (0) | 00:00:01 | * 8 | table access by index rowid | ORDER_ITEM_HIST | 1 | 204 | 2 (0) | 00:00:01 | * 9 | index unique scan | PKH_ORDER_ITEM | 1 | 1 (0) | 00:00:01 | average --------------------------------------------------------------------------------------------------------- ------ Predicate Information (identified by operation id): --------------------------------------------------- 4-access ("ACC_NBR" = '2016') 7-access ("". "CREATE_DATE"> = TO_DATE ('00:00:00 ', 'syyyy-mm-dd hh24: mi: ss') 8-filter ("SERV_ID" = "PROD_INST_ID ") 9-access ("". "SUBS_ID" = "ORDER_ITEM_ID ")...... getting segment size ...... OWNER SEGMENT_NAME SEGMENT_TYPE Size (Mb )---------------- ---- Zookeeper ---------- CRM_DG partition INDEX 602.0625CRM _ DG partition INDEX 1799.5625CRM _ DG PKH_ORDER_ITEM INDEX 6199CRM_DG PROD_INST TABLE 5126CRM_DG Partition TABLE partition ORDER_ITEM_HIST TABLE 487766 rows selected. elapsed: 00:00:01. 26 ...... getting table infomation ...... OWNER TABLE_NAME Size (Mb) par degree NUM_RO Ws glo stats gather time exceed ---------- --- -------------------- CRM_DG * PROD_INST 3958.84835 NO 1 15205690 YES 7.84770833CRM _ DG PROD_INST 3958.84835 NO 1 15205690 YES limit _ DG * limit 5265.49083 NO 1 64956086 YES 102.696563CRM _ DG TB_BA_CHANNELSTAFF 5265.49083 NO 1 64956086 YES 102.696563CRM _ DG * ORDER_ITEM_HI ST 40876.7086 NO 1 210109488 YES 10.20.532crm _ DG ORDER_ITEM_HIST 40876.7086 NO 1 210109488 YES 10.42605326 rows selected. elapsed: 00:00:01. 20 ...... getting index infomation ...... OWNER INDEX_NAME TABLE_NAME par uniquenes degree INDEX_TYPE LEAF_BLOCKS BLEVEL CLUSTERING_FACTOR -------------------- certificate ----------------------------------------------------------------------------------------------------------------------- ---- ---------- Adjust ---- CRM_DG limit no nonunique 1 NORMAL 84968 2 50669112 36.412511CRM _ DG limit PROD_INST no nonunique 1 NORMAL 37438 2 12501881 100CRM_DG PKH_ORDER_ITEM ORDER_ITEM_HIST no unique 1 NORMAL 399394 2 166506822 100 here c and table B is a view. The final result is only two records. If the returned data volume is small, nested loops can be considered as indexes. IDX_BA_CHANNELSTAFF_CRT_DATE is very selective, and nearly 1.8G is very large. It is very slow to scan an index for a single read. To avoid using IDX_BA_CHANNELSTAFF_CRT_DATE, the hint "no_index" is used here. oracle automatically selects the index of the associated column and uses the primary key index PK_CHANNELSTAFF_SUBS_ID to generate results instantly. The following are the optimized statements: SQL> explain plan for 2 select/* + leading (c, B) use_nl (c, B) no_index (a, IDX_BA_CHANNELSTAFF_CRT_DATE) */* 3 from crm_dg.tb_ba_channelstaff a, 4 crm_dg.tb_ba_subscription_hist B, 5 crm_dg.tb_cm_serv c 6 where. subs_id = B. subs_id 7 and B. serv_id = c. serv_id 8 and. create_date> = to_date ('20140901', 'yyyymmdd') 9 and c. acc_nbr = '000000'; Explained. elapsed: 00:00:00. 09SQL> @ getplan 'General, outline, starts 'enter value for plan type: PLAN_TABLE_OUTPUT partition Plan hash value: 3198218290 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 562 | 39 (0) | 00:00:01 | 1 | nested loops | 1 | 562 | 39 (0) | 00:00:01 | 2 | nested loops | 16 | 7632 | 18 (0) | 00:00:01 | 3 | table access by index rowid | PROD_INST | 1 | 273 | 4 (0) | 00:00:01 | * 4 | index range scan | IX_PROD_INST_NUM | 1 | 3 (0) | 00:00:01 | 5 | table access by index rowid | ORDER_ITEM_HIST | 16 | 3264 | 14 (0) | 00:00:01 | * 6 | index range scan | IXH_ORDERITEM_SERVID | 16 | 2 (0) | 00:00:01 | * 7 | table access by index rowid | TB_BA_CHANNELSTAFF | 1 | 85 | 2 (0) | 00:00:01 | * 8 | index unique scan | PK_CHANNELSTAFF_SUBS_ID | 1 | 1 (0) | 00:00:01 | descripredicate Information (identified by operation id ): ----------------------------------------------- 4-access ("ACC_NBR" = '000000') 6-access ("SERV_ID" = "PROD_INST_ID") 7-filter ("". "CREATE_DATE"> = TO_DATE ('00:00:00 ', 'syyyy-mm-dd hh24: mi: ss') 8-access ("". "SUBS_ID" = "ORDER_ITEM_ID") SQL>