A SQL Minute: Using a Table To Provide Query Parameters (Query AntiPatterns)
From a book that I am writing called Practical SQL Using Oracle Database 11g:
According to AntiPatterns: Refactoring Software, Architectures, and Projects in Crisis, an AntiPattern “describes a commonly occurring solution to a problem that generates decidedly negative consequences. The AntiPattern may be the result of a manager or developer not knowing any better, not having sufficient knowledge or experience in solving a particular type of problem, or having applied a perfectly good pattern in the wrong context.” An example of a SQL AntiPattern is the use of a table to provide query parameters. For example, suppose that we need a list of employees in the specified department. We could use one of the following SQL formulations. In the second formulation, Parameters is a table with one row and contains the parameters for our query.
SELECT e.first_name, e.last_name, e.salary FROM employees e WHERE e.department_id = 50; SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'TYPICAL IOSTATS LAST')); SELECT e.first_name, e.last_name, e.salary FROM employees e, parameters p WHERE e.department_id = p.department_id;
The above two queries produce the same answer but their execution plans may not be equally efficient. In the case of the second query, the Oracle optimizer is likely to wrongly estimate the number of required rows because it bases its decision on the average number of employees in each department instead of the number of employees in just the department of interest. In the demonstration below, we see that, in the case of the first query, the Oracle optimizer accurately estimates that 45 rows are required and realizes that a full scan of the Employees table would be cheaper than using an index. In the second case, it wrongly estimates that only 10 rows are required and therefore uses an index. The second execution plan touches 60% more data buffers than the first execution plan.
SQL> SELECT e.first_name, e.last_name, e.salary
2 FROM employees e
3 WHERE e.department_id = 50;
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Donald OConnell 2600
Douglas Grant 2600
Matthew Weiss 8000
Adam Fripp 8200
Payam Kaufling 7900
Shanta Vollman 6500
Kevin Mourgos 5800
Julia Nayer 3200
Irene Mikkilineni 2700
James Landry 2400
Steven Markle 2200
Laura Bissot 3300
Mozhe Atkinson 2800
James Marlow 2500
TJ Olson 2100
Jason Mallin 3300
Michael Rogers 2900
Ki Gee 2400
Hazel Philtanker 2200
Renske Ladwig 3600
Stephen Stiles 3200
John Seo 2700
Joshua Patel 2500
Trenna Rajs 3500
Curtis Davies 3100
Randall Matos 2600
Peter Vargas 2500
Winston Taylor 3200
Jean Fleaur 3100
Martha Sullivan 2500
Girard Geoni 2800
Nandita Sarchand 4200
Alexis Bull 4100
Julia Dellinger 3400
Anthony Cabrio 3000
Kelly Chung 3800
Jennifer Dilly 3600
Timothy Gates 2900
Randall Perkins 2500
Sarah Bell 4000
Britney Everett 3900
Samuel McCain 3200
Vance Jones 2800
Alana Walsh 3100
Kevin Feeney 3000
45 rows selected.
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'TYPICAL IOSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
SQL_ID 5560q864ux10v, child number 0
-------------------------------------
SELECT e.first_name, e.last_name, e.salary FROM employees e WHERE
e.department_id = 50
Plan hash value: 1445457117
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 45 |00:00:00.01 | 10 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 45 | 990 | 3 (0)| 00:00:01 | 45 |00:00:00.01 | 10 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."DEPARTMENT_ID"=50)
19 rows selected.
SQL>
SQL> SELECT e.first_name, e.last_name, e.salary
2 FROM employees e, parameters p
3 WHERE e.department_id = p.department_id;
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Donald OConnell 2600
Douglas Grant 2600
Matthew Weiss 8000
Adam Fripp 8200
Payam Kaufling 7900
Shanta Vollman 6500
Kevin Mourgos 5800
Julia Nayer 3200
Irene Mikkilineni 2700
James Landry 2400
Steven Markle 2200
Laura Bissot 3300
Mozhe Atkinson 2800
James Marlow 2500
TJ Olson 2100
Jason Mallin 3300
Michael Rogers 2900
Ki Gee 2400
Hazel Philtanker 2200
Renske Ladwig 3600
Stephen Stiles 3200
John Seo 2700
Joshua Patel 2500
Trenna Rajs 3500
Curtis Davies 3100
Randall Matos 2600
Peter Vargas 2500
Winston Taylor 3200
Jean Fleaur 3100
Martha Sullivan 2500
Girard Geoni 2800
Nandita Sarchand 4200
Alexis Bull 4100
Julia Dellinger 3400
Anthony Cabrio 3000
Kelly Chung 3800
Jennifer Dilly 3600
Timothy Gates 2900
Randall Perkins 2500
Sarah Bell 4000
Britney Everett 3900
Samuel McCain 3200
Vance Jones 2800
Alana Walsh 3100
Kevin Feeney 3000
45 rows selected.
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'TYPICAL IOSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
SQL_ID fqqduywc343w9, child number 0
-------------------------------------
SELECT e.first_name, e.last_name, e.salary FROM employees e, parameters
p WHERE e.department_id = p.department_id
Plan hash value: 3418166505
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 45 |00:00:00.01 | 16 |
| 1 | NESTED LOOPS | | 1 | | | | | 45 |00:00:00.01 | 16 |
| 2 | NESTED LOOPS | | 1 | 10 | 250 | 4 (0)| 00:00:01 | 45 |00:00:00.01 | 11 |
| 3 | TABLE ACCESS FULL | PARAMETERS | 1 | 1 | 3 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 7 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | | 0 (0)| | 45 |00:00:00.01 | 4 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 45 | 10 | 220 | 1 (0)| 00:00:01 | 45 |00:00:00.01 | 5 |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="P"."DEPARTMENT_ID")
23 rows selected.
So Many Oracle Manuals, So Little Time 
Recent Comments