Create INDEX on ORACLE Standard Edition

  I have a table which contains 10 million records (mostly insertion /selection) , I need to create an index on a column , but if I try to create an index it locks the table and insertions at the same time fails .
I can not use the
Create index cust_index on billing(item) unused
alter index cust_index rebuild nologging (ONLINE) /* online option is not supported in SE and this is the only option which I feel allow the online rebuilding and doesn't lock the table (as it copies the DML operation in a temp table and merge after the operation finishes)

Does anybody has any option which allow me to create an index on 10 million records and does not lock the table

NS : I am using 9iR2 SE


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jrb1senior developerCommented:
I dont' think there is a way.  You could partition and parallel on enterprise edition, which would allow for faster index builds.  But on standard edition, I can't think of anything.
Ivo StoykovCommented:
Hello Jyotikadian,

even if there is a possible way I do not recommend it.

insert/update/delete during index creation might destroy data consistancy with is the main care of Ocarle.

Even if you succed creating index during insert/update/delete the index will be invalid with high probability.

Creating index mean marking blocks where data exists. You want from one hand to mark that blocks for a fast access and from the other to change block contents...


JyotikadianAuthor Commented:
What I said is mainly insert operations are done on this table ,then if data consistancy is taken under consideration say index is being build on 10 million row which is based on rowids of the table , say meanwhile 1000 rows are being inserted , these rowid will not be used in the index then I can chose these rowids (say can check from timestamp ) will delete these rows from the table put in a temp table and insert these rows again and hence all the values are uptodate in the index (as these 1000 rows has been used in index as I inserted these rows again after the creation of index ) ,

Now any idea apart from this consistancy factor

Do anyone have any option
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ivo StoykovCommented:
Hi Jyotikadian,

you cannot create a partial inted on a standard table - i.e. old rows to have index, newly inserted not.

you suggested a possible solution in your last post - insert into a temporary table and after index creation do insert...


jrb1senior developerCommented:
You could:

select the max(rowid) from the table
create a copy of the table (create table xxx as select * from yourtable where rowid <= {maxrowidvalue}
build all existing indexes on the copy
build the new index on the copy
insert all of the new rows (rowid > the max(rowid) you retreived before)
drop the old table
rename the new table
Table partitioning and some other features are not included in Standart edition of Oracle.

Your problem is complex.
You have to know that the indez will be used for SELECT only if it can select
not more 4-5% of the rows. In the other case the optimizer will use full table scan.
So you have to investigate if your index is sensitive enough to be used by selects.
If this is not so you will waste disk space and CPU time for support of
not used index.

A solution of your locking problem could be:
1. Point the application to insert for limited time interval not to the table.
It should insert in another table that have the same structure as the original table.
2. Drop and create the index on the original table.
3. Insert the record from the another table into the original table.
-set your sort area size to some large number (say 524288000 = 500M)
-create index with parallel option in nologging mode.

I think that will do it in minimum time.
jrb1senior developerCommented:
>-create index with parallel option in nologging mode.

I thought the parallel option was only available with Enterprise, not Standard.
JyotikadianAuthor Commented:
Parallel option is there in SE
Sort_area_size can be kept at this size 500M if I still have this much of Dynamic memory available (and it will affect all other operations going on , likely at a time more than 200 users are connected to database through web interface, I can't affect them)
and secondly it is billing table in a telecommunication production Db , now you can assume I can;t afford to loose billing records

Second thing if I create another table (this table takes 13GB of space now) I need to occupy more 13G and build all indexes and then rename the table will see minimum impact , So if any one else has any other better idea ...

Otherwise I will have to look on CTAS and the rebuild all existing and new indexes and the drop the old one and rename to new one
but in this process too I will have outage of atlesast 1/.2 hour to select new records from the old one and put into new one and then drop
Otherwise I will have to look on CTAS and the rebuild all existing and new indexes and the drop the old one and rename to new one
but in this process too I will have outage of atlesast 1/.2 hour to select new records from the old one and put into new one and then drop

instead of doing this, i would strongly recommend you using simple command:

create index with parallel option in nologging mode. (with some appropriate sort area size):

Let us see an example:

SQL> select count(*) from t;


SQL> alter session set sort_area_size = 524288000;

Session altered.

SQL> create index test_idx on t(object_id) parallel nologging;

Index created.

Elapsed: 00:01:48.77

SQL> alter index test_idx logging;

Index altered.

Its really very fast.....

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.