这个语句只是说B能访问A的T1是吗? 访问的时候还是要用select * from a.t1对吧? 我的问题是不想带“a.”
首先要在a用户模式下把select权限赋予b 然后为b创建同义词。 a模式下sql grant select on t1 to b; create synonym b.t1 for t1; 这样在b模式下就可以直接用select * from t1:了
最好是创建全局同义词 create public synonym t1 for b.t1;
Well, the two are processed very very differently.Select * from T1 where x in ( select y from T2 )is typically processed as:select * from t1, ( select distinct y from t2 ) t2 where t1.x = t2.y;The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table -- typically. As opposed to select * from t1 where exists ( select null from t2 where y = x )That is processed more like: for x in ( select * from t1 ) loop if ( exists ( select null from t2 where y = x.x ) then OUTPUT THE RECORD end if end loopIt always results in a full scan of T1 whereas the first query can make use of an index on T1(x). So, when is where exists appropriate and in appropriate?Lets say the result of the subquery ( select y from T2 )is "huge" and takes a long time. But the table T1 is relatively small and executing ( select null from t2 where y = x.x ) is very very fast (nice index on t2(y)). Then the exists will be faster as the time to full scan T1 and do the index probe into T2 could be less then the time to simply full scan T2 to build the subquery we need to distinct on. Lets say the result of the subquery is small -- then IN is typicaly more appropriate. If both the subquery and the outer table are huge -- either might work as well as the other -- depends on the indexes and other factors. -----------------------------------Tom
访问的时候还是要用select * from a.t1对吧?
我的问题是不想带“a.”
然后为b创建同义词。
a模式下sql
grant select on t1 to b;
create synonym b.t1 for t1;
这样在b模式下就可以直接用select * from t1:了
create public synonym t1 for b.t1;
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then
joined to the original table -- typically.
As opposed to select * from t1 where exists ( select null from t2 where y = x )That is processed more like:
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loopIt always results in a full scan of T1 whereas the first query can make use of
an index on T1(x).
So, when is where exists appropriate and in appropriate?Lets say the result of the subquery
( select y from T2 )is "huge" and takes a long time. But the table T1 is relatively small and
executing ( select null from t2 where y = x.x ) is very very fast (nice index on
t2(y)). Then the exists will be faster as the time to full scan T1 and do the
index probe into T2 could be less then the time to simply full scan T2 to build
the subquery we need to distinct on.
Lets say the result of the subquery is small -- then IN is typicaly more
appropriate.
If both the subquery and the outer table are huge -- either might work as well
as the other -- depends on the indexes and other factors. -----------------------------------Tom
Select * from A.T1
嘿嘿