Index Controlled to Table Controlled - BIG SWITCH

Not many shops use TABLE CONTROLLED partitioning - common reason(s) : It's confusing, not good for reorgs, we don't like it.

Are these reasons truly genuine ? Well, my shop doesn't use a table controlled portioning either - but, hey not because of the above reasons. I came across the above reasons when I planned to convert an index controlled partition to table controlled, I heard that they are good when it comes to DML - DELETE .

Wait, what exactly is INDEX controlled and TABLE controlled partitioning ?

I got a Tablespace with NUMPARTS defined as 24, a table with a primary key and a Unique Index - there, its partitioned table ?  yes, now a partitioned object is planned to be assigned a set of ' key values ' - simple words - you decide the location ( partition number ) where you want to your records to be placed depending upon the primary key. If you define the key values on the index - it goes as index controlled else it is table controlled.

Like I said, most of the shops wish to have the partition range on the index - they don't like seeing numbers on the table.

So what if I had to drop-recreate this UNIQUE index ? OK - I dropped the primary key first, then I dropped the UNIQUE index. So what happens to the key values that you had on this index ? This method automatically enforces the partition range on the TABLE ! Thus, you just made a BIG SWITCH from index controlled to table controlled partitioning. Since the values are now present on the table - you cannot create a unique index with partition range - all you can do is create a UNIQUE index which is partitioned ( NUMPARTS 24 ) and not partitioning ( no partition range can be provided ) !!

Keynote :

Be careful while dropping UNIQUE indexes which have a range defined over it. You may not like its outcome. As always, you can drop and recreate the entire structure from the tablespace level to gain your old structure back, tedious isn't it ? So, be careful !