优化前的代码
SELECT
P.*
FROM
PROGRAM P
WHERE
P.SERIES_FLAG <> '1'
AND EXISTS(
SELECT
1
FROM
MAP
WHERE
P.CONTENT_ID=ELEMENT_ID
AND ACTION <> '3'
AND PARENT_ID = '00000001000000110000000000200992'
)
消耗时间 14s
优化后的sql:
SELECT
P.*
FROM
PROGRAM P
WHERE
P.SERIES_FLAG <> '1'
AND EXISTS(
SELECT
1
FROM
MAP
WHERE
P.CONTENT_ID=ELEMENT_ID
AND ACTION <> '3'
AND PARENT_ID = '00000001000000110000000000200992'
AND PARENT_TYPE='Service'
AND ELEMENT_TYPE='Program'
)
消耗时间:0.1s
PROGRAM 4000多条 MAP8000多条
SELECT
P.*
FROM
PROGRAM P
WHERE
P.SERIES_FLAG <> '1'
AND EXISTS(
SELECT
1
FROM
MAP
WHERE
P.CONTENT_ID=ELEMENT_ID
AND ACTION <> '3'
AND PARENT_ID = '00000001000000110000000000200992'
)
消耗时间 14s
优化后的sql:
SELECT
P.*
FROM
PROGRAM P
WHERE
P.SERIES_FLAG <> '1'
AND EXISTS(
SELECT
1
FROM
MAP
WHERE
P.CONTENT_ID=ELEMENT_ID
AND ACTION <> '3'
AND PARENT_ID = '00000001000000110000000000200992'
AND PARENT_TYPE='Service'
AND ELEMENT_TYPE='Program'
)
消耗时间:0.1s
PROGRAM 4000多条 MAP8000多条