TIMESTAMP 타입과 SYSTIMESTAMP 함수

TIMESTAMP 타입 칼럼을 SYSTIMESTAMP 함수로 조회하면 인덱스를 사용할 수 없다.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 TIMESTAMP, c2 NUMBER);
CREATE INDEX t1_x1 ON t1 (c1);

아래 쿼리는 SYSTIMESTAMP 함수를 사용했다. SYSTIMESTAMP 함수가 TIMESTAMP WITH TIMEZONE 값을 반환하기 때문에 암시적 데이터 변환이 발생하여 인덱스를 사용할 수 없다.

-- 2
SELECT * FROM t1 WHERE c1 = SYSTIMESTAMP;

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |
------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("C1"))=SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))

SYSTIMESTAMP 함수 대신 TIMESTAMP 값을 반환하는 LOCALTIMESTAMP 함수를 사용하면 인덱스를 사용할 수 있다.

-- 3
SELECT * FROM t1 WHERE c1 = LOCALTIMESTAMP;

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|*  2 |   INDEX RANGE SCAN          | T1_X1 |
---------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=LOCALTIMESTAMP(6))

소수점 이하 초가 필요하지 않다면 SYSDATE 함수를 사용할 수 있다. DATE 타입이 TIMESTAMP 타입으로 변환된다.

-- 4
SELECT * FROM t1 WHERE c1 = SYSDATE;

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|*  2 |   INDEX RANGE SCAN          | T1_X1 |
---------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=SYSDATE@!)

쿼리를 수정할 수 없다면 아래처럼 FBI를 생성해야 한다.

--5
CREATE INDEX t1_x2 ON t1 (SYS_EXTRACT_UTC (c1));

SELECT * FROM t1 WHERE c1 = SYSTIMESTAMP;

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|*  2 |   INDEX RANGE SCAN          | T1_X2 |
---------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("C1"))=SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))