Many ways writing a query

Iggy Fernandez is writing about SQL Which Query is Better?—Part III. I am checking here queries from the original article that he did not include in his post. And something else.

HASH JOIN plans

SELECT lname
FROM personnel
WHERE 199170 = any (
  SELECT salary 
  FROM payroll 
  WHERE personnel.empid = payroll.empid) ;

SELECT lname
FROM personnel
WHERE 199170 in ( 
  SELECT salary 
  FROM payroll 
  WHERE personnel.empid = payroll.empid) ;

SELECT lname
FROM personnel
WHERE empid = any (
  SELECT empid 
  FROM payroll
  WHERE salary = 199170);

The HASH JOIN RIGHT SEMI plan:

SELECT lname

FROM personnel

WHERE 0 < (

SELECT count(*)

FROM payroll

WHERE personnel.empid = payroll.empid AND salary = 199170);


Mr Date mentioned in EMEA Harmony 2010 about using any operator that it is behaving relationally. Even thou one should not start using it.

Two additional queries resulting HASH JOIN plan:

SELECT lname

FROM personnel, (select empid from payroll where salary = 199170) payr

WHERE personnel.empid = payr.empid;

SELECT lname

FROM personnel inner join payroll

ON personnel.empid = payroll.empid and salary = 199170;

HASH JOIN RIGHT ANTI plan also possible plan for the question here.

SELECT lname

FROM personnel

WHERE 0 = (

SELECT count(*)

FROM payroll

WHERE personnel.empid = payroll.empid

AND salary != 199170);

SQL_ID b90mkx99aux26, child number 1

-------------------------------------

SELECT lname FROM personnel WHERE 0 = (SELECT count(*) FROM payroll

WHERE personnel.empid = payroll.empid AND salary != 199170)

Plan hash value: 103534934

--------------------------------------------------------------------------------------------

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | 1004 |00:00:00.08 | 261 |

|* 1 | HASH JOIN RIGHT ANTI| | 1 | 9900 | 1004 |00:00:00.08 | 261 |

|* 2 | TABLE ACCESS FULL | PAYROLL | 1 | 8910 | 8896 |00:00:00.01 | 38 |

| 3 | TABLE ACCESS FULL | PERSONNEL | 1 | 9900 | 9900 |00:00:00.01 | 223 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("PERSONNEL"."EMPID"="PAYROLL"."EMPID")

2 - filter("SALARY"!=199170)

Without this rehersal I would not have writen anti join this way. Actually I would like to see an alternative sql resulting this plan.
The original article talked also about indexing the salary column.

create index payroll_salary_idx on payroll(salary);

With this data - no changes in the query plans. Trying a fat one

create index payroll_salary_fat_idx on payroll(salary,empid);

The only change in plans is that FULL TABLE SCAN of payroll changes to INDEX payroll_salary_fat_idx RANGE SCAN in some queries.The join method stays always the same.