SQL 튜닝의 시작 (2013년)
날짜와 관련된 유용한 함수 들
TO_DATE & TO_TIMESTAMP & TO_CHAR(datetime)
TO_DATE
- Syntex : TO_DATE( CHAR ［, FORMAT］［, nlsparam］ )
TO_DATE : 책에선 에러 날거라 했지만...
SQL> SELECT TO_DATE('20131019') "Date" FROM dual;
Date
--------
13/10/19
NLS_PARAMETER 도 책이랑 똑같다.
SQL> show parameter nls
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_calendar string
nls_comp string BINARY
nls_currency string
nls_date_format string
nls_date_language string
nls_dual_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string
nls_sort string
nls_territory string AMERICA
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string
NLS 관련 Dictionary 를 뒤져보자.
SQL> SELECT table_name FROM dict WHERE table_name LIKE '%NLS%';
TABLE_NAME
------------------------------
NLS_DATABASE_PARAMETERS
NLS_INSTANCE_PARAMETERS
NLS_SESSION_PARAMETERS
V$NLS_PARAMETERS
V$NLS_VALID_VALUES
GV$NLS_PARAMETERS
GV$NLS_VALID_VALUES
7 개의 행이 선택되었습니다.
aaa
SELECT a.parameter
, a.value "DATABASE"
, b.value "INSTANCE"
, c.value "SESSION"
FROM NLS_DATABASE_PARAMETERS a
, NLS_INSTANCE_PARAMETERS b
, NLS_SESSION_PARAMETERS c
WHERE a.parameter = b.parameter(+)
AND a.parameter = c.parameter(+)
;
| PARAMETER | DATABASE | INSTANCE | SESSION |
|---|---|---|---|
| NLS_LANGUAGE | AMERICAN | AMERICAN | KOREAN |
| NLS_TERRITORY | AMERICA | AMERICA | KOREA |
| NLS_CURRENCY | $ | ￦ | |
| NLS_ISO_CURRENCY | AMERICA | KOREA | |
| NLS_NUMERIC_CHARACTERS | ., | ., | |
| NLS_CALENDAR | GREGORIAN | GREGORIAN | |
| NLS_DATE_FORMAT | DD-MON-RR | RR/MM/DD | |
| NLS_DATE_LANGUAGE | AMERICAN | KOREAN | |
| NLS_SORT | BINARY | BINARY | |
| NLS_TIME_FORMAT | HH.MI.SSXFF AM | HH24:MI:SSXFF | |
| NLS_TIMESTAMP_FORMAT | DD-MON-RR HH.MI.SSXFF AM | RR/MM/DD HH24:MI:SSXFF | |
| NLS_TIME_TZ_FORMAT | HH.MI.SSXFF AM TZR | HH24:MI:SSXFF TZR | |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR | RR/MM/DD HH24:MI:SSXFF TZR | |
| NLS_DUAL_CURRENCY | $ | ￦ | |
| NLS_COMP | BINARY | BINARY | BINARY |
| NLS_LENGTH_SEMANTICS | BYTE | BYTE | BYTE |
| NLS_NCHAR_CONV_EXCP | FALSE | FALSE | FALSE |
| NLS_NCHAR_CHARACTERSET | AL16UTF16 | ||
| NLS_RDBMS_VERSION | 11.2.0.1.0 | ||
| NLS_CHARACTERSET | KO16MSWIN949 |
세션레벨 포멧 변경
SQL> SELECT TO_DATE('20131019') "Date" FROM dual;
Date
--------
13/10/19
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'yyyymmdd';
세션이 변경되었습니다.
SQL> SELECT TO_DATE('20131019') "Date" FROM dual;
Date
--------
20131019
TO_DATE 에서 NLSPARAM 지정
SQL> SELECT TO_CHAR(sysdate, 'dd/Mon/yyyy') "Korean"
2 , TO_CHAR(sysdate, 'dd/Mon/yyyy', 'nls_date_language=American') "American"
3 FROM dual
4 ;
Korean American
---------------- --------------
19/10월/2013 19/Oct/2013
RR 포멧의 문제점
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy/mm/dd';
세션이 변경되었습니다.
SQL> WITH t AS
2 (
3 SELECT '00' rr FROM dual
4 UNION ALL SELECT '01' FROM dual
5 UNION ALL SELECT '49' FROM dual
6 UNION ALL SELECT '50' FROM dual
7 UNION ALL SELECT '51' FROM dual
8 UNION ALL SELECT '99' FROM dual
9 )
10 SELECT rr
11 , TO_DATE(rr, 'rr') d_rr
12 , TO_DATE(rr, 'yy') d_yy
13 FROM t
14 ;
RR D_RR D_YY
-- ---------- ----------
00 2000/10/01 2000/10/01
01 2001/10/01 2001/10/01
49 2049/10/01 2049/10/01
50 1950/10/01 2050/10/01
51 1951/10/01 2051/10/01
99 1999/10/01 2099/10/01
6 개의 행이 선택되었습니다.
SQL>
RR 포멧의 문제점
| 포멧 | 범위 | 제한된 범위의 연도만 표시 가능 |
|---|---|---|
| RR | 00 ~ 49 | 2000 ~ 2049 |
| RR | 50 ~ 99 | 1950 ~ 1999 |
| YY | 00 ~ 99 | 2000 ~ 2099 |
| RRRR | 0001 ~ 9999 | 0001 ~ 9999 |
| YYYY | 0001 ~ 9999 | 0001 ~ 9999 |
- 2자리 포멧인 RR 로는 1900 ~ 1949 와 2050 ~ 2099 를 표현 할 수 없다.
- 2자리 포멧인 YY 로는 2000 년대만 표현 할 수 있다.
- 1949 년을 2049년으로 잘못 추출하는 것을 막기 위해
- 다양한 연도를 표현하기 위해서는 4자리 포멧(RRRR 또는 YYYY)를 사용해야 겠다.
TO_TIMESTAMP
SQL> SELECT TO_TIMESTAMP('20131019', 'yyyymmdd') timestamp FROM dual
2 UNION ALL
3 SELECT TO_TIMESTAMP('20131019 235959 999999999', 'yyyymmdd hh24miss ff') timestamp FROM dual
4 ;
TIMESTAMP
---------------------------------------------------------------------------
13/10/19 00:00:00.000000000
13/10/19 23:59:59.999999999
TO_CHAR
SQL> SELECT 'DATE_FORMAT' gubun, TO_CHAR(sysdate) "TO_CHAR" FROM dual
2 UNION ALL SELECT 'TIMESTAMP_TZ_FORMAT', TO_CHAR(systimestamp) FROM dual
3 UNION ALL SELECT 'TIMESTAMP_FORMAT', TO_CHAR(TO_TIMESTAMP('20131019', 'yyyymmdd')) FROM dual
4 ;
GUBUN TO_CHAR
------------------- ------------------------------------------------------------
DATE_FORMAT 2013/10/19
TIMESTAMP_TZ_FORMAT 13/10/19 01:18:19.547000 +09:00
TIMESTAMP_FORMAT 13/10/19 00:00:00.000000000
ADD_MONTHS
SQL> SELECT ADD_MONTHS(sysdate, -1) "Date" FROM dual
2 UNION ALL SELECT ADD_MONTHS(sysdate, 0) FROM dual
3 UNION ALL SELECT ADD_MONTHS(sysdate, 1) FROM dual
4 UNION ALL SELECT ADD_MONTHS(sysdate, 1.9) FROM dual -- 소수점 무시
5 UNION ALL SELECT ADD_MONTHS(TO_DATE('20130228', 'yyyymmdd'), 1) FROM dual -- 월말
6 UNION ALL SELECT ADD_MONTHS(TO_DATE('20130327', 'yyyymmdd'), -1) FROM dual
7 UNION ALL SELECT ADD_MONTHS(TO_DATE('20130328', 'yyyymmdd'), -1) FROM dual
8 UNION ALL SELECT ADD_MONTHS(TO_DATE('20130329', 'yyyymmdd'), -1) FROM dual
9 UNION ALL SELECT ADD_MONTHS(TO_DATE('20130330', 'yyyymmdd'), -1) FROM dual
10 UNION ALL SELECT ADD_MONTHS(TO_DATE('20130331', 'yyyymmdd'), -1) FROM dual
11 ;
Date
----------
2013/09/19
2013/10/19
2013/11/19
2013/11/19
2013/03/31
2013/02/27
2013/02/28
2013/02/28
2013/02/28
2013/02/28
10 개의 행이 선택되었습니다.
MONTHS_BETWEEN
SQL> WITH t AS
2 (
3 SELECT '20130101' sdt, '20130131' edt FROM dual
4 UNION ALL SELECT '20130101', '20130201' FROM dual
5 UNION ALL SELECT '20130210', '20130201' FROM dual
6 )
7 SELECT MONTHS_BETWEEN( TO_DATE(edt, 'yyyymmdd')
8 , TO_DATE(sdt, 'yyyymmdd')
9 ) AS "Mon"
10 FROM t
11 ;
Mon
----------
.967741935
1
-.29032258
LAST_DAY
SQL> SELECT LAST_DAY(sysdate) FROM dual;
LAST_DAY(S
----------
2013/10/31
NEXT_DAY
SQL> SELECT NEXT_DAY(sysdate, '월') FROM dual -- DY(일~토)
2 UNION ALL SELECT NEXT_DAY(sysdate, '토요일') FROM dual -- DAY(일요일~토요일)
3 UNION ALL SELECT NEXT_DAY(sysdate, 1) FROM dual -- D(1~7) 숫자
4 ;
NEXT_DAY(S
----------
2013/10/21
2013/10/26
2013/10/20
SQL> SELECT NEXT_DAY(sysdate, '1') FROM dual -- 숫자가 아닌 문자 주면 에러 발생
2 ;
SELECT NEXT_DAY(sysdate, '1') FROM dual -- 숫자가 아닌 문자 주면 에러 발생
*
1행에 오류:
ORA-01846: 지정한 요일이 부적합합니다.
ROUND
SQL> SELECT ROUND(TO_DATE('20131019115959', 'yyyymmdd hh24miss')) down
2 , ROUND(TO_DATE('20131019120000', 'yyyymmdd hh24miss')) up
3 , ROUND(TO_DATE('20131019115959', 'yyyymmdd hh24miss'), 'dd') down
4 , ROUND(TO_DATE('20131019120000', 'yyyymmdd hh24miss'), 'dd') up
5 FROM dual
6 ;
DOWN UP DOWN UP
---------- ---------- ---------- ----------
2013/10/19 2013/10/20 2013/10/19 2013/10/20
SQL> SELECT ROUND(TO_DATE('20130630235959', 'yyyymmdd hh24miss'), 'yyyy') down
2 , ROUND(TO_DATE('20130701000000', 'yyyymmdd hh24miss'), 'year') up
3 FROM dual
4 ;
DOWN UP
---------- ----------
2013/01/01 2014/01/01
SQL> SELECT ROUND(TO_DATE('20130215235959', 'yyyymmdd hh24miss'), 'Q') down
2 , ROUND(TO_DATE('20130216000000', 'yyyymmdd hh24miss'), 'Q') up
3 FROM dual
4 ;
DOWN UP
---------- ----------
2013/01/01 2013/04/01
SQL> SELECT ROUND(TO_DATE('20130215235959', 'yyyymmdd hh24miss'), 'Mon') down
2 , ROUND(TO_DATE('20130216000000', 'yyyymmdd hh24miss'), 'Month') up
3 FROM dual
4 ;
DOWN UP
---------- ----------
2013/02/01 2013/03/01
SQL> SELECT TO_CHAR(ROUND(TO_DATE('20130215072959', 'yyyymmdd hh24miss'), 'hh'), 'yyyymmdd hh24miss') down
2 , TO_CHAR(ROUND(TO_DATE('20130215073000', 'yyyymmdd hh24miss'), 'hh24'), 'yyyymmdd hh24miss') up
3 FROM dual
4 ;
DOWN UP
--------------- ---------------
20130215 070000 20130215 080000
SQL> SELECT TO_CHAR(ROUND(TO_DATE('20130215070029', 'yyyymmdd hh24miss'), 'mi'), 'yyyymmdd hh24miss') down
2 , TO_CHAR(ROUND(TO_DATE('20130215070030', 'yyyymmdd hh24miss'), 'mi'), 'yyyymmdd hh24miss') up
3 FROM dual
4 ;
DOWN UP
--------------- ---------------
20130215 070000 20130215 070100
SQL> SELECT TO_CHAR(ROUND(TO_DATE('20131016115959', 'yyyymmdd hh24miss'), 'd'), 'yyyymmdd hh24miss') down
2 , TO_CHAR(ROUND(TO_DATE('20131016120000', 'yyyymmdd hh24miss'), 'dy'), 'yyyymmdd hh24miss') up
3 FROM dual
4 ;
DOWN UP
--------------- ---------------
20131013 000000 20131020 000000
TRUNC
SQL> SELECT gubun
2 , TO_CHAR(dt, 'yyyy/mm/dd hh24:mi:ss') "Trunc"
3 FROM (SELECT '' gubun, TRUNC(sysdate) dt FROM dual
4 UNION ALL SELECT 'yy', TRUNC(sysdate, 'yy') FROM dual
5 UNION ALL SELECT 'mm', TRUNC(sysdate, 'mm') FROM dual
6 UNION ALL SELECT 'dd', TRUNC(sysdate, 'dd') FROM dual
7 UNION ALL SELECT 'hh', TRUNC(sysdate, 'hh') FROM dual
8 UNION ALL SELECT 'mi', TRUNC(sysdate, 'mi') FROM dual
9 UNION ALL SELECT 'Q', TRUNC(sysdate, 'Q') FROM dual
10 UNION ALL SELECT 'dy', TRUNC(sysdate, 'dy') FROM dual
11 )
12 ;
GU Trunc
-- -------------------
2013/10/19 00:00:00
yy 2013/01/01 00:00:00
mm 2013/10/01 00:00:00
dd 2013/10/19 00:00:00
hh 2013/10/19 02:00:00
mi 2013/10/19 02:21:00
Q 2013/10/01 00:00:00
dy 2013/10/13 00:00:00
8 개의 행이 선택되었습니다.
EXTRACT
SQL> SELECT 'sysdate' g, TO_CHAR(sysdate, 'yyyy/mm/dd hh24:mi:ss') v FROM dual
2 UNION ALL SELECT 'Year' , ''||EXTRACT(Year FROM sysdate ) FROM dual
3 UNION ALL SELECT 'Month' , ''||EXTRACT(Month FROM sysdate ) FROM dual
4 UNION ALL SELECT 'Day' , ''||EXTRACT(Day FROM sysdate ) FROM dual
5 UNION ALL SELECT 'Hour' , ''||EXTRACT(Hour FROM systimestamp) FROM dual
6 UNION ALL SELECT 'Second' , ''||EXTRACT(Second FROM systimestamp) FROM dual
7 UNION ALL SELECT 'timezone_hour' , ''||EXTRACT(timezone_hour FROM systimestamp) FROM dual
8 UNION ALL SELECT 'timezone_minute', ''||EXTRACT(timezone_minute FROM systimestamp) FROM dual
9 UNION ALL SELECT 'timezone_region', ''||EXTRACT(timezone_region FROM systimestamp) FROM dual
10 UNION ALL SELECT 'timezone_abbr' , ''||EXTRACT(timezone_abbr FROM systimestamp) FROM dual
11 ;
G V
--------------- ----------------------------------------------------------------
sysdate 2013/10/19 02:33:00
Year 2013
Month 10
Day 19
Hour 17
Second .657
timezone_hour 9
timezone_minute 0
timezone_region UNKNOWN
timezone_abbr UNK
10 개의 행이 선택되었습니다.
Julian date
SQL> SELECT LEVEL lv
2 , TO_CHAR(TO_DATE(LEVEL, 'j'), 'BC yyyy/mm/dd') "Julian date"
3 , TO_CHAR(TO_DATE(LEVEL, 'j'), 'j' ) "숫자"
4 , TO_CHAR(TO_DATE(LEVEL, 'j'), 'jsp' ) "영문숫자"
5 , TO_CHAR(TO_DATE(LEVEL, 'j'), 'jspTH') "영문숫자_순서"
6 FROM dual
7 CONNECT BY LEVEL <= 20
8 ;
LV Julian date 숫자 영문숫자 영문숫자_순서
---------- ----------------- ------- ---------- -------------
1 기원전 4712/01/01 0000001 one first
2 기원전 4712/01/02 0000002 two second
3 기원전 4712/01/03 0000003 three third
4 기원전 4712/01/04 0000004 four fourth
5 기원전 4712/01/05 0000005 five fifth
6 기원전 4712/01/06 0000006 six sixth
7 기원전 4712/01/07 0000007 seven seventh
8 기원전 4712/01/08 0000008 eight eighth
9 기원전 4712/01/09 0000009 nine ninth
10 기원전 4712/01/10 0000010 ten tenth
11 기원전 4712/01/11 0000011 eleven eleventh
12 기원전 4712/01/12 0000012 twelve twelfth
13 기원전 4712/01/13 0000013 thirteen thirteenth
14 기원전 4712/01/14 0000014 fourteen fourteenth
15 기원전 4712/01/15 0000015 fifteen fifteenth
16 기원전 4712/01/16 0000016 sixteen sixteenth
17 기원전 4712/01/17 0000017 seventeen seventeenth
18 기원전 4712/01/18 0000018 eighteen eighteenth
19 기원전 4712/01/19 0000019 nineteen nineteenth
20 기원전 4712/01/20 0000020 twenty twentieth
20 개의 행이 선택되었습니다.
"데이터베이스 스터디모임" 에서 2013년에 "SQL튜닝의시작
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3813
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.