SELECT *
FROM BUGS
WHERE DESCRIPTION LIKE '%crash%';
SELECT *
FROM BUGS
WHERE DESCRIPTION REGEXP '%crash%';
3-1) 성능 저하
3-2) 패턴 매칭 불일치
SELECT *
FROM BUGS
WHERE DESCRIPTION LIKE '%one%';
SELECT *
FROM BUGS
WHERE DESCRIPTION REGEXP '[[:<:]]one[[:>:]]';
다음과 같은 질문은 보통 가난한 자의 검색 엔진 안티패턴이 사용되고 있음을 나타냄
ALTER TABLE BUGS ADD FULLTEXT INDEX BUGFTS (SUMMARY, DESCRIPTION);
SELECT *
FROM BUGS
WHERE MATCH(SUMMARY, DESCRIPTION) AGAINST ('crash');
SELECT *
FROM BUGS
WHERE MATCH(SUMMARY, DESCRIPTION) AGAINST ('+crash -save' IN BOOLEAN MODE);
☞ CONTEXT
-- 1. 인덱스 생성(오타. CTSSYS 가 아니라 CTXSYS)
CREATE INDEX BUGSTEXT ON BUGS(SUMMARY) INDEXTYPE IS CTXSYS.CONTEXT;
-- 2. 검색
SELECT *
FROM BUGS
WHERE CONTAINS(SUMMARY, 'crash') > 0;
-- 1. 테이블 생성
DROP TABLE TX_TEST;
CREATE TABLE TX_TEST(TXTFIELD VARCHAR2(2000));
-- 2. 데이터 생성
INSERT INTO TX_TEST VALUES('asian institute of technology');
-- 3. CONTEXT 인덱스 생성
CREATE INDEX TX_TEST_INDEX ON TX_TEST(TXTFIELD) INDEXTYPE IS CTXSYS.CONTEXT;
-- 4. CONTEXT 인덱스에 포함된 단어 검색이 검색 가능(대소문자 구별 안함)
col TXTFIELD format a40
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'institute') > 0
;
TXTFIELD
----------------------------------------
asian institute of technology
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'INsTItUTe') > 0
;
TXTFIELD
----------------------------------------
asian institute of technology
-- 5. 텍스트 필드에 포함되지 않은 단어를 지정하면 검색되지 않음
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'techy') > 0
;
no rows selected
-- 6. 단어의 일부분을 검색어로 사용할 경우 검색되지 않음
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'tech') > 0
;
no rows selected
-- 7. 텍스트 필드에 전체 단어로 검색해야 검색 가능
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'technology') > 0
;
TXTFIELD
----------------------------------------
asian institute of technology
-- 8. 두 글자 단어는 검색되지 않음
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'of') > 0
;
no rows selected
-- 9. 텍스트 필드의 시작 부분 단어를 FULL로 검색할 경우 검색 가능
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'asian') > 0
;
TXTFIELD
----------------------------------------
asian institute of technology
-- 10. 새로운 데이터 생성
INSERT INTO TX_TEST VALUES('no problem of money');
-- 11. 새로운 텍스트가 자동으로 생성되지 않음
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'of') > 0
;
no rows selected
-- 12. 특히 두 글자 단어는 검색 안됨
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'no') > 0
;
no rows selected
-- 13. 그리고 다섯 글자 단어도 안됨 -_-
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'money') > 0
;
no rows selected
-- 14. 심지어 일곱 글자 단어도 안됨 ㅎㅎ
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'problem') > 0
;
no rows selected
-- 15. 검색 시 빠른 조회는 이전에 텍스트 항목에 대한 작업이 있는 단어만 검색 가능함
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'technology') > 0
;
TXTFIELD
----------------------------------------
asian institute of technology
-- 16. 테이블에 생성된 데이터 확인
SELECT TXTFIELD
FROM TX_TEST
;
TXTFIELD
----------------------------------------
asian institute of technology
no problem of money
-- 17. COMMIT
COMMIT;
-- 18. 을 한 후에도 CONTEXT 작업 하기 전 단어가 검색되는지 확인
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'problem') > 0
;
no rows selected
-- 19. 테이블과 인덱스가 동기화 되어 있지 않아, 이를 동기화 하는 작업 수행
EXEC CTX_DDL.SYNC_INDEX('TX_TEST_INDEX');
-- 19_2. 내부에서 수행되는 SQL(동기화 엄청 느림 -_- 결국 20분 넘게 걸려 포기함)
-- 이 정도 수준이라면 이 기능 사용 못할것으로 보임
[ Bind Variable ]
-------------------------------------------------------------------------------------------------------------------------------
Bind_Name Data Type Value Position
-------------------------------------------------------------------------------------------------------------------------------
:B1 NUMBER 0 2
:B2 NUMBER 55388 1
[ Current SQL ]
-------------------------------------------------------------------------------------------------------------------------------
DELETE FROM DR$WAITING
WHERE WTG_CID = :B2
AND WTG_PID = :B1
AND EXISTS (SELECT 1
FROM DR$PENDING
WHERE DR$PENDING.PND_CID = DR$WAITING.WTG_CID
AND DR$PENDING.PND_PID = DR$WAITING.WTG_PID
AND DR$PENDING.PND_ROWID = DR$WAITING.WTG_ROWID)
[ EXECUTION PLAN ]
-------------------------------------------------------------------------------------------------------------------------------
{( CHILD NUMBER = 0 }}
*DELETE STATEMENT - ALL_ROWS- Cost Estimate:
* DELETE :CTXSYS.DR$WAITING(Ali:)
* HASH JOIN SEMI (SEMI)
* TABLE ACCESS FULL {{FULL:14286}}:CTXSYS.DR$WAITING(T)(POS:1)(Ali:DR$WAITING@DEL$1)
* INDEX RANGE SCAN (RANGE SCAN):CTXSYS.SYS_IOT_TOP_27194(I)(Ali:DR$PENDING@SEL$1)(IOT:PND_CID,PND_PID,PND_ROWID)(SC:2)
-- 20. 동기화 이후 검색 시 새로운 텍스트에 대해 검색 가능
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'problem') > 0
;
TXTFIELD
----------------------------------------
no problem of money
-- 21. 5글자 단어도 검색 가능
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'money') > 0
;
TXTFIELD
----------------------------------------
no problem of money
-- 22. 하지만 두 글자 단어는 동기화 이후에도 여전히 검색 불가함
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'on') > 0
;
no rows selected
-- 23. 새로운 데이터 생성
INSERT INTO TX_TEST VALUES('was there a war inside the store here');
COMMIT;
-- 24. 동기화 되기 전이므로 검색 불가
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'war') > 0
;
no rows selected
-- 25. 동기화 진행
EXEC CTX_DDL.SYNC_INDEX('TX_TEST_INDEX')
;
-- 26. 이번 검색은 3 글자로 된 단어 결과를 보여줌
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'war') > 0
;
TXTFIELD
----------------------------------------
was there a war inside the store here
-- 27. 일반적인 영어 단어 "here"는 검색되지 않는다는 테스트인데 검색은 됨 -_-
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'here') > 0
;
TXTFIELD
----------------------------------------
was there a war inside the store here
-- 28. 같은 원리로 "the"는 검색되지 않음
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'the') > 0
;
no rows selected
-- 29. "was"도 마찬가지
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'was') > 0
;
no rows selected
-- 30. 하지만 "war"는 검색 가능
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'war') > 0
;
TXTFIELD
----------------------------------------
was there a war inside the store here
-- 31. 마찬가지로 "there"는 검색 불가
SELECT TXTFIELD
FROM TX_TEST
WHERE CONTAINS(TXTFIELD, 'there') > 0
;
no rows selected
☞ CTXCAT
CTX_DDL.CREATE_INDEX_SET('BugsCatalogSet');
CTX_DDL.ADD_INDEX('BugsCatalogSet', 'status');
CTX_DDL.ADD_INDEX('BugsCatalogSet', 'priority');
CREATE INDEX BUGSCATALOG ON BUGS(SUMMARY) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS('BugsCatalogSet');
CATSEARCH() 연산자는 텍스트 컬럼과 일반 컬럼 집합을 위한 두 개의 인수를 취함
SELECT *
FROM BUGS
WHERE CATSEARCH(SUMMARY, '(crash save)', 'status = "NEW"') > 0;
☞ CTXXPATH
CREATE INDEX BUGTEXTXML ON BUGS(TESTOUTPUT) INDEXTYPE IS CTXSYS.CTXXPATH;
SELECT *
FROM BUGS
WHERE TESTOUTPUT.EXISTSNODE('/testsuite/test(@status="fail")') > 0;
☞ CTXRULE
-- 1. crash란 단어를 포함하는 버그를 검색하려면, 먼저 전체 텍스트 기능을 활성화하고,
-- 데이터베이스에 카탈로그 정의해야 함.
EXEC sp_fulltext_database 'enable'
EXEC sp_fulltext_catalog 'BugsCatalog', 'create'
-- 2. 이후 BUGS 테이블에 전체 텍스트 인덱스를 정의하고, 인덱스에 컬럼을 추가하고,
-- 인덱스를 활성화함.
EXEC sp_fulltext_table 'Bugs', 'create', 'BugsCatalog', 'bug_id'
EXEC sp_fulltext_column 'Bugs', 'summary', 'add', '2057'
EXEC sp_fulltext_column 'Bugs', 'description', 'add', '2057'
EXEC sp_fulltext_table 'Bugs', 'activate'
-- 3. 전체 텍스트 인덱스에 대해 자동 동기화 기능을 활성화 해,
-- 인덱스가 걸린 컬럼의 데이터가 바뀌면 변경 내용이 전파되도록 하고,
-- 이후 인덱스를 띄울 프로세스를 시작함.
-- 이 프로세스는 백그라운드로 실행되며, 쿼리가 인덱스를 활용할 수 있게 되기까지 약간 시간 소요됨
EXEC sp_fulltext_table 'Bugs', 'start_change_tracking'
EXEC sp_fulltext_table 'Bugs', 'start_background_updateindex'
EXEC sp_fulltext_table 'Bugs', 'start_full'
-- 4. 마지막으로 CONTAINS() 연산자를 사용하는 쿼리 실행
SELECT * FROM Bugs WHERE CONTAINS(summary, '"crash"');
CREATE TABLE BUGS (BUG_ID SERIAL PRIMARY KEY,
SUMMARY VARCHAR(80),
DESCRIPTION TEXT,
TS_BUGTEXT TSVECTOR
-- OTHER COLUMNS
);
CREATE TRIGGER ts_bugtext BEFORE INSERT OR UPDATE ON Bugs
FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(ts_bugtext, 'pg_catalog.english', summary, description);
CREATE INDEX bugs_ts ON Bugs USING GIN(ts_bugtext);
SELECT *
FROM BUGS
WHERE TS_BUGTEXT @@ TO_TSQUERY('crash');
-- 1. MakeFile.in에서 다음 옵션을 추가하여 SQLite 빌드
TCC += -DSQLITE_CORE=1
TCC += -DSQLITE_ENABLE_FTS3=1
-- 2. 텍스트 검색을 위한 가상테이블 생성(데이터 타입, 제약조건, 다른 컬럼 옵션은 무시)
CREATE VIRTUAL TABLE BugsText USING fts3(summary, description);
-- 3. 다른 테이블의 텍스트를 인덱싱하려면, 데이터를 가상 테이블에 복사해주어야 함
INSERT INTO BUGSTEXT (DOCID,
SUMMARY,
DESCRIPTION)
SELECT BUG_ID,
SUMMARY,
DESCRIPTION
FROM BUGS
;
-- 4. 전체 텍스트 검색 연산자 MATCH를 이용해 FTS 가상 테이블 BUGSTEXT를 조회하고,
-- 매칭되는 행을 원본 테이블인 BUGS와 조인할 수 있음.
SELECT B.*
FROM BUGSTEXT T
JOIN BUGS B ON (T.DOCID = B.BUG_ID)
WHERE BUGSTEXT MATCH 'crash'
;
-- 5. 또한 패턴 매칭에서 제한된 BOOLEAN 수식 사용 가능
SELECT *
FROM BUGSTEXT
WHERE BUGSTEXT MATCH 'crash -save'
-- 1. sphinx.conf에 설정
source bugsrc
{
type = mysql
sql_user = bugsuser
sql_pass = xyzzy
sql_db = bugsdatabase
sql_query = \
SELECT bug_id, status, date_reported, summary, description \
FROM Bugs
sql_attr_timestamp = date_reported
sql_attr_str2ordinal = status
sql_query_info = SELECT * FROM Bugs WHERE bug_id = $id
}
index bugs
{
source = bugsrc
path = /opt/local/var/db/sphinx/bugs
}
-- 2. 1번에서 설정된 내용으로 Shell에서 indexer 명령어로 인덱스 생성 가능
indexer -c sphinx.conf bugs
-- 3. search 명령을 사용해 인덱스 검색 가능
search -b "crash -save"
CREATE TABLE KEYWORDS (KEYWORD_ID SERIAL PRIMARY KEY,
KEYWORD VARCHAR(40) NOT NULL,
UNIQUE KEY (KEYWORD));
CREATE TABLE BUGSKEYWORDS (KEYWORD_ID BIGINT UNSIGNED NOT NULL,
BUG_ID BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (KEYWORD_ID, BUG_ID),
FOREIGN KEY (KEYWORD_ID) REFERENCES KEYWORDS(KEYWORD_ID),
FOREIGN KEY (BUG_ID) REFERENCES BUGS(BUG_ID));
CREATE PROCEDURE BugsSearch(keyword VARCHAR(40))
BEGIN
SET @keyword = keyword;
-- 1. 사용자가 지정한 키워드를 검색함.
-- 정수형 PK인 keywords.keyword_id 값을 리턴함.
-- 이전에 검색된 적이 없는 단어라면 NULL을 리턴함.
PREPARE s1 FROM 'SELECT MAX(keyword_id) INTO @k FROM Keywords -- <callout id="code.search.getk"/> WHERE keyword = ?';
EXECUTE s1 USING @keyword;
DEALLOCATE PREPARE s1;
IF (@k IS NULL) THEN
-- 2. 단어가 없으면, 새로운 키워드로 등록함.
PREPARE s2 FROM 'INSERT INTO Keywords (keyword) VALUES (?)'; -- <callout id="code.search.insert1"/>
EXECUTE s2 USING @keyword;
DEALLOCATE PREPARE s2;
-- 3. Keywords에서 생성된 PK 값을 조회함.
SELECT LAST_INSERT_ID() INTO @k; -- <callout id="code.search.lastid"/>
-- 4. Bugs에서 새로운 키워드를 포함하는 행을 검색해 교차 테이블에 넣는다.
PREPARE s3 FROM 'INSERT INTO BugsKeywords (bug_id, keyword_id) -- <callout id="code.search.insert2"/>
SELECT bug_id, ? FROM Bugs
WHERE summary REGEXP CONCAT(''[[:<:]]'', ?, ''[[:>:]]'')
OR description REGEXP CONCAT(''[[:<:]]'', ?, ''[[:>]]'')';
EXECUTE s3 USING @k, @keyword, @keyword;
DEALLOCATE PREPARE s3;
END IF;
-- 5. 마지막으로, 키워드를 찾았든 새로운 항목을 추가했든 keyword_id를 이용해 Bugs의 전체 행을 조회함.
PREPARE s4 FROM 'SELECT b.* FROM Bugs b -- <callout id="code.search.select"/>
JOIN BugsKeywords k USING (bug_id)
WHERE k.keyword_id = ?';
EXECUTE s4 USING @k;
DEALLOCATE PREPARE s4;
END
CALL BugsSearch('crash');
DELIMITER //
CREATE TRIGGER Bugs_Insert AFTER INSERT ON Bugs
FOR EACH ROW
BEGIN
INSERT INTO BugsKeywords (bug_id, keyword_id)
SELECT NEW.bug_id, k.keyword_id FROM Keywords k
WHERE NEW.description REGEXP CONCAT('[[:<:]]', k.keyword, '[[:>:]]')
OR NEW.summary REGEXP CONCAT('[[:<:]]', k.keyword, '[[:>:]]');
END;
모든 문제를 SQL로 풀어야 하는 것은 아니다.
1. techxplore
2. oracle-base