Oracle-L: RE: nologging for IOT

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Wed, 05 Nov 2003 11:44:33 -0800
Message-ID: <F001.005D5AF1.20031105114433@fatcity.com>

Well, that's not a trick -:)
I wouldn't be asking, if I had enough space for both table and index. It's a huge "narrow" table, which never gets updated (only inserts/deletes) - perfectly fits IOT.

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
Khedr, Waleed
Sent: Wednesday, November 05, 2003 2:19 PM To: Multiple recipients of list ORACLE-L

A trick, use a regular table and create an index that has all the needed columns.

Waleed

-----Original Message-----
Sent: Wednesday, November 05, 2003 10:29 AM To: Multiple recipients of list ORACLE-L

Yong,

M.b. my question was not clear.
I know, "nologging" doesn't work with IOTs. What I'd like to know, if there are any "tricks" (similar to direct-path) to minimize undo/redo when inserting into IOT.

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
Yong Huang
Sent: Wednesday, November 05, 2003 9:49 AM To: Multiple recipients of list ORACLE-L

Hi, Igor,

Direct-path insert does not work for IOTs. This is documented in SQL Reference
for INSERT.

Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear
to me. Documentation says the table has to be NOLOGGING, or its tablespace has
to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+
APPEND */ SELECT, there won't be redo (except for the minimum data dictionary
change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message
was not intended to prove my observation). If somebody reads that differently,
please correct me.

Yong Huang

Igor Neyman <ineyman_at_perceptron.com> wrote:
> As it was recently discussed,
>
> Insert /*+ append */ into <destination_table> select * from
> <source_table>
>
> will produce minimum redo/undo if <destination_table> specified as
> "nologging".
>
>
> But, what if <destination_table> is index-organized table?
> Is it possible to achieve the same results (in regards to amount of
> redo/undo)?
>
> Igor Neyman, OCP DBA
> ineyman_at_perceptron.com

Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Yong Huang
  INET: yong321_at_yahoo.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: Igor Neyman
  INET: ineyman_at_perceptron.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: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.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: Igor Neyman
  INET: ineyman_at_perceptron.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 Wed Nov 05 2003 - 13:44:33 CST