过程如下

-----
select * from kpi_zone_employee
where user_no=1069483867

查询user_no=1069483867 是没有数据
----------分析表kpi_zone_cell前
select g.user_no,count(*)cs from cdr200706@gsmmis g,kpi_zone_cell c
where ltrim(g.cell_id,'0')=c.cell_id
and c.zone_name like'%06%' and
not exists(select * from kpi_zone_employee where user_no=g.user_no)
and user_no=1069483867
group by user_no

此时有数据
user_no cs
1069483867 44
---------分析表kpi_zone_cell
begin
dbms_stats.gather_table_stats(OWNNAME=>'localdb',
TABNAME=>'kpi_zone_cell',
ESTIMATE_PERCENT=>100,
METHOD_OPT=>'FOR ALL INDEXED COLUMNS');
end;
---------查询
select g.user_no,count(*)cs from cdr200706@gsmmis g,kpi_zone_cell c
where ltrim(g.cell_id,'0')=c.cell_id
and c.zone_name like'%06%' and
not exists(select * from kpi_zone_employee where user_no=g.user_no)
and user_no=1069483867
group by user_no

此时没有数据
user_no cs
---------查询 (去掉 not exists 条件可以查询到)
select g.user_no,count(*)cs from cdr200706@gsmmis g,kpi_zone_cell c
where ltrim(g.cell_id,'0')=c.cell_id
and c.zone_name like'%06%'
and user_no=1069483867
group by user_no

此时却有数据
user_no cs
1069483867 44
--------删除分析信息
BEGIN
dbms_stats.delete_table_stats(
ownname =>'localdb', tabname=>'kpi_zone_cell');
END;
---------查询
select g.user_no,count(*)cs from cdr200706@gsmmis g,kpi_zone_cell c
where ltrim(g.cell_id,'0')=c.cell_id
and c.zone_name like'%06%' and
not exists(select * from kpi_zone_employee where user_no=g.user_no)
and user_no=1069483867
group by user_no

此时又有数据
user_no cs
1069483867 44
------------中间未做任何操作(测试库上)

-----
select * from kpi_zone_employee
where user_no=1069483867

查询user_no=1069483867 是没有数据
----------分析表kpi_zone_cell前
select g.user_no,count(*)cs from cdr200706@gsmmis g,kpi_zone_cell c
where ltrim(g.cell_id,'0')=c.cell_id
and c.zone_name like'%06%' and
not exists(select * from kpi_zone_employee where user_no=g.user_no)
and user_no=1069483867
group by user_no

此时有数据
user_no cs
1069483867 44
---------分析表kpi_zone_cell
begin
dbms_stats.gather_table_stats(OWNNAME=>'localdb',
TABNAME=>'kpi_zone_cell',
ESTIMATE_PERCENT=>100,
METHOD_OPT=>'FOR ALL INDEXED COLUMNS');
end;
---------查询
select g.user_no,count(*)cs from cdr200706@gsmmis g,kpi_zone_cell c
where ltrim(g.cell_id,'0')=c.cell_id
and c.zone_name like'%06%' and
not exists(select * from kpi_zone_employee where user_no=g.user_no)
and user_no=1069483867
group by user_no

此时没有数据
user_no cs
---------查询 (去掉 not exists 条件可以查询到)
select g.user_no,count(*)cs from cdr200706@gsmmis g,kpi_zone_cell c
where ltrim(g.cell_id,'0')=c.cell_id
and c.zone_name like'%06%'
and user_no=1069483867
group by user_no

此时却有数据
user_no cs
1069483867 44
--------删除分析信息
BEGIN
dbms_stats.delete_table_stats(
ownname =>'localdb', tabname=>'kpi_zone_cell');
END;
---------查询
select g.user_no,count(*)cs from cdr200706@gsmmis g,kpi_zone_cell c
where ltrim(g.cell_id,'0')=c.cell_id
and c.zone_name like'%06%' and
not exists(select * from kpi_zone_employee where user_no=g.user_no)
and user_no=1069483867
group by user_no

此时又有数据
user_no cs
1069483867 44
------------中间未做任何操作(测试库上)

执行计划
 

Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\Owner>sqlplus /nolog

SQL*Plus: Release 9.2.0.3.0 - Production on 星期四 7月 5 15:02:57 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn @localdb
已连接。
SQL> set autotrace on;
SQL> select g.user_no,count(*)cs from cdr200706@gsmmis g,kpi_zone_cell c
2 where ltrim(g.cell_id,'0')=c.cell_id
3 and c.zone_name like'%06%' and
4 not exists(select * from kpi_zone_employee where user_no=g.user_no)
5 and user_no=1069483867
6 group by user_no;

USER_NO CS
---------- ----------
1069483867 44


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 FILTER
3 2 MERGE JOIN
4 3 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'KPI_ZONE_CELL'
6 3 SORT (JOIN)
7 6 REMOTE* GSMMIS.U
S.ORACLE
.COM

8 2 TABLE ACCESS (FULL) OF 'KPI_ZONE_EMPLOYEE'


7 SERIAL_FROM_REMOTE SELECT "USER_NO","CELL_ID" FROM "CDR200706"
"SYS_ALIAS_1" WHERE "USER_NO"=106948



Statistics
----------------------------------------------------------
7 recursive calls
1 db block gets
34 consistent gets
0 physical reads
212 redo size
355 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> begin
2 dbms_stats.gather_table_stats(OWNNAME=>'localdb',
3 TABNAME=>'kpi_zone_cell',
4 ESTIMATE_PERCENT=>10,
5 METHOD_OPT=>'FOR ALL INDEXED COLUMNS');
6 end;
7 /

PL/SQL 过程已成功完成。

SQL> select g.user_no,count(*)cs from cdr200706@gsmmis g,kpi_zone_cell c
2 where ltrim(g.cell_id,'0')=c.cell_id
3 and c.zone_name like'%06%' and
4 not exists(select * from kpi_zone_employee where user_no=g.user_no)
5 and user_no=1069483867
6 group by user_no;

未选定行


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=82)
1 0 SORT (GROUP BY NOSORT) (Cost=12 Card=1 Bytes=82)
2 1 MERGE JOIN (CARTESIAN) (Cost=12 Card=1 Bytes=82)
3 2 HASH JOIN (Cost=6 Card=3 Bytes=222)
4 3 REMOTE* (Cost=1 Card=3 Bytes=60) GSMMIS.U
S.ORACLE
.COM

5 3 TABLE ACCESS (FULL) OF 'KPI_ZONE_CELL' (Cost=4 Card=
98 Bytes=5292)

6 2 BUFFER (SORT) (Cost=8 Card=4 Bytes=32)
7 6 TABLE ACCESS (FULL) OF 'KPI_ZONE_EMPLOYEE' (Cost=2 C
ard=4 Bytes=32)



4 SERIAL_FROM_REMOTE SELECT "USER_NO","CELL_ID" FROM "CDR200706"
"G" WHERE "USER_NO"=1069483867



Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
31 consistent gets
6 physical reads
212 redo size
235 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed

SQL> BEGIN
2 dbms_stats.delete_table_stats(
3 ownname =>'localdb', tabname=>'kpi_zone_cell');
4 END;
5 /

PL/SQL 过程已成功完成。

SQL> select g.user_no,count(*)cs from cdr200706@gsmmis g,kpi_zone_cell c
2 where ltrim(g.cell_id,'0')=c.cell_id
3 and c.zone_name like'%06%' and
4 not exists(select * from kpi_zone_employee where user_no=g.user_no)
5 and user_no=1069483867
6 group by user_no;

USER_NO CS
---------- ----------
1069483867 44


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 FILTER
3 2 MERGE JOIN
4 3 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'KPI_ZONE_CELL'
6 3 SORT (JOIN)
7 6 REMOTE* GSMMIS.U
S.ORACLE
.COM

8 2 TABLE ACCESS (FULL) OF 'KPI_ZONE_EMPLOYEE'


7 SERIAL_FROM_REMOTE SELECT "USER_NO","CELL_ID" FROM "CDR200706"
"SYS_ALIAS_1" WHERE "USER_NO"=106948



Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
30 consistent gets
0 physical reads
212 redo size
355 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

怀疑分析表之后 会影响字段类型转换

select g.user_no,count(*)cs from cdr200706@gsmmis g,kpi_zone_cell c
where ltrim(g.cell_id,'0')=c.cell_id
and c.zone_name like'%06%' and
not exists(select * from kpi_zone_employee where user_no=g.user_no)
and user_no=1069483867
group by user_no

没有数据

但是

select g.user_no,count(*)cs from cdr200706@gsmmis g,kpi_zone_cell c
where ltrim(g.cell_id,'0')=c.cell_id
and c.zone_name like'%06%' and
not exists(select * from kpi_zone_employee where user_no=to_char(g.user_no))
and user_no=1069483867
group by user_no

就有数据（把user_no=g.user_no改为user_no=to_char(g.user_no)）

不解