Re: Partition view limitations, suggestions?

Oracle will support views containing 48 partitions, however, the parse time and shareable memory requirements to parse the query start to increase ( exponentially as you get over 100 tables in the view. I've performed tests which at the extreme take 300s of parse execpu and 95MB of sharable memory !)

Your parse time with 48 partitions will probably be in the order of 0.5->1.0 s. which is acceptable as long as this is a small percentage of the overall parse.fetch,execute CPU time. If your queries are meant to be fast e.g. index, Nested Loop type queries, this parse overhead becomes a major problem.In that case the queries would be faster against the unpartitioned or 4 table\ partition.

If you use static sql, the CBO can't perform partition elimination. Whilst this doesn't loose you anything when comparing 48 small full scans vs. 4 large scans vs 1 mother of a scan, on the index access front you lose out.... 48 index lookups involve more I/O than 4 or 1. Depending on your indexing strategy it could involve a heap more physical I/O.

Motto : Static sql + partion index lookups is a performance gotcha.

Full table scan queries which can eliminate a number of the monthly  partitions will run faster.If your queries can eliminate a proportion of the tables then this will be a big performance plus.

 Index maintainance associated with data load is simpler when using  finer grain partitions.

Finer grain ( monthly) partitions can reduce the size of your backup requirements
by using read-only tablespaces.         

Conclusion :
Use 48 partitions provided your not reliant upon NL index driven queries.
Use dynamic SQL to get the benefits of partition elimination

        Hope this helps.

-- 


=======================================================================

Mike Farrar - Database/Web Consultant    Phone : +61 3 9882 8211
Silicon Graphics Pty Ltd                 Fax   : +61 3 9882 8030
357 Camberwell Rd                    Email : michaelf_at_melbourne.sgi.com
Melbourne, Australia


=======================================================================