使用hash join 优化 sql
低效：SELECT a.*, b.object_nameFROM 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_nameFROM 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_nameFROM 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)
页:
[1]