sql - SQL 性能优化

Question

我得到了两个返回相同结果的 SQL，我原以为 SQL2 会比 SQL1 快，但事实证明 SQL1 比 SQL2 快 5 倍。有人可以向我解释一下吗？提前致谢。

SQL1: 2 seconds
select count(*) from (
select * from (
SELECT distinct job_id, REGEXP_SUBSTR(JOB_Description, '(ABC|CDE)([[:digit:]]){5}', 1, LEVEL) AS substr
FROM (
    select  job_id, JOB_DESCRIPTION from smms.job where TO_NUMBER(to_char(CREATE_DATE,'YYYY'))=2017
)
CONNECT BY LEVEL <= REGEXP_COUNT(JOB_Description, '(ABC|CDE)([[:digit:]]){5}')
and prior job_description = job_description
AND PRIOR DBMS_RANDOM.VALUE IS NOT null
order by job_id
) **where substr is not null**);

SQL2: 11 seconds
select count(*) from (
SELECT distinct job_id, REGEXP_SUBSTR(JOB_Description, '(ABC|CDE)([[:digit:]]){5}', 1, LEVEL) AS substr
FROM (
    select  job_id, JOB_DESCRIPTION from smms.job where TO_NUMBER(to_char(CREATE_DATE,'YYYY'))=2017 **and  REGEXP_COUNT(JOB_Description, '(ABC|CDE)([[:digit:]]){5}')>0**
)
CONNECT BY LEVEL <= REGEXP_COUNT(JOB_Description, '(ABC|CDE)([[:digit:]]){5}')
and prior job_description = job_description
AND PRIOR DBMS_RANDOM.VALUE IS NOT null
order by job_id
);

标签： sqlregexstringoracle11gquery-performance