Oracle-L: Re: would imp speed affect by setting indexes to NOLOGGING?

From: Ron Thomas <rthomas_at_hypercom.com>
Date: Tue, 27 May 2003 09:50:18 -0800
Message-ID: <F001.005A3712.20030527095018@fatcity.com>

Have you considered dropping the indexes before import, then rebuilding parallel nologging?

Ron Thomas
Hypercom, Inc
rthomas_at_hypercom.com
Each new user of a new system uncovers a new class of bugs. -- Kernighan

                                                                                                                                                    
                      gmei_at_incyte.com                                                                                                               
                      Sent by:                 To:       ORACLE-L_at_fatcity.com                                                                       
                      root_at_fatcity.com         cc:                                                                                                  
                                               Subject:  would imp speed affect by setting indexes to NOLOGGING?                                    
                                                                                                                                                    
                      05/27/2003 11:19                                                                                                              
                      AM                                                                                                                            
                      Please respond to                                                                                                             
                      ORACLE-L

Hi,

Would the imp speed increase by setting all indexes to NOLOGGING?

I have a schema with 249 tables and 442 indexes and I have a weekly schema refresh program running as this:

exp schema from Server A (Oracle 8173 on Sun Solaris box) ftp dump file from Server A to Server B, the dump file size is 1.9GB after compression
imp schema on Server B (also Oracle 8173 on Sun Solaris box), the imp usually take about 7 hours.

With everything elase the same, last week I changed all indexes in the schema on Server A to "NOLOGGING" (they were all set to "logging" before), hoping to increase the speed of imp on Server B. Because I thought the "create index ... nologging" would reduce redo log writing, therefore increase the imp speed quite a bit (there are some big indexes there!). But this morning I found the imp still took about the same time as before, although I was told that the system I/O seems to be lower.

Does anyone have similar experience? TIA.

Guang

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
  INET: gmei_at_incyte.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ron Thomas
  INET: rthomas_at_hypercom.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Tue May 27 2003 - 12:50:18 CDT