I have written 3 scripts which may be widely useful to those using current versions of Oracle.

1.  Identifies good candidates of Heap Organized Tables (HOT) that may be Index Organized (IOT).
2.  Converts a Heap Organized Tables (HOT) to Index Organized (IOT).
3.  Converts a Index Organized Tables (IOT) to Heap Organized (HOT).

FindGoodHOTs.sql

HOT2IOT.sql

IOT2HOT.sql

IOTs are excellent for performance as http://otn.oracle.com/products/oracle9i/datasheets/iots/iot_ds.html explains in excellent detail. Yet Oracle or even Toad does not have anything to convert HOT to IOT.

My scripts therefore fill a great void!

I have used these scripts with great success.

Only unpleasant side effect of converting to IOT was that one has to use Universal ROWID UROWID instead of ROWID. UROWID has the Primary Key Value and the Guess ROWID.

The solution in PL/SQL and ProC programs is to assign sufficient storage for bind and host variables that receive and use UROWID.

HOT2IOT is for optimists. IOT2HOT is for the nervous who always need insurance to beat hasty retreats.

"The greatest glory in living lies not in never falling, but in rising every time we fall."
- Nelson Mandela 

So let HOT 2 IOT take off.
