在客户的11.2.0.2环境中碰到了这个问题，Oracle在处理包含ROWNUM固化的外部表加载数据时返回错误的结果。
这一篇描述利用ROW_NUMBER来避免错误的产生。
外部表构造描述可以参考：https://yangtingkun.net/?p=35
ROWNUM固化外部表结果集存在问题：：https://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读取中指定时间出现重复的问题。