SELECT dt + INTERVAL lv-1 DAY dt
FROM (
SELECT @ROWNUM:=@ROWNUM+1 AS lv
, CONCAT('202001','01') dt
FROM information_schema.`COLUMNS` A
, (SELECT @ROWNUM:=0) R
LIMIT 100
) a
1. /*+ gather_plan_statistics ckj_as_01 */ 사용하여 쿼리
SELECT /*+ gather_plan_statistics ckj_as_01 */ S8.ITEM_VALUE
FROM SOD_8 S8 INNER JOIN (SELECT DISTINCT PROD_CD AS ITEM_VALUE
FROM SOD_24 S24 INNER JOIN SOD_44 S44 ON S24.PROD_CD = S44.ITEM_VALUE
AND S44.STR_CD IN (:1,
'981') INNER JOIN (SELECT SUBSTR(S13.ITEM_VALUE, 1, 8) AS ITEM_VALUE
FROM SOD_13 S13
WHERE S13.CUST_ID = :2 ) A ON S44.CATEGORY_ID LIKE A.ITEM_VALUE||'%'
WHERE ROWNUM <= 20 ) A ON S8.ITEM_VALUE = A.ITEM_VALUE
2. SQL_ID 확인
SELECT SQL_ID,SQL_FULLTEXT
FROM V$SQL
WHERE SQL_FULLTEXT LIKE '%ckj_as_01%'
AND SQL_FULLTEXT NOT LIKE '%$%';
SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
1gtk80xx65vah SELECT /*+ gather_plan_statistics ckj_as_01 */ S8.ITEM_VALUE
FROM SOD_8 S8 INNER JOIN (SELECT DISTINCT PROD_CD AS ITEM_VALUE
FROM SOD_24 S24 INNER
3. 실행 쿼리 플랜 확인
select * from table(dbms_xplan.display_cursor('1gtk80xx65vah',null,'ALLSTATS LAST LAST'));
SQL> select * from table(dbms_xplan.display_cursor('1gtk80xx65vah',null,'ALLSTATS LAST LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1gtk80xx65vah, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics ckj_as_01 */ S8.ITEM_VALUE
FROM
SOD_8 S8 INNER JOIN (SELECT DISTINCT PROD_CD AS ITEM_VALUE
FROM SOD_24 S24 INNER JOIN SOD_44 S44 ON S24.PROD_CD =
S44.ITEM_VALUE
AND S44.STR_CD IN (:1,
'981') INNER JOIN (SELECT SUBSTR(S13.ITEM_VALUE, 1, 8) AS ITEM_VALUE
FROM SOD_13 S13
WHERE S13.CUST_ID =
:2 ) A ON S44.CATEGORY_ID LIKE A.ITEM_VALUE||'%'
WHERE ROWNUM
<= 20 ) A ON S8.ITEM_VALUE = A.ITEM_VALUE
Plan hash value: 2246381816
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 74 | | | |
| 1 | NESTED LOOPS | | 1 | 20 | 4 |00:00:00.01 | 74 | | | |
| 2 | VIEW | | 1 | 20 | 6 |00:00:00.01 | 66 | | | |
| 3 | HASH UNIQUE | | 1 | 20 | 6 |00:00:00.01 | 66 | 1558K| 1558K| 749K (0)|
|* 4 | COUNT STOPKEY | | 1 | | 20 |00:00:00.01 | 66 | | | |
|* 5 | HASH JOIN | | 1 | 1078 | 20 |00:00:00.01 | 66 | 1557K| 1557K| 1632K (0)|
| 6 | INDEX FAST FULL SCAN| PK_SOD_24 | 1 | 1415 | 1379 |00:00:00.01 | 17 | | | |
| 7 | NESTED LOOPS | | 1 | 80878 | 5237 |00:00:00.01 | 49 | | | |
|* 8 | INDEX RANGE SCAN | PK_SOD_13 | 1 | 7 | 3 |00:00:00.01 | 3 | | | |
| 9 | INLIST ITERATOR | | 3 | | 5237 |00:00:00.01 | 46 | | | |
|* 10 | INDEX RANGE SCAN | IDX_STD_SOD_44_1 | 5 | 11385 | 5237 |00:00:00.01 | 46 | | | |
|* 11 | INDEX UNIQUE SCAN | PK_SOD_8 | 6 | 1 | 4 |00:00:00.01 | 8 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(ROWNUM<=20)
5 - access("S24"."PROD_CD"="S44"."ITEM_VALUE")
8 - access("S13"."CUST_ID"=:2)
10 - access((("S44"."STR_CD"=:1 OR "S44"."STR_CD"='981')) AND "S44"."CATEGORY_ID" LIKE
SUBSTR("S13"."ITEM_VALUE",1,8)||'%')
filter("S44"."CATEGORY_ID" LIKE SUBSTR("S13"."ITEM_VALUE",1,8)||'%')
11 - access("S8"."ITEM_VALUE"="A"."ITEM_VALUE")
41 rows selected.
[Tip!] 2~3단계 한번에 실행하기
SELECT *
FROM TABLE(
SELECT dbms_xplan.display_cursor(SQL_ID,null,'ALLSTATS LAST LAST')
FROM V$SQL WHERE SQL_FULLTEXT LIKE '%ckj_to_01%' AND SQL_FULLTEXT NOT LIKE '%$%');
- - URL Encode function
CREATE OR REPLACE FUNCTION form_url_encode(
data IN VARCHAR2,
charset IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
RETURN utl_url.escape(data, TRUE, charset);
END;
-- URL Decode function
CREATE OR REPLACE FUNCTION form_url_decode(
data IN VARCHAR2,
charset IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
RETURN utl_url.unescape(replace(data, '+', ' '), charset);
END;
일반적으로 비밀번호에 영어/숫자가 아닌 한글이 들어가는 경우는 프로그램에서 Filtering하지 못한 경우가 대부분이다.
이렇게 들어간 데이터를 검색하고자 할때 regexp를 사용해서 검사하면 모두 검색할수 있다.
SELECT * FROM MEMBER
WHERE USER_PASSWORD REGEXP '[가-힣]';
추가로 정규표현식을 공부하면 쉽게 알수 있는 거지만
한글로 시작하는 경우엔 '^[가-힣]'
모두가 한글로 구성된 경우만 검색할경우엔 '^[가-힣]+$'
[출처] MySQL 정규식(regexp)검사를 통한 한글포함여부 체크|작성자 아찌
[출처] MySQL 정규식(regexp)검사를 통한 한글포함여부 체크|작성자 아찌
[출처] MySQL 정규식(regexp)검사를 통한 한글포함여부 체크|작성자 아찌
쿼리앞에 explain 이라고만 붙여주면 된다.
조회되는 필드들의 의미는 엮인글을 참고. 매우 잘 정리해놓으셨굼.
SQL> var idenno varchar2(20)
SQL>
SQL> begin
SQL> :idenno := '00001ADEED13DC6C';
SQL> end;
SQL> /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from ITSMEMEMBERINFOTT where iden_no = :idenno;