使用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)