Home » SQL & PL/SQL » SQL & PL/SQL » Can I improve create index
Can I improve create index [message #195453] Thu, 28 September 2006 15:03 Go to next message
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
Re: Can I improve create index [message #195459 is a reply to message #195453] Thu, 28 September 2006 16:54 Go to previous messageGo to next message
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
Re: Can I improve create index [message #195568 is a reply to message #195453] Fri, 29 September 2006 08:46 Go to previous messageGo to next message
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
Re: Can I improve create index [message #195626 is a reply to message #195568] Fri, 29 September 2006 14:19 Go to previous messageGo to next message
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
Re: Can I improve create index [message #195627 is a reply to message #195626] Fri, 29 September 2006 14:22 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Read up on

ALTER INDEX xxx REBUILD UNRECOVERABLE;
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:
  


Current Time: Fri Dec 02 18:44:29 CST 2016

Total time taken to generate the page: 0.34890 seconds