我想要完成的是從一個表中獲取與基于特定過濾器的另一個表不匹配的行。這兩個表相對較大，所以我試圖根據一定的時間范圍過濾它們。

到目前為止我所經歷的步驟。

1. 從“T1”獲取過去 3 天的 ID

SELECT
id 
FROM T1
WHERE STARTTIME BETWEEN '3 days ago' AND 'now';

執行時間為4.5s。

2. 從“T2”獲取過去 3 天的 ID

SELECT
id 
FROM T2
WHERE STARTTIME BETWEEN '3 days ago' AND 'now';

執行時間為2.5s。

3. 現在我嘗試使用NOT EXISTS將兩個陳述句的結果合并為一個

SELECT
CID
FROM T1
WHERE STARTTIME BETWEEN '3 days ago' AND 'now'
AND NOT EXISTS (
  SELECT NULL FROM T2
  WHERE T1.ID = T2.ID 
  AND STARTTIME BETWEEN '3 days ago' AND 'now'
);

執行時間為23s。

我也嘗試了這個答案INNER JOIN的邏輯，認為它是有道理的，但我沒有得到任何結果，所以我無法正確評估。

Is there a better way to construct this statement that could possibly lead to a faster execution time?

19.01.2022 - Update based on comments

1. Expected result can contain any number of rows between 1 and 10 000

2. The used columns have the following indexes:

CREATE INDEX IX_T1_CSTARTTIME
   ON T1 (CSTARTTIME ASC)
   TABLESPACE MYHOSTNAME_DATA1;

CREATE INDEX IX_T2_CSTARTTIME
   ON T2 (CSTARTTIME ASC)
   TABLESPACE MYHOSTNAME_DATA2;

NOTE: Just noticed that the indexes are located on different table spaces, could this be a potential issue as well?

3. Following the excellent comments from Marmite Bomber here is the execution plan for the statement:

   ---------------------------------------------------------------------------------------------
   | Id  | Operation            | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
   --------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT     |       | 21773 |  2019K|       |  1817K  (1)| 00:01:12 |
   |*  1 |  HASH JOIN RIGHT ANTI|       | 21773 |  2019K|   112M|  1817K  (1)| 00:01:12 |
   |*  2 |   TABLE ACCESS FULL  | T2     |  2100K|    88M|       |  1292K  (1)| 00:00:51 |
   |*  3 |   TABLE ACCESS FULL  | T1     |  2177K|   105M|       |   512K  (1)| 00:00:21 |
   ---------------------------------------------------------------------------------------------
   
   Predicate Information (identified by operation id):
   ---------------------------------------------------
   
   1 - access("T2"."ID"="T1"."ID")
   2 - filter("STARTTIME">=1642336690000 AND "T2"."ID" IS NOT NULL 
              AND "STARTTIME"<=1642595934000)
   3 - filter("STARTTIME">=1642336690000 AND 
              "STARTTIME"<=1642595934000)
   
   Column Projection Information (identified by operation id):
   -----------------------------------------------------------
   
   1 - (#keys=1; rowset=256) "T1"."ID"[CHARACTER,38]
   2 - (rowset=256) "T2"."ID"[CHARACTER,38]
   3 - (rowset=256) "ID"[CHARACTER,38]
   

uj5u.com熱心網友回復：

有沒有更好的方法來構造這個可能導致更快執行時間的陳述句？

你的基本職責是寫SQL陳述句，Oracle的基本職責是自帶執行計劃

如果您不滿意（但您應該知道，兩個來源的組合使用NOT EXISTS將花費更長的時間從來源中提取資料的時間總和）您的第一步應該是驗證執行計劃（而不是嘗試重寫宣告）。

在此處查看更多詳細資訊

EXPLAIN PLAN  SET STATEMENT_ID = 'stmt1' into   plan_table  FOR
SELECT
PAD
FROM T1
WHERE STARTTIME BETWEEN date'2021-01-11' AND date'2021-01-13'
AND NOT EXISTS (
  SELECT NULL FROM T2
  WHERE T1.ID = T2.ID 
  AND STARTTIME BETWEEN date'2021-01-11' AND date'2021-01-13'
);

SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'stmt1','ALL'));

這是你應該看到的

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  1999 |   150K| 10175   (1)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT ANTI|      |  1999 |   150K| 10175   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | T2   |  2002 | 26026 |  4586   (1)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL  | T1   |  4002 |   250K|  5589   (1)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."ID"="T2"."ID")
   2 - filter("STARTTIME"<=TO_DATE(' 2021-01-13 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss') AND "STARTTIME">=TO_DATE(' 2021-01-11 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))
   3 - filter("STARTTIME"<=TO_DATE(' 2021-01-13 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss') AND "STARTTIME">=TO_DATE(' 2021-01-11 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))

請注意，hash join（這里anti由于not exists）是連接兩個大行源的最佳方式。另請注意，該計劃不使用索引。原因是一樣的——訪問大資料你不想遍歷索引。

與低基數行源 ( OTPL) 的情況相反，您希望看到索引訪問和NESTED LOOPS ANTI.

有時，Oracle 會感到困惑（例如，在看到陳舊的統計資料時）并決定NESTED LOOP對大資料采取這種方式——這會導致經過很長時間。

這至少可以幫助您確定是否有問題。

uj5u.com熱心網友回復：

也許一個簡單的MINUS操作將完成您正在尋找的內容：

    select id 
      from ( select id 
               from t1
              where starttime between '3 days ago' and 'now' 
             MINUS
             select id 
               from t2
              where starttime between '3 days ago' and 'now' 
           );

但是，您實際上定義了starttime between '3 days ago' and 'now'. 這實際上使用了您當前的查詢，因為 MINUS 操作從第一個中洗掉那些確實存在于第二個中的值并回傳結果。在此處查看MINUS 演示。

標籤：

上一篇：連接具有相同列的表

下一篇：獲取屬于一個用戶的所有帖子