we know about the advantages of partitioning.
what i know is partitioning means "creating a table across multiple tablespaces".
so when ever a query is fired, only that partition is accessed.
but my doubt is,
I create a table X and it has columns A,B and C.
Iam partitioning the table on column A.
Iam creating 3 partitions(P1,P2,P3)
For A < 1000 iam inserting into Partition P1.
For A < 2000 iam inserting into Partition P2.
any other value in Partition P3.
But the thing is all the Partitions(P1,P2,P3) all are in the same tablespace(T1).
Is that kind of partitioning is having any advantages and disadvantages...pls elabore the adv and disadv
And more over, in case of parallel DML what will happen in the above mentioned situation.
Well it is better to partition across separate tablespaces as you can spread io load better (potentially but may not be the case if is striped across disks) and reduce file locking issues (more so on RAC). But you still get advantages from partitioning even in a single tablespace as it means you can archive off 'old' partitions easily. You can also do maintainance on single partitions while leaving other partitions accessable (be careful with the indexes if though).