首页 » ORACLE 9i-23ai » 数据去哪了？现实版 （partition data invalid）
数据去哪了？现实版 （partition data invalid）
前几天有业务部门反应有个表的数据带上条件查询不出来,不带数据则可以，表没有做特殊处理，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
Summary:
这是一个比较经典的案例，因为本月分区名不同于原分区规则，后来加载数据时使用了交换分区，而且完全依赖分区名，交换时没有验证有效性，才导致了分区内查询数据不显示，全表或不使用分区键时数据正常显示的现象。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。