Home > DBA, Oracle, SQL > A SQL Minute: Using a Table To Provide Query Parameters (Query AntiPatterns)

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.
Categories: DBA, Oracle, SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: