使用hash join 优化 sql

hudingchen

低效：
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) > 3UNION ALLSELECT 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)