[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Create INDEX on ORACLE Standard Edition

Posted on 2006-07-11
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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 48

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

656 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