Table Partitioning Question

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Sun, 20 Aug 2006 17:04:10 GMT
Message-ID: <eC0Gg.1536$yO7.882@newssvr14.news.prodigy.com>

Where I work there are a number of large partitioned tables which have terrible performance. The database is Oracle 10g Version 1.0 under a Windows XP operating system on our end. The actual database is in a remote location out of state and I honestly have no idea what operating system is on the server.

My question is concerning HOW the tables are partitioned. There are 10 partitions partitioned by year ranging from 2001 through 2010. When they created the tables they used a command similar to below for the partitions:

PARTITION BY RANGE (SRV_DT_FROM)
PARTITION CLM_HDR_2001
VALUES LESS THAN(TO_DATE('2001-01-01 00:00:00', 'SYYYY-MM-DD 24:MI:SS','NLS_CALENDAR=GREGORIAN') ...

They also only have global indexes on these tables. The primary key seems to be a system generated sequence number. There is a non-unique index on the SRV_DT_FROM field.

I know from using an explain plan on a query that even with a date specified in the query that Oracle will go through every partition instead of only the partition(s) the year or years are in.

I know this is pretty sketchy but other than trying to get them to use partitioned indexes what other avenues are there for getting better performance out of these partitioned tables? Received on Sun Aug 20 2006 - 12:04:10 CDT