sql - Oracle-加速查询
问题描述
此查询查找 K2 的 W_G 是什么(例如:'L_E' 由'E1' 找到)。然后它找到K1是什么(例如:'IS'被'E1'找到)。然后它根据 K1,K2('30','10' by 'IS' 和 'C01A')找到 K4,K5。
为了加快这个查询,有什么东西可以修改这个查询吗?
执行计划如下。
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6617_CD798E | 1 | 26 | 2 (0)| 00:00:01 |
| 3 | TEMP TABLE TRANSFORMATION | | | | | |
| 4 | LOAD AS SELECT | SYS_TEMP_0FD9D6618_CD798E | | | | |
|* 5 | TABLE ACCESS FULL | C_W_B | 1 | 273 | 3 (0)| 00:00:01 |
| 6 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6617_CD798E | 1 | 26 | 2 (0)| 00:00:01 |
|* 8 | VIEW | | 1 | 39 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6618_CD798E | 1 | 273 | 2 (0)| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | 13 | | |
| 11 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6618_CD798E | 1 | 273 | 2 (0)| 00:00:01 |
| 13 | TEMP TABLE TRANSFORMATION | | | | | |
| 14 | LOAD AS SELECT | SYS_TEMP_0FD9D6617_CD798E | | | | |
|* 15 | TABLE ACCESS FULL | C_W_B | 1 | 78 | 3 (0)| 00:00:01 |
| 16 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
4 recursive calls
16 db block gets
30 consistent gets
2 physical reads
1156 redo size
682 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
表如下
CREATE TABLE C_W_B (
HE VARCHAR2(22),
WE VARCHAR2(22),
G_D VARCHAR2(22),
W_G VARCHAR2(22),
W_V VARCHAR2(22),
K1 VARCHAR2(22),
K2 VARCHAR2(22),
K3 VARCHAR2(22),
K4 VARCHAR2(22),
K5 VARCHAR2(22),
K6 VARCHAR2(22),
K7 VARCHAR2(22),
K8 VARCHAR2(22),
K9 VARCHAR2(22),
K10 VARCHAR2(22),
K11 VARCHAR2(22),
K12 VARCHAR2(22),
K13 VARCHAR2(22),
K14 VARCHAR2(22),
K22 VARCHAR2(22),
K16 VARCHAR2(22)
);
数据如下
INSERT INTO C_W_B(HE,WE,G_D,W_G,W_V,K1,K3,K4,K6) VALUES
('C6','61','R3','L_C_F','1','IS','C01A','30','10');
INSERT INTO C_W_B(HE,WE,G_D,W_G,K1,K2) VALUES
('C6','61','R3','L_E','IS','E1');
查询如下
WITH G_S AS (
SELECT /*+FIRST_ROWS(1)*/
A.W_G, A.K1
FROM C_W_B A
WHERE 1=1
AND A.HE = 'C6'AND A.WE = '61' AND A.G_D ='R3' AND A.K2='E1'
)
SELECT CASE
WHEN (SELECT A.W_G FROM G_S A)='L_E'
THEN (
WITH SEO AS (
SELECT A.K1 FROM G_S A
),
SE AS (SELECT *
FROM C_W_B B
WHERE B.W_G = 'L_C_F'
AND B.HE = 'C6'
AND B.WE = '61'
AND B.G_D ='R3'
AND B.K1 = (SELECT K1 FROM SEO)
AND B.K3 LIKE 'C01A'
)
SELECT (L_T /QT*1000)
FROM ( SELECT NVL(TO_NUMBER(K4),0) AS L_T,
NVL(TO_NUMBER(K6),0) AS QT
FROM SE
WHERE W_V IN (SELECT TO_CHAR(MAX(TO_NUMBER(W_V))) FROM SE )
)
)
WHEN 1=2
THEN TO_NUMBER(0)
ELSE TO_NUMBER(0)
END
FROM DUAL;
解决方案
您的查询可以简化,并可能提供如下性能:
SELECT CASE
WHEN W_G ='L_E'
AND K6 IS NOT NULL -- ADDED K6 IS NOT NULL CONDITION TO AVOID NUMBER DIVIDE BY 0 ERROR
THEN NVL(K4,0) / (K6*1000)
ELSE 0
END
FROM (SELECT A.W_G, B.K6, B.K4,
ROW_NUMBER()
OVER (ORDER BY TO_NUMBER(W_V) DESC NULLS LAST) AS RN
FROM C_W_B A JOIN C_W_B S ON (A.K1=S.K1)
WHERE A.HE = 'C6'
AND A.WE = '61'
AND A.G_D ='R3'
AND A.K2='E1'
AND S.W_G = 'L_C_F'
AND S.HE = 'C6'
AND S.WE = '61'
AND S.G_D ='R3'
AND S.K3 LIKE 'C01A')
WHERE RN = 1;
干杯!!
推荐阅读
- express - 通用 vue.js。使用 lambda 部署
- sql - SQL 查询 TA BioStar
- mqtt - 未在 cloudmqtt 上使用 MQTTjs 连接用户
- browser - 如何在新的 Chrome / Firefox / Safari 版本上收到通知?
- python - 如何使用字典来操作字符串中每个元素的列表?
- c# - 为 id 参数和 action/id 创建 MVC 路由
- rxjs - RxJS - 一位观察者等待多个事件
- javascript - 如何正确将从 django 响应返回的 JSON 对象解析为数据属性
- uwp-xaml - 如何在 uwp 中使用 x:Double 标记中的值绑定?
- python - ABC儿童课问题