DBA Hub

📋Steps in this guide1/5

Join Elimination

This article describes how join elimination can improve performance of queries by removing tables from the plan.

oracle miscconfigurationintermediate
by OracleDba
14 views
1

Setup

Create the schema objects for a parent-child (master-detail) relationship. Notice the foreign key (FK) between the column and the column. Because of this we know that for the record to be present the record *must* exist. This gives the optimizer information to make some clever decisions. Assuming there are no additional filters, a join from the table to the table will never reduce the number of rows in the resultset, so if no columns from the table are referenced, Oracle can remove that table using join elimination.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE TABLE parent (
  id          NUMBER NOT NULL,
  description VARCHAR2(50) NOT NULL,
  CONSTRAINT parent_pk PRIMARY KEY (id)
);

INSERT INTO parent VALUES (1, 'PARENT ONE');
INSERT INTO parent VALUES (2, 'PARENT TWO');
COMMIT;

CREATE TABLE child (
  id          NUMBER NOT NULL,
  parent_id   NUMBER NOT NULL,
  description VARCHAR2(50) NOT NULL,
  CONSTRAINT child_pk PRIMARY KEY (id),
  CONSTRAINT child_parent_fk FOREIGN KEY (parent_id) REFERENCES parent(id)
);

CREATE INDEX child_parent_fk_idx ON child(parent_id);

INSERT INTO child VALUES (1, 1, 'CHILD ONE');
INSERT INTO child VALUES (2, 1, 'CHILD ONE');
INSERT INTO child VALUES (3, 2, 'CHILD TWO');
INSERT INTO child VALUES (4, 2, 'CHILD TWO');
COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'PARENT');
EXEC DBMS_STATS.gather_table_stats(USER, 'CHILD');
2

Basic Join Elimination

The following query performs a join between the and tables, but only references columns from the table in the select list. Notice also, there are no filters are on any columns in the table. The execution plan shows the optimizer was able to safely eliminate the join to the table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
SET AUTOTRACE TRACEONLY EXPLAIN

SELECT c.id, c.parent_id, c.description
FROM   child c
       JOIN parent p ON c.parent_id = p.id;

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     4 |    64 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| CHILD |     4 |    64 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
3

Join Elimination in Views

Obviously, it's a bit stupid to join tables that aren't being used, but sometimes you might have views that include tables that in some contexts are not needed. For example, let's create a view based on the tables used in the last query. As long as we don't reference columns in the parent table in the select list or in the cause as filters, the parent table can be eliminated. This is possible because of view merging. The contents of the view are merged into the query block that contains it, allowing subsequent optimizations like join elimination to happen.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE VIEW parent_child_v AS
  SELECT p.id AS parent_id,
         p.description AS parent_description,
         c.id AS child_id,
         c.parent_id AS child_parent_id,
         c.description AS child_description
  FROM   child c
  JOIN   parent p ON c.parent_id = p.id;

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT child_id,
       child_parent_id,
       child_description
FROM   parent_child_v;

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     4 |    64 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| CHILD |     4 |    64 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
4

Additional Transformation to Allow Join Elimination

As soon as we reference the columns in the table, we have to perform the join. This is not always the case though. Look what happens if we query the column, which is sourced from the column. We can see this even more clearly if we query the tables directly. How can this be? Because of the foreign key between the and the columns, Oracle knows that any reference to can actually be replaced by a reference to . In both the above cases, Oracle transformed the query to use the column from the table, allowing the table to be removed by join elimination.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
SET AUTOTRACE TRACEONLY EXPLAIN

SELECT parent_description,
       child_id,
       child_parent_id,
       child_description
FROM   parent_child_v;

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     4 |   120 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                     |       |       |            |          |
|   2 |   NESTED LOOPS               |                     |     4 |   120 |     5   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | PARENT              |     2 |    28 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | CHILD_PARENT_FK_IDX |     2 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| CHILD               |     2 |    32 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT parent_id,
       child_id,
       child_parent_id,
       child_description
FROM   parent_child_v;

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     4 |    64 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| CHILD |     4 |    64 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT p.id AS p_id, c.id, c.parent_id, c.description
FROM   child c
       JOIN parent p ON c.parent_id = p.id;

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     4 |    64 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| CHILD |     4 |    64 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
5

Breaking Join Elimination

It is very easy to prevent join elimination by not giving the optimizer enough information. Simply removing the foreign key removes the posibility of join elimination. With the constraint gone, the optimizer no longer knows about the special relationship between the and columns. Indeed, there is nothing on the database to make sure the relationship is maintained, so even if our application enforces the behaviour, the optimizer can not take advantage of it. For more information see: - Query Transformations - Join Elimination in Oracle Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
ALTER TABLE child DROP CONSTRAINT child_parent_fk;

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT parent_id,
       child_id,
       child_parent_id,
       child_description
FROM   parent_child_v;

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     4 |    76 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                     |       |       |            |          |
|   2 |   NESTED LOOPS               |                     |     4 |    76 |     3   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PARENT_PK           |     2 |     6 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | CHILD_PARENT_FK_IDX |     2 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| CHILD               |     2 |    32 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Comments (0)

Please to add comments

No comments yet. Be the first to comment!