Large Index organized tables: just a marketing feature ?! (for experts)

Hi,

I am having a curious problem with index organized tables. Our database is very large. A lot of tables are 200GB-300GB and have billions of rows. I am trying to convert some of these table to Index Organized tables. A good candidate table has just one PK and no other index. I have defined a sample of 46 000 000 rows that I have exported into a file.

If I want to create a regular heap table the load takes 23 minutes and the index creation takes less than 10 minutes. Everything is done in 33 minutes.

However I never managed to really create the Index Organized table. Hang on: The load just runs for ever( 2 days). Larger the input file is more the load takes time. The load time explodes exponentially! I have a presumption that I have a sort of Index Splitting. (This problem occures in 9.2 and 8.1.4).

Is there a particular way of loading Index Organized tables? Could it help if I order the input file? I am looking for DBAs with similar experience on very large Index Organized table.

The table that I want to create has this type of structure : product_id, sales_date, sales_amount, currency. The primary key is product_id and sales_date.

Thanks for any feed back. Received on Tue Jul 29 2003 - 17:31:51 CDT