I had a technical question regarding Oracle 8i/9i datawarehouse and database partitioning. In a large data mart say we have several large cubes and 20Gb tables, what are some best practices for setting up partitions and maintenance on these partitioned tables? In an interview a DBA asked me how to load data and modify partitions in a 20Gb table that is using range partitioning. Why range over hash partitioning? He also was asking me how to move and manage partitions.
With range (and list) partitioning, the optimizer can eliminate partitions when querying using the partition key. For that reason it can be worth making sure that the partition key is something you can use in typical queries.
I have just created a script to create a Range-List partition table (I have Oracle 9.0.2 64 bit). After I ran my create table script and when i check my table, the part that create the list partition has been converted by Oracle to be a Hast subpartition instead of List subpartition. The Range partition column is a date and list parition is a number column. Anyone have similar problem?