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 Connor 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.
Is this answer out of date? If it is, please let us know via a Comment