Oracle

如何提高 Oracle 中 Order By 子句的性能

  • December 13, 2018

我有一個UAVT_BINA在 oracle 數據庫中呼叫的表，它有 28897352 行。當我寫：

select * from UAVT_BINA

數據庫在 0.1 秒內回复我。但是當我寫

select * from UAVT_BINA order by CBSBMKODU

數據庫在 66 秒內回复我。我有一個關於CSBMKODU. 由於應用程序標準，我必須對數據進行排序如何提高性能，有人可以幫助我嗎？

這是排序查詢的解釋計劃的結果

在此處輸入圖像描述

之前說過，我們在框架端使用動態查詢，過濾數據的簡單結果查詢在這裡

SELECT 
"Project2"."C1" AS "C1", 
"Project2"."CRTM" AS "CRTM", 
"Project2"."ADANO" AS "ADANO", 
"Project2"."BINADURUMSTRING" AS "BINADURUMSTRING", 
"Project2"."C2" AS "C2", 
"Project2"."BINANUMARATAJTIPISTRING" AS "BINANUMARATAJTIPISTRING", 
"Project2"."BINAYAPITIPISTRING" AS "BINAYAPITIPISTRING", 
"Project2"."BLOKADI" AS "BLOKADI", 
"Project2"."CSBMADI" AS "CSBMADI", 
"Project2"."C3" AS "C3", 
"Project2"."DISKAPINO" AS "DISKAPINO", 
"Project2"."C4" AS "C4", 
"Project2"."C5" AS "C5", 
"Project2"."C6" AS "C6", 
"Project2"."NITELIKSTRING" AS "NITELIKSTRING", 
"Project2"."C7" AS "C7", 
"Project2"."PAFTANO" AS "PAFTANO", 
"Project2"."PARSELNO" AS "PARSELNO", 
"Project2"."POSTAKODU" AS "POSTAKODU", 
"Project2"."SITEADI" AS "SITEADI", 
"Project2"."C8" AS "C8", 
"Project2"."C9" AS "C9", 
"Project2"."UPTRID" AS "UPTRID", 
"Project2"."UPTM" AS "UPTM", 
"Project2"."STATESTRING" AS "STATESTRING", 
"Project2"."STATEID" AS "STATEID", 
"Project2"."CRTRID" AS "CRTRID", 
"Project2"."MAHALLEADI" AS "MAHALLEADI", 
"Project2"."BINADURUMID" AS "BINADURUMID", 
"Project2"."BINANUMARATAJTIPIID" AS "BINANUMARATAJTIPIID", 
"Project2"."BINAYAPITIPIID" AS "BINAYAPITIPIID", 
"Project2"."MAHALLEID" AS "MAHALLEID"
FROM ( SELECT 
   "Distinct1"."DISKAPINO" AS "DISKAPINO", 
   "Distinct1"."SITEADI" AS "SITEADI", 
   "Distinct1"."BLOKADI" AS "BLOKADI", 
   "Distinct1"."POSTAKODU" AS "POSTAKODU", 
   "Distinct1"."CRTM" AS "CRTM", 
   "Distinct1"."CRTRID" AS "CRTRID", 
   "Distinct1"."UPTM" AS "UPTM", 
   "Distinct1"."UPTRID" AS "UPTRID", 
   "Distinct1"."STATEID" AS "STATEID", 
   "Distinct1"."STATESTRING" AS "STATESTRING", 
   "Distinct1"."ADANO" AS "ADANO", 
   "Distinct1"."PARSELNO" AS "PARSELNO", 
   "Distinct1"."PAFTANO" AS "PAFTANO", 
   "Distinct1"."BINADURUMSTRING" AS "BINADURUMSTRING", 
   "Distinct1"."BINADURUMID" AS "BINADURUMID", 
   "Distinct1"."BINAYAPITIPIID" AS "BINAYAPITIPIID", 
   "Distinct1"."BINAYAPITIPISTRING" AS "BINAYAPITIPISTRING", 
   "Distinct1"."BINANUMARATAJTIPIID" AS "BINANUMARATAJTIPIID", 
   "Distinct1"."BINANUMARATAJTIPISTRING" AS "BINANUMARATAJTIPISTRING", 
   "Distinct1"."NITELIKSTRING" AS "NITELIKSTRING", 
   "Distinct1"."CSBMADI" AS "CSBMADI", 
   "Distinct1"."MAHALLEID" AS "MAHALLEID", 
   "Distinct1"."MAHALLEADI" AS "MAHALLEADI", 
   1 AS "C1", 
    CAST( "Distinct1"."BINANO" AS number(19,0)) AS "C2", 
    CAST( "Distinct1"."CSBMID" AS number(19,0)) AS "C3", 
    CAST( "Distinct1"."ESKIBINAKIMLIKNO" AS number(19,0)) AS "C4", 
    CAST( "Distinct1"."ESKIBINAKODU" AS number(19,0)) AS "C5", 
    CAST( "Distinct1"."ID" AS number(19,0)) AS "C6", 
    CAST( "Distinct1"."NITELIKID" AS number(19,0)) AS "C7", 
    CAST( "Distinct1"."YOLALTIKATSAYISI" AS number(19,0)) AS "C8", 
    CAST( "Distinct1"."YOLUSTUKATSAYISI" AS number(19,0)) AS "C9"
   FROM ( SELECT DISTINCT 
       "Extent1"."ID" AS "ID", 
       "Extent1"."DISKAPINO" AS "DISKAPINO", 
       "Extent1"."SITEADI" AS "SITEADI", 
       "Extent1"."BLOKADI" AS "BLOKADI", 
       "Extent1"."POSTAKODU" AS "POSTAKODU", 
       "Extent1"."ESKIBINAKODU" AS "ESKIBINAKODU", 
       "Extent1"."CRTM" AS "CRTM", 
       "Extent1"."CRTRID" AS "CRTRID", 
       "Extent1"."UPTM" AS "UPTM", 
       "Extent1"."UPTRID" AS "UPTRID", 
       "Extent1"."STATEID" AS "STATEID", 
       "Extent1"."NITELIKID" AS "NITELIKID", 
       "Extent1"."STATESTRING" AS "STATESTRING", 
       "Extent1"."CSBMID" AS "CSBMID", 
       "Extent1"."ADANO" AS "ADANO", 
       "Extent1"."PARSELNO" AS "PARSELNO", 
       "Extent1"."PAFTANO" AS "PAFTANO", 
       "Extent1"."BINANO" AS "BINANO", 
       "Extent1"."YOLALTIKATSAYISI" AS "YOLALTIKATSAYISI", 
       "Extent1"."YOLUSTUKATSAYISI" AS "YOLUSTUKATSAYISI", 
       "Extent1"."ESKIBINAKIMLIKNO" AS "ESKIBINAKIMLIKNO", 
       "Extent1"."BINADURUMSTRING" AS "BINADURUMSTRING", 
       "Extent1"."BINADURUMID" AS "BINADURUMID", 
       "Extent1"."BINAYAPITIPIID" AS "BINAYAPITIPIID", 
       "Extent1"."BINAYAPITIPISTRING" AS "BINAYAPITIPISTRING", 
       "Extent1"."BINANUMARATAJTIPIID" AS "BINANUMARATAJTIPIID", 
       "Extent1"."BINANUMARATAJTIPISTRING" AS "BINANUMARATAJTIPISTRING", 
       "Extent1"."NITELIKSTRING" AS "NITELIKSTRING", 
       "Extent1"."CSBMADI" AS "CSBMADI", 
       "Extent1"."MAHALLEID" AS "MAHALLEID", 
       "Extent1"."MAHALLEADI" AS "MAHALLEADI"
       FROM  (SELECT 
"UAVT_BINA_EVW"."ID" AS "ID", 
"UAVT_BINA_EVW"."DISKAPINO" AS "DISKAPINO", 
"UAVT_BINA_EVW"."SITEADI" AS "SITEADI", 
"UAVT_BINA_EVW"."BLOKADI" AS "BLOKADI", 
"UAVT_BINA_EVW"."POSTAKODU" AS "POSTAKODU", 
"UAVT_BINA_EVW"."ESKIBINAKODU" AS "ESKIBINAKODU", 
"UAVT_BINA_EVW"."CRTM" AS "CRTM", 
"UAVT_BINA_EVW"."CRTRID" AS "CRTRID", 
"UAVT_BINA_EVW"."UPTM" AS "UPTM", 
"UAVT_BINA_EVW"."UPTRID" AS "UPTRID", 
"UAVT_BINA_EVW"."STATEID" AS "STATEID", 
"UAVT_BINA_EVW"."NITELIKID" AS "NITELIKID", 
"UAVT_BINA_EVW"."STATESTRING" AS "STATESTRING", 
"UAVT_BINA_EVW"."CSBMID" AS "CSBMID", 
"UAVT_BINA_EVW"."ADANO" AS "ADANO", 
"UAVT_BINA_EVW"."PARSELNO" AS "PARSELNO", 
"UAVT_BINA_EVW"."PAFTANO" AS "PAFTANO", 
"UAVT_BINA_EVW"."BINANO" AS "BINANO", 
"UAVT_BINA_EVW"."YOLALTIKATSAYISI" AS "YOLALTIKATSAYISI", 
"UAVT_BINA_EVW"."YOLUSTUKATSAYISI" AS "YOLUSTUKATSAYISI", 
"UAVT_BINA_EVW"."ESKIBINAKIMLIKNO" AS "ESKIBINAKIMLIKNO", 
"UAVT_BINA_EVW"."BINADURUMSTRING" AS "BINADURUMSTRING", 
"UAVT_BINA_EVW"."BINADURUMID" AS "BINADURUMID", 
"UAVT_BINA_EVW"."BINAYAPITIPIID" AS "BINAYAPITIPIID", 
"UAVT_BINA_EVW"."BINAYAPITIPISTRING" AS "BINAYAPITIPISTRING", 
"UAVT_BINA_EVW"."BINANUMARATAJTIPIID" AS "BINANUMARATAJTIPIID", 
"UAVT_BINA_EVW"."BINANUMARATAJTIPISTRING" AS "BINANUMARATAJTIPISTRING", 
"UAVT_BINA_EVW"."NITELIKSTRING" AS "NITELIKSTRING", 
"UAVT_BINA_EVW"."CSBMADI" AS "CSBMADI", 
"UAVT_BINA_EVW"."MAHALLEID" AS "MAHALLEID", 
"UAVT_BINA_EVW"."MAHALLEADI" AS "MAHALLEADI"
FROM "ATLAS"."UAVT_BINA_EVW" "UAVT_BINA_EVW") "Extent1"
       INNER JOIN (SELECT 
"BS_AUTHORIZEDREGION_EVW"."ID" AS "ID", 
"BS_AUTHORIZEDREGION_EVW"."MAHALLEID" AS "MAHALLEID", 
"BS_AUTHORIZEDREGION_EVW"."KOYID" AS "KOYID", 
"BS_AUTHORIZEDREGION_EVW"."BUCAKID" AS "BUCAKID", 
"BS_AUTHORIZEDREGION_EVW"."ILCEID" AS "ILCEID", 
"BS_AUTHORIZEDREGION_EVW"."ROLEID" AS "ROLEID", 
"BS_AUTHORIZEDREGION_EVW"."USERID" AS "USERID",
"BS_AUTHORIZEDREGION_EVW"."ILID" AS "ILID",
"BS_AUTHORIZEDREGION_EVW"."ATUHORIZEDREGIONTYPEID" AS "ATUHORIZEDREGIONTYPEID"
FROM "ATLAS"."BS_AUTHORIZEDREGION_EVW" "BS_AUTHORIZEDREGION_EVW") "Extent2" ON (("Extent1"."MAHALLEID" = "Extent2"."MAHALLEID") OR (("Extent1"."MAHALLEID" IS NULL) AND ("Extent2"."MAHALLEID" IS NULL))) AND ((:p__linq__0 = "Extent2"."USERID") OR ((:p__linq__0 IS NULL) AND ("Extent2"."USERID" IS NULL)))
   )  "Distinct1"
)  "Project2"
ORDER BY "Project2"."CRTM" DESC

查詢似乎沒有使用索引。如果它將使用索引，則必須執行以下操作：

  • 獲取索引的第一個條目

    • 通過rowid在表中查找該行的其他數據

  • 獲取索引的下一個條目

    • 通過rowid在表中查找該行的其他數據

  • 獲取索引的下一個條目

    • 從表中查找屬於該行的其他數據

  • …

必須在表中查找數據，因為您*的 select 子句中有一個。我假設索引不包含一行的所有數據。所以如果一個人獲取所有的數據

select *
from BINA
order by CBSBMKODU

語句，那麼避免索引但從表中獲取所有數據並對其進行排序會更快。獲取所有數據並對其進行排序需要 66 秒。在此之後，可以將第一條記錄發送給客戶端。

無論如何，您都可以嘗試說服您的數據庫使用索引（我不知道它們是否會起作用）

使用會話/系統參數 OPTIMIZER_MODE並將其設置為FIRST_ROWS. 然後數據庫嘗試最小化直到獲取第一行的時間，因此可以使用索引。

使用提示將優化器引導到索引以將查詢更改為

select /*+ INDEX(BINA CBSBMKODU) */ *
from BINA
order by CBSBMKODU

或者，您可以更改數據的結構：

您更改索引 CBSBMKODU 以使其包含一行的所有欄位。然後數據庫可以從索引中檢索一行的所有值，而不必在表中查找。因此它可以使用索引訪問而不嘗試掃描表。請注意，使用此解決方案，您將儲存表的兩個完整副本：一次在表本身中，另一次在索引 CBSBMKODU 中。

或者，您可以更改表的結構並使其成為索引組織表，而不是通常的堆組織表。然後該表已經像索引一樣組織起來，您不必保存數據兩次。

但總而言之，這些解決方案並不真正令人滿意。您應該查看與數據庫相關的這些“應用程序標準”。顯然，最好不要返回 3000 萬行預排序的數據。

當我嘗試提取此查詢的本質時，我得到了這個：

SELECT Project2.*
FROM 
   (SELECT Distinct1.*
   FROM 
       (SELECT DISTINCT Extent1.*
       FROM (SELECT UAVT_BINA_EVW.* FROM ATLAS.UAVT_BINA_EVW UAVT_BINA_EVW) Extent1
           INNER JOIN (SELECT BS_AUTHORIZEDREGION_EVW.* FROM ATLAS.BS_AUTHORIZEDREGION_EVW BS_AUTHORIZEDREGION_EVW) Extent2 ON 
               ((Extent1.MAHALLEID = Extent2.MAHALLEID) 
                   OR ((Extent1.MAHALLEID IS NULL) AND (Extent2.MAHALLEID IS NULL))) 
               AND ((:p__linq__0 = Extent2.USERID) OR ((:p__linq__0 IS NULL) AND (Extent2.USERID IS NULL)))
       ) Distinct1
   ) Project2
ORDER BY Project2.CRTM DESC;

我看不出所有這些子查詢的任何原因。如果我沒有錯，這等於

SELECT DISTINCT ...
FROM ATLAS.UAVT_BINA_EVW UAVT_BINA_EVW Extent1
   INNER JOIN ATLAS.BS_AUTHORIZEDREGION_EVW Extent2 ON 
       ((Extent1.MAHALLEID = Extent2.MAHALLEID) 
           OR ((Extent1.MAHALLEID IS NULL) AND (Extent2.MAHALLEID IS NULL))) 
       AND ((:p__linq__0 = Extent2.USERID) OR ((:p__linq__0 IS NULL) AND (Extent2.USERID IS NULL)))
ORDER BY CRTM DESC;

INNER JOIN ... ON ((Extent1.MAHALLEID = Extent2.MAHALLEID) OR ((Extent1.MAHALLEID IS NULL) AND (Extent2.MAHALLEID IS NULL))

看起來像 a FULL OUTER JOIN，所以最後你應該能夠將查詢減少到類似於這樣的內容：

SELECT DISTINCT ...
FROM ATLAS.UAVT_BINA_EVW UAVT_BINA_EVW Extent1
   FULL OUTER JOIN ATLAS.BS_AUTHORIZEDREGION_EVW Extent2 ON Extent1.MAHALLEID = Extent2.MAHALLEID 
WHERE :p__linq__0 = Extent2.USERID OR (:p__linq__0 IS NULL AND Extent2.USERID IS NULL)
ORDER BY CRTM DESC;

你看，唯一的謂詞是 on :p__linq__0 = Extent2.USERID（通常你會這樣寫Extent2.USERID = :p__linq__0- 你有一個索引ATLAS.BS_AUTHORIZEDREGION_EVW.USERID嗎？這將是 Oracle可以使用的唯一索引。

DISTINCT我認為由於子句，CRTM 上的索引沒有幫助。這迫使 Oracle 讀取整個結果並將其放入 TEMP 段進行排序 - 但我不確定。

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

相關問答

Sql-Server

查找與目標日期範圍重疊的所有日期範圍

  • September 12, 2017
檢視問答
Oracle

甲骨文灰分報告

  • March 6, 2012
檢視問答
Oracle

查詢優化OracleDBM小號○r一個Cl和D乙米小號Oracle DBMS

  • July 17, 2022
檢視問答
Performance

為什麼對強實體同時使用通用標識符和單獨遞增的主鍵？

  • April 8, 2022
檢視問答
Mysql

優化 ORDER BY

  • December 20, 2021
檢視問答
Mysql

將電子商務商店中的產品圖像作為單獨的表與作為產品表的列實現的性能？

  • November 12, 2021
檢視問答