让有instr的join改走hash

来源：互联网发布：ray kurzweil 骗知乎编辑：程序博客网时间：2024/05/01 12:59

一QQ群友语句中有条件ON INSTR(B.SCODE,SUBSTR(LTRIM(A.SYMBOL),1,1)) > 0

plan无法走hash

经象群友确认SUBSTR(LTRIM(A.SYMBOL),1,1)) 仅在SCODE中出现一次，于是想到用拆分字符串的方式更改

构建示例如下

DROP TABLE TEST1 PURGE;DROP TABLE test2 PURGE;CREATE TABLE test1 AS SELECT * FROM dba_objects;CREATE TABLE test2 ASSELECT DISTINCT reverse(max(SUBSTR(object_type,1,2))) AS ID,max(object_type) AS object_type FROM TEST1GROUP BY SUBSTR(object_type,1,1);

原方式

SELECT A.OBJECT_NAME, B.OBJECT_TYPE, B.ID  FROM TEST1 A INNER JOIN TEST2 B    ON INSTR(B.ID, SUBSTR(LTRIM(A.OBJECT_TYPE), 1, 1)) > 0;----------------------------------------------------------------------------| Id  | Operation   | Name  | Rows  | Bytes | Cost (%CPU)| Time   |----------------------------------------------------------------------------|   0 | SELECT STATEMENT   |   | 66852 |  6006K|  5255   (2)| 00:01:04 ||   1 |  NESTED LOOPS   |   | 66852 |  6006K|  5255   (2)| 00:01:04 ||   2 |   TABLE ACCESS FULL| TEST2 |18 |   270 | 3   (0)| 00:00:01 ||*  3 |   TABLE ACCESS FULL| TEST1 |  3714 |   279K|   292   (2)| 00:00:04 |----------------------------------------------------------------------------

改后语句如下

WITH B AS (SELECT SUBSTR(B.ID, LEVEL, 1) AS ID, B.OBJECT_TYPE    FROM TEST2 B  CONNECT BY PRIOR ID = ID         AND LEVEL <= LENGTH(ID)         AND PRIOR DBMS_RANDOM.VALUE() IS NOT NULL)SELECT *  FROM TEST1 A INNER JOIN B    ON SUBSTR(LTRIM(A.OBJECT_TYPE), 1, 1) = B.ID;----------------------------------------------------------------------------------------| Id  | Operation       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT       |       | 13370 |  2885K|   299 (3)| 00:00:04 ||*  1 |  HASH JOIN       |       | 13370 |  2885K|   299 (3)| 00:00:04 ||   2 |   VIEW       |       |    18 |   252 |     3 (0)| 00:00:01 ||*  3 |    CONNECT BY WITHOUT FILTERING|       |       |       |    |       ||   4 |     TABLE ACCESS FULL       | TEST2 |    18 |   270 |     3 (0)| 00:00:01 ||   5 |   TABLE ACCESS FULL       | TEST1 | 74280 |    14M|   295 (2)| 00:00:04 |----------------------------------------------------------------------------------------