...
| Code Block | ||
|---|---|---|
| ||
Mach> CREATE TABLE search_table (id INTEGER, name VARCHAR(20)); Created successfully. Mach> CREATE KEYWORD INDEX idx_SEARCH ON SEARCH_table (name) INDEX_TYPE KEYWORD; Created successfully. Mach> INSERT INTO search_table VALUES(1, 'time flys'); 1 row(s) inserted. Mach> INSERT INTO search_table VALUES(1, 'time runs'); 1 row(s) inserted. Mach> SELECT * FROM search_table WHERE name SEARCH 'time' OR name SEARCH 'runs2' ; ID NAME ------------------------------------- 1 time runs 1 time flys [2] row(s) selected. Mach> SELECT * FROM search_table WHERE name SEARCH 'time' AND name SEARCH 'runs2' ; ID NAME ------------------------------------- [0] row(s) selected. Mach> SELECT * FROM search_table WHERE name SEARCH 'flys' OR name SEARCH 'runs2' ; ID NAME ------------------------------------- 1 time flys [1] row(s) selected. |
...
마크베이스는 ASCII와 UTF-8로 저장된 여러 가지 종류의 언어의 가변길이 문자열에 대한 검색이 가능하다. 한국어나 일본어와 같은 언어의 문장에서 일부분만을 검색하기 위해서, 2-gram 기법을 이용한다.
SyxtaxSyntax:
| Code Block | ||
|---|---|---|
| ||
SELECT column_name(s) FROM table_name WHERE column_name SEARCH pattern; |
...
| Code Block | ||
|---|---|---|
| ||
Mach> CREATE tableTABLE multi_table (message varchar(100)); Created successfully. Mach> CREATE KEYWORD INDEX idx_multi ON multi_table(message)INDEX_TYPE KEYWORD; Created successfully. Mach> INSERT INTO multi_table VALUES("Machbase is the combination of ideal solutions"); 1 row(s) inserted. Mach> INSERT INTO multi_table VALUES("Machbase is a columnar DBMS"); 1 row(s) inserted. Mach> INSERT INTO multi_table VALUES("Machbaseは理想的なソリューションの組み合わせです"); 1 row(s) inserted. Mach> INSERT INTO multi_table VALUES("Machbaseは円柱状のDBMSです"); 1 row(s) inserted. Mach> SELECT * from multi_table WHERE message SEARCH 'Machbase DBMS'; MESSAGE ------------------------------------------------------------------------------------ Machbaseは円柱状のDBMSです Machbase is a columnar DBMS [2] row(s) selected. Mach> SELECT * from multi_table WHERE message SEARCH 'DBMS is'; MESSAGE ------------------------------------------------------------------------------------ Machbase is a columnar DBMS [1] row(s) selected. Mach> SELECT * from multi_table WHERE message SEARCH 'DBMS' OR message SEARCH 'ideal'; MESSAGE ------------------------------------------------------------------------------------ Machbaseは円柱状のDBMSです Machbase is a columnar DBMS Machbase is the combination of ideal solutions [3] row(s) selected. Mach> SELECT * from multi_table WHERE message SEARCH '組み合わせ'; MESSAGE ------------------------------------------------------------------------------------ Machbaseは理想的なソリューションの組み合わせです [1] row(s) selected. Elapsed time: 0.001 Mach> SELECT * from multi_table WHERE message SEARCH '円柱'; MESSAGE ------------------------------------------------------------------------------------ Machbaseは円柱状のDBMSです [1] row(s) selected. |
...
| Code Block | ||
|---|---|---|
| ||
Mach> CREATE TABLE esearch_table(id INTEGER, name VARCHAR(20), data VARCHAR(40)); Created successfully. Mach> CREATE KEYWORD indexINDEX idx1 ON esearch_table(name) INDEX_TYPE KEYWORD; Created successfully. Mach> CREATE KEYWORD indexINDEX idx2 ON esearch_table(data) INDEX_TYPE KEYWORD; Created successfully. Mach> INSERT INTO esearch_table VALUES(1, 'machbase', 'Real-time search technology'); 1 row(s) inserted. Mach> INSERT INTO esearch_table VALUES(2, 'mach2flux', 'Real-time data compression'); 1 row(s) inserted. Mach> INSERT INTO esearch_table VALUES(3, 'DB MS', 'Memory cache technology'); 1 row(s) inserted. Mach> INSERT INTO esearch_table VALUES(4, 'ファ ッションアドバイザー、', 'errors'); 1 row(s) inserted. Mach> INSERT INTO esearch_table VALUES(5, '인피 니 플럭스', 'socket232'); 1 row(s) inserted. Mach> SELECT * FROM esearch_table where name ESEARCH '%mach'; ID NAME DATA -------------------------------------------------------------------------------- 1 machbase Real-time search technology [1] row(s) selected. Elapsed time: 0.001 Mach> SELECT * FROM esearch_table where data ESEARCH '%echn%'; ID NAME DATA -------------------------------------------------------------------------------- 3 DB MS Memory cache technology 1 machbase Real-time search technology [2] row(s) selected. Mach> SELECT * FROM esearch_table where name ESEARCH '%피니%럭스'; ID NAME DATA -------------------------------------------------------------------------------- [0] row(s) selected. Mach> SELECT * FROM esearch_table where data ESEARCH '%232'; ID NAME DATA -------------------------------------------------------------------------------- 5 인피 니 플럭스 socket232 [1] row(s) selected. |
...
REGEXP 연산자는 정규표현식을 통하여 데이터에 대한 텍스트 검색을 수행하기 위해서 사용된다. REGEXP 연산자는 대상 칼럼에 정규표현식을 수행하여 실행되며, 색인을 사용할 수 없기 때문에, 검색 성능이 저하될 수 있다. 따라서 , 검색 속도를 향상시키기 위해 색인을 사용할 수 있는 다른 검색 조건을 AND 연산자로 추가하여 사용하는 것이 좋다.
...
| Code Block | ||
|---|---|---|
| ||
Mach> CREATE TABLE liklike_table (id INTEGER, name VARCHAR(20), data VARCHAR(40)); Created successfully. Mach> INSERT INTO liklike_table VALUES(1, 'machbase', 'Real-time search technology'); 1 row(s) inserted. Mach> INSERT INTO liklike_table VALUES(2, 'mach2fluxmach2base', 'Real-time data compression'); 1 row(s) inserted. Mach> INSERT INTO liklike_table VALUES(3, 'DBMS', 'Memory cache technology'); 1 row(s) inserted. Mach> INSERT INTO liklike_table VALUES(4, 'ファ ッションアドバイザー、', 'errors'); 1 row(s) inserted. Mach> INSERT INTO liklike_table VALUES(5, '인피 니 플럭스', 'socket232'); 1 row(s) inserted. Mach> SELECT * FROM liklike_table WHERE name LIKE 'mach%'; ID NAME DATA -------------------------------------------------------------------------------- 2 mach2base Real-time data compression 1 machbase Real-time search technology [2] row(s) selected. Mach> SELECT * FROM liklike_table WHERE name LIKE '%니%'; ID NAME DATA -------------------------------------------------------------------------------- 5 인피 니 플럭스 socket232 [1] row(s) selected. Mach> SELECT * FROM liklike_table WHERE data LIKE '%technology'; ID NAME DATA -------------------------------------------------------------------------------- 3 DBMS Memory cache technology 1 machbase Real-time search technology [2] row(s) selected. |
...