Re: Insert append and indexes

"Eltschinger Markus" <do_not_email_at_home.com> сообщил/сообщила в новостях следующее: news:1052583373.146691_at_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...
> >

Well,
From the section, you just quoted, I see that "...index maintenance is DONE...", in contrast to 'ALTER TABLE MOVE'. And not exclusions from this rule.

Some how the index could be <<'valid' but not 'correct' since it may not have 'read' the rowid of the bulk inserted rows>>?

Best regards,
Sergey Adamenko Received on Sun May 11 2003 - 03:32:11 CDT