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

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Wed, 24 Aug 2005 01:07:27 +0000
Message-Id: <1124845647l.5362l.0l@medo.noip.com>

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

Received on Tue Aug 23 2005 - 20:09:27 CDT