[20180507]FBI Limitation.txt

[20180507]FBI Limitation.txt

--//重複測試:

1.環境:

SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.建立測試資料:
CREATE  PUBLIC DATABASE LINK LOOPBACK USING 'localhost:1521/book:DEDICATED';

create table t1
segment creation immediate
nologging
as
with generator as (
        select
                rownum id
        from dual
        connect by
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        rownum                          id,
        rownum                          n1,
        lpad(rownum,10,'0')             v1,
        lpad('x',100,'x')               padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e6 -- > comment to avoid WordPress format issue
;
 
create table t2
nologging
as
select * from t1
;
 
alter table t1 add constraint t1_pk primary key(id);
alter table t2 add constraint t2_pk primary key(id);
create unique index t2_f1 on t2(id+1);
 
begin
        dbms_stats.gather_table_stats(
                ownname     => user,
                tabname     => 'T1',
                cascade     => true,
                method_opt  => 'for all columns size 1'
        );
 
        dbms_stats.gather_table_stats(
                ownname     => user,
                tabname     => 'T2',
                cascade     => true,
                method_opt  => 'for all columns size 1'
        );
end;
/

3.測試:

set serveroutput off
 
select
        t1.v1, t2.v1
from
        t1,
        t2
--      t2@orcl@loopback
where
        t2.id+1 = t1.id
and     t1.n1 between 101 and 110
;
 
 
select * from table(dbms_xplan.display_cursor);

--//執行計劃如下:
Plan hash value: 1798294492
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |        |       |  4915 (100)|          |
|   1 |  NESTED LOOPS                |       |     11 |   407 |  4915   (1)| 00:00:59 |
|*  2 |   TABLE ACCESS FULL          | T1    |     11 |   231 |  4893   (1)| 00:00:59 |
|   3 |   TABLE ACCESS BY INDEX ROWID| T2    |      1 |    16 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | T2_F1 |      1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T1@SEL$1
   3 - SEL$1 / T2@SEL$1
   4 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("T1"."N1"<=110 AND "T1"."N1">=101))
   4 - access("T2"."SYS_NC00005$"="T1"."ID")

--//透過dblink呢?
select
        t1.v1, t2.v1
from
        t1,
--      t2
        t2@loopback
where
        t2.id+1 = t1.id
and     t1.n1 between 101 and 110
;
 
select * from table(dbms_xplan.display_cursor);

--//執行計劃如下:
Plan hash value: 1770389500
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Inst   |IN-OUT|  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |  8029 (100)|          |        |      |       |       |          |
|*  1 |  HASH JOIN         |      |     11 |   506 |  8029   (1)| 00:01:37 |        |      |  1483K|  1483K| 1277K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |     11 |   231 |  4893   (1)| 00:00:59 |        |      |       |       |          |
|   3 |   REMOTE           | T2   |   1000K|    23M|  3133   (1)| 00:00:38 | LOOPB~ | R->S |       |       |          |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T1@SEL$1
   3 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID"+1)
   2 - filter(("T1"."N1"<=110 AND "T1"."N1">=101))
Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT "ID","V1" FROM "T2" "T2" (accessing 'LOOPBACK' )

--//可以發現無法使用函式索引.

4.如何保證能使用函式索引呢?
BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
      OwnName        => 'SCOTT'
     ,TabName        => 'T2'
    ,Estimate_Percent  => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
    ,Method_Opt        => 'FOR ALL HIDDEN COLUMNS SIZE REPEAT '
    ,Degree            => 4
    ,Cascade           => TRUE
    ,No_Invalidate     => FALSE
    ,Force             => TRUE);
END;
/

select t1.v1, t2.v1 from t1,t2@loopback where t2.id+1 = t1.id and t1.n1 between 101 and 110;
--//不行.不知道有什麼方法使用函式索引...