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

Andras Kovacs wrote:

> 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.

What version and edition?
What hardware and operating system?
Do you have partitioning available?
Can you use ORDER BY to create your load file?

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)