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
|
|
|
|
Current Time: Thu Apr 25 17:09:15 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
