Re: Index

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: 2000/05/23
Message-ID: <o4slisk8r2r3jlmjp5kl1djvc6t1ucu6jb@4ax.com>#1/1

To create an index, Oracle has to read through all the rows, extract the keys, sort the keys, and write out the index entries. The length of your key, amount of memory available for sorting, and your disk I/O speed would all probably affect performance. The size of your rollback segments might factor into this too.

You might consider experimenting on a subset of your data. Take, say 10,000 rows. Create an index on those. Time how long that takes, and then extrapolate that to 1,000,000. For example:

create table subset_table as

   select * from the_big_table
   where rownum <= 10000;

create index test on subset_table (...);

You could do a test with 10,000 rows, and another with 100,00 rows, and then see how well your 10,000 row test worked to predict performance of the 100,000 row test.

Look at the NOLOGGING option too. You can buy some performance by not writing the index creation to your redo log.

Jonathan

---

jonathan_at_gennick.com
http://gennick.com
Brighten the Corner Where You Are

On Tue, 23 May 2000 15:56:00 +0100, "Michael Alomo" <m.alomo_at_adictive.com> wrote:

>Can anyone tell me how long it would take to create an index on a table that
>has 1,000,000 rows.
>And also what factors to take into consideration
>
>Thanks
>Michael
Received on Tue May 23 2000 - 00:00:00 CDT