• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5485
  • Last Modified:

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


  • 3
  • 2
  • 2
  • +2
1 Solution
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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


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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now