cursor through partitions... is there a more elegant solution

[vadimg]

hi.
i need to perform an operation on every partition in the table. I want to make the procedure flexible, so that if more partitions are added, the procedure doesn't have to change.

I'm planning to cursor through partition names, build a string and execute it for every iteration.

Is there a more elegant solution in plsql?

 

[carp]

A cursor is probably a pretty good choice.
If you could be more specific on what it is you want to do to the partitions, we might be able to give you a more specific answer.

 

[vadimg]

i need to select something from every partition and insert it into another table. The result from the whole table can be a half billion rows, so I don't want to do it in 1 transaction.

thanks for the help.

 

[carp]

Ah! Then that would preclude doing it as a single SQL command! In that case, I would stay the course with the cursor (presumably with a commit after each loop).

 

[Advocate]

how many partitions ?

approx size (rows) each partition ?

how many rows would you expect to return from each
partition ?



regards, david - no such thing as problems - only solutions.