被驱动表 拼接列无法走索引

create table t100(id int,name varchar(10));


create table t200(id int,name varchar(10));


begin 
  for i in  1..10
    loop
      insert into t100 values(i,i||'a');
    end loop;
    end;


begin 
  for i in  1..100000
    loop
      insert into t200 values(i,i);
    end loop;
    end;

        insert into t100 values(9999999,9999999||'a');


	SQL> explain plan for select name from t100 where name in (select name||'a' from t200);

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4167129566

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    14 |    65   (2)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |     1 |    14 |    65   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T100 |    11 |    77 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T200 | 90964 |   621K|    63   (2)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("NAME"="NAME"||'a')

Note
-----
   - dynamic sampling used for this statement (level=2)

已选择19行。

create index t200_idx1 on t200(name);

BEGIN  
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SYSTEM',  
                                tabname          => 'T200',  
                                estimate_percent => 30,  
                                method_opt       => 'for all columns size repeat',  
                                no_invalidate    => FALSE,  
                                degree           => 8,  
                                cascade          => TRUE);  
END;  
/  


SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4167129566

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    10 |    65   (2)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |     1 |    10 |    65   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T100 |    11 |    44 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T200 | 99803 |   584K|    63   (2)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("NAME"="NAME"||'a')

已选择15行。

没法走索引:


create index t200_idx2 on t200(name||'a');

SQL> explain plan for select name from t100 where name in (select name||'a' from t200);

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 155094578

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    11 |   121 |    13   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |           |    11 |   121 |    13   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T100      |    11 |    44 |     2   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | T200_IDX2 | 99803 |   682K|     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("NAME"="NAME"||'a')

已选择15行。