通過將子查詢替換為具有 INLINE 提示的命名子查詢來進行優化

讓我們有這兩個表：

create table table_x(
 x_id varchar2(100) primary key
);

create table table_y(
 x_id varchar2(100) references table_x(x_id),
 stream varchar2(10),
 val_a number,
 val_b number
);

create index table_y_idx on table_y (x_id, stream);

假設我們在每個表中有數百萬行，每個表table_y包含 0 到 10 行x_id。

以下範例中的查詢通過 filter 返回 200 行substr(x_id, 2, 1) = 'B'。

需要優化查詢：

QUERY 1

select
   x.x_id,
   y.val_a,
   y.val_b
 from table_x x

 left join (select
                x_id,
                max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
                max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
              from table_y
             group by x_id
  ) y on x.x_id = y.x_id

where substr(x.x_id, 2, 1) = 'B'; -- intentionally not use the primary key filter

------
PLAN 1
----------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows    | Bytes    | Cost  | Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |   10000 |  2400000 | 22698 | 00:04:33 |
| * 1 |   HASH JOIN OUTER      |         |   10000 |  2400000 | 22698 | 00:04:33 |
| * 2 |    TABLE ACCESS FULL   | TABLE_X |   10000 |   120000 |   669 | 00:00:09 |
|   3 |    VIEW                |         |   10692 |  2437776 | 22029 | 00:04:25 |
|   4 |     SORT GROUP BY      |         |   10692 |   245916 | 22029 | 00:04:25 |
|   5 |      TABLE ACCESS FULL | TABLE_Y | 1069200 | 24591600 | 19359 | 00:03:53 |
----------------------------------------------------------------------------------
* 1 - access("X"."X_ID"="Y"."X_ID"(+))
* 2 - filter(SUBSTR("X"."X_ID", 2, 1)='B')

有一種顯著優化的方法，因此QUERY 2返回行的速度比QUERY 1. INLINE提示非常重要，因為沒有它，第二個執行的速度與第一個一樣慢。

QUERY 2

with
 table_y_total as (
   select --+ INLINE
       x_id,
       max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
       max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
     from table_y
    group by x_id
)
select
   x.x_id,
   (select val_a from table_y_total y where y.x_id = x.x_id) as val_a,
   (select val_b from table_y_total y where y.x_id = x.x_id) as val_b
 from table_x x
where substr(x.x_id, 2, 1) = 'B';

------
PLAN 2
-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes  | Cost | Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             | 10000 | 120000 |  669 | 00:00:09 |
|   1 |   SORT GROUP BY NOSORT         |             |     1 |     19 |  103 | 00:00:02 |
|   2 |    TABLE ACCESS BY INDEX ROWID | TABLE_Y     |   100 |   1900 |  103 | 00:00:02 |
| * 3 |     INDEX RANGE SCAN           | TABLE_Y_IDX |   100 |        |    3 | 00:00:01 |
|   4 |   SORT GROUP BY NOSORT         |             |     1 |     20 |  103 | 00:00:02 |
|   5 |    TABLE ACCESS BY INDEX ROWID | TABLE_Y     |   100 |   2000 |  103 | 00:00:02 |
| * 6 |     INDEX RANGE SCAN           | TABLE_Y_IDX |   100 |        |    3 | 00:00:01 |
| * 7 |   TABLE ACCESS FULL            | TABLE_X     | 10000 | 120000 |  669 | 00:00:09 |
----------------------------------------------------------------------------------------- 
* 3 - access("X_ID"=:B1)
* 6 - access("X_ID"=:B1)
* 7 - filter(SUBSTR("X"."X_ID", 2, 1)='B')

由於第一個查詢使用較少的程式碼重複，我寧願保留它。

是否有提示或其他技巧可以同時滿足以下條件？

• 保留第一個查詢碼 ( QUERY 1)
• 強制優化器使用第二個計劃 ( PLAN 2)

TL;DR重寫查詢不會讓你到任何地方。使用功能索引來降低訪問數百萬行中的 200 行的成本。

---

好的，用 CTE 和 INLINE 提示替換子查詢與優化無關。提高可讀性？當然。表現？幾乎不。

計劃不同，因為您的查詢並不真正等效。第一個查詢執行連接。第二個查詢在SELECT子句中有單獨的子查詢，而優化器顯然不同。

要使它們“等價”，您必須在查詢 2 中加入結果集。

with
 table_y_total as (
   select --+ INLINE
       x_id,
       max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
       max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
     from table_y
    group by x_id
)
select
   x.x_id,
   y.val_a,
   y.val_b
from table_x x
left outer join table_y on y.x_id = x.x_id
where substr(x.x_id, 2, 1) = 'B';

也沒有“程式碼重複”。該計劃可能與第一個查詢的計劃相匹配。

在您的情況下發生的情況是您有兩個表table_x的行數比table_y. table_y通過內聯對in的訪問，SELECT優化器別無選擇，只能使用索引。通過索引訪問 1% 的行通常比全掃描快。這裡沒有驚喜。

但是，您在每個表中指定了數百萬行的規模。我不知道 X 和 Y 的基數如何相關，但我們真的不需要知道。

全掃描不能很好地擴展。

另一方面，無論你只有 10000 行還是 1000 萬行，你總是只需要 200 個符合substr(x_id, 2, 1) = 'B'條件的行，對吧？

功能索引肯定會在這里工作。

create index table_x_2nd_char_of_pk_idx on table_x (
 substr(x_id, 2, 1) ASC);

一旦您將基數降低到這 200 行，table_y通過其 FK 訪問就是小菜一碟。

---

好的，我在“x 上的全掃描沒有問題”之後添加了這部分。我確定是這樣，但您可以單獨處理。

無論您是否索引訪問謂詞，您都可以確保訪問是隔離的，並且在連接更大的表時基數足夠低。

with
 table_x_filtered as (
   select x.x_id
   from   table_x x
   where  substr(x.x_id, 2, 1) = 'B'
   and    rownum > 0) -- well, better than hints, right?
select 
   x.x_id,
   max(val_a)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_a,
   max(val_b)  KEEP (DENSE_RANK FIRST ORDER BY stream) as val_b
 from table_x_filtered X 
 left outer join table_y y on y.x_id = x.x_id
group by x.x_id

如果table_x要永遠保持很小，優化器應該選擇先對其進行全掃描，然後應用過濾器然後加入。如果沒有，那麼您可以隨時使用leading，正如@panick 建議的那樣，但是如果沒有適當的索引，它無論如何都會變得越來越慢。

引用自：https://dba.stackexchange.com/questions/179624