referencing the table name twice in the query
Hi all,
Our application in its legacy stage was using MS sql server. When the proceduress were changed to suit to Oracle, there may have been some inefficient statements left, which would have been working great in sql server. One such common occurance, was found to be a variant of the following sql:
INSERT INTO T#CORPRANGE ( DCORP1,DCORP2 ) SELECT C.DCORP,D.DCORP FROM LYRANGEENTITY A,LYRANGE B,LYENTITY C,LYENTITY D WHERE B.RANGEKEY = A.RANGEKEY AND B.RANGENAME = :b1 AND :b1 IS NOT NULL AND B.ENTERPRISEKEY = :b3 AND C.ENTITYKEY = A.ENTITYKEY_1 AND D.ENTITYKEY = A.ENTITYKEY_2
In absence of any self-joins, I asked the developers why the need for referencing LYENTITY twice. I was told that in sql server this is a necessity and the same thing has been retained in oracle too. I dont know ablut sql server but when I tried this statement with and without referencing the LYENTITY as C and D, I got the following results ( I had to include some hard values in place of variables):
*********************************************************************
Case 1. Referencing only once
SQL> set autotrace on exp stat
SQL> INSERT INTO T#CORPRANGE ( DCORP1,DCORP2 )
2 SELECT C.DCORP,C.DCORP FROM LYRANGEENTITY A,LYRANGE B,LYENTITY C
3 WHERE B.RANGEKEY = A.RANGEKEY AND B.RANGENAME = 'RSCONS' AND
4 'RSCONS' IS NOT NULL AND B.ENTERPRISEKEY = 82 AND C.ENTITYKEY = A.ENTITYKEY_1
5 AND C.ENTITYKEY = A.ENTITYKEY_2;
21 rows created.
Elapsed: 00:00:02.00
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=43)
1 0 HASH JOIN (Cost=6 Card=1 Bytes=43)
2 1 TABLE ACCESS (FULL) OF 'LYRANGEENTITY' (Cost=1 Card=864
Bytes=10368)
3 1 NESTED LOOPS (Cost=4 Card=963 Bytes=29853)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'LYRANGE' (Cost=1 Car
d=1 Bytes=16)
5 4 INDEX (UNIQUE SCAN) OF 'UKLYRANGE1' (UNIQUE)
6 3 TABLE ACCESS (FULL) OF 'LYENTITY' (Cost=3 Card=963 Byt
es=14445)
Statistics
----------------------------------------------------------
1474 recursive calls
17 db block gets
439 consistent gets
0 physical reads
404 redo size
848 bytes sent via SQL*Net to client
804 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
26 sorts (memory)
0 sorts (disk)
21 rows processed
********************************************************************
Case 2. Referencing twice
SQL> INSERT INTO T#CORPRANGE ( DCORP1,DCORP2 )
2 SELECT C.DCORP,D.DCORP FROM LYRANGEENTITY A,LYRANGE B,LYENTITY C,LYENTITY D
3 WHERE B.RANGEKEY = A.RANGEKEY AND B.RANGENAME = 'RSCONS'
4 AND 'RSCONS' IS NOT NULL AND B.ENTERPRISEKEY = 82
5 AND C.ENTITYKEY = A.ENTITYKEY_1 AND D.ENTITYKEY = A.ENTITYKEY_2;
21 rows created.
Elapsed: 00:00:01.53
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=12 Card=6 Bytes=348)
1 0 NESTED LOOPS (Cost=12 Card=6 Bytes=348)
2 1 HASH JOIN (Cost=6 Card=6 Bytes=258)
3 2 TABLE ACCESS (FULL) OF 'LYRANGEENTITY' (Cost=1 Card=86
4 Bytes=10368)
4 2 NESTED LOOPS (Cost=4 Card=963 Bytes=29853)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'LYRANGE' (Cost=1 C
ard=1 Bytes=16)
6 5 INDEX (UNIQUE SCAN) OF 'UKLYRANGE1' (UNIQUE)
7 4 TABLE ACCESS (FULL) OF 'LYENTITY' (Cost=3 Card=963 B
ytes=14445)
8 1 TABLE ACCESS (BY INDEX ROWID) OF 'LYENTITY' (Cost=1 Card
=963 Bytes=14445)
9 8 INDEX (UNIQUE SCAN) OF 'PKLYENTITY' (UNIQUE)
Statistics
----------------------------------------------------------
1277 recursive calls
17 db block gets
434 consistent gets
0 physical reads
404 redo size
847 bytes sent via SQL*Net to client
815 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
27 sorts (memory)
0 sorts (disk)
21 rows processed
SQL>
*************************************************************
The timing was less in case 2, but the execution plan suggested that more work is being done. Also I tried another generic test:
09:49:45 SQL> select cse,amt from ctxamt where rownum<5;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1379 Card=318249 Byt
es=1272996)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'CTXAMT' (Cost=1379 Card=318249 B
ytes=1272996)
Statistics
----------------------------------------------------------
458 recursive calls
4 db block gets
181 consistent gets
8 physical reads
0 redo size
554 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processed
09:51:09 SQL> select a.cse,b.amt from ctxamt a, ctxamt b where rownum<5;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=182358056 Card=10128
2426001 Bytes=405129704004)
1 0 COUNT (STOPKEY)
2 1 MERGE JOIN (CARTESIAN) (Cost=182358056 Card=101282426001
Bytes=405129704004)
3 2 TABLE ACCESS (FULL) OF 'CTXAMT' (Cost=1379 Card=318249
Bytes=636498)
4 2 SORT (JOIN) (Cost=182356677 Card=318249 Bytes=636498)
5 4 INDEX (FAST FULL SCAN) OF 'UKCTXAMT1' (UNIQUE) (Cost
=573 Card=318249 Bytes=636498)
Statistics
----------------------------------------------------------
0 recursive calls
56 db block gets
3824 consistent gets
3828 physical reads
0 redo size
533 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
1 sorts (disk)
4 rows processed
**********************************************************************
See the difference in the consistent gets and physical reads?
My question is, is it really a bad practice to use the tablenames twice when there are no self-joins? Or, are there some circumstances when it is required, or that it does not simply matter, one way or the other? I am sure it is a costly practice to use the tablenames twice, but would like to confirm from the others who might have come across such statements.
Our application is full of such occurances, and I would like to make a start on correcting these.
Thanks
Manjunath