数据去哪了?现实版 (partition data invalid)

张维照 2019-05-31
18
0 0
摘要:这是一个比较经典的案例,因为本月分区名不同于原分区规则,后来加载数据时使用了交换分区,而且完全依赖分区名,交换时没有验证有效性,才导致了分区内查询数据不显示。

问题描述

前几天有业务部门反应有个表的数据带上条件查询不出来,不带数据则可以,表没有做特殊处理,11.2.0.3 RAC on Exadata,后来找到了问题的原因,和圈里朋友Toms_zhang谈起此事,
weibo圈子影响力就是大,张天师发weibo后引起不小风波,崔华和杨廷坤等大师也纷纷参于讨论, 今晚值班无事在这里把这个事件的源头生产库现实版分享一下。

SQL> select cycle,CODE from ANBOB.T_HIST where cycle=201507 and rownum<10;

CYCLE     CODE
---------- ----------
201507        310
201507        310
201507        310
201507        310
201507        310
201507        310
201507        310
201507        310
201507        310

9 rows selected.

SQL> select cycle,CODE from ANBOB.T_HIST where cycle=201507 and rownum<10 and CODE=310;
no rows selected

SQL> @desc ANBOB.T_HIST
Name                            Null?    Type
------------------------------- -------- ----------------------------
1      CYCLE                                    NUMBER(6)
2      CODE                                      NUMBER(3)
3      USERNUM                                  NUMBER(13)
4      SMSCLASS                                 VARCHAR2(10)
5      BILLSMSCODE                              VARCHAR2(20)
6      SERVCODE                                 VARCHAR2(30)
7      ROAMTYPE                                 VARCHAR2(20)

SQL> @tab ANBOB.T_HIST
Show tables matching condition "%ANBOB.T_HIST%" (if schema is not specified then current users tables only are shown)...

OWNER                TABLE_NAME                     TYPE     NUM_ROWS        BLOCKS     EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE     COMPRESS
-------------------- ------------------------------ ---- ------------ ------------- --------- ------ ------ ----------------- ---------- --------
ANBOB               T_HIST                          PTAB   3446436100      27769664         0      0    112 20140523 15:24:14          1

SQL> @partkeys ANBOB.T_HIST

OBJEC OWNER                          NAME                           COLUMN_NAME                    COLUMN_POSITION
----- ------------------------------ ------------------------------ ------------------------------ ---------------
TABLE ANBOB                         T_HIST                 CYCLE                                        1
TABLE ANBOB                         T_HIST                 CODE                                       2

SQL> @seg ANBOB.T_HIST

SEG_MB OWNER           SEGMENT_NAME              SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- --------------- ------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
3996 ANBOB               T_HIST                 B2_201507_310                  TABLE PARTITION      TBSHIS                        255744        711    1248261
4 ANBOB               T_HIST                 B2_201507_311                  TABLE PARTITION      TBSHIS                           256        711      54789
5568 ANBOB               T_HIST                 B2_201507_312                  TABLE PARTITION      TBSHIS                        356352        711      26629
4 ANBOB               T_HIST                 B2_201507_313                  TABLE PARTITION      TBSHIS                           256        711      33797
4 ANBOB               T_HIST                 B2_201507_314                  TABLE PARTITION      TBSHIS                           256        711      10757
4216 ANBOB               T_HIST                 B2_201507_315                  TABLE PARTITION      TBSHIS                        269824        711     904197
4 ANBOB               T_HIST                 B2_201507_316                  TABLE PARTITION      TBSHIS                           256        711      28421
3268 ANBOB               T_HIST                 B2_201507_317                  TABLE PARTITION      TBSHIS                        209152        711     211461
4 ANBOB               T_HIST                 B2_201507_318                  TABLE PARTITION      TBSHIS                           256        711     241669
4 ANBOB               T_HIST                 B2_201507_319                  TABLE PARTITION      TBSHIS                           256        711     208901
1748 ANBOB               T_HIST                 B2_201507_335                  TABLE PARTITION      TBSHIS                        111872        711     595717
1140 ANBOB               T_HIST                 B2_201507_MAX                  TABLE PARTITION      TBSHIS                         72960        273     398597


专家解答

note:
可以看到这就是一个普通的分区表是以两个数值字段分区,当查询不带CODE字段时数据可以显示,带上则无返回数据,数据去哪了?
后来发现如果字段上带上to_char就可以显示,如下:

<pre>
SQL> select cycle,CODE,dump(CODE,16) dmp  from ANBOB.T_HIST where cycle=201507 and rownum<10 and to_char(CODE)=310;

CYCLE     CODE DMP
---------- ---------- --------------------
201507        310 Typ=2 Len=3: c2,4,b
201507        310 Typ=2 Len=3: c2,4,b
201507        310 Typ=2 Len=3: c2,4,b
201507        310 Typ=2 Len=3: c2,4,b
201507        310 Typ=2 Len=3: c2,4,b
201507        310 Typ=2 Len=3: c2,4,b
201507        310 Typ=2 Len=3: c2,4,b
201507        310 Typ=2 Len=3: c2,4,b
201507        310 Typ=2 Len=3: c2,4,b

9 rows selected.

Note:

开始怀疑是数据编码中有不可显示字符,但是从dump函数看数据没有问题,不存在数据库上的特殊处理,也有想到是索引指向问题,所以使用了no_index和full hint不使用索引效果依然存在.
下面是表的定义:

SQL> @ddl ANBOB.T_HIST

PL/SQL procedure successfully completed.

DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
-----------------------------------------------------------------------------------------------

CREATE TABLE "ANBOB"."T_HIST"
(    "CYCLE" NUMBER(6,0),
"CODE" NUMBER(3,0),
"USERNUM" NUMBER(13,0),
"SMSCLASS" VARCHAR2(10),
"BILLSMSCODE" VARCHAR2(20),
"SERVCODE" VARCHAR2(30),
"ROAMTYPE" VARCHAR2(20)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS"
PARTITION BY RANGE ("CYCLE","CODE")
(PARTITION "B2_201408_310"  VALUES LESS THAN (201408, 311) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
...
PARTITION "B2_201506_MAX"  VALUES LESS THAN (201506, MAXVALUE) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBSHIS" ,
PARTITION "B2_201507_310"  VALUES LESS THAN (201507, 310) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBSHIS" ,
PARTITION "B2_201507_311"  VALUES LESS THAN (201507, 311) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBSHIS" ,
PARTITION "B2_201507_312"  VALUES LESS THAN (201507, 312) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBSHIS" ,
PARTITION "B2_201507_313"  VALUES LESS THAN (201507, 313) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBSHIS" ,
PARTITION "B2_201507_314"  VALUES LESS THAN (201507, 314) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBSHIS" ,
PARTITION "B2_201507_315"  VALUES LESS THAN (201507, 315) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBSHIS" ,
PARTITION "B2_201507_316"  VALUES LESS THAN (201507, 316) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBSHIS" ,
PARTITION "B2_201507_317"  VALUES LESS THAN (201507, 317) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBSHIS" ,
PARTITION "B2_201507_318"  VALUES LESS THAN (201507, 318) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBSHIS" ,
PARTITION "B2_201507_319"  VALUES LESS THAN (201507, 319) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBSHIS" ,
PARTITION "B2_201507_335"  VALUES LESS THAN (201507, 335) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBSHIS" ,
PARTITION "B2_201507_MAX"  VALUES LESS THAN (201507, MAXVALUE) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBSHIS" ,

SQL> @tabpart ANBOB.T_HIST

TABLE_OWNER          TABLE_NAME                    POS COM PARTITION_NAME             NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE_RAW                HIGH_VALUE_LENGTH COMPRESS COMPRESS_FOR
-------------------- ---------------------- ---------- --- ------------------------ ---------- ------------------ ----------------------------- ----------------- -------- ------------
ANBOB                   T_HIST                121 NO  B2_201506_310                                        0 201506, 311                                  11 DISABLED
ANBOB                   T_HIST                122 NO  B2_201506_311                                        0 201506, 312                                  11 DISABLED
ANBOB                   T_HIST                123 NO  B2_201506_312                                        0 201506, 313                                  11 DISABLED
ANBOB                   T_HIST                124 NO  B2_201506_313                                        0 201506, 314                                  11 DISABLED
ANBOB                   T_HIST                125 NO  B2_201506_314                                        0 201506, 315                                  11 DISABLED
ANBOB                   T_HIST                126 NO  B2_201506_315                                        0 201506, 316                                  11 DISABLED
ANBOB                   T_HIST                127 NO  B2_201506_316                                        0 201506, 317                                  11 DISABLED
ANBOB                   T_HIST                128 NO  B2_201506_317                                        0 201506, 318                                  11 DISABLED
ANBOB                   T_HIST                129 NO  B2_201506_318                                        0 201506, 319                                  11 DISABLED
ANBOB                   T_HIST                130 NO  B2_201506_319                                        0 201506, 320                                  11 DISABLED
ANBOB                   T_HIST                131 NO  B2_201506_335                                        0 201506, 336                                  11 DISABLED
ANBOB                   T_HIST                132 NO  B2_201506_MAX                                        0 201506, MAXVALUE                             16 DISABLED
ANBOB                   T_HIST                133 NO  B2_201507_310                                        0 201507, 310                                  11 DISABLED
ANBOB                   T_HIST                134 NO  B2_201507_311                                        0 201507, 311                                  11 DISABLED
ANBOB                   T_HIST                135 NO  B2_201507_312                                        0 201507, 312                                  11 DISABLED
ANBOB                   T_HIST                136 NO  B2_201507_313                                        0 201507, 313                                  11 DISABLED
ANBOB                   T_HIST                137 NO  B2_201507_314                                        0 201507, 314                                  11 DISABLED
ANBOB                   T_HIST                138 NO  B2_201507_315                                        0 201507, 315                                  11 DISABLED
ANBOB                   T_HIST                139 NO  B2_201507_316                                        0 201507, 316                                  11 DISABLED
ANBOB                   T_HIST                140 NO  B2_201507_317                                        0 201507, 317                                  11 DISABLED
ANBOB                   T_HIST                141 NO  B2_201507_318                                        0 201507, 318                                  11 DISABLED
ANBOB                   T_HIST                142 NO  B2_201507_319                                        0 201507, 319                                  11 DISABLED
ANBOB                   T_HIST                143 NO  B2_201507_335                                        0 201507, 335                                  11 DISABLED
ANBOB                   T_HIST                144 NO  B2_201507_MAX                                        0 201507, MAXVALUE                             16 DISABLED

NOTE:

当然你会发现201507的分区和以前的分区表与partkey的关系有些变化,之前比如name 310对应的是less 311的,不过理论上分区名错误又能怎样?不应该影响数据吧。下面看sql的执行计划

SQL> SET AUTOT TRACE EXP STAT
SQL> select cycle,CODE from ANBOB.T_HIST where cycle=201507 and rownum<10 and CODE=310;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2777563331

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |     9 |  8714   (8)| 00:00:30 |       |       |
|*  1 |  COUNT STOPKEY              |                |       |       |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE    |                |     1 |     9 |  8714   (8)| 00:00:30 |   134 |   134 |
|*  3 |    TABLE ACCESS STORAGE FULL|         T_HIST |     1 |     9 |  8714   (8)| 00:00:30 |   134 |   134 |
--------------------------------------------------------------------------------------------------------------

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

1 - filter(ROWNUM<10)
3 - storage("CYCLE"=201507 AND "CODE"=310)
filter("CYCLE"=201507 AND "CODE"=310)

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

note:

这是一个EXADATA 一体机,执行计划看到’TABLE ACCESS STORAGE FULL’, 因为对EXADATA不了解开始怀疑是Exadata BUG? 是不是存储上有什么地方不同?请教了几位exadata前辈, 没有发现明确bug.

SQL> create table test1 as select cycle,CODE from ANBOB.T_HIST where cycle=201507 and rownum<10;
Table created.

SQL> select * from test1 where CODE=310;
9 rows selected.

note:
把表里的数据通过CTAS发现数据在新表里是可以查询的。那会是哪的问题?

SQL> SELECT rowid, DBMS_ROWID.Rowid_object(ROWID),cycle,CODE from ANBOB.T_HIST where cycle=201507 and rownum<10;

ROWID              DBMS_ROWID.ROWID_OBJECT(ROWID)      CYCLE     CODE
------------------ ------------------------------ ---------- ----------
AAE63bAEXAAAUlAAAA                        1289691     201507        310
AAE63bAEXAAAUlAAAB                        1289691     201507        310
AAE63bAEXAAAUlAAAC                        1289691     201507        310
AAE63bAEXAAAUlAAAD                        1289691     201507        310
AAE63bAEXAAAUlAAAE                        1289691     201507        310
AAE63bAEXAAAUlAAAF                        1289691     201507        310
AAE63bAEXAAAUlAAAG                        1289691     201507        310
AAE63bAEXAAAUlAAAH                        1289691     201507        310
AAE63bAEXAAAUlAAAI                        1289691     201507        310
9 rows selected.

SQL> create table test1 as select * from  ANBOB.T_HIST where rowid='AAE63bAEKAAApdAAAA';
Table created.

SQL> delete ANBOB.T_HIST where rowid='AAE63bAEKAAApdAAAA';
1 row deleted.

SQL> insert into ANBOB.T_HIST select * from  test1 ;
1 row created.

SQL>  select cycle,CODE from ANBOB.T_HIST where cycle=201507 and rownum<10 and CODE=310;

CYCLE     CODE
---------- ----------
201507        310

Note:
把表数据库重新insert 回原表就可以查询,下面dump block看是否有不同?

SELECT rowid,
DBMS_ROWID.Rowid_relative_fno(ROWID) "FILE",
DBMS_ROWID.Rowid_block_number(ROWID) "BLOCK",
DBMS_ROWID.Rowid_row_number(ROWID)   "ROW",
cycle,CODE from ANBOB.T_HIST where cycle=201507 and rownum<10 and CODE=310;

ROWID                   FILE      BLOCK        ROW      CYCLE     CODE
------------------ --------- ---------- ---------- ---------- ----------
AAE64tALHAAANYVAAA       711      54805          0     201507        310

System altered.
Object id on Block? Y
seg/obj: 0x13ae2d  csc: 0xd2c.73708a00  itc: 2  flg: E  typ: 1 - DATA
brn: 0  bdba: 0xb1c0d600 ver: 0x01 opc: 0
inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x002a.021.000f4487  0xc081f386.2deb.21  --U-    1  fsc 0x0000.73710c31
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0xb1c0d615
data_block_dump,data header at 0x7f92b7c96a64
===============
tsiz: 0x3f98
hsiz: 0x14
pbl: 0x7f92b7c96a64
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x3f19
avsp=0x3f05
tosp=0x3f05
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x3f19
block_row_dump:
tab 0, row 0, @0x3f19
tl: 127 fb: --H-FL-- lb: 0x1  cc: 24
col  0: [ 4]  c3 15 10 08
col  1: [ 3]  c2 04 0b
col  2: [ 8]  c7 04 0b 15 0b 45 4c 63
col  3: [ 4]  49 53 4d 47
col  4: [ 6]  39 30 33 39 32 32
col  5: [ 3]  4d 43 4e
col  6: [ 1]  30
...
end_of_block_dump
2015-08-04 17:08:05.760776 : kjbmbassert [0xd615.2c7]
2015-08-04 17:08:05.768817 : kjbmsassert(0xd615.2c7)(to 2)(lvl 4)
End dump data blocks tsn: 17 file#: 711 minblk 54805 maxblk 54805

SQL> SELECT rowid,
2         DBMS_ROWID.Rowid_relative_fno(ROWID) "FILE",
3         DBMS_ROWID.Rowid_block_number(ROWID) "BLOCK",
4         DBMS_ROWID.Rowid_row_number(ROWID)   "ROW",
5        cycle,CODE from ANBOB.T_HIST where cycle=201507 and rownum<10;

ROWID                  FILE      BLOCK        ROW      CYCLE     CODE
------------------ -------- ---------- ---------- ---------- ----------
AAE63bAEXAAAUmAAAA      279      84352          0     201507        310
AAE63bAEXAAAUmAAAB      279      84352          1     201507        310
AAE63bAEXAAAUmAAAC      279      84352          2     201507        310
AAE63bAEXAAAUmAAAD      279      84352          3     201507        310
AAE63bAEXAAAUmAAAE      279      84352          4     201507        310
AAE63bAEXAAAUmAAAF      279      84352          5     201507        310
AAE63bAEXAAAUmAAAG      279      84352          6     201507        310
AAE63bAEXAAAUmAAAH      279      84352          7     201507        310
AAE63bAEXAAAUmAAAI      279      84352          8     201507        310

Object id on Block? Y
seg/obj: 0x13addb  csc: 0xd2c.53908371  itc: 3  flg: E  typ: 1 - DATA
brn: 0  bdba: 0x45c14902 ver: 0x01 opc: 0
inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x03dc.043.0000d5a7  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x00fb.032.000484ce  0x990eea2b.356b.83  --U-  112  fsc 0x0000.6b59d2a0
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x45c14980
data_block_dump,data header at 0x7fcf7e05da7c
===============
tsiz: 0x3f80
hsiz: 0xf2
pbl: 0x7fcf7e05da7c
76543210
flag=--------
ntab=1
nrow=112
frre=-1
fsbo=0xf2
fseo=0x761
avsp=0x66f
tosp=0x66f
0xe:pti[0]      nrow=112        offs=0
0x12:pri[0]     offs=0x3efd
0x14:pri[1]     offs=0x3e7a
0x16:pri[2]     offs=0x3df7
0x18:pri[3]     offs=0x3d74
0x1a:pri[4]     offs=0x3cf2
0x1c:pri[5]     offs=0x3c73
0x1e:pri[6]     offs=0x3bf6
0x20:pri[7]     offs=0x3b70
0x22:pri[8]     offs=0x3aea
...
block_row_dump:
tab 0, row 0, @0x3efd
tl: 131 fb: --H-FL-- lb: 0x2  cc: 24
col  0: [ 4]  c3 15 10 08
col  1: [ 3]  c2 04 0b
col  2: [ 8]  c7 04 0b 09 08 32 3a 58
col  3: [ 6]  49 53 4d 47 4d 33
col  4: [ 6]  38 30 31 32 31 38
col  5: [12]  31 30 36 35 34 30 33 33 30 30 30 37
col  6: [ 1]  30
...

Note:
col 1# 记录存储值没有什么不同,但是应该注意到两类记录的SEG DATA_OBJ#不同,相同的分区键正常应该是同一个分区才对。

SQL> @doid 1289773

owner        object_name            O_PARTITION         object_type        CREATED           LAST_DDL_TIME     status    DATA_OBJECT_ID
------------ ---------------------  ------------------- ------------------ ----------------- ----------------- --------- --------------
ANBOB        T_HIST                 B2_201507_311       TABLE PARTITION    20150626 06:29:59 20150804 10:25:10 VALID            1289773

SQL> @doid 1289691

owner           object_name             O_PARTITION            object_type        CREATED           LAST_DDL_TIME     status    DATA_OBJECT_ID
--------------- ----------------------- ---------------------- ------------------ ----------------- ----------------- --------- --------------
ANBOB          T_HIST                 B2_201507_310             TABLE PARTITION    20150626 06:29:59 20150804 09:46:40 VALID            1289691

Note:
到这里相信都已经知道原因, 因为cycle和code是范围分区的分区键,where 条件中用201507和310 通过表定义很容易查询落到一个分区那就是less than (201507,311),但是这些数据实际都在(201507,310)的分区,所以这就为什么加上code后查不到数据的原因,那为什么to_char就能显示数据呢?下面看一下执行计划就明白了

SQL> select cycle,CODE from ANBOB.T_HIST where cycle=201507 and rownum<10 and to_char(CODE)=310;
9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1223269115

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |     9 | 62363   (8)| 00:03:32 |       |       |
|*  1 |  COUNT STOPKEY              |                |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR  |                |     1 |     9 | 62363   (8)| 00:03:32 |   133 |   144 |
|*  3 |    TABLE ACCESS STORAGE FULL|         T_HIST |     1 |     9 | 62363   (8)| 00:03:32 |   133 |   144 |
--------------------------------------------------------------------------------------------------------------

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

1 - filter(ROWNUM<10)
3 - storage("CYCLE"=201507 AND TO_NUMBER(TO_CHAR("CODE"))=310)
filter("CYCLE"=201507 AND TO_NUMBER(TO_CHAR("CODE"))=310)

Note:
刚才没有TO_char前使用的执行计划是”PARTITION RANGE SINGLE” 单分区扫描,现在加为第2个分区键列数据类型不符CBO先择了使用第1个分区键的”PARTITION RANGE ITERATOR”多分区扫描,所以to_char后可以查出数据。

找到了问题的原因解决方法就非常简单了。从max临时拆分出一临时分区201507_336,然后通过交换分区的方式把数据换出去再重新换一下,再重新批量rename partition, drop 掉最小的分区。

create table ANBOB.change_tmp as select * from ANBOB.T_HIST where 1=2;

ALTER TABLE ANBOB.T_HIST
EXCHANGE PARTITION B2_201507_335
WITH TABLE ANBOB.change_tmp
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;

ALTER TABLE ANBOB.T_HIST
EXCHANGE PARTITION B2_201507_336
WITH TABLE ANBOB.change_tmp
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;
重复的不再演示...

SQL> alter table ANBOB.T_HIST  rename partition B2_201507_316 to B2_201507_315;
...

Note:
调整后数据恢复了正常, 奇怪的是数据是怎么进来的, 了解到是通过一个包装的java程序处理的,而且在数据库里发现了名字相仿的临时表,怀疑也是和我刚才修正时一样使用的交换分区,并且WITHOUT VALIDATION,完全依赖于分区名,这里值得深思,如何才能避免?
正常的insert 是无法把不符合分区条件的数据加载到分区中的,如果不通过特殊手段的处理,暂时想不出除了交换分区还有哪种原因。

SQL> insert into ANBOB.T_HIST  partition(B2_201507_310) select * from test1;
insert into ANBOB.T_HIST  partition(B2_201507_310) select * from test1
*
ERROR at line 1:
ORA-14401: inserted partition key is outside specified partition

ummary:
这是一个比较经典的案例,因为本月分区名不同于原分区规则,后来加载数据时使用了交换分区,而且完全依赖分区名,交换时没有验证有效性,才导致了分区内查询数据不显示,全表或不使用分区键时数据正常显示的现象。

「喜欢文章,快来给作者赞赏墨值吧」

评论

0
0
Oracle
订阅
欢迎订阅Oracle频道,订阅之后可以获取最新资讯和更新通知。
墨值排行
今日本周综合
近期活动
全部
相关课程
全部