Oracle Scratchpad

February 21, 2014

Index Compression – aargh

Filed under: Bugs,compression,Indexing,Infrastructure,Oracle — Jonathan Lewis @ 7:57 am GMT Feb 21,2014

The problem with telling people that some feature of Oracle is a “good thing” is that some of those people will go ahead and use it; and if enough people use it some of them will discover a hitherto undiscovered defect. Almost inevitably the bug will turn out to be one of those “combinations” bugs that leaves you thinking: “Why the {insert preferred expression of disbelief here} should {feature X} have anything to do with {feature Y}”.

Here – based on index compression, as you may have guessed from the title – is one such bug. I got it first on, but it’s still there on and [update: fixed by]

rem     Script:         ind_compress_bug.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Feb 2014

create table t1 (
	id1	number not null,
	id2	number not null,
	n1	number,
	v1	varchar2(10)

alter table t1 add constraint t1_pk primary key(id1, id2) using index (
	create index t1_pk on t1(id1, id2) compress 1

create table t2(
	id1	number not null,
	id2	number not null,
	id3	number not null,
	n1	number,
	v1	varchar2(10)

alter table t2 add constraint t2_fk_t1 foreign key(id1, id2) references t1;

It’s quite simple – I’ve got a multi-column primary key, and it’s worth compressing on the first column because that column is fairly repetitive. Then I’ve created another table that has a foreign key constraint referencing my first table. Because I’ve got some replication going on and want to enable parallelism I’ve enabled rowdependencies all over the place. So let’s insert a couple of rows and see what happens – the next bit of text is cut-n-pasted from an SQL*Plus session running a script after a call to set echo on:

SQL> insert into t1 values(1,1,1,'x');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t2 values(1,1,1,1,'x');
insert into t2 values(1,1,1,1,'x')
ERROR at line 1:
ORA-00600: internal error code, arguments: [25027], [5], [394178], [], [], [], [], [], [], [], [], []

For further details, and before you get completely thrilled at the possibility of compressing lots of indexes, keep an eye on:


“Inserting into a table with a foreign key where the base table has a primary key using index key compression and the table also has row dependencies enabled. Stack will include kdsgrds and kdiexi0 (in 12) / kdiexi (in 11, 10)”

As implied by that note from the bug, it also affects 10g.  The bug note reports it as fixed in 12.2.


  1. Jonathan,
    This is not reproduced on 10g, actual version i have tested it is 64 bit on linux, sqlplus connected from windows.

    Comment by jagdeepsangwan — February 21, 2014 @ 9:28 am GMT Feb 21,2014 | Reply

    • Jagdeepsangwan,

      Thanks for checking – it prompted me to have a go with which I now have at hand and that version does display the bug. Presumably it appeared somewhere between 1 and 5. (The comment in the blog should have said: “it seems to apply to” – I hadn’t checked it when I wrote the note.

      Comment by Jonathan Lewis — February 21, 2014 @ 9:53 am GMT Feb 21,2014 | Reply

  2. This bug exists in Oracle

    SQL> insert into t2 values(1,1,1,1,’x’);
    insert into t2 values(1,1,1,1,’x’)
    ERROR at line 1:
    ORA-00600: internal error code, arguments: [25027], [4], [202975939], [], [], [], [], []

    SQL> select * from v$version;

    Oracle Database 10g Enterprise Edition Release – 64bi
    PL/SQL Release – Production
    CORE Production
    TNS for Linux: Version – Production
    NLSRTL Version – Production

    Comment by user — February 21, 2014 @ 10:30 am GMT Feb 21,2014 | Reply

  3. We ran into a nasty oracle bug related to Basic compression where it corrupted a data block and undo segment. Oracle’s internal mechanism called QMI/QMD( Quick Multi insert-delete) don’t deal with oracle basic compressed blocks. In our case, it happened due to large volume delete and cancel of that operation after couple of hours.
    If you have basic compression enabled, make sure you have this patch installed or workaround in place for this bug#14551844. You can get the details of this Bug and patch at
    Also please note that there are many bugs reported for Advanced (OLTP) Compression. Please make sure you do research and apply patches for them.
    Another quick technical detail around compression – if you have a physically corrupt compressed block then oracle support can’t get you the raw data from the block dump since the header is corrupt with physical corruption and the data inside the block has no meaning without the symbol.

    Comment by orasuds — May 21, 2014 @ 6:30 pm BST May 21,2014 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: