Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 子查询执行计划
|
username: 胡杨树旁
Execution plan for the subquery:
explain SELECT
*
FROM
PLC_CAR plccareo0_
WHERE
(
plccareo0_.topId IN (
SELECT
plcmaineo1_.topId
FROM
PLC_MAIN plcmaineo1_
WHERE
1 = 1
AND plcmaineo1_.lstVerFlag = '1'
AND plcmaineo1_.lstEffVerFlag = '1'
AND (
plcmaineo1_.businessType IN ( '66', '68' ))
AND plcmaineo1_.coinsLinkCode <> '4'
AND (
plcmaineo1_.productCode IN ( '0507' ))
AND topid <> '20482241691808'
))
AND plccareo0_.licensePlateNo = '续A8B30C'
AND plccareo0_.licenseType = '02'
ORDER BY
plccareo0_.startTime DESC;
The plccareo0_ table uses the index on licenseplateno, and the subquery returns 0 results. Why doesn’t the plccareo0_ table use the topId index? This way, the execution returns 0 rows, but the plccareo0_ table uses the licenseplateno index, matching 1003 rows for plccareo0_.licensePlateNo = '续A8B30C'. When a hint is added to force plccareo0_ to use the topId index, the execution plan shows a full table scan on the index.
|
username: 
|
username: 
|
username: