Oracle 19c全表扫描对临时表空间的影响及查询执行逻辑问询

• 单次全表扫描本身通常不会直接消耗临时表空间，因为全表扫描是直接读取表数据块返回结果，无需将数据写入临时段。但如果扫描过程中伴随以下操作，就会占用临时表空间：
  • 排序操作：比如ORDER BY、GROUP BY、DISTINCT这类需要排序的逻辑
  • 哈希连接：当Oracle选择哈希连接执行计划时，会将其中一张表的数据构建成哈希表存入临时表空间，另一张表扫描后匹配哈希表。若两张表都做全表扫描且数据量较大，哈希表会占用大量临时空间
  • 并行查询：并行全表扫描可能会将中间结果写入临时段
• 同一查询中的多次全表扫描（比如你的语句里对sales和book_author都做FTS），如果执行计划选择哈希连接，两张表的全表扫描会触发哈希表构建——这是临时空间耗尽的常见原因：哪怕最终返回行数少，哈希连接的构建阶段需要处理全量数据，临时空间占用取决于参与连接的表的大小，而非最终结果集。

针对你给出的查询：

select /*+ FULL(a) FULL(b) */
 b.author_key
 from
   sales a,
   book_author b
where
   a.book_key=b.book_key

• Oracle查询优化器不会先返回两张表的所有行再过滤。即使指定了FTS提示，优化器依然会根据连接条件（a.book_key=b.book_key）选择高效的执行方式：

  • 若选择嵌套循环连接：会先扫描其中一张表（通常是数据量更小的表），然后用每一条book_key去另一张表扫描匹配，本质是边扫描边过滤，不会生成全量笛卡尔积
  • 若选择哈希连接：会先扫描其中一张表构建哈希表，再扫描另一张表逐行匹配哈希表中的book_key，同样不会先取出两张表的所有行再过滤
  • 只有在完全没有连接条件（即笛卡尔积查询）的情况下，才会返回两张表的所有行组合

• 如果添加ORDER BY子句：

  • 当查询结果需要排序时，Oracle会先完成连接操作得到符合条件的结果集，再对结果集进行排序。如果结果集较大，排序操作会将数据写入临时表空间进行磁盘排序，这会进一步消耗临时空间，可能加剧临时表空间耗尽的问题
  • 优化器理论上可能尝试调整执行计划（比如在连接前先对单表排序），但因为你指定了FTS提示，这种可能性较低，大概率还是先连接再排序，依赖临时空间完成排序操作

内容的提问来源于stack exchange，提问作者goswell