全表扫描对临时表空间的影响

huangapple go评论47阅读模式
英文:

Impacts of a full table scan on temp tablespace

问题

我正在处理在Oracle 19c中运行的查询。特定查询中有许多FTS提示,但查询经常耗尽临时空间,尽管返回的行数并不多。我想知道一个或多个全表扫描对临时空间有什么影响?

在下面的查询中,查询引擎是否会从ales和book_author表中返回每一行,然后应用where子句,还是where子句将首先执行并限制返回的行数。如果添加了order by子句会怎样?

选择
/*+ FULL(a) FULL(b) */
b.author_key

销售 a,
book_author b
在哪里
a.book_key = b.book_key

英文:

I am working on a query being run in Oracle 19c. The particular query has many FTS hints and the query routinley runs out of temp space, even though there isn't a lot of rows returned.
I'm wondering what impact one or many full table scans has on temp space?

In the query below, will the query engine return every row from both the ales and book_author tables and then apply the where clase, or will the where clause be executed first and limit the number of rows retuned. What if there was a order by clause added?

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

答案1

得分: 1

以下是您要翻译的内容:

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

假设Oracle选择散列连接(hash join),这将读取sales表的100%(或实际上是非空book_key的行的100%,因为您使用内连接,而NULL永远不等于NULL,Oracle将隐式添加一个"WHERE book_key IS NOT NULL",希望减少输入的大小)。然后,它将对book_author执行相同操作(顺序可以颠倒)。这些全表扫描不使用临时空间。您需要支付它们的成本主要是I/O和一些CPU。

然后,每个行源的结果将使用连接键(book_key)上的散列机制进行连接,这个操作需要大量的CPU和内存,并且如果每个进程的PGA不足,则通常需要临时空间。这将需要额外的读/写I/O到临时表。散列操作的结果将仅包括两侧都有匹配项的行(内连接)。

添加ORDER BY子句将需要额外的排序,除非分配了足够的PGA,否则也将在临时空间中执行。一般来说,不要在SQL中添加不必要的ORDER BY子句,因为它们会带来成本。

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

Assuming Oracle chooses a hash join, this will read 100% of the sales table (or actually, 100% of the rows with a non-null book_key, as because you have an inner join and NULL never equals NULL, Oracle will implicitly add a "WHERE book_key IS NOT NULL" in hopes of reducing the size of the inputs). It will then (the order could be reversed) do the same thing to book_author. These full table scans do not use temp space. You pay their cost in (mainly) I/O and some CPU.

The results of each row source will then be joined together using the hashing mechanism on the join key (book_key), which operation is CPU and memory intensive, and often requires temp space if insufficient PGA is available per process. That will require additional read/write I/O to temp. The result of that hashing operation will be only the rows that have a match on both sides (inner join).

Adding an order by will require additional sorting, which unless you have sufficient PGA assigned will also be done in temp space. In general, don't throw unneeded ORDER BY clauses on your SQL, they have a cost.

huangapple
  • 本文由 发表于 2023年3月7日 10:06:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/75657448.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定