From oracle-l-bounce@freelists.org Tue Aug 23 20:09:27 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j7O19R24031463 for ; Tue, 23 Aug 2005 20:09:27 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j7O19PIP031451 for ; Tue, 23 Aug 2005 20:09:25 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3307C1E5FDC; Tue, 23 Aug 2005 20:09:21 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 20855-07; Tue, 23 Aug 2005 20:09:21 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9C9371E5EE9; Tue, 23 Aug 2005 20:09:20 -0500 (EST) Date: Wed, 24 Aug 2005 01:07:27 +0000 From: Mladen Gogala Subject: Re: ORA-1578...block corrupted...error is normal...a block...had a NOLOGGING...operation performed against To: Brandon.Allen@OneNeck.com Cc: Jared Still , oracle-l@freelists.org In-Reply-To: <04DDF147ED3A0D42B48A48A18D574C45023614AA@NT15.oneneck.corp> (from Brandon.Allen@OneNeck.com on Tue Aug 23 20:11:10 2005) Message-Id: <1124845647l.5362l.0l@medo.noip.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis X-archive-position: 24389 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: gogala@sbcglobal.net Precedence: normal Reply-To: gogala@sbcglobal.net X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-3.7 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 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