Creating an index on Oracle 10g?? Sounds simple...

also take a look at your UNDO tablespace size, make sure it has enough free space in it.

manually increase the tablespace size if needed, because letting it grow automatically will slow down the index creation process.

I'm not sure you can really optimize this too much.  400 million rows is a lot of data to index.

Make sure the tablespace for the index (are you specifying which tablespace?  Make sure it isn't defaulting to SYSTEM or something like that) is large enough.  This is an intensive process, and you don't want to waste time with autoextending the space.  Make the datafile large enough before creating the index.

Likewise, make sure you have enough TEMP tablespace.  Increase the size of some of your rollback segments as well.  Bump up your sort area size.  

Make sure there are no updates to this table while indexing.  Then, let it run overnight.

Thanks for those suggestions but I had tried all of them... :-(

(i.e NOLOGGING, naming my tablespace, large TEMP tablespace etc)

I ran something similiar on a 32 bit instance on Oracle 9i, on 120 million rows and it only took 1 hour 40 mins. 400 million rows shouldn't take 8 hours on a vastly superior machine (and supposedly superior database) - should it??

Here's my code...

CREATE INDEX big_index ON very_big_table (column_1, column_2) NOLOGGING
STORAGE (INITIAL 10M
         NEXT 10M
         PCTINCREASE 0)
TABLESPACE x_1
PARALLEL (DEGREE 2);

Thanks.

Aidan

aidan:

you can also enable the PARALLEL query on the table level considering the table is monstrously large.

alter table very_big_table  PARALLEL (degree 8);

I don't remember the syntax exactly, but that's what you should do

Thanks Seazodiac,

I'm going to try those PARALLEL settings (they weren't set in my init.ora).

I'll post an update soon.

Thanks
Aidan

make sure you make changes in the SPFILE, because most likely you are starting up your server in SPFILE , not PFILE as you are in 10g.

400 million rows is a large table indeed.

Is the table partitioned over several disks? If not, you will only have limited disk IO, and if all the disk IO is being used autoextending tablespaces, it will run slow indeed.

If the table is partitioned, build parallel indexes (each partition), as 9i and above supports combined indexes (local only I think)

Dont forget, an index uses more tablespace than the table itself usually, so make sure its sized appropriateley.

If the table is not partitioned, I would seriously consider it, as an index scan on 400 million rows will still take a while. and you will get a huge performance increase by partitioning (as well as gain the ability to perform parallel updates and deletes which unpartitioned tables dont support!!)

All the best

What sort of hardware you got? How many CPU's? Speed? Memory? RAID Array? My guess is your just pushing the limits on your hardware and there's not much you can do. A few thing you can check within the database are:

  1) Check your sort tablespace and make sure a tremendous number of extents aren't being created (SMON intensive)
  2) Try increasing the sort_area_size at the session level before you start the index build.
  3) If you disable logging, make sure you re-enable it and take a backup. Otherwise the index won't be recoverable.
  4) Use the parallel option, but don't set it too high. Otherwise you will hit disk contention which will slow you down.
  5) If the table is volitale, that will also slow you down. Try building the index in off hours.

If you're creating a bitmap index you can try adjusting the below mentioned in the init.ora file, but they are memory intensive.

  1) create_bitmap_area_size = size in bytes
  2) bitmap_merge_area_size = size in bytes (think this is for queries, not index builds)


I've got a 40m row table and creating a multi-column index on it takes a few hours (we don't have the fastest hardware by any means and are also RAID 5).

aidin:

in 10g, all the above Parameters are obsolete, they exist solely for backward compatibility.

so don't bother with these.

Also use some big sort_area_size before building index. i ussally use 500M or so .

SQL> alter session set sort_area_size=524288000
SQL> create index <In_Name>
on ......

parallel
nologging;

I do not believe that this is the right way.
You have a table with 400 milions records.
it is questionable that the CBO will try to use the index due the many levels
and too much entries there.
If you really would like to use the table try to partition it using range or hash partitioning
and partitioned indexes also.

Hi folks,

Thanks for all the advice. I have changed various init.ora parameters and in a test I can create an index on a 50 million row table in 13 minutes. However, the 400 million rows seem to be pushing the machine to its limits.

Alot of the parameters mentioned above are obsolete ?

Has anybody any insights on the paramter "pga_aggregate_target"  and how the pga is used in index creation?


Regards
Aidan

Alot of the parameters mentioned above are obsolete ?

Yes, in 10g, pga_aggregate_target prevails


in my production, I adjust pga_aggregate_target to 512MB.


of course, you have also to set WORKAREA_SIZE_POLICY=AUTO to make pga work.