Re: ORA-1578...block corrupted...error is normal...a block...had a NOLOGGING...operation performed against
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: Brandon.Allen@xxxxxxxxxxx
- Date: Wed, 24 Aug 2005 01:07:27 +0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
