16

谓词推入，

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;

注意：如果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  

这种只能拆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如果列有空值不会去走索引