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

Are you trying to rebuild it with 'create as select' or by dumping to file and using SQL*Load ?

If you try the SQL Load option, you should find that you can load the IOT most rapidly using:

    direct = true
    unrecoverable load
    truncate
    sorted indexes (iot_pk)

Unfortunately there is not option (I believe) to take advantage of all these options from any other tool (not even the OCI direct path calls) when IOTs are involved.

By the way, I think I would probably be considering a partitioned IOT for the example you have described, (which may mean loading and exchanging each partition separately to get maximum efficiency)..

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Finland__September 22nd - 24th
____Norway___September 25th - 26th


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA_(CA, TX)_August
____USA__October
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Andras Kovacs" <andkovacs_at_yahoo.com> wrote in message
news:412ebb69.0307291431.71eeb748_at_posting.google.com...


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