Re: Insert append and indexes

From: Eltschinger Markus <do_not_email_at_home.com>
Date: Sat, 10 May 2003 18:16:02 +0200
Message-ID: <1052583373.146691@exnews>

Hello,

please have a look at section "Index Maintenance" in chapter 22 "Direct-Load INSERT" of the
"Oracle8i Concepts"
http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76965/c21dlins.htm#3142 which clearly says:

"For direct-load INSERT on nonpartitioned tables or partitioned tables that have local or
global indexes, index maintenance is done at the end of the INSERT operation. This index
maintenance is performed by the parallel execution servers for parallel direct-load INSERT
or by the single process for serial direct-load INSERT on partitioned or nonpartitioned tables.

If your direct-load INSERT modifies most of the data in a table, you can avoid the
performance impact of index maintenance by dropping the index before the INSERT
and then rebuilding it afterwards."

(The same information on "Index Maintenance", but for Oracle9i, you'll find on
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c21dlins.htm#10696)

Kind regards,
Markus Eltschinger

---

Swisscom IT Services Ltd
Data Warehouse Development
1752 Villars-Sur-Glāne FR
Switzerland
http://www.swisscom.com/it/content/index_EN.html

"TurkBear" <john.greco_at_dot.state.mn.us> wrote in message news:6ftkbvk1vj01gqfv790d64qpf5lih0gbfg_at_4ax.com...
> "Sergey Adamenko" <asv_22_no_spam_at_softline.kiev.ua> wrote:
>
> >
> >>
> >SB> The indexes will be invalid after the load due to the append option
> >>
> >
> >
> >Hi!
> >
> >Does that mean index invalid state?
> >I've made a little test and see no index invalidataion.
> >
> >Sorry if I'm missing something.
> >
> >Best regards,
> >Sergey Adamenko
> >
> >
> >
> >-----------------------------------------------
> >SQL*Plus: Release 8.1.7.0.0 - Production on ×ņ. Ņšā 8 14:53:11 2003
> >
> >(c) Copyright 2000 Oracle Corporation. All rights reserved.
> >
> >
> >Connected to:
> >Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
> >With the Partitioning option
> >JServer Release 8.1.7.4.1 - Production
> >
> >SQL>
> >SQL>
> >SQL> drop table tap;
> >
> >Table dropped.
> >
> >SQL>
> >SQL> create table tap (id number );
> >
> >Table created.
> >
> >SQL>
> >SQL> create unique index idx_tap on tap(id);
> >
> >Index created.
> >
> >SQL>
> >SQL> insert into tap values (1);
> >
> >1 row created.
> >
> >SQL>
> >SQL> insert into tap values (2);
> >
> >1 row created.
> >
> >SQL>
> >SQL> delete from tap;
> >
> >2 rows deleted.
> >
> >SQL>
> >SQL> insert --+append
> > 2 into tap
> > 3 select obj#+1000 from sys.obj$;
> >
> >7478 rows created.
> >
> >SQL>
> >SQL>
> >SQL> select index_name, table_name, status from user_indexes;
> >
> >INDEX_NAME TABLE_NAME STATUS
> >------------------------------ ------------------------------ --------
> >IDX_TAP TAP VALID
> >
> >SQL>
> >SQL>
> >
> >
> Before the insert /* +append */ use the
> alter table idx_tab nologging
> and see if it still works without invalidating the index...The other issue
may be that it is 'valid' but not 'correct' since
> it may not have 'read' the rowid of the bulk inserted rows...
>
>
>
>
> ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
> http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000
Newsgroups
> ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=--- Received on Sat May 10 2003 - 11:16:02 CDT