谓词推入,
Pushing Predicate(谓词推入):当SQL语句中包含有不能合并的视图,并且视图有谓词过滤(也就是where过滤条件),CBO会将where过滤条件推入视图中,这个就叫做谓词推入。谓词推入的主要目的就是让Oracle尽可能早的过滤掉无用的数据,从而提升查询性能。
create or replace view v_sb as
select a.* from test a where rownum<1000000
union all
select * from test where 1=1; create or replace view v_sb as
select a.* from test a where rownum<1000000
union all
select * from test where 1=1;
select * from a, v_b where a.id=b.id;
select * from a, v_b where a.id=b.id;
v_b 有 100w
a 有100条
如果 a 无法 传入到 v_b 咋办
v_b ---rownum 推不进去 rownumber over ...
发生了谓词退出那么必然会产生关键字VIEW PUSHED PREDICATE,
1. 执行计划里面的rows是假的
2. 看执行计划里面 :B1 ,有变量就类似潜逃循环nl
3. 改sql
SELECT employee_id, first_name, last_name, salary
FROM employees a
WHERE salary = (SELECT MIN(salary)
FROM employees b
WHERE b.department_id = a.department_id);
with x as
(select e.*,min(salary) over (partition by e.department_id) as min_salary from employees e )
select employee_id, first_name, last_name, salary
from x where x.salary=x.min_salary;
FROM employees a
WHERE salary = (SELECT MIN(salary)
FROM employees b
WHERE b.department_id = a.department_id);
with x as
(select e.*,min(salary) over (partition by e.department_id) as min_salary from employees e )
select employee_id, first_name, last_name, salary
from x where x.salary=x.min_salary;
注意:如果department_id可以为null,那么2个SQL查询结果不是等价的
select employee_id, first_name, last_name, salary
from (select employee_id,
first_name,
last_name,
salary,
min(salary) over(partition by department_id) as min_sala from employees where department_id is not null ---过滤掉NULL)
where salary = min_sala ;
作业:写脚本抓取同一个sql类,一个表被访问了多次?
4.
select ... from a,b,c,d,e,f ....where ....
a,b,c,d,e,f 都超级大 4G
a,b,c,d,e,f 都超级大 4G
这种只能拆sql
create table xxx nologging as select ... from a,b;
5.
select * from test where owner like '%SB%'
create table index_test as select (owner,rowid as rid from test);
select * from test where rowid in (select rid from index_test where owner like '%SB%')
物化视图 刷新 index_test
index_test 驱动test
作业:把那些表占用buffer cache抓出来。什么对象耗费了多少buffer cache的空间,判断这个表是不是热点表。
6.
select ... from test where owner is null;
owner,0
oracle如果列有空值不会去走索引