使用hash join 优化 sql

低效：

SELECT a.*,
       b.object_name
  FROM TEST_OBJECT a,
       TEST_OBJ1   b
 WHERE SUBSTR(a.object_name, 1, LENGTH(b.object_name)) = b.object_name;

実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=26 Card=67 Bytes=2077)
1 0 NESTED LOOPS (Cost=26 Card=67 Bytes=2077)
2 1 TABLE ACCESS (FULL) OF 'TEST_OBJECT' (TABLE) (Cost=2 Card=82 Bytes=1558)
3 1 TABLE ACCESS (FULL) OF 'TEST_OBJ1' (TABLE) (Cost=0 Card=1 Bytes=12)

高效：

SELECT a.*,
       b.object_name
  FROM TEST_OBJECT a,
       TEST_OBJ1   b
 WHERE 
   SUBSTR(a.object_name, 1, LENGTH(b.object_name)) = b.object_name
   SUBSTR(a.object_name, 1, 4) = SUBSTR(b.object_name, 1, 4)--hash join
   AND LENGTH(b.object_name) > 3
UNION ALL
SELECT a.*,
       b.object_name
  FROM TEST_OBJECT a,
       TEST_OBJ1   b
 WHERE SUBSTR(a.object_name, 1, LENGTH(b.object_name)) = b.object_name
   AND LENGTH(b.object_name) < 4;

実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=4 Bytes=124)
1 0 UNION-ALL
2 1 HASH JOIN (Cost=5 Card=1 Bytes=31)
3 2 TABLE ACCESS (FULL) OF 'TEST_OBJ1' (TABLE) (Cost=2 Card=4 Bytes=48)
4 2 TABLE ACCESS (FULL) OF 'TEST_OBJECT' (TABLE) (Cost=2 Card=82 Bytes=1558)
5 1 NESTED LOOPS (Cost=5 Card=3 Bytes=93)
6 5 TABLE ACCESS (FULL) OF 'TEST_OBJ1' (TABLE) (Cost=2 Card=4 Bytes=48)
7 5 TABLE ACCESS (FULL) OF 'TEST_OBJECT' (TABLE) (Cost=1 Card=1 Bytes=19)