Solved

Create INDEX on ORACLE Standard Edition

Posted on 2006-07-11
12
4,909 Views
Last Modified: 2008-01-09
Hi
  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

Thanks

0
Comment
Question by:Jyotikadian
  • 3
  • 2
  • 2
  • +2
12 Comments
 
LVL 25

Expert Comment

by:jrb1
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.
0
 
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...

HTH

I
0
 
LVL 1

Author Comment

by:Jyotikadian
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
0
 
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...

regards

I
0
 
LVL 25

Expert Comment

by:jrb1
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
0
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 47

Expert Comment

by:schwertner
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.
0
 
LVL 13

Expert Comment

by:riazpk
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.
0
 
LVL 25

Expert Comment

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

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

Author Comment

by:Jyotikadian
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
0
 
LVL 13

Accepted Solution

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

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;

  COUNT(*)
----------
   5789056

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

Featured Post

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.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

762 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

19 Experts available now in Live!

Get 1:1 Help Now