I'm working in a data warehouses with typical facts tables.
Some of my fact tables are big and I want to partition them.
I want to know the criterions of partitioning. is there any rules??
- Number of rows of the table or the partitions.
If I put partitions on my tables, the tables will have 1 million records in each partitions???, is a good number???
What are the advantages and disadvantages of partitions???.
I have massive load process and masive delete/truncate process. Have partitions any effect on this processs??
Any advice about partitioning will be greatly apreciatted.
The number of rows in a partition is not really significant so long as the partitioning is useful.
Each partition is physicallly just a separate table, but they are presented together as one object. This allows you to truncate just a partition of the table rather than deleting it, for example. Also queries using the partition key in the WHERE clause can go straight to the relevant partition. You need to define the partitions to take advantage of these features, in particular from the point of view of
1. Maintenance (bulk loading, truncation, archiving, backup etc)
Partitions also work well with parallel query, if you use that. One PQ slave can work on each partition or subpartition, which can divide up the work quite efficiently. What you often see is partitions based on a business key or date range, and then subpartitions using hashing (essentially dividing the data evenly but randomly).
a typical partition in one of my fact tables consists of 5 million plus records. and that is not the largest. It is range and hash partitioned.
which type of partitioning to use all depends on the types of data you have, what you are trying to store ..etc . there is no one super answer to all partitioning question, the answer depends on the data involved and the business reqs. this is a design question. i suggest you check out Rittman's site or AskTom for sites on Datawarehousing (especially Rittman). A good book on the basics of Datawarehousing would be "Oracle DBA: Guide to Datawarehousing and Star Schemas" by Bert Scalzo and there are many others i'm sure.