Oracle go wrong index no results

There was a script that ran for a long time without results, and the results were instantly optimized. 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_s Ubscription_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 (' 20150201 ', ' YYYYMMDD ') 9 and C.ACC_NBR = ' 1532292 6784 '; explained.elapsed:00:00:00.03sql> @getplan ' general,outline,starts ' Enter value for plan Type:plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- ----------------Plan Hash value:1257311340---------------------------------------------------------------------- -----------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |---------------------------------------------------------------------------------------------------------- -----| 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 |------------------------------------------------------------------------------------------------------ ---------predicate information (identified by Operation ID):---------------------------------------------------4- Access ("ACC_NBR" = ' 15322926784 ') 7-access ("A". ") Create_date ">=to_date (' 2015-02-01 00:00:00 ', ' syyyy-mm-dd hh24:mi:ss ')) 8-filter (" serv_id "=" prod_inst_id ") 9-a Ccess ("A". ") subs_id "=" order_item_id ") ... getting segment size .... OWNER segment_name segment_type Size (Mb)-------------------------------------- ------------------------------------------CRM_DG ix_prod_inst_num INDEX 602 .0625CRM_DG idx_ba_channelstaff_crt_date INDEX 1799.5625CRM_DG pkh_order_ite         M INDEX 6199CRM_DG      Prod_inst TABLE 5126CRM_DG Tb_ba_channelstaff TA BLE 7390crm_dg order_item_hist TABLE 487766 rows sel Ected. elapsed:00:00:01.26......getting Table Infomation ... OWNER table_name Size (MB) PAR degree num_rows GLO STATS GATHER time------------ --------------------------------------------------------------------------------------------crm_dg *p                      Rod_inst 3958.84835 NO 1 15205690 YES 7.84770833CRM_DG prod_inst            3958.84835 NO 1 15205690 YES 7.84770833CRM_DG *tb_ba_channelstaff 5265.49083 NO 1 64956086 YES 102.696563crm_dg tb_ba_channelstaff 5265 .49083 NO 1 64956086 YES 102.696563CRM_DG *order_iTem_hist 40876.7086 NO 1 210109488 YES 10.4260532CRM_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 LEA F_blocks blevel clustering_factor------------------------------------------------------------------------------- ---------------------------------------------------------------------------CRM_DG Idx_ba_channelstaf F_crt_date tb_ba_channelstaff NO nonunique 1 NORMAL 84968 2 50669112 36.41251 1CRM_DG ix_prod_inst_num prod_inst NO nonunique 1 NORMAL 3743                8 2 12501881 100CRM_DG Pkh_order_item order_item_hist NO UNique 1 NORMAL 399394 2 166506822 100 here both the C and B tables are views. The final result is only 2 records. The amount of data returned is small, and you can consider nesting loops to walk the index. Idx_ba_channelstaff_crt_date very poor selectivity, and will be nearly 1.8G very large, index scan single block read, very slow. In order to avoid walking idx_ba_channelstaff_crt_date, here no_index this hint,oracle automatically select the index of the associated column, and is the primary key index pk_channelstaff_subs_id, basically instantaneous results.        The following is an optimized statement: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 a.subs_id = b.subs_id 7 and b.serv_id = c.serv_id 8 and A.create_date >= to_date (' 20150201 ', ' YYYYMMDD ') 9 and C.ACC_NBR = ' 15322926784 '; explained.elapsed:00:00:00.09sql> @getplan ' general,outline,starts ' Enter value for plan Type:plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- ---------Plan Hash value:3198218290---------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |    Time |---------------------------------------------------------------------------------------------------------| 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 |------------------------------------------------------------------------------------------------------ ---predicate information (identified by Operation ID):---------------------------------------------------4-access (" Acc_nbr "= ' 15322926784 ') 6-access (" serv_id "=" prod_inst_id ") 7-filter (" A ".") Create_date ">=to_date (' 2015-02-01 00:00:00 ', ' syyyy-mm-dd hh24:mi:ss ')) 8-access (" A ".") subs_id "=" order_item_id ") sql>

Oracle go wrong index no results