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  |
Andrey_R
Messages: 441
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 #675045 is a reply to message #675044] |
Wed, 06 March 2019 09:33  |
cookiemonster
Messages: 13959
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 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
cookiemonster wrote on Wed, 06 March 2019 17:33Depends 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 #675061 is a reply to message #675044] |
Thu, 07 March 2019 03:00 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
BlackSwan wrote on Wed, 06 March 2019 17:25I 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 |
Andrey_R
Messages: 441
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
|
|
|
|
Goto Forum:
Current Time: Thu Nov 28 08:47:31 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
