2)中 a, b, c三个表的连接是在数据库端完成的,返回的结果集总共20000万条记录,总的传输量大致上等于这20000纪录的字节。3)中,不知道这句sql的explain plan是怎样的,但我怀疑是把30万*3 个记录都传到本地来处理,这当然很慢,网络通讯量加大了1万多倍。4)比 3)要好很多,但是比 2)还是要慢得多。解决办法: 在远端数据库建立一个view,create or replace view myview as select a.a_id,a_name,b_name,c_name from a, b, c where a.b_id = b.b_id and a.c_id = c.c_id;然后访问view, select * from myview@to_remote where a_id=100;
和远端数据库的管理员商量一下,让他帮你建一个view。其他我想不出什么好办法。
select /*+ DRIVING_SITE (a,b,c) */ a.a_id,a_name,b_name,c_name from a@to_remote,b@to_remote,c@to_remote where a.b_id=b.b_id and a.c_id=c.c_id and a.a_id=100
select /*+ DRIVING_SITE (a b c) */ a.a_id,a_name,b_name,c_name from a@to_remote,b@to_remote,c@to_remote where a.b_id=b.b_id and a.c_id=c.c_id and a.a_id=100
谢谢bobfang,DRIVING_SITE应该能解决这个问题。
学习...... DRIVING_SITE是什么意思啊?
DRIVING_SITE的作用是什么?它在什么版本下可以使用?
The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization.For example:SELECT /*+DRIVING_SITE(departments)*/ * FROM employees, departments@rsite WHERE employees.department_id = departments.department_id; If this query is executed without the hint, then rows from departments are sent to the local site, and the join is executed there. With the hint, the rows from employees are sent to the remote site, and the query is executed there, returning the result to the local site.This hint is useful if you are using distributed query optimization. 这是oracle的Oracle9i Database Performance Tuning Guide and Reference书中的原话。但我不清楚多个表是否可以按我上面写的那样来写。 这个提示在8i下支持。
假设a,b,c三表均是本地oracle表,且b表和c表的b_id和c_id均建立了索引: a表,其字段有:a_name,b_id,c_id,a_id----该表有200万条记录 b表,其字段有:b_name,b_id----该表有200万条记录 c表,其字段有:c_name,c_id----该表有200万条记录用下面哪种方法更好?或者说更能提高速度 (1)不用游标: select a.a_id,a_name,b_name,c_name from a,b,c where a.b_id=b.b_id and a.c_id=c.c_id and a.a_id=100(3)使用游标: create or replace procedure get_cust_info is cursor a_cursor is select b_id,c_id from a where a_id=100;v_b_id b.b_id%type; v_b_name b.b_name%type; v_c_id c.c_id%type; v_c_name b.c_name%type;begin open a_cursor; loop fetch a_cursor into v_b_id,v_c_id; exit when a_cursor%notfound; select b_name into v_b_name from b where b_id=v_b_id; select c_name into v_c_name from c where c_id=v_c_id; insert into 结果表; end loop; close c_cursor; commit; end get_cust_info;
假设a,b,c三表均是本地oracle表,且b表和c表的b_id和c_id均建立了索引: a表,其字段有:a_name,b_id,c_id,a_id----该表有200万条记录 b表,其字段有:b_name,b_id----该表有200万条记录 c表,其字段有:c_name,c_id----该表有200万条记录用下面哪种方法更好?或者说更能提高速度 (1)不用游标: select a.a_id,a_name,b_name,c_name from a,b,c where a.b_id=b.b_id and a.c_id=c.c_id and a.a_id=100(3)使用游标: create or replace procedure get_cust_info is cursor a_cursor is select b_id,c_id from a where a_id=100;v_b_id b.b_id%type; v_b_name b.b_name%type; v_c_id c.c_id%type; v_c_name b.c_name%type;begin open a_cursor; loop fetch a_cursor into v_b_id,v_c_id; exit when a_cursor%notfound; select b_name into v_b_name from b where b_id=v_b_id; select c_name into v_c_name from c where c_id=v_c_id; insert into 结果表; end loop; close c_cursor; commit; end get_cust_info;哪位高手能帮我解释???
在远端数据库建立一个view,create or replace view myview as
select a.a_id,a_name,b_name,c_name
from a, b, c
where a.b_id = b.b_id
and a.c_id = c.c_id;然后访问view,
select *
from myview@to_remote
where a_id=100;
a@to_remote,b@to_remote,c@to_remote where
a.b_id=b.b_id and a.c_id=c.c_id and a.a_id=100
a@to_remote,b@to_remote,c@to_remote where
a.b_id=b.b_id and a.c_id=c.c_id and a.a_id=100
DRIVING_SITE是什么意思啊?
FROM employees, departments@rsite
WHERE employees.department_id = departments.department_id;
If this query is executed without the hint, then rows from departments are sent to the local site, and the join is executed there. With the hint, the rows from employees are sent to the remote site, and the query is executed there, returning the result to the local site.This hint is useful if you are using distributed query optimization.
这是oracle的Oracle9i Database Performance Tuning Guide and Reference书中的原话。但我不清楚多个表是否可以按我上面写的那样来写。
这个提示在8i下支持。
a表,其字段有:a_name,b_id,c_id,a_id----该表有200万条记录
b表,其字段有:b_name,b_id----该表有200万条记录
c表,其字段有:c_name,c_id----该表有200万条记录用下面哪种方法更好?或者说更能提高速度
(1)不用游标:
select a.a_id,a_name,b_name,c_name from
a,b,c where
a.b_id=b.b_id and a.c_id=c.c_id and a.a_id=100(3)使用游标:
create or replace procedure get_cust_info is cursor a_cursor is
select b_id,c_id from a where a_id=100;v_b_id b.b_id%type;
v_b_name b.b_name%type;
v_c_id c.c_id%type;
v_c_name b.c_name%type;begin
open a_cursor;
loop
fetch a_cursor into v_b_id,v_c_id;
exit when a_cursor%notfound;
select b_name into v_b_name from b where b_id=v_b_id;
select c_name into v_c_name from c where c_id=v_c_id;
insert into 结果表;
end loop;
close c_cursor;
commit;
end get_cust_info;
/************
3)中,不知道这句sql的explain plan是怎样的,但我怀疑是把30万*3 个记录都传到本地来处理,这当然很慢,网络通讯量加大了1万多倍。
************/
bobfang(匆匆过客)能否解释解下???
帮帮忙偶们是中国人哦~~
a表,其字段有:a_name,b_id,c_id,a_id----该表有200万条记录
b表,其字段有:b_name,b_id----该表有200万条记录
c表,其字段有:c_name,c_id----该表有200万条记录用下面哪种方法更好?或者说更能提高速度
(1)不用游标:
select a.a_id,a_name,b_name,c_name from
a,b,c where
a.b_id=b.b_id and a.c_id=c.c_id and a.a_id=100(3)使用游标:
create or replace procedure get_cust_info is cursor a_cursor is
select b_id,c_id from a where a_id=100;v_b_id b.b_id%type;
v_b_name b.b_name%type;
v_c_id c.c_id%type;
v_c_name b.c_name%type;begin
open a_cursor;
loop
fetch a_cursor into v_b_id,v_c_id;
exit when a_cursor%notfound;
select b_name into v_b_name from b where b_id=v_b_id;
select c_name into v_c_name from c where c_id=v_c_id;
insert into 结果表;
end loop;
close c_cursor;
commit;
end get_cust_info;哪位高手能帮我解释???