Quote:
Originally posted by sonaliak
I read that making partitioned table improves performance for large data tables with frequent DMLs.
Some times, but not all. If all your partitions are in the same tablespace on the same physical disk, the performance is similar (as compared to an indexed read).
Quote:
I also read that when you have to do large deletes(not all data) on a table dropping a partition will be fastest approach.
Can I drop a partition from inside PL/SQl block(stored procedure as it will be getting data from procedure and I would like to clear it at the end of procedure- just for that session).
Can you drop a partition from inside PL/SQL? Sure, you have to use Dynamic SQL, but it can be done. I'm not sure what you mean by the second part.
Quote:
I know you cannot do DML in Pl/sql. I am using Oracle8.
Not true. PL/SQL is mostly DML operations.
Quote:
Are partitions treated seperately ?
Is there any thumb rule as to when one should or should not do a partition and what column and values will go in partition, is it PK column based ?
Does it use same index across the partition ?
If you can take advantage of partitioning, you should use it. Otherwise, you shouldn't. For example, if you have a table that is partitioned by the PK and most of the table accesses are via the PK, Partitioning probably won't help you. If you have a table that can be partitioned by a date value and most of your queries span multiple partitions and all the partitions are in the same tablespace, partitioning will help slightly. If you have a tables that can be partitioned by a date value and most of your queries span multiple partitions and all the partitions are in different tablespaces on different devices, partitioning will help greatly.
Both tables and indexes can be partitioned.