Selective performance hit on date ranged partition table

We have just added 12 monthly partitions to a pair of large tables in Oracle 8.1.6. Since then, select statements on those tables either reply very fast or have response times orders of magnitude larger than they were before the partitions were added. The deciding factor in whether they run fast or slow seems to depend on how we pass dates in the WHERE clause of the SELECT.

Some background. The two tables store readings from recording devices. One table is keyed by a device id (NUMBER) and a reading date (DATE). The other is keyed by a device id (NUMBER), a channel (NUMBER), and a reading date (DATE). There are a couple hundred devices and each one has 288 readings from the previous day inserted every night. (The readings are done every 5 minutes by the device).

The table had been previously partitioned, with the MAXVALUE partition being everything from Jan 1, 2004 on. Using ALTER TABLE, we added 12 new partitions to the tables ranged by month. The value list was set with

TO_DATE(' 2004-02-01 00:05:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN' ) though, of course, each partion was set to progressive months.

There is a global index on each table which use the primary keys for the table and are also partitioned by month using the exact same value list. These indexes were dropped and rebuilt after the table partitions were added.

Okay, now on to the problem. We have some big monthly summary queries that use these tables. The join to these tables are like:

...
and big_table.id = device_list.id
and big_table.read_date >=
TO_DATE( '10/01/2004 00:05','MM/DD/YYYY HH24:MI') and big_table.read_date <
TO_DATE( '11/01/2004 00:05','MM/DD/YYYY HH24:MI') After adding the partions, "some" of the queries seemed to freeze up, though I now think they were just taking an extremely long time. After experimenting a bit, we found that using a date format mask with a YYYY in it would "usually" give an excellent response, usually about 3 or 4 seconds. Whereas, using an RRRR would increase the query time by orders of magnitude. Using no mask at all would depend on what the individual users NLS_DATE_FORMAT was set to. And using an actual DATE variable in the PL/SQL code would also kill the query.

If it wasn't for the "usually", we would just set all the reports and procedures to use the "good" format.

Our main questions are:
1) Why would the query care if YYYY or RRRR was used in the format mask? Especially since we always use 4 digit years.

2) Is there a better way to range partition a table by date where it would be less sensitive to the date format. Especially a way were we can use the DATE variables that all the PL/SQL procedures use.

3) Should we create a second global index with the device id as the partition range variable. Should we create a local index on the primary keys and let Oracle do its partitioning automatically? Thanks for any light you can shed on this.

  Mike McCormick Received on Wed Jan 12 2005 - 14:30:08 CST