如果我创建view时写了提示,但在我写语句时查询数据从这个视图没用提示,这个view 里的提示有什么用呢?
如果用提示应该怎么用?
create or replace view test_view
as
select /*+rule*/
ur.user_id, ur.role_id, mr.test_number, mr.booking_center, c.access_type, 'ALL' RELATION
from sel_test_client c,
client cv,
user_role ur,
mand_test_relation mr,
data_load_ctrl_table ct
where ct.load_type = 'test'
and ct.status = 'A'
and cv.batch_id = ct.batch_id
and c.client_test_id = cv.crm_id
and c.relation_type = 'test'
and mr.batch_id = cv.batch_id
and mr.client_sid = cv.test_id
and mr.client_type = cv.client_type
and ur.user_id = c.user_sd;
如果用提示应该怎么用?
create or replace view test_view
as
select /*+rule*/
ur.user_id, ur.role_id, mr.test_number, mr.booking_center, c.access_type, 'ALL' RELATION
from sel_test_client c,
client cv,
user_role ur,
mand_test_relation mr,
data_load_ctrl_table ct
where ct.load_type = 'test'
and ct.status = 'A'
and cv.batch_id = ct.batch_id
and c.client_test_id = cv.crm_id
and c.relation_type = 'test'
and mr.batch_id = cv.batch_id
and mr.client_sid = cv.test_id
and mr.client_type = cv.client_type
and ur.user_id = c.user_sd;
select * from test_veiw where role_id=100;
我在这里没有用rule提示,那我执行这个语句时,view里面的rule提示会起作用马?如果要view里面的rule提示起作用,是否要写成这样:
select /*+rule*/ * from test_veiw where role_id=100;
select * from test_veiw where role_id=100;
会首先被替换为
select * from
(
select /*+rule*/
ur.user_id, ur.role_id, mr.test_number, mr.booking_center, c.access_type, 'ALL' RELATION
from sel_test_client c,
client cv,
user_role ur,
mand_test_relation mr,
data_load_ctrl_table ct
where ct.load_type = 'test'
and ct.status = 'A'
and cv.batch_id = ct.batch_id
and c.client_test_id = cv.crm_id
and c.relation_type = 'test'
and mr.batch_id = cv.batch_id
and mr.client_sid = cv.test_id
and mr.client_type = cv.client_type
and ur.user_id = c.user_sd;
)
where role_id=100; 这下清楚了吧