Oracle semi-join (in, exists) Parameters

The parameters related to oracle semi-join (in, exists) Semi-join and anti-connection are actually in and exists. Before that, I was confused about these things, I think there are some online saying that exists is better than in, and so on. in fact, it is not so blind when I understand some of its internal mechanisms. First, let's take a look at several related parameters: _ always_semi_join = CHOOSE. this parameter is set to semi-join at the minimum cost. You can use select NAME_KSPVLD_VALUES name, VALUE_KSPVLD_VALUES valuefrom X $ KSPVLD_VALUESwhere variables like nvl ('& name', NAME_KSPVLD_VALUES); Enter value for name: _ always_semi_join to view all values. Hash join semi is also called hash semi-JOIN for an experiment: create table filter (sex varchar2 (2); insert into filter values ('male '); insert into filter values ('female '); insert into filter values ('male'); insert into filter values ('female '); commit; create table emp1 (emp_no number, sex varchar2 (2); insert into emp1 select rownum, sex from filter order by 2; insert into emp1 select rownum, sex from filter order by 2; co Mmit; execute dbms_stats.gather_table_stats (ownname => user, tabname => 'filter') execute dbms_stats.gather_table_stats: first, the hash semi-join _ always_semi_join = CHOOSE parameter must be choose, that is, the semi-join is selected based on the minimum cost. SELECT/* + gather_plan_statistics */* FROM EMP1 where exists (SELECT 0 from filterwhere filter. SEX = EMP1.SEX); SELECT * FROM table (dbms_xplan.display_cursor (null, null, 'iostats last'); see results: PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID bkvqwm45n1fdb, child number 0 ----------- -------------------------- SELECT/* + gather_plan_statistics */* FROM EMP1 where exists (SELECT 0 fromfilter where filter. SEX = EMP1.SEX) Plan hash value: 1392637843 bytes | Id | Operation | Name | Starts | E-Rows | A-Time | Buffers | average -------------------------------------------------------------------------------- ------- | * 1 | hash join semi | 1 | 10 | 10 | 00:00:00. 01 | 14 | 2 | table access full | EMP1 | 1 | 10 | 10 | 00:00:00. 01 | 7 | 3 | table access full | FILTER | 1 | 5 | 2 | 00:00:00. 01 | 7 | your Predicate Information (identified by operation id): ----------------------------------------------------- 1-access ("FILTER ". "S EX "=" EMP1 ". "SEX") Well, Let's explain this execution plan. Now we can see that 1 is a hash semi-join. First scan the emp1 table and then scan the filter table. Here emp1 is the driver table, retrieve 10 rows and generate a hash table. Each row is retrieved and filtered out from the filter table of another hash table. emp1 has two gender types. Therefore, one gender is filtered once and ends, you don't need to perform any further matching. Here, the 'male' matches once and the 'Female matches once, two times in total. The above A-Rows is 2, which indicates that this table filters two values. A few values have A-Rows. the above example uses exists. The following example uses in: SELECT/* + gather_plan_statistics */* FROM EMP1 WHERE sex in (SELECT/* + USE_HASH */sex from filter ); the results are the same as the preceding execution plan. Here, in and exists are the same, both of which are hash semi-join. Next let's take a look at the semi-join without hash: alter session set "_ always_semi_join" = OFF; SELECT/* + gather_plan_statistics */* FROM EMP1 WHERE sex in (SELECT/* + USE_HASH */sex from filter); SELECT * FROM table (dbms_xplan.display_cursor (null, null, 'iostats last'); PLAN_TABLE_OUTPUT tables ---------------------------------------------------------------------------------------------------------------------------------------------------- -- SQL _ID 1p9a7sq3tdb4p, child number 0 ------------------------------------- SELECT/* + gather_plan_statistics */* FROM EMP1 WHERE sex in (SELECT/* + USE_HASH */sex from filter) plan hash value: 3840124480 bytes | Id | Operation | Name | Starts | E-Rows | A-Time | Buffers | --------------------------------------- -------------------------------------------- | * 1 | FILTER | 1 | 10 | 00:00:00. 01 | 20 | 2 | table access full | EMP1 | 1 | 10 | 10 | 00:00:00. 01 | 8 | * 3 | table access full | FILTER | 2 | 1 | 2 | 00:00:00. 01 | 12 | your Predicate Information (identified by operation id ):------------------------------------------ --------- 1-filter (is not null) 3-filter ("SEX" =: B1) here we can see that the filter IS better than the semi-join performance. Nested semi-join: alter session set "_ always_semi_join" = 'choose '; first modify back SELECT/* + gather_plan_statistics */* FROM EMP1 where exists (SELECT/* + NL_SJ */0 from filterwhere filter. SEX = EMP1.SEX); SELECT * FROM table (dbms_xplan.display_cursor (null, null, 'iostats last'); Plan hash value: 2921932404 bytes | Id | Operation | Name | Starts | E-Rows | A-Time | Buffers | bytes | 1 | nested loops semi | 1 | 10 | 10 | 00:00:00. 01 | 20 | 2 | table access full | EMP1 | 1 | 10 | 10 | 00:00:00. 01 | 8 | * 3 | table access full | FILTER | 2 | 5 | 2 | 00:00:00. 01 | 12 | Hangzhou Pr Edicate Information (identified by operation id): --------------------------------------------------- 3-filter ("FILTER ". "SEX" = "EMP1 ". "SEX") NL_SJ needs to be placed IN the IN clause, or IN the EXISTS and not exists statements, which forces the nested semi-join. here we can see that 1 goes through the nested semi-join. So why is starts 2? In fact, the sex in the emp1 driver table is only male and female, so it needs to be compared twice. Insert into emp1 values (100, '中'); commit; SELECT/* + gather_plan_statistics */* FROM EMP1 where exists (SELECT/* + NL_SJ */0 from filterwhere filter. SEX = EMP1.SEX); SELECT * FROM table (dbms_xplan.display_cursor (null, null, 'iostats last'); insert a 'zhong' gender, and then check the Plan: Plan hash value: 2921932404 bytes | Id | Operation | Name | Starts | E-Rows | A-Time | Buffers | bytes | 1 | nested loops semi | 1 | 10 | 10 | 00:00:00. 01 | 27 | 2 | table access full | EMP1 | 1 | 10 | 11 | 00:00:00. 01 | 8 | * 3 | table access full | FILTER | 3 | 5 | 2 | 00:00:00. 01 | 19 | Pred Icate Information (identified by operation id): ----------------------------------------------- 3-filter ("FILTER". "SEX" = "EMP1". "SEX") You Can See That starts is 3. The filter table is operated three times, that is, three times in comparison. If we add a condition: SELECT/* + gather_plan_statistics */* FROM EMP1 WHERE sex = 'femal' and EXISTS (SELECT/* + NL_SJ */0 from filterwhere filter. SEX = EMP1.SEX); SELECT * FROM table (dbms_xplan.display_cursor (null, null, 'iostats last'); Plan hash value: 2921932404 partitions | Id | Operation | Name | Starts | E-Rows | A-Time | Buffers | Metric | 1 | nested loops semi | 1 | 5 | 4 | 00:00:00. 01 | 14 | * 2 | table access full | EMP1 | 1 | 5 | 4 | 00:00:00. 01 | 8 | * 3 | table access full | FILTER | 1 | 3 | 1 | 00:00:00. 01 | 6 | descripredicate Information (identified by operat Ion id): ------------------------------------------------- 2-filter ("SEX" = 'female ') 3-filter ("FILTER ". "SEX" = 'female 'AND "FILTER ". "SEX" = "EMP1 ". "SEX") So here there is a female filter, then the filter table can be operated only once, so starts is 1. insert into filter values ('中'); commit; here I insert a filter table and execute it again: SELECT/* + gather_plan_statistics */* FROM EMP1 where exists (SELECT/* + NL_SJ */0 from filterwhere filter. SEX = EMP1.SEX); SELECT * FROM table (dbms_xp Lan. display_cursor (null, null, 'iostats last'); Plan hash value: 2921932404 Rows | Id | Operation | Name | Starts | E-Rows | A-Time | Buffers | 1 | nested loops semi | 1 | 10 | 11 | 00:00:00. 01 | 26 | 2 | TABLE ACCESS FULL | EMP1 | 1 | 10 | 11 | 00:00:00. 01 | 8 | * 3 | table access full | FILTER | 3 | 5 | 3 | 00:00:00. 01 | 18 | your Predicate Information (identified by operation id): --------------------------------------------------- 3-filter ("FILTER ". "SEX" = "EMP1 ". "SEX") when the-Rows of 3 changes, it is 3, so A-Rows is the actual number of returned Rows. Nested full join: delete emp1 where sex = '中'; commit; SELECT/* + gather_plan_statistics USE_NL (EMP1, FILTER) */EMP1. * FROM EMP1, filter where filter. SEX = EMP1.SEX; SELECT * FROM table (dbms_xplan.display_cursor (null, null, 'iostats last'); Plan hash value: 3269263915 Rows | Id | Operation | Name | Starts | E-Rows | A-Time | Buffer S | bytes | 1 | nested loops | 1 | 25 | 26 | 00:00:00. 01 | 53 | 2 | table access full | FILTER | 1 | 5 | 6 | 00:00:00. 01 | 9 | * 3 | table access full | EMP1 | 6 | 5 | 26 | 00:00:00. 01 | 44 | descripredicate Information (identified by operati On id): --------------------------------------------------- 3-filter ("FILTER ". "SEX" = "EMP1 ". "SEX") We can see that starts 3 is 6, because it is nested full join, not semi join, so the rows of 2 are compared once. Here we use the least number of rows and the lowest cost as the driving table. Therefore, we can see that the filter is the driving table and emp1 is driven. Here 3, the operation is performed six times and 26 rows are returned. In this way, we can clearly understand that the in and exists performance is not necessarily good. We will record it here today.