Skip to Main Content
  • Questions
  • Performance literal/bind variables vs JOIN CLAUSE

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Marco.

Asked: July 24, 2020 - 10:20 pm UTC

Answered by: Connor McDonald - Last updated: July 27, 2020 - 3:04 am UTC

Category: SQL - Version: Oracle 12g

Viewed 100+ times

You Asked

Hello!
my client's DBA states that Oracle works better when instead of setting, in the where clause, literals or bind variables is explicit in the condition the reference to a JOIN column.
please forgive my english I hope the simple example attached is clearer

it's true that QUERY 2 is better for Oracle?
is there any literature about it?
if it is true to what extent does the performance improvement occur?
What evidence can I find, for example, in the trace file?




CREATE TABLE TABLE1
(
    PKCOL1    NUMBER NOT NULL,
    COL2      VARCHAR2 (10),
    COL3      VARCHAR2 (10)
);

ALTER TABLE TABLE1
    ADD (CONSTRAINT TABLE1_PK PRIMARY KEY (PKCOL1) ENABLE VALIDATE);

CREATE TABLE TABLE2
(
    PKCOL1    NUMBER NOT NULL,
    COL2      VARCHAR2 (10),
    COL3      VARCHAR2 (10)
);

ALTER TABLE TABLE2
    ADD (CONSTRAINT TABLE2_PK PRIMARY KEY (PKCOL1) ENABLE VALIDATE);

BEGIN
    FOR I IN 1 .. 100000
    LOOP
        INSERT INTO TABLE1
             VALUES (i, 'A' || I, 'B' || I);

        INSERT INTO TABLE2
             VALUES (i, 'A' || I, 'B' || I);
    END LOOP;

    COMMIT;
END;
/

--QUERY 1

SELECT A.PKCOL1, A.COL2, A.COL3,
B.PKCOL1, B.COL2, B.COL3
FROM TABLE1 A, TABLE2 B
WHERE   
A.PKCOL1 = 200
AND B.PKCOL1 = 200;

--QUERY 2

SELECT A.PKCOL1, A.COL2, A.COL3,
B.PKCOL1, B.COL2, B.COL3
FROM TABLE1 A, TABLE2 B
WHERE   
A.PKCOL1 = 200
AND B.PKCOL1 = A.PKCOL1;


with LiveSQL Test Case:

and we said...

For a join on two 1-to-1 tables both on primary key, there will be no difference. In particular, you can see that the database can infer the second literal


SQL> CREATE TABLE TABLE1
2 (
3 PKCOL1 NUMBER NOT NULL,
4 COL2 VARCHAR2 (10),
5 COL3 VARCHAR2 (10)
6 );

Table created.

SQL>
SQL>
SQL> CREATE TABLE TABLE2
2 (
3 PKCOL1 NUMBER NOT NULL,
4 COL2 VARCHAR2 (10),
5 COL3 VARCHAR2 (10)
6 );

Table created.

SQL>
SQL> insert into TABLE1
2 select rownum, 'A' || rownum, 'B' || rownum
3 from dual
4 connect by level <= 100000;

100000 rows created.

SQL>
SQL> insert into TABLE2
2 select * from table1;

100000 rows created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','table1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('','table2');

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> ALTER TABLE TABLE2
2 ADD (CONSTRAINT TABLE2_PK PRIMARY KEY (PKCOL1) ENABLE VALIDATE);

Table altered.

SQL>
SQL> ALTER TABLE TABLE1
2 ADD (CONSTRAINT TABLE1_PK PRIMARY KEY (PKCOL1) ENABLE VALIDATE);

Table altered.

SQL>
SQL> set autotrace on
SQL> SELECT A.PKCOL1, A.COL2, A.COL3,
2 B.PKCOL1, B.COL2, B.COL3
3 FROM TABLE1 A, TABLE2 B
4 WHERE
5 A.PKCOL1 = 200
6 AND B.PKCOL1 = 200;

PKCOL1 COL2 COL3 PKCOL1 COL2 COL3
---------- ---------- ---------- ---------- ---------- ----------
200 A200 B200 200 A200 B200

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1636473010

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 38 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TABLE2 | 1 | 19 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | TABLE2_PK | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 1 | 19 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | TABLE1_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("B"."PKCOL1"=200)
5 - access("A"."PKCOL1"=200)


SQL> SELECT A.PKCOL1, A.COL2, A.COL3,
2 B.PKCOL1, B.COL2, B.COL3
3 FROM TABLE1 A, TABLE2 B
4 WHERE
5 A.PKCOL1 = 200
6 AND B.PKCOL1 = A.PKCOL1;

PKCOL1 COL2 COL3 PKCOL1 COL2 COL3
---------- ---------- ---------- ---------- ---------- ----------
200 A200 B200 200 A200 B200

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1636473010

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 38 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TABLE2 | 1 | 19 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | TABLE2_PK | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 1 | 19 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | TABLE1_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("B"."PKCOL1"=200) <<====
5 - access("A"."PKCOL1"=200) <<====

SQL>

For me, unless a performance issue forces me to do otherwise, I write my SQL to respect the business/functional requirement that sits underneath it.

I want the next person who has to maintain my code have the best possible chance at understanding the data model and the motivation behind why the query was phrased in the way it was.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.