Why a SQL statement becomes so slow and how to solve it

Phenomenon: A SQL suddenly runs in a very slow way.

Select Uidtable.column_value, first_name ' last_name, Company, Job_title, Upper (Member_level), Upper ( Service_value) from (select cast (Multiset (select B to BBB) as TAAA) from dual) Uidtable,mem ber where Uidtable.column_value = member.login_id (+) and member.site= ' Alibaba ' and member.site= ' test ';

The cause of the error: The user added a condition member.site=test, resulting in the order of the connection changed, the original driver table is uidtable (up to 1024 records), now become a member table do drive (600W). So this statement has become very slow.

But since it's an outer join, why does the order of connections change? Since the connection order of the outer joins is not determined by the cost, it is determined by the conditions of the connection. The discovery execution plan is as follows:

------------------------------------------------------- Id Operation Name Rows Bytes Cost -------------------------------------------------------- 0 SELECT STATEMENT 1018 72278 8155 1 NESTED LOOPS 1018 72278 8155 2 VIEW 4072 69224 one 3 COLLECTION iterator subquery FETCH 4 TABLE ACCESS full DUAL 4072 11 5 TABLE Access full triple 287 2 6 TABLE access by INDEX ROWID member 1 2 * 7 INDEX UNIQUE SCAN member_site_lid_pk 4 1 ---------------------------- ---------------------

Why is there no external connection at all? The problem is in the condition of member.site= ' test ' because the externally connected table adds conditions that cause the outer join to fail. Instead of member.site (+) = ' Test ', the problem is solved completely.

--------------------------------------------------- Id Operation Name Rows Bytes Cost ----------------------------------------------------- 0 SELECT STATEMENT 1018 72278 8155 1 NESTED LOOPS 1018 72278 8155 2 VIEW 4072 69224 One 3 COLLECTION iterator subquery FETCH 4 TABLE ACCESS full DUAL 4072 5 TABLE ACCESS full BBB 287 2 6 TABLE ACCESS by INDEX ROWID member 1 2 * 7 INDEX UNIQUE SCAN MEMBER_SITE_LID_PK 4 1 -----------------------------------------------------------