Create INDEX on ORACLE Standard Edition

Posted on 2006-07-11
Last Modified: 2008-01-09
  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


Question by:Jyotikadian
  • 3
  • 2
  • 2
  • +2
LVL 25

Expert Comment

ID: 17088285
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.
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 17088294
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...



Author Comment

ID: 17088330
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 22

Expert Comment

by:Ivo Stoykov
ID: 17088362
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...


LVL 25

Expert Comment

ID: 17088379
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
LVL 47

Expert Comment

ID: 17088598
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.
LVL 13

Expert Comment

ID: 17089229
-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.
LVL 25

Expert Comment

ID: 17090341
>-create index with parallel option in nologging mode.

I thought the parallel option was only available with Enterprise, not Standard.

Author Comment

ID: 17095601
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
LVL 13

Accepted Solution

riazpk earned 250 total points
ID: 17098495
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now