The work encountered such a SQL:
select b.unitcode, b.unitname, b.fathercorp, pk_corp from bd_corp bwhere b.pk_corp in (SELECT v.pk_corp FROM v_sm_userpower1 v where v.fun_code like '20021025%' and v.fun_name = 'Query' and v.userid = '0001AA1000000000000uyq') Order by B.Unitcode
v_sm_userpower1 is a view
The results obtained by the query have repeated value: ---- Error results
Unitcode UnitName Fathercorp PK_CORP ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------- ---------- ------- 01 Honghezhou Tobacco Company (本) 100101 Honghe Prefecture Tobacco company (本)
10010101 Honghezhou Tobacco Company Maitreya Management Department 1001 10020101 Honghezhou Tobacco Company Maitreya Management Department 1001 10020102 Honghe State Tobacco Company Six Business Department 1001 10030102 Honghe State Tobacco Company Six Business Department 1001 10030103 Honghezhou Tobacco Company Construction Water Management Department 1001 10040103 Red River State Tobacco Company Building Water Management Department 1001 1004010 Honghe State Tobacco Corporation Honghe Marketing Department 1001 10050104 Honghe State Tobacco Corporation Honghe Marketing Department 1001 10050105 Honghezhou Tobacco Company Screen Marketing Department 1001 10060105 Honghe State Tobacco Company Screening Department 1001 1006010 Honghezhou Tobacco Company Mongolian Business Department 1001 1007010 Honghezhou Tobacco Company Mongolian Business Department 1001 10070107 Honghezhou Tobacco Company Shiping Business Department 1001 10080107 Honghezhou Tobacco Company Shiping Business Department 1001 10080108 Honghezhou Tobacco Company Old Business Department 1001 10090108 Honghezhou Tobacco Company Old Business Department 1001 10090109 Honghezhou Tobacco Company Kaiyuan Business Department 1001 10100109 Honghezhou Tobacco Company Kaiyuan Business Department 1001 10100110 Honghezhou Tobacco Company River Marketing Department 1001 10110110 Red River State Tobacco Company Riverside 1001 10110111 Honghezhou Tobacco Company Jinping Marketing Department 1001 10120111 Honghezhou Tobacco Company Jinping Marketing Department 1001 10120112 Honghezhou Tobacco Company Six Factory 1001 10130112 Honghezhou Tobacco
Si Western Rehabilitation Factory 1001 1013 Oracle: Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionPL / SQL Release 9.2.0.1.0 - ProductionCore 9.2.0.1.0 Production
Optimizer mode: CBO
Let's take a look at the results and implementation plans obtained in various optimizer modes:
SQL> SELECT / * ALL_ROWS * / 2 B.Unitcode, B.UnitName, B.fathercorp, PK_CORP FROM BD_CORP B 3 Where b.pk_corp 4 in (SELECT V.PK_CORP from v_sm_userpower1 v where v.fun_code limited "20021025% ' 5 and v.fun_name = 'Query' and v.userid = '0001AA10000000000uyq') 6 Order by B.Unitcode 7 /
Unitcode UnitName Fathercorp PK_CORP ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------- ---------- ------- 01 Honghezhou Tobacco Company (本) 100101 Honghe Prefecture Tobacco company (本)
10010101 Honghezhou Tobacco Company Maitreya Management Department 1001 10020101 Honghezhou Tobacco Company Maitreya Management Department 1001 10020102 Honghe State Tobacco Company Six Business Department 1001 10030102 Honghe State Tobacco Company Six Business Department 1001 10030103 Honghezhou Tobacco Company Construction Water Management Department 1001 10040103 Red River State Tobacco Company Building Water Management Department 1001 1004010 Honghe State Tobacco Corporation Honghe Marketing Department 1001 10050104 Honghe State Tobacco Corporation Honghe Marketing Department 1001 10050105 Honghezhou Tobacco Company Screen Marketing Department 1001 10060105 Honghe State Tobacco Company Screening Department 1001 1006010 Honghezhou Tobacco Company Mongolian Business Department 1001 1007010 Honghezhou Tobacco Company Mongolian Business Department 1001 10070107 Honghezhou Tobacco Company Shiping Business Department 1001 10080107 Honghezhou Tobacco Company Shiping Business Department 1001 10080108 Honghezhou Tobacco Company Old Business Department 1001 10090108 Honghezhou Tobacco Company Old Business Department 1001 10090109 Honghezhou Tobacco Company Kaiyuan Business Department 1001 10100109 Honghezhou Tobacco Company Kaiyuan Business Department 1001 10100110 Honghezhou Tobacco Company River Marketing Department 1001 10110110 Red River State Tobacco Company River Delivery Department 1001 10110111 Honghezhou Tobacco Company Jinping Marketing Department 1001 10120111 Honghezhou Tobacco Company Jinping Marketing Department 1001 10120112 Honghezhou Tobacco Company Sixu Factory 1001 10130112 Honghezhou Tobacco Company
West Repair Factory 1001 101326 Rows SELECTED
Rows Row Source Operation --------------------------------------------- ------------ 26 Sort ORDER BY 26 NESTED LOOPS 27 View 27 Sort Unique 27 Union-All 0 Table Access by Index Rowid Sm_UserGrouppower - DRIVING TABLE 1 NESTED LOOPS 0 MERGE JOIN CARTESIAN 0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA 0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 32028) 0 BUFFER SORT 0 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER 0 INDEX RANGE SCAN I_SM_FUNCREGISTER (object id 32005) 0 INDEX RANGE SCAN I_SM_USERGROUPPOWE (object id 32023) 0 NESTED LOOPS 0 NESTED Loops 0 Table Access Full Sm_UserGroupPower 0 Table Access by Index Rowid Sm_User_RELA 0 Index Range Scan IX_SM_USER_RELA (Object ID 32028) 0 Table Access By Index Rowid Sm_ButnRegister 0 Index Uni QUE SCAN PK_SM_BUTNREGISTER (object id 31993) 14 NESTED LOOPS 60369 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER 60369 INDEX RANGE SCAN IX_SM_APPUSERPOWER (object id 31986) 14 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER 7031 INDEX UNIQUE SCAN PK_SM_FUNCREGISTER (object id 32006) 13 NESTED LOOPS 60369 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER 60369 INDEX RANGE SCAN IX_SM_APPUSERPOWER (object id 31986) 13 TABLE ACCESS BY INDEX ROWID SM_BUTNREGISTER 53338 INDEX UNIQUE SCAN PK_SM_BUTNREGISTER (object id 31993) 26 TABLE ACCESS BY INDEX ROWID BD_CORP 26 INDEX UNIQUE SCAN PK_BD_CORP (object id 30491)
SQL> select / * first_rows * / 2 b.unitcode, b.unitname, b.fathercorp, pk_corp from bd_corp b 3 where b.pk_corp 4 in (SELECT v.pk_corp FROM v_sm_userpower1 v where v.fun_code like '20021025%' 5 and v.fun_name = 'query' and v.userid = '0001AA10000000000uyq') 6 Order by B.Unitcode 7 / Correct Result:
Unitcode UnitName Fathercorp PK_CORP ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------- ---------------- 01 Honghezhou Tobacco Company (本) 10010101 Honghezhou Tobacco Company Maitreya Management Department 1001 10020102 Honghezhou Tobacco Company West Business Department 1001 10030103 Honghezhou Tobacco Company Construction Water Management Department 1001 10040104 Honghe State Tobacco Corporation Honghe Marketing Department 1001 10050105 Honghezhou Tobacco Company Screen Marketing Department 1001 10060106 Honghezhou Tobacco Company Mongolian Business Department 1001 1007010 Honghezhou Tobacco Company Business department 1001 10080108 Honghezhou Tobacco Company Old Business Department 1001 10090109 Honghezhou Tobacco Company Kaiyuan Business Department 1001 10100110 Honghezhou Tobacco Company River Division 1001 10110111 Honghezhou Tobacco Company Jinping Marketing Department 1001 10120112 Honghezhou Tobacco Company Sixi Factory 1001 1013
13 rows selected
Rows Row Source Operation --------------------------------------------- ------------ 13 NESTED LOOPS SEMI 13 Table Access by Index Rowid BD_CORP - NL DRIVING TABLE 13 INDEX FULL Scan i_bd_corp_1 (Object ID 30488) 13 View 104 Sort Unique 27 Union-All 0 Table ACCESS BY INDEX ROWID SM_USERGROUPPOWER 1 NESTED LOOPS 0 MERGE JOIN CARTESIAN 0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA 0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 32028) 0 BUFFER SORT 0 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER 0 INDEX RANGE SCAN I_SM_FUNCREGISTER (object id 32005) 0 INDEX RANGE SCAN I_SM_USERGROUPPOWE (object id 32023) 0 NESTED LOOPS 0 NESTED LOOPS 0 TABLE ACCESS FULL SM_USERGROUPPOWER 0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA 0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 32028) 0 TABLE ACCESS BY INDEX ROWID SM_BUTNREGISTER 0 INDEX UNIQUE SCAN PK_SM_BUTNREGISTER (object id 31993) 14 NESTED LOOPS 60369 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER 60369 INDEX RANGE SCAN IX_SM_APPUSERPOWER (object id 31986) 14 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER 7031 INDEX UNIQUE SCAN PK_SM_FUNCREGISTER (object id 32006) 13 NESTED LOOPS 60369 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER 60369 INDEX RANGE SCAN IX_SM_APPUSERPOWER (object id 31986) 13 TABLE ACCESS BY INDEX ROWID SM_BUTNREGISTER 53338 INDEX UNIQUE SCAN PK_SM_BUTNREGISTER (object id 31993)
SQL> SELECT / * Rule * / 2 B. UNITCODE, B.Unitname, B.fathercorp, PK_CORP FROM BD_CORP B 3 WHERE B.PK_CORP 4 in (SELECT V.PK_CORP FROM V_SM_USERPOWER1 V where v.fun_code like '20021025%' 5 and v.fun_name = 'query' and v.userid = '0001AA10000000000uyq') 6 Order by B.Unitcode 7 / Correct Result:
Unitcode UnitName Fathercorp PK_CORP ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------ ---------- ------- 01 Honghezhou Tobacco Company (本) 10010101 Honghezhou Tobacco Company Maitreya Management Department 1001 10020102 Honghezhou Tobacco Company Lixi Business Department 1001 10030103 Honghezhou Tobacco Company Construction Water Management Department 1001 10040104 Honghe State Tobacco Corporation Honghe Marketing Department 1001 10050105 Honghezhou Tobacco Company Screen Marketing Department 1001 10060106 Honghezhou Tobacco Company Mongolian Management Department 1001 1007010 Honghezhou Tobacco Company石屏 经营 100 100 公司 经 部 部 部 州 部 部 部 公司 公司 公司 公司 公司 公司 部 部 公司 公司 公司 公司 公司 公司 公司 公司 公司 公司 公司 公司 公司 公司 公司 公司 公司 公司 公司 公司 公司 公司 公司 公司 公司 西 公司 西 西 西 公司Grilled factory 1001 1013
13 rows selected
Use hint:
SQL> SELECT / * USE_HASH (B, V) * / B.Unitcode, B.Unitname, B.fathercorp, PK_CORP FROM BD_CORP B 2 Where b.pk_corp 3 in (Select Distinct V.pk_corp from v_sm_userpower1 v where v.fun_code Like '20021025%' 4 and v.fun_name = 'query' and v.userid = '0001AA10000000000UYQ') 5 Order by B.Unitcode 6 /
Correct results:
Unitcode UnitName Fathercorp PK_CORP ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------- ------- 01 Honghezhou Tobacco Company (Part) 10010101 Honghezhou Tobacco Company Maitreya Management Department 1001 10020102 Honghezhou Tobacco Company Ministry 1001 10030103 Honghezhou Tobacco Company Construction Water Management Department 1001 10040104 Honghe State Tobacco Company Honghe Marketing Department 1001 10050105 Honghezhou Tobacco Company Screen Marketing Department 1001 10060106 Honghezhou Tobacco Company Mengda Management Department 1001 1007010 Honghezhou Tobacco Company Shipping Department 1001 10080108 Honghezhou Tobacco Company Old Business Department 1001 10090109 Honghezhou Tobacco Company Kaiyuan Business Department 1001 10100110 Honghezhou Tobacco Company River Marketing Department 1001 10110111 Honghezhou Tobacco Company Jinping Marketing Department 1001 1012012 Honghezhou Tobacco Company Sixu Factory 1001 101313 Rows SELECTED
Rows Row Source Operation --------------------------------------------- ------------ 13 Sort ORDER BY 13 Hash Join Semi 13 Table Access Full BD_CORP - Change connection method and Driving Table 27 View 27 Sort Unique 27 Union-All 0 NESTED LOOPS 0 NESTED LOOPS 0 TABLE ACCESS FULL SM_USERGROUPPOWER 0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA 0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 288328) 0 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER 0 INDEX UNIQUE SCAN PK_SM_FUNCREGISTER (object id 288306) 0 NESTED LOOPS 0 NESTED LOOPS 0 TABLE ACCESS FULL SM_USERGROUPPOWER 0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA 0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 288328) 0 TABLE ACCESS BY INDEX ROWID SM_BUTNREGISTER 0 INDEX UNIQUE SCAN PK_SM_BUTNREGISTER (object id 288289) 14 TABLE ACCESS B Y INDEX ROWID SM_APPUSERPOWER 16 NESTED LOOPS 1 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER 1 INDEX RANGE SCAN I_SM_FUNCREGISTER (object id 288305) 14 INDEX RANGE SCAN I_APPUSERPOWER_1 (object id 288281) 13 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER 15 NESTED LOOPS 1 TABLE ACCESS FULL SM_BUTNREGISTER 13 Index Range Scan I_AppuserPower_1 (Object ID 288281) uses methods for analyzing statistics to solve this problem: Exec dbms_utility.Analyze_schema ('hhyc', 'compute');
SQL> SELECT / * ALL_ROWS * / 2 B.Unitcode, B.UnitName, B.fathercorp, PK_CORP FROM BD_CORP B 3 Where b.pk_corp 4 in (SELECT V.PK_CORP from v_sm_userpower1 v where v.fun_code limited "20021025% ' 5 and v.fun_name = 'query' and v.userid = '0001AA10000000000uyq') 6 Order by B.Unitcode 7 / Correct Result:
Unitcode UnitName Fathercorp PK_CORP ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------- ------- 01 Honghezhou Tobacco Company (本) 10010101 Honghezhou Tobacco Company Maitreya Management Department 1001 10020102 Honghezhou Tobacco Company Six Management Department 1001 10030101 Honghezhou Tobacco Company Construction Water Management Department 1001 10040104 Honghe State Tobacco Corporation Honghe Marketing Department 1001 10050105 Honghe State Tobacco Company Screen Marketing Department 1001 10060106 Honghezhou Tobacco Company Mengda Management Department 1001 1007010 Honghezhou Tobacco Company Shipping Department 1001 10080108 Honghezhou Tobacco Company Old Business Department 1001 10090109 Honghezhou Tobacco Company Kaiyuan Business Department 1001 10100110 Honghezhou Tobacco Company River Marketing Department 1001 10110111 Honghezhou Tobacco Company Jinping Marketing Department 1001 10120112 Honghezhou Tobacco Company Six Factory 1001 1013
13 rows selected
Rows Row Source Operation --------------------------------------------- ------------ 13 Sort ORDER BY 13 Hash Join Semi 13 Table Access Full BD_CORP - Analyzes Statistics, Change All_ROWS under the original execution plan 27 View 27 Sort Unique 27 Union-All 0 NESTED LOOPS 0 NESTED LOOPS 0 TABLE ACCESS FULL SM_USERGROUPPOWER 0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA 0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 288328) 0 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER 0 INDEX UNIQUE SCAN PK_SM_FUNCREGISTER (object id 288306) 0 NESTED LOOPS 0 NESTED LOOPS 0 TABLE ACCESS FULL SM_USERGROUPPOWER 0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA 0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 288328) 0 TABLE ACCESS BY INDEX ROWID SM_BUTNREGISTER 0 INDEX UNIQUE SCAN PK_SM_BUTNREGISTER (object id 288289) 14 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER 16 NESTED LOOPS 1 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER 1 INDEX RANGE SCAN I_SM_FUNCREGISTER (object id 288305) 14 INDEX RANGE SCAN I_APPUSERPOWER_1 (object id 288281) 13 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER 15 NESTED LOOPS 1 TABLE ACCESS FULL SM_BUTNREGISTER 13 Index Range Scan I_AppuserPower_1 (Object ID 288281) Oracle9204:
select / * all_rows * / b.unitcode, b.unitname, b.fathercorp, pk_corp from bd_corp bwhere b.pk_corpin (SELECT distinct v.pk_corp FROM v_sm_userpower1 v where v.fun_code like '20021025%' and v.fun_name = ' Query 'and v.userid =' 0001AA10000000000UYQ ') Order by B.Unitcode UnitCode UnitName Fathercorp PK_CORP ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- -01 Honghezhou Tobacco Company (Part) 10010101 Honghezhou Tobacco Company Maitreya Management Department 1001 10020102 Honghezhou Tobacco Company Six Business Department 1001 10030103 Honghezhou Tobacco Company Construction Water Management Department 1001 1004010 Honghezhou Tobacco Company Honghe Marketing Department 1001 10050105 Honghe State Tobacco company screen marketing department 1001 10060106 Honghezhou Tobacco Company Mongolian Business Department 1001 10070107 Honghezhou Tobacco Company Shiping Business Department 1001 10080108 Honghezhou Tobacco Company Old Business Department 1001 10090109 Honghezhou Tobacco Company Kaiyuan Business Department 1001 10100110 Honghezhou Tobacco Company River Marketing Department 1001 10110111 Honghezhou Tobacco Company Jinping Marketing Department 1001 1012012 Honghezhou Tobacco Company Sixu Factory 1001 101313 Rows SELECTED
Rows Row Source Operation --------------------------------------------- ------------ 26 SORT ORDER BY 26 NESTED LOOPS 27 VIEW 27 SORT UNIQUE 27 UNION-ALL 0 TABLE ACCESS BY INDEX ROWID SM_USERGROUPPOWER 1 NESTED LOOPS 0 MERGE JOIN CARTESIAN 0 TABLE ACCESS BY INDEX ROWID SM_USER_RELA 0 INDEX RANGE SCAN IX_SM_USER_RELA (object id 32028) 0 BUFFER SORT 0 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER 0 INDEX RANGE SCAN I_SM_FUNCREGISTER (object id 32005) 0 INDEX RANGE SCAN I_SM_USERGROUPPOWE (object id 32023) 0 NESTED LOOPS 0 NESTED LOOPS 0 TABLE ACCESS FULL SM_USERGROUPPOWER 0 Table Access by Index Rowid Sm_User_RELA 0 Index Range Scan IX_SM_USER_RELA (Object ID 32028) 0 Table Access by Index Rowid Sm_ButnRegister 0 Index Unique Scan PK_SM_BUTNRE GISTER (object id 31993) 14 NESTED LOOPS 60369 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER 60369 INDEX RANGE SCAN IX_SM_APPUSERPOWER (object id 31986) 14 TABLE ACCESS BY INDEX ROWID SM_FUNCREGISTER 7031 INDEX UNIQUE SCAN PK_SM_FUNCREGISTER (object id 32006) 13 NESTED LOOPS 60369 TABLE ACCESS BY INDEX ROWID SM_APPUSERPOWER 60369 INDEX RANGE SCAN IX_SM_APPUSERPOWER (object id 31986) 13 TABLE ACCESS BY INDEX ROWID SM_BUTNREGISTER 53338 INDEX UNIQUE SCAN PK_SM_BUTNREGISTER (object id 31993) 26 TABLE ACCESS BY INDEX ROWID BD_CORP 26 INDEX UNIQUE SCAN PK_BD_CORP (object id 30491)
We found that under 9204, the SQL uses the same execution plan as 9201, but it has been different! According to the experiment of the above circumstances, I will inference: This issue should be in the special case of driving tables in Oracle on all_rows for NL execution. It is correct after the order in which the drive table is exchanged.
The SQL is performed on 9203, and the execution result is unstable, or there is a possibility of error.
9204 previous versions, especially in all_ROWS, there are many bugs.
