create table t1(id number,name nvarchar2(200));
create table t2(id number,name varchar2(200));
insert into t1 select rownum,table_name from dba_tables;
insert into t2 select rownum,object_name from dba_objects;
commit;
SQL> select * from t1,t2 where t1.name=t2.name and rownum<=10;
已选择10行。
执行计划
----------------------------------------------------------
Plan hash value: 808789222
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 4450 | 10 (10)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | HASH JOIN | | 72191 | 30M| 10 (10)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 2780 | 583K| 7 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 72009 | 8086K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
2 - access("T1"."NAME"=SYS_OP_C2C("T2"."NAME"))
create table t2(id number,name varchar2(200));
insert into t1 select rownum,table_name from dba_tables;
insert into t2 select rownum,object_name from dba_objects;
commit;
SQL> select * from t1,t2 where t1.name=t2.name and rownum<=10;
已选择10行。
执行计划
----------------------------------------------------------
Plan hash value: 808789222
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 4450 | 10 (10)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | HASH JOIN | | 72191 | 30M| 10 (10)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 2780 | 583K| 7 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 72009 | 8086K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
2 - access("T1"."NAME"=SYS_OP_C2C("T2"."NAME"))
解析:
表关联的时候,会自动的加上 SYS_OP_C2C 函数,想要t2表join列走索引,需要建立函数索引
SQL:create index idx_t2 on t2(sys_op_c2c(name));
注意:表设计的时候,如果有2个表关联,关联列 要么全都是NVARCHAR2,要么全都是VARCHAR2,需要相同不要一个表一个样。。