OraFAQ Forum: SQL & PL/SQL » Can I improve create index

Home » SQL & PL/SQL » SQL & PL/SQL » Can I improve create index

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Can I improve create index [message #195453]

Thu, 28 September 2006 15:03

rr9876r
Messages: 12
Registered: April 2005

Junior Member

Hi,
I have a process to truncate the table and copy it from other database daily. Copy process takes 30 minutes to copy 35mn rows. But the create index takes 60 to 90 minutes. Previously I used the materialized view to refresh it. But that didn't work well. Used to got snapshot old error.

Process:
1. Dropping the index idx_u1
2. truncating the tab1 with 'reuse storage' option
3. copy the tab1 from database1 into database2
4. creating the index idx_u1 on tab1 on database2.

CREATE UNIQUE INDEX idx_u1 ON tab1
(col1, col2, col3)
NOLOGGING
TABLESPACE ts1
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
PARALLEL 4;

Is there away to improve this create statement?

Thanks

Report message to a moderator

Re: Can I improve create index [message #195459 is a reply to message #195453]

Thu, 28 September 2006 16:54

Nirmala
Messages: 43
Registered: October 2004

Member

Instead of dropping the index try marking the index as unusable and then after the load is complete rebuild the index.

Suggested Process :
-------------------
1. alter table idx_u1 unusable
2. truncating the tab1 with 'reuse storage' option
3. copy the tab1 from database1 into database2
4. alter table idx_u1 rebuild

Report message to a moderator

Re: Can I improve create index [message #195568 is a reply to message #195453]

Fri, 29 September 2006 08:46

be2sp1
Messages: 52
Registered: September 2005
Location: India

Member

Nirmala

Would like to understand what difference will it make by making the index unusabe as against dropping the index (used by rr9876r) and then rebuilding it. I am not quite clear as to how your approach would speed up the index creation process.

thanks

Report message to a moderator

Re: Can I improve create index [message #195626 is a reply to message #195568]

Fri, 29 September 2006 14:19

andrew again
Messages: 2577
Registered: March 2000

Senior Member

I guess an advantage of not dropping indexes is that you have less chance of one "mysteriously" going missing over time. I'm sure we've all experienced that before - usually due to manual intervention...

http://oramossoracle.blogspot.com/2006/07/truncate-command-marks-previously.html

Report message to a moderator

Re: Can I improve create index [message #195627 is a reply to message #195626]

Fri, 29 September 2006 14:22

andrew again
Messages: 2577
Registered: March 2000

Senior Member

Read up on

ALTER INDEX xxx REBUILD UNRECOVERABLE;

Report message to a moderator

Previous Topic:	Case in a where clause
Next Topic:	I keep trying fix these but it keep coming up syntax errors. PLEASE HELP (merged 3 threads)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 05 22:53:19 CST 2025