oralce 谓词

###强制顺序 /*+ ORDERED_PREDICATES */ 指定的顺序

select /*+ ORDERED_PREDICATES */ to_number(VALUE) as value, TYPE 2
from t_parameter 3
where type = '3'
and to_number(VALUE) >= 10;

###/*+ ORDERED_PREDICATES */ 先执行 get_name(ename) = 'smith'; 这一句判断
select /*+ ORDERED_PREDICATES */ *
from scott.emp
where f_get_hiredate(p_empno => empno) <= to_date('1980-12-17','yyyy-mm-dd')
and get_name(ename) = 'smith';


####循环与谓词 /*+ use_nl(d,e) */ e.deptno = d.dep 有索引
select /*+ use_nl(d,e) */ d.dname, d.loc, e.empno, e.ename
from dept d inner join emp e on e.deptno = d.dep

### /*+ use_nl(d,e) */ 表示循环 类似：
for d_deptno in (select deptno from dept)
loop select * from emp where deptno = :d_deptno;
end loop;
------------------------------------------------------------------------------
##被驱动表与谓词 leading（e）指定e表是驱动表 inner join 有效，若果是外连接，则驱动表不能随意指定。
select /*+ use_nl(d,e) leading(e) */ d.dname, d.loc, e.empno, e.ename
from dept d
inner join emp e on e.deptno = d.deptno;


####谓词推入
##The NO_QUERY_TRANSFORMATION hint instructs the optimizer to skip all query transformations, including but not limited to OR-expansion, view merging, subquery unnesting, star transformation, and materialized view rewrite.
select * from (select /*+ no_query_transformation */*
from emp where deptno = 30) e where e.ename = 'scott';


select * from (
select o.owner,o.object_type,o.object_name,o.created,t.avg_row_len,t.num_rows,t.status
from t_objects o left join t_tables t on t.owner = o.owner and t.table_name = o.object_name
union all
select o.owner,o.object_type,o.object_name,o.created,t.avg_row_len,t.num_rows,t.status
from t_objects_his o left join t_tables t on t.owner = o.owner and t.table_name = o.object_name
)
where created = to_date('2022-01-03','yyyy-mm-dd');


##加hint固定谓词顺序
SQL> select /*+ ORDERED_PREDICATES */ *
from scott.emp
where f_get_hiredate(p_empno => empno) <= to_date('1980-12-17','yyyy-mm-dd')
and get_name(ename) = 'smith';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
Elapsed: 00:00:01.05
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 32 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("F_GET_HIREDATE"("P_EMPNO"=>"EMPNO")<=TO_DATE(' 1980-12-17 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "GET_NAME"("ENAME")='smith')

###禁止谓词推入 :加入 and rownum >= 1 ，改变执行计划 先走type = '3' 的情况。而不是先走value >= 10
select * from
(select to_number(VALUE) as value, TYPE from t_parameter where type = '3' and rownum >= 1)
where value >= 10;

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 1 | 87 | 3 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL| EMP | 1 | 32 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("GET_NAME"("ENAME")='smith')
3 - filter(ROWNUM>=1)
4 - filter("F_GET_HIREDATE"("P_EMPNO"=>"EMPNO")<=TO_DATE('
1980-12-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


###(方法3)使用with
with e as
( select /*+ materialize */ *
from scott.emp
where f_get_hiredate(p_empno => empno) <= to_date('1980-12-17','yyyy-mm-dd')
)
select *
from e
where get_name(ename) = 'smith';

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 5 (0)|
| 1 | TEMP TABLE TRANSFORMATION | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6603_2E04FE | | | |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 32 | 3 (0)|
|* 4 | VIEW | | 1 | 87 | 2 (0)|
| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6603_2E04FE | 1 | 32 | 2 (0)|
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("F_GET_HIREDATE"("P_EMPNO"=>"EMPNO")<=TO_DATE(' 1980-12-17 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
4 - filter("GET_NAME"("ENAME")='smith')

####(方法4)使用filter
select *
from scott.emp
where f_get_hiredate(p_empno => empno) <= to_date('1980-12-17','yyyy-mm-dd')
and exists(select * from dual where get_name(ename) = 'smith');

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 5 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 32 | 3 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter( EXISTS (SELECT 0 FROM "SYS"."DUAL" "DUAL" WHERE "GET_NAME"(:B1)='smith'))
2 - filter("F_GET_HIREDATE"("P_EMPNO"=>"EMPNO")<=TO_DATE('
1980-12-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
3 - filter("GET_NAME"(:B1)='smith')

子查询换成值就可以推入
SQL> select * from v_objects where created = to_date('2022-01-03','yyyy-mm-dd');
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 3 | 405 | 13 (0)| 00:00:01 |
| 1 | VIEW | V_OBJECTS | 3 | 405 | 13 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 169 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T_OBJECTS | 1 | 103 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_T_OBJECTS_N2 | 1 | | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| T_TABLES | 1 | 66 | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_T_TABLES_N1 | 1 | | 1 (0)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 2 | 338 | 8 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| T_OBJECTS_HIS | 2 | 206 | 4 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_T_OBJECTS_HIS_N2 | 2 | | 3 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID| T_TABLES | 1 | 66 | 3 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | IDX_T_TABLES_N1 | 1 | | 1 (0)| 00:00:01 |
5 - access("O"."CREATED"=TO_DATE(' 2022-01-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
6 - filter("T"."OWNER"(+)="O"."OWNER")
7 - access("T"."TABLE_NAME"(+)="O"."OBJECT_NAME")
10 - access("O"."CREATED"=TO_DATE(' 2022-01-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
11 - filter("T"."OWNER"(+)="O"."OWNER")
12 - access("T"."TABLE_NAME"(+)="O"."OBJECT_NAME")


例2：子查询改为函数实现谓词推入
create or replace function get_curr_date return date as
v_curr_date currdate.curr_date%type;
begin
select curr_date into v_curr_date from currdate;
return v_curr_date;
end;
select * from v_objects where created = get_curr_dat