When the index fails

select count(0) from creditguarantee cg where 1=1           and cg.createddate>=to_date('2012-02-23','yyyy-MM-dd')           and cg.createddate<=to_date('2012-02-26','yyyy-MM-dd')              and 2=            (case              when                 (select count(0) from creditlevelchange where customerid=cg.customerid and createddate<=to_date('2012-02-26','yyyy-MM-dd'))>0              then                    (select newlevel from creditlevelchange where customerid=cg.customerid and                   changeid=                     (select max(changeid) from creditlevelchange where customerid=cg.customerid and createddate<=to_date('2012-02-26','yyyy-MM-dd'))                  )               else                 (case                    when                     (select count(0) from creditlevelchange where customerid=cg.customerid and createddate>to_date('2012-02-26','yyyy-MM-dd'))>0                    then                        (select oldlevel from creditlevelchange where customerid=cg.customerid and                       changeid=                         (select min(changeid) from creditlevelchange where customerid=cg.customerid and createddate>to_date('2012-02-26','yyyy-MM-dd'))                      )                    else                          cg.creditlevelid                  end)            end)

This is the developed SQL statement. The same SQL statement can be run in less than 1 second on huifu2. It takes about 3 minutes to run on jsjdata0.
Check the execution plan and you can clearly find that the problem lies in that jsjdata0 is not indexed. Creditguarantee
Createddate has an index nl_credatedate, which goes on huifu2, but access creditguarantee in jsjdata0.
Is a full table scan. My first response was that there was a problem with the index statistics and it was collected using exec dbms_stats.gather_index_stats.
The statistical information of the index. I will add hints first and try again. I will write the first line of data as follows:
Select/* + index (creditlevelchange index_customerid) */count (0) from creditguarantee CG where 1 = 1
This is a bit strange because the data in the table is related to the index.

Jsjdata0
-- Select max (createddate) from creditguarantee 17:46:01
-- Select Min (createddate) from creditguarantee 10:32:58

Huifu2
-- Select max (createddate) from creditguarantee 16:48:56
-- Select Min (createddate) from creditguarantee 14:45:52

The reason is very simple. It cannot be found in the table if it is later than the time.

First case:
Select count (0) from creditguarantee CG where 1 = 1
And CG. createddate> = to_date ('2017-02-23 09:33:00 ', 'yyyy-mm-dd hh24: MI: ss ')
And CG. createddate <= to_date ('2017-02-26 10:33:00 ', 'yyyy-mm-dd hh24: MI: ss ')
And 2 =

Write more details.

Case 2:
This can be done below
Select count (0) from creditguarantee CG where 1 = 1
And CG. createddate> = to_date ('2017-02-23 10:40:00 ', 'yyyy-mm-dd hh24: MI: ss ')
And CG. createddate <= to_date ('2017-02-26 10:33:00 ', 'yyyy-mm-dd hh24: MI: ss ')
And 2 =

Time Interval of data in the table

| ------------------------------------- |
10:32:58 10:32:58

First case:
| --------------------------------------------------------- |
09:33:00 10:33:00

Case 2:
| ------------------------------------------ |
10:40:00 10:33:00

Conclusion: the start time after where is greater than the data time in the table. If the start time is less
There is no data at that point, so we can't talk about using the index of the data at that point, so forcing hints is not
It will be effective.
During the whole SQL optimization process, I always pay attention to and 2 =... before this statement, I found them through the execution plan
Non-associated operations. Therefore, after finding the problem point, you do not need to pay attention to the following SQL statements.