Oracle 外部表。优化选择查询

Question

我必须从 Oracle 外部表执行许多选择。

我有 10 个看起来很像这样的游标（ext_temp 是外部表）

CURSOR F_CURSOR (day IN varchar,code Number,orig Number)
    IS
    select NVL(sum(table_4.f),0) 
     from ext_temp table_4
    where
      --couple of conditions here, irrelevant for the question at hand.
      AND TO_CHAR(table_4.day,'YYYYMMDD') = day
      AND table_4.CODE = code
      AND table_4.ORIG = orig;

并且外部表有大约 22659 个寄存器。

我的脚本主循环看起来像这样

   for each register in some_query: --22659 registers
       open F_cursor(register.day,register.code,register.orig);
       --open 9 more cursors

       fetch F_cursor into some_var;  
       --fetch 9 more cursors, with the same structure

查询占用太多。我从这里知道我不能有任何索引或DML。

那么有没有办法让它跑得更快呢？我可以重写我的 plsql 脚本，但我认为我没有时间了。

更新：遗漏了一个重要细节。

我不是数据库的所有者或 DBA。那家伙不希望他的数据库中有任何额外的信息（大约 3GB 的数据），而外部表是我们能从他那里得到的全部信息。他不允许我们创建临时表。我不会假装质疑他的理由，但外部表并不是解决这个问题的方法。所以，我们被他们困住了。

Answer 1

将它们设为 Oracle 表。

外部表是用来替换SQL*LOADER的，而不是每天使用它们。

每当您的底层文件发生更改时，只需运行导入脚本即可将外部表的内容加载到 Oracle 表中。

这是您的同名者对此的看法（窃取自 此处）：

您正在使用外部表而不是sqlldr。

使用外部表，您可以

• 在一个语句中将平面文件与现有表合并。
• 将平面文件排序到您想要很好压缩的表格中。
• 进行并行直接路径加载——无需分割输入文件，写入
  无数的脚本等等
• 从存储过程或触发器中有效运行 sqlldr（插入不是 sqlldr）
• 进行多表插入
• 通过管道 plsql 函数传输数据以进行清理/转换

等等。它们而不是sqlldr——将数据获取到数据库，而无需首先使用sqlldr。

您通常不会在操作系统中日常查询它们，而是使用它们来加载数据。

更新：

使用 3GB 表永远无法获得良好的性能，因为 Oracle 必须使用 3GB > 对每个查询进行全扫描，这将是一流的磁盘读取主轴移动全扫描，而不是您可以在计划中看到但在实际执行时间中几乎无法注意到的廉价缓存模仿。

尝试说服那个人为您创建一个临时表，您可以使用它来处理数据，并在会话开始时从外部表加载数据。

这不是最好的解决方案，因为它需要在临时表空间中为每个会话保留单独的表副本，但它在性能方面要好得多。

Answer 2

如果您必须解决那些没有意义但又无法更改的限制，那真的很困难……

您最好读取一次外部表，然后在类似索引的数据中构建所需的数据代码中的结构（基本上是一个数组，其中每个元素对应您要查找的每个寄存器）。

因此，您的光标将如下所示：

CURSOR F_CURSOR (day IN varchar, orig IN Number)
    IS
    select NVL(sum(table_4.f),0) value, table_4.CODE register
     from ext_temp table_4
    where
      --couple of conditions here, irrelevant for the question at hand.
      AND TO_CHAR(table_4.day,'YYYYMMDD') = day
      -- AND table_4.CODE = code -- don't use this condition!
      AND table_4.ORIG = orig;

您的寄存器循环将变成光标循环：

open F_cursor(register.day,register.orig);
LOOP
    fetch F_cursor into some_var;
    EXIT WHEN F_cursor%NOT_FOUND
    result (some_var.register) := some_var.value;
END LOOP;

因此，您只需要对所有寄存器进行一个循环，而不是为每个寄存器循环遍历外部表。

这可以扩展到您提到的十个光标。

Answer 3

您可以将外部表数据写入临时索引（如果需要）表，然后对其执行多个查询。

create your_temp_table as select * from ext_temp;
create index your_desired_index on your_temp_table(indexed_field);

然后直接使用 your_temp_table 执行所有查询。

Answer 4

虽然完全同意 Quassnoi 的建议，即外部表似乎不是正确的解决方案，以及 DCookie 的类比，即您被束缚并被扔到海里并被要求游泳，但至少可能有一种方法来构建您的程序，以便外部表只能读取一次。根据您的描述，我认为所有 10 个游标都在从外部表读取，这意味着您强制 Oracle 扫描外部表 10 次。

假设这个推论是正确的，最简单的答案可能是使外部表成为驱动光标，类似于 IronGoofy 的建议。根据下面代码片段中的 some_query 正在执行的操作，

for each register in some_query

并假设查询返回与外部表中相同数量的行这一事实并非巧合，最简单的选择是做类似

FOR register in (select * from ext_temp)
LOOP
  -- Figure out if the row should have been part of cursor 1
  IF( <<set of conditions>> ) 
  THEN
    <<do something>>
  -- Figure out if the row should have been part of cursor 2
  ELSIF( ... )
  ...
END LOOP;

or

FOR register in (select * 
                   from ext_temp a, 
                        (<<some query>>) b 
                  where a.column_name = b.column_name )
LOOP
  -- Figure out if the row should have been part of cursor 1
  IF( <<set of conditions>> ) 
  THEN
    <<do something>>
  -- Figure out if the row should have been part of cursor 2
  ELSIF( ... )
  ...
END LOOP;

的事情 更进一步将逻辑从游标（和 IF 语句）移到驱动游标中应该会更有效。使用上面更简单的代码片段（当然，您可以将 some_query 加入到这些示例中。

FOR register in (select a.*,
                        NVL(sum( (case when condition1 and condition2
                                       then table_4.f
                                       else 0
                                       end) ),
                             0) f_cursor_sum
                  from ext_temp table_4)
LOOP
  <<do something>>
END LOOP;

即使在执行此操作之后，您仍然发现正在执行一些逐行处理，那么您甚至可以更进一步，从驱动游标执行 BULK COLLECT 到本地声明的集合中，并对该集合进行操作。您几乎肯定不希望将 3 GB 的数据提取到本地集合中（尽管可能会破坏 PGA）。让 DBA 得出结论，临时表并不是一件坏事，我不建议这样做），使用 LIMIT 子句一次获取几百行应该会使事情变得更有效率。