oracle 10.2.0.4 出现查询同样的条件不同的值

zhouyh139

版本：
SMS2@gstgate>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
情况1：
SMS2@gstgate>explain plan for SELECT seq_id, status
  2    FROM g_sgip_send_phone
  3   WHERE recv_time >= to_date('2013-07-28 11:37:31', 'yyyy-mm-dd hh24:mi:ss')
  4     AND recv_time <= to_date('2013-07-28 11:37:33', 'yyyy-mm-dd hh24:mi:ss')
  5     AND to_phone = '8613000000000';

Explained.
执行计划如下：
SMS2@gstgate>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3827887301

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     1 |    32 | 16398   (1)| 00:03:17 |
|*  1 |  TABLE ACCESS FULL| G_SGIP_SEND_PHONE |     1 |    32 | 16398   (1)| 00:03:17 |
---------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

   1 - filter("TO_PHONE"='8613000000000' AND "RECV_TIME"<=TO_DATE(' 2013-07-28
              11:37:33', 'syyyy-mm-dd hh24:mi:ss') AND "RECV_TIME">=TO_DATE(' 2013-07-28
              11:37:31', 'syyyy-mm-dd hh24:mi:ss'))

15 rows selected.

执行结果：
SMS2@gstgate> SELECT seq_id, status
  2    FROM g_sgip_send_phone
WHERE recv_time >= to_date('2013-07-28 11:37:31', 'yyyy-mm-dd hh24:mi:ss')
   AND recv_time <= to_date('2013-07-28 11:37:33', 'yyyy-mm-dd hh24:mi:ss')
  5     AND to_phone = '8613000000000';

    SEQ_ID     STATUS
---------- ----------
323125039         12


情况2：
SMS2@gstgate>explain plan for SELECT seq_id,seq_id,seq_id,report_time, status
  2    FROM g_sgip_send_phone
  3   WHERE recv_time >= to_date('2013-07-28 11:37:31', 'yyyy-mm-dd hh24:mi:ss')
  4     AND recv_time <= to_date('2013-07-28 11:37:33', 'yyyy-mm-dd hh24:mi:ss')
  5     AND to_phone = '8613000000000';

Explained.
执行计划：
SMS2@gstgate>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3827887301

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     1 |    40 | 16398   (1)| 00:03:17 |
|*  1 |  TABLE ACCESS FULL| G_SGIP_SEND_PHONE |     1 |    40 | 16398   (1)| 00:03:17 |
---------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

   1 - filter("TO_PHONE"='8613000000000' AND "RECV_TIME"<=TO_DATE(' 2013-07-28
              11:37:33', 'syyyy-mm-dd hh24:mi:ss') AND "RECV_TIME">=TO_DATE(' 2013-07-28
              11:37:31', 'syyyy-mm-dd hh24:mi:ss'))

15 rows selected.
执行结果：
SELECT seq_id,seq_id,seq_id,report_time, status
  2    FROM g_sgip_send_phone
WHERE recv_time >= to_date('2013-07-28 11:37:31', 'yyyy-mm-dd hh24:mi:ss')
   AND recv_time <= to_date('2013-07-28 11:37:33', 'yyyy-mm-dd hh24:mi:ss')
  5     AND to_phone = '8613000000000';

    SEQ_ID     SEQ_ID     SEQ_ID REPORT_TIME             STATUS
---------- ---------- ---------- ------------------- ----------
                                 2013-07-29 09:17:04         12

zhouyh139

当report_time在查询条件中时，seq_id没有值，当report_time不在查询条件中时，seq_id可以显示值

Maclean Liu(刘相兵

set linesize 200 pagesize 1400
set autotrace on;

SELECT DBMS_ROWID.rowid_block_number (ROWID),
       DBMS_ROWID.rowid_relative_fno (ROWID),
       ROWID,
       DUMP (seq_id, 16),
       DUMP (status, 16)
  FROM g_sgip_send_phone
WHERE recv_time >= TO_DATE ('2013-07-28 11:37:31', 'yyyy-mm-dd hh24:mi:ss')
       AND recv_time <=
              TO_DATE ('2013-07-28 11:37:33', 'yyyy-mm-dd hh24:mi:ss')
       AND to_phone = '8613000000000';



SELECT DBMS_ROWID.rowid_block_number (ROWID),
       DBMS_ROWID.rowid_relative_fno (ROWID),
       ROWID,
       DUMP (seq_id, 16),
       DUMP (status, 16),
       DUMP(report_time,16)
  FROM g_sgip_send_phone
WHERE recv_time >= TO_DATE ('2013-07-28 11:37:31', 'yyyy-mm-dd hh24:mi:ss')
       AND recv_time <=
              TO_DATE ('2013-07-28 11:37:33', 'yyyy-mm-dd hh24:mi:ss')
       AND to_phone = '8613000000000';


并给出alter system dump datafile x block x    对应块的TRACE

zhouyh139

SMS2@gstgate>set linesize 200 pagesize 1400
SMS2@gstgate>set autotrace on;
SELECT DBMS_ROWID.rowid_block_number (ROWID),
  2         DBMS_ROWID.rowid_relative_fno (ROWID),
  3         ROWID,
  4         DUMP (seq_id, 16),
  5         DUMP (status, 16)
  6    FROM g_sgip_send_phone
  7  WHERE recv_time >= TO_DATE ('2013-07-28 11:37:31', 'yyyy-mm-dd hh24:mi:ss')
  8         AND recv_time <=
  9                TO_DATE ('2013-07-28 11:37:33', 'yyyy-mm-dd hh24:mi:ss')
10         AND to_phone = '8613000000000';

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) ROWID DUMP(SEQ_ID,16)  DUMP(STATUS,16)
------------------------------------ ------------------------------------ ------------------
     44459       143 AAAnzSACPAAAK2rABN    Typ=2 Len=6: c5,4,18,d,33,28      Typ=2 Len=2: c1,d

Execution Plan
----------------------------------------------------------
Plan hash value: 3827887301

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     1 |    44 | 16398   (1)| 00:03:17 |
|*  1 |  TABLE ACCESS FULL| G_SGIP_SEND_PHONE |     1 |    44 | 16398   (1)| 00:03:17 |
---------------------------------------------------------------------------------------

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

   1 - filter("TO_PHONE"='8613000000000' AND "RECV_TIME"<=TO_DATE(' 2013-07-28
              11:37:33', 'syyyy-mm-dd hh24:mi:ss') AND "RECV_TIME">=TO_DATE(' 2013-07-28
              11:37:31', 'syyyy-mm-dd hh24:mi:ss'))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      74211  consistent gets
          0  physical reads
          0  redo size
        916  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SMS2@gstgate>alter session set tracefile_identifier='ian';
Session altered.

SMS2@gstgate>alter system dump datafile 143 block 44459;

System altered.

zhouyh139

SMS2@gstgate>SELECT   dump(seq_id, 16),
  2         dump(status, 16)
  3    FROM g_sgip_send_phone
  4   WHERE recv_time >= to_date('2013-07-28 11:37:31', 'yyyy-mm-dd hh24:mi:ss')
  5     AND recv_time <= to_date('2013-07-28 11:37:33', 'yyyy-mm-dd hh24:mi:ss')
  6     AND to_phone = '8613000000000';

DUMP(SEQ_ID,16)                    DUMP(STATUS,16)
----------------------------------------------------
Typ=2 Len=6: c5,4,18,d,33,28      Typ=2 Len=2: c1,d


SELECT  report_time ,dump(seq_id, 16),
  2         dump(status, 16)
  3    FROM g_sgip_send_phone
  4   WHERE recv_time >= to_date('2013-07-28 11:37:31', 'yyyy-mm-dd hh24:mi:ss')
   AND recv_time <= to_date('2013-07-28 11:37:33', 'yyyy-mm-dd hh24:mi:ss')
  6     AND to_phone = '8613000000000';

REPORT_TIME    DUMP(SEQ_ID,16)    DUMP(STATUS,16)
--------------------------------------------------------------------------------
2013-07-29 09:17:04    NULL      Typ=2 Len=2: c1,d

zhouyh139

表的结构如下：
create table G_SGIP_SEND_PHONE
(
  to_phone    VARCHAR2(20) not null,
  recv_time   DATE default sysdate not null,
  send_time   DATE,
  send_code   VARCHAR2(50),
  subret_time DATE,
  subret_code VARCHAR2(50),
  report_time DATE,
  report_code VARCHAR2(100),
  status      NUMBER default 0 not null,
  send_seq_id INTEGER not null,
  seq_id      NUMBER not null
)

Maclean Liu(刘相兵

ntab=2
nrow=180

        r0_9ir2=0x0
        mec_kdbh9ir2=0x2
                      76543210
        shcf_kdbh9ir2=----------
                  76543210
        flag_9ir2=--R---OC
                fcls_9ir2[4]={ 0 32768 32768 2 }
                perm_9ir2[11]={ 4 5 6 0 7 1 10 3 2 8 9 }


tab 1, row 77, @0x4ce
tl: 46 fb: --H-FL-- lb: 0x0  cc: 10
col  0: *NULL*
col  1: *NULL*
col  2: [ 2]  c1 0d
col  3: *NULL*
col  4: [13]  38 36 31 33 30 30 30 30 30 30 30 30 30
col  5: [ 7]  78 71 07 1c 0c 26 21
col  6: *NULL*
col  7: *NULL*
col  8: [ 6]  c5 04 18 33 50 05
col  9: [ 6]  c5 04 18 0d 33 28
bindmp: 2c 00 08 03 cb c1 0d ff d5 38 36 31 33 30 30 30 30 30 30 30 30 30 cf 78 71 07 1c 0c 26 21 ff ff ce c5 04 18 33 50 05 ce c5 04 18 0d 33 28
tab 1, row 78, @0x47b
tl: 83 fb: --H-FL-- lb: 0x0  cc: 11
col  0: [ 8]  cc e1 bd bb b3 c9 b9 a6
col  1: *NULL*
col  2: [ 2]  c1 09
col  3: [12]  36 31 2c ce b4 d6 aa d4 ad d2 f2 21
col  4: [13]  38 36 31 38 36 38 36 37 32 33 33 33 36
col  5: [ 7]  78 71 07 1d 0a 14 2c
col  6: [ 7]  78 71 07 1d 0a 14 2c
col  7: *NULL*
col  8: [ 6]  c5 04 18 60 56 3c
col  9: [ 6]  c5 04 18 3a 39 5f
col 10: [ 7]  78 71 07 1d 0a 14 2c
bindmp: 2c 00 0b 00 d0 cc e1 bd bb b3 c9 b9 a6 ff ca c1 09 d4 36 31 2c ce b4 d6 aa d4 ad d2 f2 21 d5 38 36 31 38 36 38 36 37 32 33 33 33 36 cf 78 71 07 1d 0a 14 2c cf 78 71 07 1d 0a 14 2c ff ce c5 04 18 60 56 3c ce c5 04 18 3a 39 5f cf 78 71 07 1d 0a 14 2c
tab 1, row 79, @0x429
tl: 82 fb: --H-FL-- lb: 0x0  cc: 11
col  0: [ 8]  cc e1 bd bb b3 c9 b9 a6
col  1: *NULL*
col  2: [ 2]  c1 09
col  3: [12]  36 31 2c ce b4 d6 aa d4 ad d2 f2 21
col  4: [13]  38 36 31 38 36 38 36 37 32 33 33 33 36
col  5: [ 7]  78 71 07 1d 0a 14 2c
col  6: [ 7]  78 71 07 1d 0a 14 2c
col  7: *NULL*
col  8: [ 6]  c5 04 18 60 56 42
col  9: [ 5]  c5 04 18 3a 3a
col 10: [ 7]  78 71 07 1d 0a 14 2c
bindmp: 2c 00 0b 00 d0 cc e1 bd bb b3 c9 b9 a6 ff ca c1 09 d4 36 31 2c ce b4 d6 aa d4 ad d2 f2 21 d5 38 36 31 38 36 38 36 37 32 33 33 33 36 cf 78 71 07 1d 0a 14 2c cf 78 71 07 1d 0a 14 2c ff ce c5 04 18 60 56 42 cd c5 04 18 3a 3a cf 78 71 07 1d 0a 14 2c
tab 1, row 80, @0x3d6
tl: 83 fb: --H-FL-- lb: 0x0  cc: 11
col  0: [ 8]  cc e1 bd bb b3 c9 b9 a6
col  1: *NULL*
col  2: [ 2]  c1 09
col  3: [12]  36 31 2c ce b4 d6 aa d4 ad d2 f2 21
col  4: [13]  38 36 31 38 36 36 39 38 39 39 38 37 37
col  5: [ 7]  78 71 07 1d 0a 14 2c
col  6: [ 7]  78 71 07 1d 0a 14 2c
col  7: *NULL*
col  8: [ 6]  c5 04 18 60 56 46
col  9: [ 6]  c5 04 18 3a 3a 05
col 10: [ 7]  78 71 07 1d 0a 14 2c
bindmp: 2c 00 0b 00 d0 cc e1 bd bb b3 c9 b9 a6 ff ca c1 09 d4 36 31 2c ce b4 d6 aa d4 ad d2 f2 21 d5 38 36 31 38 36 36 39 38 39 39 38 37 37 cf 78 71 07 1d 0a 14 2c cf 78 71 07 1d 0a 14 2c ff ce c5 04 18 60 56 46 ce c5 04 18 3a 3a 05 cf 78 71 07 1d 0a 14 2c

clevernby

从dump内容来看，怎么是个cluster啊，贴下完整的DDL语句吧

Maclean Liu(刘相兵

ntab=2  ==> cluster table?
r0_9ir2=0x0 ==> 9ir2升级上来的？

tl: 46 fb: --H-FL-- lb: 0x0  cc: 10
==》
别人都是11个字段 仅有问题行是10个字段


上面要的DUMP(report_time,16) 你就是不贴出来

zhouyh139

SMS2@gstgate> SELECT dump(seq_id, 16)
  2     FROM g_sgip_send_phone
  3    WHERE recv_time >=
  4          to_date('2013-07-28 11:37:31', 'yyyy-mm-dd hh24:mi:ss')
  5      AND recv_time <=
  6          to_date('2013-07-28 11:37:33', 'yyyy-mm-dd hh24:mi:ss')
  7      AND to_phone = '8613000000000';

DUMP(SEQ_ID,16)
--------------------------------------------------------------------------------
Typ=2 Len=6: c5,4,18,d,33,28



SMS2@gstgate>SELECT dump(report_time, 16), dump(seq_id, 16)
  2    FROM g_sgip_send_phone
  3   WHERE recv_time >= to_date('2013-07-28 11:37:31', 'yyyy-mm-dd hh24:mi:ss')
   AND recv_time <= to_date('2013-07-28 11:37:33', 'yyyy-mm-dd hh24:mi:ss')
  5     AND to_phone = '8613000000000';

DUMP(REPORT_TIME,16)
--------------------------------------------------------------------------------
DUMP(SEQ_ID,16)
--------------------------------------------------------------------------------
Typ=12 Len=7: 78,71,7,1d,a,12,5
NULL

clevernby

有点意思
seq_id      NUMBER not null ，这表示cc只可能是11
rownum 77 offset 0x4ce的记录cc为10，这表示col 10为null，但col 10就是seq_id，是not null的
数据似乎存在corruption

Maclean Liu(刘相兵

这套库是 9iR2升级上来的 ，没错吧？  这个表是cluster的，没错吧？

zhouyh139

Maclean Liu(刘相兵 发表于 2013-7-31 16:39
这套库是 9iR2升级上来的 ，没错吧？  这个表是cluster的，没错吧？

都不是，是从10.0.1升级的，是普通堆表

Maclean Liu(刘相兵

10.1 应当使用了和9iR2一样的结构

给出该表的 DDL语句 完整的

zhouyh139

-- Create table
create table G_SGIP_SEND_PHONE_TEMP1
(
  to_phone    VARCHAR2(20) not null,
  recv_time   DATE not null,
  send_time   DATE,
  send_code   VARCHAR2(50),
  subret_time DATE,
  subret_code VARCHAR2(50),
  report_time DATE,
  report_code VARCHAR2(100),
  status      NUMBER not null,
  send_seq_id INTEGER not null,
  seq_id      NUMBER not null
)
tablespace TBS_SMS2_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 2
    next 2
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

Maclean Liu(刘相兵

show parameter compat

zhouyh139

SMS2@gstgate>show parameter compat

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      10.2.0.3.0
plsql_v2_compatibility               boolean     FALSE

Maclean Liu(刘相兵

我建议你move重建这张表