Home » RDBMS Server » Server Administration » Corrupted index causing data-integrity issues (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Corrupted index causing data-integrity issues [message #675043] Wed, 06 March 2019 08:42 Go to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

Hi all,
I have a table called MYTAB

When I try to see how much rows in it:

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 6 15:58:24 2019

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> set autot on
SQL> select count(*) from MYTAB;

  COUNT(*)
----------
     24094


Execution Plan
----------------------------------------------------------
Plan hash value: 1403349748

-----------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     1 |    76   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |            |     1 |            |          |
|   2 |   INDEX FULL SCAN| IND_UQ1_MYTAB | 54300 |    76   (0)| 00:00:01 |
-----------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        386  consistent gets
         95  physical reads
          0  redo size
        348  bytes sent via SQL*Net to client
        364  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL>

so then I try to force a full scan and find out different result:

SQL> select /*+ full(MYTAB) */ count(*) from MYTAB;

  COUNT(*)
----------
     54692


Execution Plan
----------------------------------------------------------
Plan hash value: 2395106977

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   583   (1)| 00:00:07 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MYTAB | 54300 |   583   (1)| 00:00:07 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2139  consistent gets
       2089  physical reads
          0  redo size
        348  bytes sent via SQL*Net to client
        364  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>



Another check from another angle. Here's what the index is made of:

SQL> col index_name for a30
SQL> col column_name for a30
SQL> select index_name,column_name from user_ind_columns where table_name = 'MYTAB';

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
IND_UQ1_MYTAB                     MYTAB_COL1
IND_UQ1_MYTAB                     MYTAB_COL2
IND_UQ1_MYTAB                     MYTAB_COL3
IND_UQ1_MYTAB                     MYTAB_COL4
IND_UQ1_MYTAB                     MYTAB_COL5
IND_UQ1_MYTAB                     MYTAB_COL6
IND_UQ1_MYTAB                     MYTAB_COL7
IND_UQ1_MYTAB                     MYTAB_COL8
IND_UQ1_MYTAB                     MYTAB_COL9
IND_UQ1_MYTAB                     MYTAB_COL10
IND_UQ1_MYTAB                     MYTAB_COL11

11 rows selected.



It is unique and appears as if valid:




SQL>  select UNIQUENESS, INDEX_TYPE,STATUS from user_indexes where index_name = 'IND_UQ1_MYTAB';

UNIQUENES INDEX_TYPE                  STATUS
--------- --------------------------- --------
UNIQUE    NORMAL                      VALID

SQL>


SQL>

and it is also used in a query like this:

set autot on 



SQL>
SQL> set lines 900
SQL>
SQL>
SQL> select count(*) from (
  2  select
  3  MYTAB_COL1   ,
  4  MYTAB_COL2   ,
  5  MYTAB_COL3   ,
  6  MYTAB_COL4   ,
  7  MYTAB_COL5   ,
  8  MYTAB_COL6   ,
  9  MYTAB_COL7   ,
 10  MYTAB_COL8   ,
 11  MYTAB_COL9   ,
 12  MYTAB_COL10   ,
 13  MYTAB_COL11   ,
 14  count(*)
 15  from MYTAB
 16  group by
 17  MYTAB_COL1   ,
 18  MYTAB_COL2   ,
 19  MYTAB_COL3   ,
 20  MYTAB_COL4   ,
 21  MYTAB_COL5   ,
 22  MYTAB_COL6   ,
 23  MYTAB_COL7   ,
 24  MYTAB_COL8   ,
 25  MYTAB_COL9   ,
 26  MYTAB_COL10   ,
 27  MYTAB_COL11
 28  having count(*) > 1);

  COUNT(*)
----------
         0


Execution Plan
----------------------------------------------------------
Plan hash value: 1669129406

--------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |     1 |       |    76   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE         |            |     1 |       |            |          |
|   2 |   VIEW                  |            |  1184 |       |    76   (0)| 00:00:01 |
|*  3 |    FILTER               |            |       |       |            |          |
|   4 |     SORT GROUP BY NOSORT|            |  1184 | 71040 |    76   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN    | IND_UQ1_MYTAB | 54300 |  3181K|    76   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(COUNT(*)>1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        386  consistent gets
          0  physical reads
          0  redo size
        345  bytes sent via SQL*Net to client
        364  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>




But when I force a full scan to actually access the rows of the table,
It does find duplicate rows:



SQL>
SQL>
SQL> select count(*) from (
  2  select /*+ full (MYTAB) */
  3  MYTAB_COL1    ,
  4  MYTAB_COL2    ,
  5  MYTAB_COL3    ,
  6  MYTAB_COL4    ,
  7  MYTAB_COL5    ,
  8  MYTAB_COL6    ,
  9  MYTAB_COL7    ,
 10  MYTAB_COL8    ,
 11  MYTAB_COL9    ,
 12  MYTAB_COL10    ,
 13  MYTAB_COL11    ,
 14  count(*)
 15  from MYTAB
 16  group by
 17  MYTAB_COL1   ,
 18  MYTAB_COL2   ,
 19  MYTAB_COL3   ,
 20  MYTAB_COL4   ,
 21  MYTAB_COL5   ,
 22  MYTAB_COL6   ,
 23  MYTAB_COL7   ,
 24  MYTAB_COL8   ,
 25  MYTAB_COL9   ,
 26  MYTAB_COL10   ,
 27  MYTAB_COL11
 28  having count(*) > 1);

  COUNT(*)
----------
      2139


Execution Plan
----------------------------------------------------------
Plan hash value: 2868423533

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |       |   587   (1)| 00:00:08 |
|   1 |  SORT AGGREGATE       |      |     1 |       |            |          |
|   2 |   VIEW                |      |  1184 |       |   587   (1)| 00:00:08 |
|*  3 |    FILTER             |      |       |       |            |          |
|   4 |     HASH GROUP BY     |      |  1184 | 71040 |   587   (1)| 00:00:08 |
|   5 |      TABLE ACCESS FULL| MYTAB | 54300 |  3181K|   584   (1)| 00:00:08 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(COUNT(*)>1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2139  consistent gets
          0  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        364  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL>




I assume that (after consulting with whoever needed to confirm it's ok from application logic POV ) dropping the index, removing duplicates and re-creating the index should return everything to a normal state.

The question is, is it a bug ? should I open a SR with Oracle Support for it ?


TIA,
Andrey
Re: Corrupted index causing data-integrity issues [message #675044 is a reply to message #675043] Wed, 06 March 2019 09:25 Go to previous messageGo to next message
BlackSwan
Messages: 26538
Registered: January 2009
Location: SoCal
Senior Member
I am curious what you see by doing as below

ANALYZE INDEX IND_UQ1_MYTAB VALIDATE STRUCTURE;
Re: Corrupted index causing data-integrity issues [message #675045 is a reply to message #675044] Wed, 06 March 2019 09:33 Go to previous messageGo to next message
cookiemonster
Messages: 13626
Registered: September 2008
Location: Rainy Manchester
Senior Member
Depends on how you got in that state, but there are certainly ways of getting into that state that oracle would not regard as a bug. e.g.
SQL> CREATE TABLE bob (a NUMBER);

Table created


SQL> INSERT INTO bob SELECT MOD(ROWNUM, 10) FROM dual CONNECT BY LEVEL < 20;

19 rows inserted


SQL> create index bob_idx on bob(a);

Index created


SQL> alter table bob add constraint bob_uq unique(a) deferrable enable novalidate;

Table altered


SQL> select * from bob;

         A
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
         0
         1
         2
         3
         4
         5
         6
         7
         8
         9

19 rows selected


SQL> insert into bob values (1);
insert into bob values (1)

ORA-00001: unique constraint (BOB.BOB_UQ) violated

SQL> 

It's the novalidate clause that's the issue. It's a clause that basically exists to say to oracle - this data is ok for the constraint so skip checking it to save time. If the data isn't ok for the constraint then you have a problem.
Re: Corrupted index causing data-integrity issues [message #675048 is a reply to message #675045] Wed, 06 March 2019 10:11 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

cookiemonster wrote on Wed, 06 March 2019 17:33
Depends on how you got in that state, but there are certainly ways of getting into that state that oracle would not regard as a bug. e.g.
...........
......
....
It's the novalidate clause that's the issue. It's a clause that basically exists to say to oracle - this data is ok for the constraint so skip checking it to save time. If the data isn't ok for the constraint then you have a problem.

It's not the same for sure, as it's not a constraint, it's just a "naked" unique index.. :

SQL> select UNIQUENESS from user_indexes where table_name = 'BOB'
  2  UNION ALL
  3  select UNIQUENESS from user_indexes where table_name = 'MYTAB';

UNIQUENES
---------
NONUNIQUE
UNIQUE
Re: Corrupted index causing data-integrity issues [message #675049 is a reply to message #675048] Wed, 06 March 2019 10:17 Go to previous messageGo to next message
cookiemonster
Messages: 13626
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think there's a way to get indexes in that state as well but can't find it at the moment.
Re: Corrupted index causing data-integrity issues [message #675053 is a reply to message #675049] Wed, 06 March 2019 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

An UNIQUE index, as long as it is VALID, is always correct unless something wrong happened and BlackSwan statement will tell it.
You can also rebuild the index (or better drop and recreate it) then test again.

Re: Corrupted index causing data-integrity issues [message #675061 is a reply to message #675044] Thu, 07 March 2019 03:00 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

BlackSwan wrote on Wed, 06 March 2019 17:25
I am curious what you see by doing as below

ANALYZE INDEX IND_UQ1_MYTAB VALIDATE STRUCTURE;

Unfortunately at the moment I am no longer able to do that, as the index was dropped on the testlab database.
I will post my results when I have a fresh copy of it, if stil relevant.. many thanks
Re: Corrupted index causing data-integrity issues [message #675201 is a reply to message #675053] Thu, 14 March 2019 09:20 Go to previous message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Wed, 06 March 2019 20:26

An UNIQUE index, as long as it is VALID, is always correct unless something wrong happened and BlackSwan statement will tell it.
You can also rebuild the index (or better drop and recreate it) then test again.


Eventually it was fixed with dropping, removing duplicates and creating again.

Many thanks everyone who replied and invested hist time and attention in answering this!

Andrey
Previous Topic: Need Help for Temp Tablespace
Next Topic: LOCK USER AT A SPECIFIC DATE
Goto Forum:
  


Current Time: Sat Jun 15 19:55:02 CDT 2019