sql - 尽管成本低、基数低，但 Oracle 中的选择查询需要很长时间
问题描述
尝试执行一个选择查询，该查询基本上使用正则表达式将一列中的多个值转换为多行 -
select INCIDENTID, trim(regexp_substr(CASUALFACTORS,'[^,]+', 1, level) ) value
FROM PSA.HSERS_INCIDENT_PSA WHERE ACTIVE_FLAG='1'
AND ISDELETED = 'F'
connect by regexp_substr(CASUALFACTORS, '[^,]+', 1, level) is not null
order by INCIDENTID
该查询在解释计划中的成本为 3206，基数为 1,08,849。
仍然需要很长时间才能执行（即使在 30 分钟后也会继续）。
请建议。
早期使用 XML 表创建此 SQL 的想法更加昂贵 -
SELECT INCIDENTID,
COLUMN_VALUE AS CASUALFACTORS
FROM (
SELECT INCIDENTID,
CASUALFACTORS AS STR
FROM PSA.HSERS_INCIDENT_PSA
WHERE ACTIVE_FLAG='1'
AND ISDELETED = 'F'
) T,
XMLTABLE ( ('"' || REPLACE (str, ',', '","') || '"'))
Sample data -
select incidentid, dbms_lob.substr(CASUALFACTORS,3000)
from PSA.HSERS_INCIDENT_PSA WHERE ACTIVE_FLAG='1'
AND ISDELETED = 'F' and incidentid = 526849
526849 8,7,26
There are no duplicates for incidentid column in the table
DDL for the table -
ALTER TABLE PSA.HSERS_INCIDENT_PSA
DROP PRIMARY KEY CASCADE;
DROP TABLE PSA.HSERS_INCIDENT_PSA CASCADE CONSTRAINTS;
CREATE TABLE PSA.HSERS_INCIDENT_PSA
(
INCIDENTID INTEGER,
INCIDENTTYPE INTEGER,
SUPPLEMENTALINCIDENTTYPE INTEGER,
PENDINGREVIEW VARCHAR2(1 BYTE),
NEARMISSTYPE INTEGER,
NEARMISSSUBCONTRACTOR INTEGER,
RELEASEDATE TIMESTAMP(6),
NEARMISSIDENTIFICATION INTEGER,
NEARMISSCATEGORY INTEGER,
PROFITCENTER VARCHAR2(10 BYTE),
INCIDENTNUMBER NUMBER(19),
INCIDENTCODE VARCHAR2(15 BYTE),
HIRNUMBER VARCHAR2(8 BYTE),
REDBORDERALERTSENT VARCHAR2(1 BYTE),
TASKORDER VARCHAR2(3 BYTE),
LOGCAPAREA INTEGER,
TEAMCONNECTNUMBER VARCHAR2(20 BYTE),
INCIDENTDATE TIMESTAMP(6),
INCIDENTTIME VARCHAR2(10 BYTE),
REPORTDATE TIMESTAMP(6),
REPORTTIME VARCHAR2(5 BYTE),
INSERTDATETIME TIMESTAMP(6),
REPORTEDBY VARCHAR2(40 BYTE),
SUPERVISOR1 VARCHAR2(40 BYTE),
SUPERVISOR2 VARCHAR2(40 BYTE),
CLIENT INTEGER,
PROJECTLOCATION INTEGER,
INCIDENTAREA INTEGER,
INCIDENTLOCATION INTEGER,
INCIDENTAREADESCRIPTION VARCHAR2(80 BYTE),
DRUGALCOHOLTEST INTEGER,
NODRUGTESTRESPONSE INTEGER,
NODRUGTESTCOMMENTS CLOB,
FACTS CLOB,
POTENTIALCONSEQUENCES INTEGER,
LIKELIHOODRATING INTEGER,
RISKASSESSMENTSEVERITY INTEGER,
COVEREDBYTSTI_JSA VARCHAR2(1 BYTE),
INJURINGEVENTDISCUSSEDTSTI_JSA VARCHAR2(1 BYTE),
EXTERNALASSESSMENTFINEASSESED NUMBER(15,2),
REGULATORYAGENCY INTEGER,
INSPECTIONNUMBER VARCHAR2(12 BYTE),
INSPECTIONRESULTS INTEGER,
INSPECTIONCLOSEDATE TIMESTAMP(6),
EXTERNALASSESSMENTSEVERITY INTEGER,
CORRECTIVEMEASURES CLOB,
WITNESSDETAILS CLOB,
EXTERNALINVESTIGATORCOMMENTS CLOB,
CREATEDBY VARCHAR2(50 BYTE),
CREATEDON TIMESTAMP(6),
UPDATEDBY VARCHAR2(50 BYTE),
UPDATEDON TIMESTAMP(6),
ISDELETED VARCHAR2(1 BYTE),
INCIDENTSTATUS VARCHAR2(50 BYTE),
PARENTINCIDENTID NUMBER(19),
KEYSTOLIFE CLOB,
OFFICEBEHAVIORS CLOB,
CASUALFACTORS CLOB,
STANDARDSVIOLATED CLOB,
SUBCONTRACTORADDRESS CLOB,
CONFIDENTIALFACTS CLOB,
LASTVISITEDTAB VARCHAR2(50 BYTE),
LAST_RQST_ID VARCHAR2(60 BYTE),
DATA_SYS_SK VARCHAR2(3 BYTE),
CREATED_BY VARCHAR2(50 BYTE),
CREATED_DDTM DATE,
LAST_MODIFIED_BY VARCHAR2(50 BYTE),
LAST_MODIFIED_DDTM DATE,
ACTIVE_FLAG VARCHAR2(1 BYTE)
)
LOB (NODRUGTESTCOMMENTS) STORE AS (
TABLESPACE PSA_DATA
ENABLE STORAGE IN ROW
CHUNK 16384
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
))
LOB (FACTS) STORE AS (
TABLESPACE PSA_DATA
ENABLE STORAGE IN ROW
CHUNK 16384
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
))
LOB (CORRECTIVEMEASURES) STORE AS (
TABLESPACE PSA_DATA
ENABLE STORAGE IN ROW
CHUNK 16384
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
))
LOB (WITNESSDETAILS) STORE AS (
TABLESPACE PSA_DATA
ENABLE STORAGE IN ROW
CHUNK 16384
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
))
LOB (EXTERNALINVESTIGATORCOMMENTS) STORE AS (
TABLESPACE PSA_DATA
ENABLE STORAGE IN ROW
CHUNK 16384
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
))
LOB (KEYSTOLIFE) STORE AS (
TABLESPACE PSA_DATA
ENABLE STORAGE IN ROW
CHUNK 16384
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
))
LOB (OFFICEBEHAVIORS) STORE AS (
TABLESPACE PSA_DATA
ENABLE STORAGE IN ROW
CHUNK 16384
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
))
LOB (CASUALFACTORS) STORE AS (
TABLESPACE PSA_DATA
ENABLE STORAGE IN ROW
CHUNK 16384
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
))
LOB (STANDARDSVIOLATED) STORE AS (
TABLESPACE PSA_DATA
ENABLE STORAGE IN ROW
CHUNK 16384
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
))
LOB (SUBCONTRACTORADDRESS) STORE AS (
TABLESPACE PSA_DATA
ENABLE STORAGE IN ROW
CHUNK 16384
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
))
LOB (CONFIDENTIALFACTS) STORE AS (
TABLESPACE PSA_DATA
ENABLE STORAGE IN ROW
CHUNK 16384
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
))
TABLESPACE PSA_DATA
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
-- There is no statement for index PSA.SYS_C002335882.
-- The object is created when the parent object is created.
CREATE OR REPLACE SYNONYM BI_REPORTS_USER.HSERS_INCIDENT_PSA FOR PSA.HSERS_INCIDENT_PSA;
ALTER TABLE PSA.HSERS_INCIDENT_PSA ADD (
PRIMARY KEY
(INCIDENTID, LAST_RQST_ID)
USING INDEX
TABLESPACE PSA_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
ENABLE VALIDATE);
解决方案
您可以使用递归子查询因式分解子句和简单的字符串函数（而不是慢速正则表达式）：
甲骨文设置：
CREATE TABLE HSERS_INCIDENT_PSA ( incidentid, casualfactors, active_flag, isdeleted ) AS
SELECT 1, 'a,b,c,d,e,f', '1', 'F' FROM DUAL UNION ALL
SELECT 2, 'g,h,i,j,k', '1', 'F' FROM DUAL UNION ALL
SELECT 3, 'l', '1', 'F' FROM DUAL;
查询：
WITH casualfactors_bounds ( incidentid, casualfactors, startidx, endidx ) AS (
SELECT incidentid,
casualfactors,
1,
INSTR( casualfactors, ',', 1 )
FROM HSERS_INCIDENT_PSA
WHERE ACTIVE_FLAG = '1'
AND ISDELETED = 'F'
UNION ALL
SELECT incidentid,
casualfactors,
endidx + 1,
INSTR( casualfactors, ',', endidx + 1 )
FROM casualfactors_bounds
WHERE endidx > 0
)
SELECT incidentid,
CASE
WHEN endidx = 0
THEN SUBSTR( casualfactors, startidx )
ELSE SUBSTR( casualfactors, startidx, endidx - startidx )
END AS casualfactor
FROM casualfactors_bounds
ORDER BY incidentid, startidx
输出：
事件ID | 休闲因素
---------: | :-----------
1 | 一种
1 | b
1 | C
1 | d
1 | e
1 | F
2 | G
2 | H
2 | 一世
2 | j
2 | ķ
3 | l
解释计划：
| 计划表输出 | | :------------------------------------------------ -------------------------------------------------- ------------- | | 计划哈希值：2740663158 | | | | -------------------------------------------------- -------------------------------------------------- ------------- | | | 身份证 | 操作 | 姓名 | 行 | 字节 | 成本 (%CPU)| 时间 | | | -------------------------------------------------- -------------------------------------------------- ------------- | | | 0 | 选择声明 | | 6 | 276 | 7 (15)| 00:00:01 | | | | 1 | 排序方式 | | 6 | 276 | 7 (15)| 00:00:01 | | | | 2 | 查看 | | 6 | 276 | 6 (0)| 00:00:01 | | | | 3 | 联合所有（递归）广度优先| | | | | | | | |* 4 | 表访问完全 | HSERS_INCIDENT_PSA | 3 | 78 | 3 (0)| 00:00:01 | | | |* 5 | 带泵的递归 | | | | | | | | -------------------------------------------------- -------------------------------------------------- ------------- | | | | 谓词信息（由操作 id 标识）：| | -------------------------------------------------- - | | | | 4 - 过滤器（“ACTIVE_FLAG”='1' AND “ISDELETED”='F'）| | 5 - 过滤器（“ENDIDX”>0）| | | | 注意 | | ----- | | - 用于此语句的动态采样 (level=2) |
db<>在这里摆弄
推荐阅读
- azure-devops - 如何在 Azure Devops Linux 客户端中指定 GCC 版本
- angular - 使用 RxJS 构建复杂的互连请求
- node.js - 我有什么办法可以将 nodejs 应用程序作为服务器运行并与已部署的电子应用程序通信？
- jenkins - jenkins 抛出 java.lang.IllegalArgumentException：当我尝试绕过 SSL 错误时出现预期的命名参数
- javascript - 模态窗口导致父页面正文滚动到顶部
- java - 即使在加载图像时，Spring Boot App 也会为资源中的所有静态内容返回 401
- android - 只需缓冲视频即可在视频视图中播放视频，而无需首先下载完整视频。有没有这方面的图书馆？
- selenium - 使用带有 127.0.0.1 的内容脚本和带有 Firefox 插件的自定义端口
- entity-framework - Web API 2.0 未更新 SQL 数据库上的两个新字段
- swift - 如何自动命名类的实体？斯威夫特 4