Re: ORA-1578...block corrupted...error is normal...a block...had a NOLOGGING...operation performed against

On 08/23/2005 08:11:10 PM, Allen, Brandon wrote:
> Here is another test indicating that a direct-path insert to a nologging 
> index is actually logged (like Jared has also shown), which is contrary to 
> the documentation.  

Documentation is plain wrong here. Direct insert is insert in which TABLE 
BLOCKS are
pre-formed by the client and appended after the HW mark. When everything is 
done,
HW mark is moved. B-tree index is a balanced structure (meaning that the hight 
of the 
tree is equal, for each leaf node). This balance is achieved through complex 
algorithms
specifying when the tree has to be split, when the new block will be added to 
an exiting
node etc. Details are explained in The Art Of Computer Programming by D. Knuth.
Adding bunch of blocks to such a complex structure in a bulk fashion is 
completely impossible.
As a matter of fact, standard advice to DBA is to make indexes unusable during 
direct load.
Direct load cannot be done with indexes. With indexes, it is a completely 
normal load which
is not logged. Oracle, as a matter of fact, allows you to bypass logging for 
speed. This used
to be a benchmark trick and now is a technique.

Here is the proof:

I have a table called EMP created by the following command:

 CREATE TABLE "OPS$MGOGALA"."EMP" 
   (    "EMPNO" NUMBER(4,0), 
        "ENAME" VARCHAR2(10), 
        "JOB" VARCHAR2(9), 
        "MGR" NUMBER(4,0), 
        "HIREDATE" DATE, 
        "SAL" NUMBER(7,2), 
        "COMM" NUMBER(7,2), 
        "DEPTNO" NUMBER(2,0), 
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE
   ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
     TABLESPACE "USERS" 
     PCTTHRESHOLD 50;

SQL> set autotrace on statistics;
SQL> insert into emp select * from scott.emp;

14 rows created.


Statistics
----------------------------------------------------------
          1  recursive calls
         30  db block gets
          7  consistent gets
          1  physical reads
          0  redo size
        916  bytes sent via SQL*Net to client
        954  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL> rollback;

Rollback complete.

SQL> insert /*+ append */  into emp select * from scott.emp;

14 rows created.


Statistics
----------------------------------------------------------
          1  recursive calls
         30  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        918  bytes sent via SQL*Net to client
        969  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>


As you can see, statistics is completely identical for both cases when table EMP
is created with ORGANIZATION INDEX clause. Let's see what does the statistics 
look 
like when the table is a heap-organized table:

SQL> drop table emp;

Table dropped.

SQL> purge recyclebin;

Recyclebin purged.

SQL> create table emp as select * from scott.emp where 1=2;

Table created.

SQL> insert into emp select * from scott.emp;

14 rows created.


Statistics
----------------------------------------------------------
        166  recursive calls
         21  db block gets
         63  consistent gets
          0  physical reads
          0  redo size
        922  bytes sent via SQL*Net to client
        954  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL> rollback;

Rollback complete.

SQL>  insert /*+ append */  into emp select * from scott.emp;

14 rows created.


Statistics
----------------------------------------------------------
         45  recursive calls
         29  db block gets
         21  consistent gets
          0  physical reads
       2784  redo size
        906  bytes sent via SQL*Net to client
        970  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>



For a normal insert, we have 166 recursive calls, because each row is inserted 
in 
the existing blocks, using free lists. For the direct insert, we have only 45 
recursive calls, because block(s) are formed directly and only added, bypassing 
the 
need to call SQL. For the index organized table, statistics for "direct" and 
"normal"
insert is identical. For a heap organized, it is not.


There is no such thing as a direct insert into an index (or index table, for 
that matter).
The NOLOGGING clause on an index will bypass logging but will significantly 
slow thing down,
as the index will need to be rebuilt. In other words, the nologging attribute 
on indexes for
anything but index creation is cheating, pure and simple. So called direct 
insert doesn't work
on index structures, pure and simple.

-- 
Mladen Gogala
http://www.mgogala.com


--
http://www.freelists.org/webpage/oracle-l

Other related posts: