Oracle performance on date range query

oracleoracle-11gperformancequery-performance

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 > = TO_DATE('2016-01-05', 'YYYY-MM-DD') 
 AND TIMESTAMP<= 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 > = TO_DATE('2016-01-05', 'YYYY-MM-DD')
   AND TIMESTAMP<= 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?

Best Answer

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'

Related Solutions

SQL Server Query Performance – Optimizing Group By with Many Columns

The non-clustered index you have tested is not the best for this query. It can be used for the WHERE clause and for doing an index scan instead of a full table scan but it cannot be used for the GROUP BY.

The best possible index would have to be a partial index (to filter the unwanted rows from the WHERE clause), then have all the columns used in the GROUP BY and then INCLUDE all the other columns used in the SELECT:

CREATE INDEX special_ix 
  ON dbo.Commissions_Output
    ( company, location, account, 
      salesroute, employee, producttype, 
      item, loadjdate, commissionrate ) 
INCLUDE 
  ( [Extended Sales Price], [Delivered Qty] ) 
WHERE 
  ( [Extended Sales Price] <> 0 ) ;

Oracle – physical writes to an index

I would guess that there is a weekly maintenance job that is running on that one day a week, that is affecting something that is forcing extra reads of the index. Is your daily batch job always run long on the same day of the week each week? If so, look at the views dba_scheduler_jobs and dba_scheduler_schedules to see if any jobs are scheduled.

Now the bigger question is, is why not fix the UPDATE statement to include criteria to avoid updating rows where the value is already the correct value? IE:

update student set score = 100 where score != 100 -- and whatever other criteria

Also as a temporary fix to your "temporary" index, create it using the nologging clause in the index attributes part might help a little.

Related Question