oracle performance on date range query I am trying to count the number of orders passed by a certain organization over a certain time range. But I found that the below query (time range of 2 days) is much much slower that doing two seperate queries each for one day.

 SELECT COUNT(*)  FROM ORDER_HISTORY
 WHERE organization = 'BA' AND  TIMESTAMP &gt; = TO_DATE('2016-01-05', 'YYYY-MM-DD') 
 AND TIMESTAMP&lt;= TO_DATE('2016-01-05', 'YYYY-MM-DD')+2;


I've an index on the column timestamps and another index on column organization



Here is the schema of my table. the column timestamp is of type DATE.



The execution plan of query over 2 days uses the index on organization:



The execution plan of query over 1 day uses the index on timestamp:



To have some stats:

   SELECT COUNT(*)  FROM ORDER_HISTORY
   WHERE ORGANIZATION = 'BA' ;


gives 2359847.

   SELECT COUNT(*)  FROM ORDER_HISTORY
   WHERE TIMESTAMP &gt; = TO_DATE('2016-01-05', 'YYYY-MM-DD')
   AND TIMESTAMP&lt;= TO_DATE('2016-01-05', 'YYYY-MM-DD')+1;


gives 9260. and same query over 2 days gives 16510.

Why could I get the kind of strange behaviour of the oracle DB engine?
The reason is that the cost-based optimizer has a plan in memory from a previous select, where the number of records for a given organization, namely 'LOL', was much smaller, some 14000.  When the plan is in memory, the CBO doesn't care about the parameter values anymore.  Use:

WHERE organization || '' = 'BA'