در نسخه 23c، اوراکل پکیجی را به نام DBMS_SEARCH معرفی کرده است که می تواند با استفاده از زیرساخت ORACLE TEXT و حذف پیچیدگی های آن، امکان جستجو را بر روی Objectهای مختلف فراهم کند. پکیج DBMS_SEARCH با ایجاد ایندکسی از نوع JSON Search Index می تواند قابلیت جستجو را بر روی sourceهای مختلف اعم از Table و View فراهم کند.

برخلاف ایندکسهای متعارف نظیر Btree و Bitmap که بر روی ستونهای یک جدول قابل ایجاد هستند، ایندکسی که از طریق پکیج DBMS_SEARCH ایجاد می شود، می تواند چندین جدول و ویو را به عنوان source بپذیرد و جستجوی همزمان را بر روی این sourceها انجام دهد.

جداولی که به عنوان سورس تعیین می شوند می توانند حاوی ستونهایی با نوع داده number، varchar، CLOB، JSON و … باشند و محدودیتهای بسیاری کمی در این زمینه وجود دارد ضمنا اضافه کردن source به ایندکس به راحتی و صرفا با اجرای یک دستور امکان پذیر است.

توجه! ایندکسهایی که از طریق این پکیج ایجاد می شوند، بروزرسانی آنها به صورت خودکار انجام می شود(sync on commit).

در ادامه با نحوه ایجاد این نوع از ایندکس، اضافه کردن source به آن و همچنین انجام جستجو از طریق آن آشنا خواهیم شد.

در ابتدا جداول و ویوهایی را ایجاد می کنیم تا بتوانیم از این جداول و ویو به عنوان sourceهای ایندکسی که از طریق DBMS_SEARCH ایجاد می کنیم، استفاده کنیم:

SQL> create table IranTBL1(id number primary key,des varchar2(1000));
Table created.
SQL> insert into IranTBL1 values(1,'www.usefzadeh.com');
1 row created.

SQL> create table IranTBL2(id number primary key,des1 varchar2(1000),des2 CLOB,des3 JSON);
Table created.
SQL>  insert into IranTBL2 values(1,'My name is Vahid','this is a test', '{"name":"vahid", "last_name" :"usefzadeh"}');
1 row created.

در قدم اول، با استفاده از پروسیجر CREATE_INDEX از بسته DBMS_SEARCH ایندکسی را ایجاد می کنیم در زمان ساخت ایندکس می توان tablespaceای را هم برای آن تنظیم نمود:

SQL> create tablespace TBLSP_Search_IDX ;
Tablespace created.
SQL> exec DBMS_SEARCH.CREATE_INDEX(index_name =>'Full_Search_IDX' ,tablespace =>'TBLSP_Search_IDX' );
PL/SQL procedure successfully completed

SQL> desc FULL_SEARCH_IDX
Name         Null?    Type             
 -----------  -------- ---------------- 
 METADATA     NOT NULL JSON             
 DATA                  JSON             
 OWNER                 VARCHAR2(128)    
 SOURCE                VARCHAR2(128)    
 KEY                   VARCHAR2(1024)   

نام ایندکس Full_Search_IDX است و از طریق ویوی DBA_JSON_INDEXES می توانیم اطلاعات مربوط به این نوع از ایندکسها را مشاهده کنیم. البته DBA/ALL/USER_INDEXES هم این ایندکس را به عنوان DOMAIN index می شناسد:

SQL> select INDEX_NAME,TABLE_NAME,COLUMN_NAME from DBA_JSON_INDEXES where INDEX_OWNER='USEF';
INDEX_NAME             TABLE_NAME             COLUMN_NAME
---------------------- ---------------------- ------------------------------
FULL_SEARCH_IDX        FULL_SEARCH_IDX        DATA
DR$FULL_SEARCH_IDX$BK  FULL_SEARCH_IDX        METADATA

SQL> select index_name,index_type,table_owner,table_name,ityp_owner,ityp_name from user_indexes where index_name='FULL_SEARCH_IDX';
INDEX_NAME           INDEX_TYPE TABLE_OWNE TABLE_NAME           ITYP_OWNER ITYP_NAME
-------------------- ---------- ---------- -------------------- ---------- --------------------
FULL_SEARCH_IDX      DOMAIN     USEF       FULL_SEARCH_IDX      CTXSYS     CONTEXT_V2

مابه‏ ازای ایندکس ایجاد شده فوق، جدولی هم با همین نام ایجاد می شود بعلاوه 13 جدول دیگر که اسامی همه آنها با $DR شروع می شود:

SQL> select table_name from user_tables where table_name like '%FULL_SEARC%';
TABLE_NAME
----------------------
FULL_SEARCH_IDX
DR$FULL_SEARCH_IDX$B
DR$FULL_SEARCH_IDX$C
DR$FULL_SEARCH_IDX$DG
DR$FULL_SEARCH_IDX$G
DR$FULL_SEARCH_IDX$I
DR$FULL_SEARCH_IDX$K
DR$FULL_SEARCH_IDX$KG
DR$FULL_SEARCH_IDX$N
DR$FULL_SEARCH_IDX$Q
DR$FULL_SEARCH_IDX$SN
DR$FULL_SEARCH_IDX$ST
DR$FULL_SEARCH_IDX$SV
DR$FULL_SEARCH_IDX$U
14 rows selected.

در مرحله بعد باید sourceهای ایندکس را مشخص کنیم source می تواند جدول و یا view باشد. با استفاده از پروسیجر DBMS_SEARCH.ADD_SOURCE می توانیم سورسی را به ایندکس اضافه کنیم:

SQL> exec DBMS_SEARCH.ADD_SOURCE(index_name =>'FULL_SEARCH_IDX' ,source_name => 'IranTBL1' );
PL/SQL procedure successfully completed

توجه! جداول source باید primary key داشته باشند.

بعد از اضافه کردن جدول IranTBL1، محتویات ایندکس را بررسی می کنیم:

SQL>select  owner,source,key,metadata from FULL_SEARCH_IDX;
OWNER SOURCE     KEY        METADATA
----- ---------- ---------- ------------------------------------------------------------
USEF  IRANTBL1   {"ID":1}   {"OWNER":"USEF","SOURCE":"IRANTBL1","KEY":{"ID":1}}

از طریق تابع DBMS_SEARCH.GET_DOCUMENT می توانیم اطلاعات ایندکس شده را هم ببینیم:

SQL> select json_serialize(DBMS_SEARCH.GET_DOCUMENT('FULL_SEARCH_IDX',METADATA)) FULL_SEARCH_IDX from FULL_SEARCH_IDX;
FULL_SEARCH_IDX
--------------------------------------------------------- 
{"USEF":{"IRANTBL1":{"ID":1,"DES":"www.usefzadeh.com"}}}

رکوردهای دیگری هم به جدول source اضافه می کنیم:

SQL> insert into IRANTBL1 values(2,'Ramtollah Rabbani');
1 row created.
SQL> insert into IRANTBL1 values(3,'Armin Bahamin');
1 row created.
SQL> commit;
Commit complete.

بلافاصله دیتای ایندکس هم sync خواهد شد:

SQL> select json_serialize(DBMS_SEARCH.GET_DOCUMENT('FULL_SEARCH_IDX',METADATA)) FULL_SEARCH_IDX from FULL_SEARCH_IDX;

با عبارت contains می توانیم در اطلاعات فوق جستجو انجام دهیم:

SQL> select json_serialize(DBMS_SEARCH.GET_DOCUMENT('FULL_SEARCH_IDX',METADATA)) FULL_SEARCH_IDX from FULL_SEARCH_IDX where contains(data, '%usef%')>0;
FULL_SEARCH_IDX
------------------------------------------------------------
{"USEF":{"IRANTBL1":{"ID":1,"DES":"www.usefzadeh.com"}}}

این جستجو از طریق تابع JSON_TEXTCONTAINS هم قابل انجام است:

SQL> select json_serialize(DBMS_SEARCH.GET_DOCUMENT('FULL_SEARCH_IDX',METADATA)) FULL_SEARCH_IDX from FULL_SEARCH_IDX where json_textcontains(data, '$.USEF.IRANTBL1.DES', '%Ramtollah%');
FULL_SEARCH_IDX
------------------------------------------------------------
{"USEF":{"IRANTBL1":{"ID":2,"DES":"Ramtollah Rabbani"}}}

در ادامه جدول IRANTBL2 را هم به source ایندکس FULL_SEARCH_IDX اضافه می کنیم:

SQL> exec DBMS_SEARCH.ADD_SOURCE(index_name =>'FULL_SEARCH_IDX' ,source_name => 'IranTBL2' );
PL/SQL procedure successfully completed

SQL> select  owner,source,key,metadata from FULL_SEARCH_IDX;
OWNER SOURCE     KEY        METADATA
----- ---------- ---------- ------------------------------------------------------------
USEF  IRANTBL1   {"ID":1}   {"OWNER":"USEF","SOURCE":"IRANTBL1","KEY":{"ID":1}}
USEF  IRANTBL1   {"ID":2}   {"OWNER":"USEF","SOURCE":"IRANTBL1","KEY":{"ID":2}}
USEF  IRANTBL1   {"ID":3}   {"OWNER":"USEF","SOURCE":"IRANTBL1","KEY":{"ID":3}}
USEF  IRANTBL2   {"ID":1}   {"OWNER":"USEF","SOURCE":"IRANTBL2","KEY":{"ID":1}}

با اضافه کردن جدول IranTBL2 به عنوان source دوم، ایندکس FULL_SEARCH_IDX اطلاعات هر دو جدول را نمایش خواهد داد:

SQL> select json_serialize(DBMS_SEARCH.GET_DOCUMENT('FULL_SEARCH_IDX',METADATA)) FULL_SEARCH_IDX from FULL_SEARCH_IDX;
FULL_SEARCH_IDX
----------------------------------------------------------------------------------------------------------------------------------
{"USEF":{"IRANTBL1":{"ID":1,"DES":"www.usefzadeh.com"}}}
{"USEF":{"IRANTBL1":{"ID":2,"DES":"Ramtollah Rabbani"}}}
{"USEF":{"IRANTBL1":{"ID":3,"DES":"Armin Bahamin"}}}
{"USEF":{"IRANTBL2":{"ID":1,"DES1":"My name is Vahid","DES2":"this is a test","DES3":{"name":"vahid","last_name":"usef"}}}}

و جستجو هم می تواند بر روی اطلاعات هر دو جدول انجام شود:

SQL> select json_serialize(DBMS_SEARCH.GET_DOCUMENT('FULL_SEARCH_IDX',METADATA)) FULL_SEARCH_IDX from FULL_SEARCH_IDX where json_textcontains(data, '$.USEF.IRANTBL2.DES3', '%usef%');

SQL> select json_serialize(DBMS_SEARCH.GET_DOCUMENT('FULL_SEARCH_IDX',METADATA)) FULL_SEARCH_IDX from FULL_SEARCH_IDX where contains(data, '%am%')>0;

امکان جستجو به صورت زیر و نمایش خروجی به صورت Relational هم وجود دارد که یکی از کاربردهای مهم آن می تواند جستجو در دیتای با نوع داده LOB باشد:

select b.id,b.des
 from FULL_SEARCH_IDX a, IRANTBL1 b 
where b.id = a.metadata."KEY"."ID" 
  and json_textcontains(data, '$.USEF.IRANTBL1.DES', '%usef%');
   ID DES
----- --------------------
    1 www.usefzadeh.com

برای حذف هر کدام از sourceها می توانیم از تابع DBMS_SEARCH.REMOVE_SOURCE استفاده کنیم:

SQL> exec DBMS_SEARCH.REMOVE_SOURCE(index_name => 'FULL_SEARCH_IDX',source_name =>'IranTBL2');
PL/SQL procedure successfully completed

برای حذف ایندکس هم می توانیم از دستور زیر استفاده کنیم:

SQL> exec DBMS_SEARCH.DROP_INDEX(index_name => 'Full_Search_IDX');
PL/SQL procedure successfully completed