Oracle Scratchpad

February 21, 2014

Index Compression – aargh

Filed under: Bugs,compression,Indexing,Infrastructure,Oracle — Jonathan Lewis @ 7:57 am BST 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 11.1.0.7, but it’s still there on 11.2.0.4 and 12.1.0.1


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

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)
)
rowdependencies
;

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 11.2.0.4 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>
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:

Bug 18125878 : ORA-600 [25027] ON INSERT IF TABLE HAS ROWDEPENDENCIES AND COMPRESS ON PK

“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.

5 Comments »

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

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

    • Jagdeepsangwan,

      Thanks for checking – it prompted me to have a go with 10.2.0.5 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 BST Feb 21,2014 | Reply

  2. This bug exists in Oracle 10.2.0.4.0:

    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;

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

    Comment by user — February 21, 2014 @ 10:30 am BST 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 support.oracle.com.
    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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,173 other followers