SQL Execution Plan

I'm running Oracle 9.2.0.5 on a Sun box with the optimizer mode set to CHOOSE. I'm running a query against a table which has ~30 million rows in it. The table has a date column which is indexed called created and it can't be null. The table has about a year and a half of data which is pretty much evenly skewed per day.

We're using DBMS_STATS to apply stats:

exec dbms_stats.gather_schema_stats(ownname => 'NGDREP', cascade => true, -
estimate_percent => dbms_stats.auto_sample_size, method_opt=>'for all indexed columns size AUTO', -
degree => DBMS_STATS.DEFAULT_DEGREE);

The stats are a couple of days old. When we look at the plan for the following query it does a full table scan:

SELECT
   created,
   par_evt_id,
   name
FROM
  activity
WHERE
SELECT
   created,
   par_evt_id,
   name
FROM
  activity
WHERE
  created between to_date('11/07/2004 00:00:00','mm/dd/yyyy hh24:mi:ss') and to_date('11/13/2004 23:59:59','mm/dd/yyyy hh24:mi:ss')
order by created

SELECT STATEMENT **CHOOSE** Cost=66875   SORT ORDER BY
    TABLE ACCESS FULL SIEBEL.S_EVT_ACT **ANALYZED** The FTS takes 15-20 minutes to run vs < a minute when the index is being used.

We've had similar problems with other more complex queries not using indexes on date columns.

Any thoughts?

Thanks... Received on Tue Nov 16 2004 - 10:59:38 CST