Oracle fails to return an index due to an error.

Oracle fails to return an index due to an error.

There is an Oracle script that has been running for a long time without any results. After optimization, the results are instantly displayed. The original statement is as follows:
SQL> explain plan
2 select *
3 from crm_dg.tb_ba_channelstaff,
4 crm_dg.tb_ba_subscription_hist B,
5 crm_dg.tb_cm_serv c
6 where a. subs_id = B. subs_id
7 and B. serv_id = c. serv_id
8 and a. create_date> = to_date ('201312', 'yyyymmdd ')
9 and c. acc_nbr = '20140901 ';

Explained.

Elapsed: 00:00:00. 03
SQL> @ getplan
'General, outline, starts'

Enter value for plan type:

PLAN_TABLE_OUTPUT
Certificate -----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1257311340

Bytes ---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ---------------------------------------------------------------------------------------------------------------
| 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 access 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 |
Bytes ---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------

4-access ("ACC_NBR" = '000000 ')
7-access ("A". "CREATE_DATE"> = TO_DATE ('00:00:00 ', 'syyyy-mm-dd hh24: mi: ss '))
8-filter ("SERV_ID" = "PROD_INST_ID ")
9-access ("A". "SUBS_ID" = "ORDER_ITEM_ID ")

...... Getting segment size ......

OWNER SEGMENT_NAME SEGMENT_TYPE Size (Mb)
--------------------------------------------------------------------------------
CRM_DG IX_PROD_INST_NUM INDEX 602.0625
CRM_DG IDX_BA_CHANNELSTAFF_CRT_DATE INDEX 1799.5625
CRM_DG PKH_ORDER_ITEM INDEX 6199
CRM_DG PROD_INST TABLE 5126
CRM_DG TB_BA_CHANNELSTAFF TABLE 7390
CRM_DG ORDER_ITEM_HIST TABLE 48776

6 rows selected.

Elapsed: 00:00:01. 26
...... Getting table infomation ......

OWNER TABLE_NAME Size (Mb) par degree NUM_ROWS GLO STATS GATHER TIME
--------------------------------------------------------------------------------------------------------
CRM_DG * PROD_INST 3958.84835 NO 1 15205690 YES 7.84770833
CRM_DG PROD_INST 3958.84835 NO 1 15205690 YES 7.84770833
CRM_DG * TB_BA_CHANNELSTAFF 5265.49083 NO 1 64956086 YES 102.696563
CRM_DG TB_BA_CHANNELSTAFF 5265.49083 NO 1 64956086 YES 102.696563
CRM_DG * ORDER_ITEM_HIST 40876.7086 NO 1 210109488 YES 10.4260532
CRM_DG ORDER_ITEM_HIST 40876.7086 NO 1 210109488 YES 10.4260532

6 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
----------------------------------------------------------------------------------------------------------------------------------------------------------
CRM_DG IDX_BA_CHANNELSTAFF_CRT_DATE TB_BA_CHANNELSTAFF no nonunique 1 NORMAL 84968 2 50669112 36.412511
CRM_DG IX_PROD_INST_NUM PROD_INST no nonunique 1 NORMAL 37438 2 12501881 100
CRM_DG PKH_ORDER_ITEM ORDER_ITEM_HIST no unique 1 NORMAL 399394 2 166506822 100

Here, tables c and B are both views.
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
2 select/* + leading (c, B) use_nl (c, B) no_index (a, IDX_BA_CHANNELSTAFF_CRT_DATE )*/*
3 from crm_dg.tb_ba_channelstaff,
4 crm_dg.tb_ba_subscription_hist B,
5 crm_dg.tb_cm_serv c
6 where a. subs_id = B. subs_id
7 and B. serv_id = c. serv_id
8 and a. create_date> = to_date ('201312', 'yyyymmdd ')
9 and c. acc_nbr = '20140901 ';

Explained.

Elapsed: 00:00:00. 09
SQL> @ getplan
'General, outline, starts'

Enter value for plan type:

PLAN_TABLE_OUTPUT
Bytes ----------------------------------------------------------------------------------------------------------------------------

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 |
Bytes ---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------

4-access ("ACC_NBR" = '000000 ')
6-access ("SERV_ID" = "PROD_INST_ID ")
7-filter ("A". "CREATE_DATE"> = TO_DATE ('00:00:00 ', 'syyyy-mm-dd hh24: mi: ss '))
8-access ("A". "SUBS_ID" = "ORDER_ITEM_ID ")
SQL>