[20180507]FBI Limitation.txt
--//重复测试:https://jonathanlewis.wordpress.com/2018/05/04/fbi-limitation/
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;
--//不行.不知道有什么方法使用函数索引...
