Josh Berkus <[EMAIL PROTECTED]> writes: > Stacy, > > > Thanks again for the reply. So it sounds like the answer to my original > > question is that it's expected that the pseudo-partitioning would introduce > > a fairly significant amount of overhead. Correct? > > Correct. For that matter, Oracle table partitioning introduces significant > overhead, from what I've seen. I don't think there's a way not to.
Well Oracle has lots of partitioning intelligence pushed up to the planner to avoid overhead. If you have a query with something like "WHERE date = '2004-01-01'" and date is your partition key (even if it's a range) then Oracle will figure out which partition it will need at planning time. Even if your query is something like "WHERE date = ?" then Oracle will still recognize that it will only need a single partition at planning time, though it has to decide which partition at execution time. We didn't notice any run-time performance degradation when we went to partitioned tables. Maybe we were so blinded by the joy they brought us on the maintenance side though. I don't think we specifically checked for run-time consequences. But I'm a bit puzzled. Why would Append have any significant cost? It's just taking the tuples from one plan node and returning them until they run out, then taking the tuples from another plan node. It should have no i/o cost and hardly any cpu cost. Where is the time going? > What would improve the situation significantly, and the utility of > pseudo-partitioning, is the ability to have a single index span multiple > partitions. This would allow you to have a segmented index for the > partitioned axis, yet still use an unsegmented index for the other columns. > However, there's a *lot* of work to do to make that happen. In my experience "global indexes" defeat the whole purpose of having the partitions. They make dropping and adding partitions expensive which was always the reason we wanted to partition something anyways. It is handy having a higher level interface to deal with partitioned tables. You can create a single "local" or "segmented" index and not have to manually deal with all the partitions as separate tables. But that's just syntactic sugar. -- greg ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings