默认用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