为什么我在 2 个表中执行 MAX 和 ORDER BY 的 SQL 查询需要很长时间

Question

嗨，我对SQL了解不多，这绝对是我的弱点之一。 这是一个甲骨文数据库。

我有2张桌子：

TAXI_ROUTES_TABLE:
ID  | ORIGIN   | DESTINATION | TAXI_NUMBER |  STOPS   | DAY_OF_OPERATION | INSERT_DATE
123  NORTH AVE   CAMPBELL      1552          1W;2T;4Y   01-FEB-20          27-DEC-19
124  LEMMON AVE  HENRY ST      1511          1R;5G;P3   02-FEB-20          25-DEC-19

---

TAXI_FARE_TABLE:
ID  | ORIGIN   | DESTINATION | TAXI_NUMBER |  STOPS   |  FARE_TYPE | FARE_TYPE_AVAIL 
123  NORTH AVE   CAMPBELL      1552          1W;2T;4Y      SENIOR        5             
123  NORTH AVE   CAMPBELL      1552          1W;2T;4Y      STUDENT       10            
123  NORTH AVE   CAMPBELL      1552          1W;2T;4Y      EMPLOYEE      10            
124  LEMMON AVE  HENRY ST      1511          1R;5G;P3      SENIOR        5             
124  LEMMON AVE  HENRY ST      1511          1R;5G;P3      STUDENT       3             
124  LEMMON AVE  HENRY ST      1511          1R;5G;P3      EMPLOYEE      10  

TAXI_ROUTES_TABLE指数：

STOPS, TAXI_NUMBER, DESTINATION, ORIGIN, DAY_OF_OPERATION
CREATE INDEX "TAXI_ADMIN"."IX01_TAXI_ROUTES_TABLE" ON 
"TAXI_ADMIN"."TAXI_ROUTES_TABLE" ("STOPS", "TAXI_NUMBER", "DESTINATION", 
"ORIGIN", "DAY_OF_OPERATION ") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TAXI_ADMIN" ; 

TAXI_FARE_TABLE没有任何索引。

这些表中有数百万条记录。 两个表中都有相同的记录，只是由于每辆出租车的票价类型，出租车票价表重复了它们。

所以我正在尝试进行联接，以便能够获取所有最近插入的记录及其票价类型。所以我应该只取回 27-DEC-19 插入的记录。

我的问题：我的查询正在最大化时间限制，它需要永远。我非常感谢您对此的指导。

.SQL：

SELECT R.ID,
R.ORIGIN,
R.DESTINATION,
R.TAXI_NUMBER,
R.STOPS,
R.DAY_OF_OPERATION,
R.INSERT_DATE,
F.FARE_TYPE,
F.FARE_TYPE_AVAIL
FROM TAXI_ADMIN.TAXI_ROUTES_TABLE R, TAXI_ADMIN.TAXI_FARE_TABLET F
WHERE INSERT_DATE = (SELECT MAX(INSERT_DATE) FROM TAXI_ADMIN.TAXI_ROUTES_TABLE)
AND R.ID = F.ID
ORDER BY R.ORIGIN, R.DESTINATION, R.DAY_OF_OPERATION;

我不确定如何使查询更快。当我在没有订单的情况下运行时，它的速度非常快，但订单需要很长时间。

谢谢

Answer 1

我建议使用SQL监视器报告来识别查询的SQL ID，因为它会准确地告诉您执行计划是什么以及SQL花费大部分时间的位置。

从 SQL*Plus 获取 SQL 监视器报告的简单方法如下：

spool c:tempSQL_Monitor_rpt.html
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
alter session set "_with_subquery" = optimizer;
SELECT DBMS_SQLTUNE.report_sql_monitor(
sql_id       => '&SQLID' ,
type         => 'HTML',
report_level => 'ALL') AS report
FROM dual;
spool off

此外，在这种情况下，您可能还需要三个索引： TAXI_ADMIN。TAXI_ROUTES_TABLE。INSERT_DATE以涵盖子集条件 和两个表上的 ID 列以涵盖连接条件

Answer 2

对于这个查询(我已经清理了一点(：

SELECT R.ID, R.ORIGIN, R.DESTINATION, R.TAXI_NUMBER,
R.STOPS, R.DAY_OF_OPERATION, R.INSERT_DATE,
F.FARE_TYPE, F.FARE_TYPE_AVAIL
FROM TAXI_ADMIN.TAXI_ROUTES_TABLE R JOIN 
TAXI_ADMIN.TAXI_FARE_TABLET F
ON R.ID = F.ID
WHERE R.INSERT_DATE = (SELECT MAX(R2.INSERT_DATE) FROM TAXI_ADMIN.TAXI_ROUTES_TABLE R2)
ORDER BY R.ORIGIN, R.DESTINATION, R.DAY_OF_OPERATION;

您希望索引：TAXI_ROUTES_TABLE(INSERT_DATE, ID)和TAXI_FARE_TABLET(ID)。 很难消除ORDER BY的开销。 因此，如果有很多数据，这可能没有多大帮助。

如果Oracle足够聪明，那么将第一个索引扩展到TAXI_ROUTES_TABLE(INSERT_DATE, ORIGIN, DESTINATION, DAY_OF_OPERATION, ID)会有所帮助。 这是一个有趣的优化，因为它需要在执行JOIN之前考虑ORDER BY。