SQL Optimization-Role of hint

SQL Optimization-the role of hint currently, oracle uses the CBR optimizer, so in some cases, the machine will execute the SQL as needed, of course, oracle makes decisions based on some of its own information, such as statistical information. However, in some cases, the machine may not be executed as expected. Today, we have encountered such a problem. It takes a long time to view the shipment details within a certain period of time. The SQL statement is as follows:

[sql] select *    from C1.T_DECLAREDETAIL t1   where t1.commitdate >= to_date('2013-01-01', 'YYYY-MM-DD')     and t1.commitdate < to_date('2013-06-30', 'YYYY-MM-DD') + 1  

 

A full table scan is performed by checking the execution plan. Because the commitdate field has an index, I thought it would take an index.

Obviously, a full table scan is performed. After an experiment, if the value of commitdate is equal to a certain date, the data is indexed. Through this experiment, I suspect that oracle has made its own claim that the amount of data in this period accounted for more than 10% of the total amount of data, and the selection is not high, so a full scan was conducted. In order to verify my hypothesis, I narrowed the scope and found that the index was obvious:
I have no better way to test it, so I have to add hint to the script. In general, it is best not to use hint for processing. The modified script is as follows:

[sql] select <span style="color:#ff6666;">/*+ INDEX (t1 IND_T_DECLAREDETAIL_COMMIT)*/</span> *    from C1.T_DECLAREDETAIL t1   where t1.commitdate >= to_date('2013-01-01', 'YYYY-MM-DD')     and t1.commitdate < to_date('2013-06-30', 'YYYY-MM-DD') + 1  

 

The execution plan is as follows:

The execution plan shows that the script follows the execution plan. The practice also proves that the execution time after this modification is indeed shortened.