Help on creating a partitioned-subpartitioned table in Oracle...

[ForumMaster]

Hi folks...I'd like to say i know oracle pretty well, but I'm no DBA.

We're creating a logger based on the Microsoft Enterprise Library.

Since the easiest and most comfortable way of storing logs is a database, that what we want to do. Now our logger creates different amounts of messages per the severity levels.

So what we want to do, is create the main table in oracle, partitioned first by list (by severity) and then subpartitioned by range (by the date).

This will allow us to easily keep different "severity" levels for different periods of time per the space concerns we may have.

Now i've been doing some reading and oracle claim and i quote:

In Oracle Database 11g, you can create—in addition to already available range-hash and range-list composite partitioning—the following: range-range, list-range, list-hash, and list-list composite partitioning.

yet depite trying (i have oracle 11.1.0.6 installed) and looking, everywhere i look, says range-list is supported, but not list range.

Does anyone have any experience with creating composite list-range tables and can shed some light on how to create such a table? I've already tried google...now i turn to the minds of anandtech...

Thanks in advance...

 

[dealcrawler]

Why don't you use range-list (instead of list-range). Your new logs would always be stored in the new partition.

 

[ForumMaster]

well that's doesn't work as well.

i don't want to choose for a specific partition first if i want to keep by the subpartition. using list-range, the partition management is much simpler, plus oracle can do partition pruning much better.

if I'm looking for an error, I'll always be searching by a specific severity which can eliminate all the partitions except one and then by the date, the oracle will search through the specific subpartitions.

anyway, i figured out how to do it. i had the wrong syntax. it's really weird the sql developer wouldn't let me do it through the gui... i didn't configure the subpartitions properly for each partition.

our dba's helped me do it but here's a link that i found later on how to do it.