This is my very first post, so please be kind if my question is lame.
My customer is running 5.5 (CU4) with the database being Oracle 10gR2 (10.2.0.3). They've experienced some interesting performance issues related to two (2) SQL statements against the i_log table.
The first is a SELECT COUNT(*) from the i_log table Where the i_transaction_no is equal to a Bind Variable (:1) and the code is equal to an "E". I suspect this is a check to see if any of the i_log rows written were Error ("E") records.
The second is a DELETE from the i_log table Where the i_transaction_no is equal to a Bind Variable (:1).
We verified that an index exists on the i_log table which contains the i_transction_no as the high order column and that stats were current. The AWR report indicated that each execution of either of the above 2 statements "touched" several thousand blocks to complete. What made it more interesting is the Plan chosen was using the expected index.
After further research, we realized that a large number (almost 100K) of i_log rows contained an i_transaction_no value of negative one (-1). This resulted in Oracle's CBO picking an access path that "touched" a large number of blocks.
Can anyone explain what a value of negatie one (-1) in the i_transaction_no column represents? Any insight would be greatly appreciated. TIA.
Lenny