Has the following SQL statement:
SELECT COUNT (*)
The FROM SP
INNER JOIN EEPP
ON SP. SP_STORE_ID=EEPP_ACCOUNT_PICKUP_STORE_ID
AND SP. SP_COMPANY_GROUP_ID=EEPP. EEPP_COMPANY_GROUP_ID

INNER JOIN SIE
ON EEPP_ACCOUNT_PICKUP_STORE_ID=SIE_STORE_ID
AND EEPP_COLLECT_DATE=SIE_DATE
AND EEPP_COMPANY_GROUP_ID=SIE_COMPANY_GROUP_ID
AND SIE_NO='01'

INNER JOIN TSM
ON TSM_ID='0220'
AND TSM_SUB_VALUE=https://bbs.csdn.net/topics/EEPP.EEPP_ECCODE
AND TSM_SUB_VALUE3=SP. SP_COMPANY_ID

WHERE 1=1
AND SP_ACCOUNTANT='SP'
AND SP_COMPANY_ID='3100'
AND SP_COMPANY_GROUP_ID='40'
This method has been performed, check out results,
1. If the "3100" to "1100", the '40' to '30' can perform a result,
2. Comments or conditions (except 1=1) in one line also can perform as a result, not always perform;
3. Or remove the "INNER JOIN TSM
ON TSM_ID='0220'
AND TSM_SUB_VALUE=https://bbs.csdn.net/topics/EEPP.EEPP_ECCODE
AND TSM_SUB_VALUE3=SP. SP_COMPANY_ID "this association, also can perform as a result,
Above this query is not the result is what reason is caused, thanks for your help!

CodePudding user response:

The table is large? Or what

CodePudding user response:

All associated fields, all need to add alias table,

CodePudding user response:

Add no table is not big, alias field?

CodePudding user response:

Also didn't find a lock table, the conditions of data is only 143 records, don't know what are the mechanisms of ORACEL query, is there is something wrong with the table?

CodePudding user response:

Look at the execution plan,,,

CodePudding user response:

Execution plan is as follows:

CodePudding user response:

Can try to use the left join replacing inner join

CodePudding user response:

Can change LEFT out of the results, but that the results of the retrieved is not correct, LEFT the JOIN and INNER JOIN what is the difference between the performance of the query?

CodePudding user response:

At the time of execution see wait for events

CodePudding user response:

Reconstruction of SP table can perform the results, is the table there is a problem, can cause the query a deadlock?

CodePudding user response:

Is just someone to modify this table, hasn't been submitted? Query alone won't lock table

CodePudding user response:

Encountered similar problems, the reason to you

CodePudding user response:

Statistics, execution plan two cartesian product connection, to collect the execution plan or disable cartesian product
/* + opt_param (' _optimizer_mjc_enabled ', 'false') */