因此,这是一个仅在连接的字符串上进行连接的查询的解释计划:
SQL> explain plan for
2 select e.* from emp e
3 join big_table bt on bt.col2 = 'search'||trim(to_char(e.empno))
4 /
Explained.
SQL> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
Plan hash value: 179424166
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1052 | 65224 | 43 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1052 | 65224 | 43 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 20 | 780 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | BIG_VC_I | 53 | 1219 | 2 (0)| 00:00:01 |
Predicate Information (identified by operation id):
3 - access("BT"."COL2"='search'||TRIM(TO_CHAR("E"."EMPNO")))
15 rows selected.
SQL>
将查询计划中包含LIKE子句的查询与本计划进行比较和对比:
SQL> explain plan for
2 select e.* from emp e
3 join big_table bt on (bt.col2 like 'search%'
4 and bt.col2 = 'search'||trim(to_char(e.empno)))
5 /
Explained.
SQL> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
Plan hash value: 179424166
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 62 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 62 | 5 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 39 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | BIG_VC_I | 1 | 23 | 2 (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - filter('search'||TRIM(TO_CHAR("E"."EMPNO")) LIKE 'search%')
3 - access("BT"."COL2"='search'||TRIM(TO_CHAR("E"."EMPNO")))
filter("BT"."COL2" LIKE 'search%')
17 rows selected.
SQL>
第二个查询的成本比第一个要低得多。但这是因为优化器估计第二个查询返回的行数比第一个要少得多。更多的信息可以使数据库做出更准确的预测。(事实上,查询不会返回任何行)。
当然,这需要假设连接列已经建立索引,否则不会有任何区别。
另一件需要注意的事情是被查询的列可能会影响执行计划。这个版本从BIG_TABLE而不是EMP中选择。
SQL> explain plan for
2 select bt.* from emp e
3 join big_table bt on (bt.col2 like 'search%'
4 and bt.col2 = 'search'||trim(to_char(e.empno)))
5 /
Explained.
SQL> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
Plan hash value: 4042413806
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 46 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 46 | 4 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN | PK_EMP | 1 | 4 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | BIG_VC_I | 1 | | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 1 | 42 | 3 (0)| 00:00:01 |
Predicate Information (identified by operation id):
3 - filter('search'||TRIM(TO_CHAR("E"."EMPNO")) LIKE 'search%')
4 - access("BT"."COL2"='search'||TRIM(TO_CHAR("E"."EMPNO")))
filter("BT"."COL2" LIKE 'search%')
19 rows selected.
SQL>
t1.someID = ( 'search.', t0.ID )
。在我工作过的大多数数据库中,它都是无效的。你可以使用t1.someID In( 'search.', t0.ID )
,但我从未见过一个数据库可以直接将列设置为元组相等。 - Thomas