2010-12-09

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.

1 comment:

  1. Hi, Timo,

    re: I would like to see an alternative sql resulting this plan.

    The following queries will probably use the HASH JOIN RIGHT ANTI method.

    SELECT lname
    FROM personnel
    WHERE empid NOT IN (SELECT empid
    FROM payroll
    WHERE salary != 199170);

    SELECT lname
    FROM personnel
    WHERE NOT EXISTS (SELECT *
    FROM payroll
    WHERE personnel.empid = payroll.empid
    AND salary != 199170);

    Kind regards,

    Iggy

    ReplyDelete

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.