默认用OR，走两次HASH JOIN：
SELECT *
FROM a,b
WHERE (LENGTH(a.object_name)=2 AND a.object_name=SUBSTR(b.object_name,1,2))
OR (LENGTH(a.object_name)=3 AND a.object_name=SUBSTR(b.object_name,1,3));
Elapsed: 00:00:00.13
Execution Plan
----------------------------------------------------------
Plan hash value: 2423518281
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2542 | 476K| 1309 (1)| 00:00:16 |
| 1 | CONCATENATION | | | | | |
|* 2 | HASH JOIN | | 1271 | 238K| 654 (1)| 00:00:08 |
|* 3 | TABLE ACCESS FULL| A | 826 | 79296 | 326 (1)| 00:00:04 |
| 4 | TABLE ACCESS FULL| B | 82580 | 7741K| 327 (1)| 00:00:04 |
|* 5 | HASH JOIN | | 1271 | 238K| 654 (1)| 00:00:08 |
|* 6 | TABLE ACCESS FULL| A | 826 | 79296 | 326 (1)| 00:00:04 |
| 7 | TABLE ACCESS FULL| B | 82580 | 7741K| 327 (1)| 00:00:04 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"=SUBSTR("B"."OBJECT_NAME",1,3))
3 - filter(LENGTH("A"."OBJECT_NAME")=3)
5 - access("A"."OBJECT_NAME"=SUBSTR("B"."OBJECT_NAME",1,2))
filter(LNNVL(LENGTH("A"."OBJECT_NAME")=3) OR
LNNVL("A"."OBJECT_NAME"=SUBSTR("B"."OBJECT_NAME",1,3)))
6 - filter(LENGTH("A"."OBJECT_NAME")=2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4844 consistent gets
0 physical reads
0 redo size
204080 bytes sent via SQL*Net to client
2423 bytes received via SQL*Net from client
175 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2609 rows processed
不用OR，但是走不了HASH JOIN，更差：
dingjun123@ORCL> SELECT *
2 FROM a,b
3 WHERE a.object_name=SUBSTR(b.object_name,1,LENGTH(a.object_name))
4 AND length(a.object_name) IN (2,3);
2609 rows selected.
Elapsed: 00:00:01.31
Execution Plan
----------------------------------------------------------
Plan hash value: 4030965610
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2528 | 474K| 535K (1)| 01:47:05 |
| 1 | NESTED LOOPS | | 2528 | 474K| 535K (1)| 01:47:05 |
|* 2 | TABLE ACCESS FULL| A | 1643 | 154K| 327 (1)| 00:00:04 |
|* 3 | TABLE ACCESS FULL| B | 2 | 192 | 326 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(LENGTH("A"."OBJECT_NAME")=2 OR
LENGTH("A"."OBJECT_NAME")=3)
3 - filter("A"."OBJECT_NAME"=SUBSTR("B"."OBJECT_NAME",1,LENGTH("A"."O
BJECT_NAME")))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
72623 consistent gets
0 physical reads
0 redo size
163393 bytes sent via SQL*Net to client
2423 bytes received via SQL*Net from client
175 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2609 rows processed
--根据数据分布，可以建立合适的索引
CREATE INDEX idx_a ON a(LENGTH(object_name));
SELECT *
FROM a,b
WHERE (LENGTH(a.object_name)=2 AND a.object_name=SUBSTR(b.object_name,1,2))
UNION ALL
SELECT *
FROM a,b
WHERE (LENGTH(a.object_name)=3 AND a.object_name=SUBSTR(b.object_name,1,3));
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 250111326
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2542 | 476K| 772 (51)| 00:00:10 |
| 1 | UNION-ALL | | | | | |
|* 2 | HASH JOIN | | 1271 | 238K| 386 (2)| 00:00:05 |
| 3 | TABLE ACCESS BY INDEX ROWID| A | 826 | 79296 | 58 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_A | 330 | | 5 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | B | 82580 | 7741K| 327 (1)| 00:00:04 |
|* 6 | HASH JOIN | | 1271 | 238K| 386 (2)| 00:00:05 |
| 7 | TABLE ACCESS BY INDEX ROWID| A | 826 | 79296 | 58 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_A | 330 | | 5 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | B | 82580 | 7741K| 327 (1)| 00:00:04 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"=SUBSTR("OBJECT_NAME",1,2))
4 - access(LENGTH("OBJECT_NAME")=2)
6 - access("A"."OBJECT_NAME"=SUBSTR("OBJECT_NAME",1,3))
8 - access(LENGTH("OBJECT_NAME")=3)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
817 consistent gets
0 physical reads
0 redo size
163536 bytes sent via SQL*Net to client
2423 bytes received via SQL*Net from client
175 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2609 rows processed