Oracle SQL查询优化

我们有一个名为PROTOKOLL的表，其定义如下:

  CREATE TABLE "PROTOKOLL" 
   ( "UUID" CHAR(36 BYTE) NOT NULL ENABLE, 
 "VERSIONSNUMMER" VARCHAR2(20 BYTE), 
 "TYP" CHAR(1 BYTE) NOT NULL ENABLE, 
 "BENUTZER_ID" VARCHAR2(250 BYTE) NOT NULL ENABLE, 
 "SERVICE" VARCHAR2(250 BYTE) NOT NULL ENABLE, 
 "ERFOLG" NUMBER, 
 "STANDESAMT_NR" VARCHAR2(250 BYTE), 
 "REGISTER_ART" VARCHAR2(250 BYTE), 
 "EINTRAG_JAHR" VARCHAR2(5 BYTE), 
 "EINTRAG_NR" VARCHAR2(250 BYTE), 
 "ZEITPUNKT" TIMESTAMP (6) NOT NULL ENABLE, 
 "DATEN" "SYS"."XMLTYPE" , 
 "ANDERES_PROTOKOLL_UUID" CHAR(36 BYTE), 
 "MANDANT" VARCHAR2(250 BYTE), 
 "RESERVIERUNGSNACHWEIS" VARCHAR2(60 CHAR), 
 "VERFUEGUNGSSTATUS" NCHAR(1), 
 "BEZUG" VARCHAR2(1500 BYTE) DEFAULT NULL, 
  CONSTRAINT "PROTOKOLL_PK" PRIMARY KEY ("UUID")
   )  ;

"PROTOKOLL_BENUTZER_ID_IX1" ON "PROTOKOLL" ("BENUTZER_ID")  ;
"PROTOKOLL_EINTRAGJAHR_IX1" ON "PROTOKOLL" ("EINTRAG_JAHR")  ;
"PROTOKOLL_EINTRAGNR_IX1" ON "PROTOKOLL" ("EINTRAG_NR")  ;
"PROTOKOLL_MANDANT_ID_IX1" ON "PROTOKOLL" ("MANDANT")  ;
"PROTOKOLL_REGISTERART_IX1" ON "PROTOKOLL" ("REGISTER_ART")  ;
"PROTOKOLL_SERVICE_IX1" ON "PROTOKOLL" ("SERVICE")  ;
"PROTOKOLL_STANDESAMTNR_IX1" ON "PROTOKOLL" ("STANDESAMT_NR")  ;
"PROTOKOLL_TYP_IX1" ON "PROTOKOLL" ("TYP")  ;
"PROTOKOLL_TYP_IX2" ON "PROTOKOLL" ("ANDERES_PROTOKOLL_UUID", "TYP")  ;
"PROTOKOLL_ZEITPUNKT_IX1" ON "PROTOKOLL" ("ZEITPUNKT")  ;

该表包含ca.1000万条记录。

我们有以下查询:

SELECT *
FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  
FROM (SELECT 
t0.*, t1.*
FROM PROTOKOLL t0, PROTOKOLL t1 
WHERE (((t0.BENUTZER_ID = 'A07BU0006') AND (t0.TYP = 'E')) AND ((t1.UUID = t0.ANDERES_PROTOKOLL_UUID) AND (t1.TYP = 'A'))) 
ORDER BY t0.ZEITPUNKT DESC) a WHERE ROWNUM <= 5000) WHERE rnum > 0;

因此，实际上，我们通过ANDERES_PROTOKOLL_UUID字段将表与自身连接，我们应用简单的过滤。根据创建时间对结果进行排序，结果记录集的数量限制为5000。

查询经过的时间约为10分钟!--- 这是不可接受的☹
我已经有了执行计划和统计信息，并试图弄清楚如何加快查询:

------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                         | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | 
|   0 | SELECT STATEMENT                  |                         |     1 |    13 |  4903K  (1)| 16:20:44 | 
|   1 |  SORT AGGREGATE                   |                         |     1 |    13 |            |          |
|*  2 |   VIEW                            |                         |   446 |  5798 |  4903K  (1)| 16:20:44 |
|*  3 |    COUNT STOPKEY                  |                         |       |       |            |          | 
|   4 |     VIEW                          |                         |   446 |       |  4903K  (1)| 16:20:44 |   
|   5 |      NESTED LOOPS                 |                         |       |       |            |          |
|   6 |       NESTED LOOPS                |                         |   446 |   186K|  4903K  (1)| 16:20:44 |
|*  7 |        TABLE ACCESS BY INDEX ROWID| PROTOKOLL               |   446 | 95444 |  4902K  (1)| 16:20:33 |
|   8 |         INDEX FULL SCAN DESCENDING| PROTOKOLL_ZEITPUNKT_IX1 |  6541K|       | 50286   (1)| 00:10:04 |
|*  9 |        INDEX UNIQUE SCAN          | PROTOKOLL_PK            |     1 |       |     1   (0)| 00:00:01 |
|* 10 |       TABLE ACCESS BY INDEX ROWID | PROTOKOLL               |     1 |   214 |     2   (0)| 00:00:01 |

Predicate Information (identified by operation id): 
   2 - filter("RNUM">0)   
   3 - filter(ROWNUM<=4999)
   7 - filter("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."BENUTZER_ID"='A07BU0006' AND "P"."TYP"='E') 
   9 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID") 
  10 - filter("P"."TYP"='A')
 
Statistics
         21  recursive calls  
          0  db block gets 
    2170736  consistent gets 
    1383094  physical reads
          0  redo size 
        527  bytes sent via SQL*Net to client 
        524  bytes received via SQL*Net from client  
          2  SQL*Net roundtrips to/from client 
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed 

我的第一个认识是，优化器在where子句中附加了 “P”。“ANDERES_PROTOKOLL_UUID” 不为NULL ”条件，但我不知道为什么。有问题吗？

或者查询的瓶颈在哪里？
如果我使用UUID和ANDERES_PROTOKOLL_UUID字段创建第二个表并通过该表将PROTOKOLL表连接在一起，是否有帮助？

为了帮助SQL调优，我们真的需要看看查询到底做了什么，即它的执行计划。

您可以使用gather_plan_statistics运行查询，然后调用dbms_xplan:

set serveroutput off
SELECT /*+ gather_plan_statistics */*
FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  
FROM (SELECT 
t0.*, t1.*
FROM PROTOKOLL t0, PROTOKOLL t1 
WHERE (((t0.BENUTZER_ID = 'A07BU0006') AND (t0.TYP = 'E')) AND ((t1.UUID = t0.ANDERES_PROTOKOLL_UUID) AND (t1.TYP = 'A'))) 
ORDER BY t0.ZEITPUNKT DESC) a WHERE ROWNUM <= 5000) WHERE rnum > 0;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

确保这包含E (stimatied) 行和A (tual) 行列。一旦你有了计划，把它粘贴在这里，我们会看到如何帮助你。

您可以在以下位置阅读有关如何获取这些信息的更多信息:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan

the optimizer puts “"P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL” condition additionally to the where clause, but I do not know why. Is it a problem?

不。优化器通常会生成额外的谓词来帮助它。在这种情况下，您的查询具有:

t1.UUID = t0.ANDERES_PROTOKOLL_UUID

仅当ANDERES_PROTOKOLL_UUID不为null (null = null返回未知) 时才成立。所以它可以安全地添加这个。

最后，你得到了第一行提示。正确的形式是:

first_rows (N)

其中N是您期望查询返回的行数。所以，在这种情况下，你会想要:

first_rows (5000)

当然，5,000仍然是waaaay更多的行比一个人会检查!你确定你想要这么多吗？你能选择少一点吗？