3) where ename < to_date('9999/12/31','RRRR/MM/DD')
- 묵시적(암시적) 형변환
- 숫자가 우선 순위에 있다.
- 비교하는값이 문자라면 컬럼 값이 숫자여도 문자로 변환된다.
- 묵시적 형변환으로 인하여 함수 기반 인덱스를 생성할 경우도 있다.
(그러나 그렇게 추천할 만한 사항은 아니다.)
2) 부정형 비교시
- full table scan
- index full scan
- index unique scan
- index skip scan
- index fast full scan
- index merge scan
- index bitmap merge scan
- index join
⑴ rowid란
- row의 물리적 주소 (file#+block#+row#)
⑵ rowid hint
- /*+ rowid(테이블명) */
예시)
SELECT ename ,sal
FROM EMP
WHERE ROWID = 'AAASlIAAEAAAALuAAL';
* SQL_실행계획 읽는 법
---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 40 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_SAL | 2 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
-> 가장 안 쪽으로 들어가 있는 부분부터 읽으면서 위로 올라간다.
on emp(deptno) ;
from emp
where deptno > 0 ;
from emp
on emp(sal) ;
from emp
where sal > 0;
on emp(ename);
from emp
where ename > ' ';
2) 문자 > ' '
3) where ename < to_date('9999/12/31','RRRR/MM/DD')
on emp(hiredate) ;
FROM emp
WHERE hiredate between TO_DATE('1981/01/01','RRRR/MM/DD') AND TO_DATE('1981/12/31','RRRR/MM/DD');
SELECT ename, sal
FROM emp
WHERE sal=3000;
2 select ename, sal
3 from emp
4 where ename='SCOTT';
----------------------------------------------------------------------------------------------------
Plan hash value: 106684950
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ENAME | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
---------------------------------------------------
-----
- dynamic sampling used for this statement (level=2)
from emp
where sal*12 =36000;
FROM EMP
WHERE sal = 36000/12;
on emp(job);
from emp
where substr(job,1,5)='SALES';
SELECT /*+ index(emp emp_job) */ ename, job, sal
FROM EMP
WHERE job LIKE 'SALES%' ;
FROM EMP
WHERE hiredate >= to_date('16/01/08','RR/MM/DD')
AND hiredate < TO_DATE('16/01/08','RR/MM/DD')+1;
CREATE TABLE EMP700
(ename VARCHAR2(20),
SAL VARCHAR2(20));
ON emp700(sal);
select ename, sal
from emp700
where sal = 3000;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP700 | 1 | 24 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
---------------------------------------------------
-----
- dynamic sampling used for this statement (level=2)
from emp700
where sal = '3000';
from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +alias +outline +predicate'))
select ename, sal
from emp
where sal like '30%';
on emp(to_char(sal));
from emp
where sal like '30%';
select /*+ index(emp emp_sal_func) */ename, sal
from emp
where sal like :"SYS_B_0"
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 16 | 672 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_SAL_FUNC | 16 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
valuse(2345, ' JANE ', 4600);
on emp(TRIM(ename));
FROM EMP
WHERE TRIM(ename) = 'JANE';
FROM EMP
WHERE ename LIKE '%EN%'
or ename LIKE '%IN%';
FROM EMP
WHERE regexp_like(ename,'(EM|IN)');
FROM EMP
WHERE ROWID in (SELECT /*+ index(emp emp_ename) */ rowid
from emp
where ename like '%EN%' or ename like '%IN%') ;
SELECT /*+ no_query_transformation rowid(emp) */ ename, sal, job, hiredate
FROM EMP e,(SELECT /*+ index(emp emp_ename) */ rowid
from EMP
where ename like '%EN%' or ename like '%IN%') b