ROWNUM固化外部表结果集存在问题(二)

在客户的11.2.0.2环境中碰到了这个问题,Oracle在处理包含ROWNUM固化的外部表加载数据时返回错误的结果。
这一篇描述利用ROW_NUMBER来避免错误的产生。
外部表构造描述可以参考:http://yangtingkun.net/?p=35
ROWNUM固化外部表结果集存在问题::http://yangtingkun.net/?p=38
这个例子是在本机上运行,因此只是告警日志的名称发生了变化,其他的语法和上一篇文章中的一致,当然D_ALERT目录需要指向background_dump_dest参数指定的路径:

SQL> CREATE TABLE T_ALERT
  2  (TEXT VARCHAR2(4000)
  3  )
  4  ORGANIZATION EXTERNAL
  5  (TYPE ORACLE_LOADER
  6  DEFAULT DIRECTORY D_ALERT
  7  ACCESS PARAMETERS
  8  (RECORDS DELIMITED BY NEWLINE
  9  FIELDS (TEXT (1:255) CHAR))
 10  LOCATION (' alert_ytk102.log'));
TABLE created.
SQL> SELECT * FROM T_ALERT WHERE ROWNUM <= 10;
TEXT
--------------------------------------------------------------------------
Dump file d:\oracle\product\admin\ytk102\bdump\alert_ytk102.log
Sat DEC 25 15:55:43 2010
ORACLE V10.2.0.5.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows NT Version V6.1
CPU : 4 - TYPE 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1594M/2995M, Ph+PgF:4318M/5989M, VA:1911M/2047M
Sat DEC 25 15:55:43 2010
Starting ORACLE instance (normal)
已选择10行。

如果我们用ROW_NUMBER() OVER()分析函数来代替ROWNUM伪列,则上一篇提到的错误不再会出现,而且两种写法都可以得到正确的结果。
需要注意的是,ROW_NUMBER() OVER()分析函数需要一个ORDER BY列,而在这个例子中,没有明确的排序列,是根据读取顺序的,因此这里选择了ROWID进行排序:

SQL> WITH A AS (SELECT ROW_NUMBER() OVER(ORDER BY ROWID) RN, TEXT FROM T_ALERT)
  2  SELECT * FROM A B 
  3  WHERE B.RN >= (SELECT MIN(C.RN) FROM A C WHERE TEXT = 'Mon Sep 19 07:18:21  2011');
        RN TEXT
---------- --------------------------------------------------------------------------------
      5739 Mon Sep 19 07:18:21  2011
      5740 Successfully onlined Undo Tablespace 1.
      5741 Mon Sep 19 07:18:21  2011
      5742 SMON: enabling tx recovery
      5743 Mon Sep 19 07:18:21  2011
      5744 DATABASE Characterset IS ZHS16GBK
      5745 Opening WITH internal Resource Manager plan
      5746 replication_dependency_tracking turned off (no async multimaster replication found)
      5747 Starting background process QMNC
      5748 QMNC started WITH pid=17, OS id=5412
      5749 Mon Sep 19 07:18:29  2011
      5750 Completed: ALTER DATABASE OPEN
已选择12行。
SQL> WITH A AS (SELECT ROW_NUMBER() OVER(ORDER BY ROWID) RN, TEXT FROM T_ALERT)
  2  SELECT B.* 
  3  FROM A B, 
  4   (SELECT MIN(RN) RN FROM A WHERE TEXT = 'Mon Sep 19 07:18:21  2011') C
  5  WHERE B.RN >= C.RN;
        RN TEXT
---------- --------------------------------------------------------------------------------
      5739 Mon Sep 19 07:18:21  2011
      5740 Successfully onlined Undo Tablespace 1.
      5741 Mon Sep 19 07:18:21  2011
      5742 SMON: enabling tx recovery
      5743 Mon Sep 19 07:18:21  2011
      5744 DATABASE Characterset IS ZHS16GBK
      5745 Opening WITH internal Resource Manager plan
      5746 replication_dependency_tracking turned off (no async multimaster replication found)
      5747 Starting background process QMNC
      5748 QMNC started WITH pid=17, OS id=5412
      5749 Mon Sep 19 07:18:29  2011
      5750 Completed: ALTER DATABASE OPEN
已选择12行。
SQL> WITH A AS (SELECT ROWNUM RN, TEXT FROM T_ALERT)
  2  SELECT B.* 
  3  FROM A B, 
  4   (SELECT MIN(RN) RN FROM A WHERE TEXT = 'Mon Sep 19 07:18:21  2011') C
  5  WHERE B.RN >= C.RN;
        RN TEXT
---------- --------------------------------------------------------------------------------
      5739 Mon Sep 19 07:18:21  2011
      5740 Successfully onlined Undo Tablespace 1.
      5741 Mon Sep 19 07:18:21  2011
      5742 SMON: enabling tx recovery
      5743 Mon Sep 19 07:18:21  2011
      5744 DATABASE Characterset IS ZHS16GBK
      5745 Opening WITH internal Resource Manager plan
      5746 replication_dependency_tracking turned off (no async multimaster replication found)
      5747 Starting background process QMNC
      5748 QMNC started WITH pid=17, OS id=5412
      5749 Mon Sep 19 07:18:29  2011
      5750 Completed: ALTER DATABASE OPEN
已选择12行。

同时还修改了上一篇文章SQL的一个小bug,解决了ALERT读取中指定时间出现重复的问题。

This entry was posted in BUG and tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *