Database Software

Hi,

Could you please guide me whether the following approch is right way to implement my requirment, I'm working for one of the fuzzy search requiremnet, My table contains 100 million rows with varchar2 datatype, for this i have been created the following steps,


1. Convert the table data in to XML documents with help of following pl/sql procedure


CREATE OR REPLACE PACKAGE Chrdscr IS
p_dir_name varchar2(13) := '/opt/app/log/';
procedure OpenXmlFile(p_dir_name varchar2);
END Chrdscr;




CREATE OR REPLACE PACKAGE Chrdscr IS
p_dir_name varchar2(13) := '/opt/app/log/';
procedure OpenXmlFile(p_dir_name varchar2);
END Chrdscr;

create or replace package body Chrdscr is

v_FILENAME varchar2(30);
f_XML_FILE UTL_FILE.file_type;

procedure OpenXmlFile(p_dir_name varchar2) is
v_record_data varchar2(4000) := null;
v_DSCR varchar2(4000) := null;

cursor orders_cursor is
select t.dscr
from CHR_DSCR_T t;

begin
--v_FILENAME := TO_CHAR(SYSDATE, 'DDMMYYYYHH24MI') || '.xml';
v_FILENAME := 'chrdscr.xml';

f_XML_FILE := UTL_FILE.fopen('DATA_PUMP_DIR', v_FILENAME, 'W');

v_RECORD_DATA := '<?xml version="1.0" encoding="UTF-8"?>';
UTL_FILE.put_line(f_XML_FILE, v_RECORD_DATA);

open orders_cursor;
loop
fetch orders_cursor
into v_DSCR;
EXIT WHEN orders_cursor%NOTFOUND;

UTL_FILE.put_line(f_XML_FILE,
' <DSCR>' || v_DSCR || '</DSCR>');

end loop;
close orders_cursor;

UTL_FILE.FCLOSE(f_XML_FILE);

EXCEPTION
WHEN UTL_FILE.INTERNAL_ERROR THEN
raise_application_error(-20500,
'Cannot open file :' || v_FILENAME ||
', internal error; code:' || sqlcode ||
',message:' || sqlerrm);
WHEN UTL_FILE.INVALID_OPERATION THEN
raise_application_error(-20501,
'Cannot open file :' || v_FILENAME ||
', invalid operation; code:' || sqlcode ||
',message:' || sqlerrm);
WHEN UTL_FILE.INVALID_PATH THEN
raise_application_error(-20502,
'Cannot open file :' || v_FILENAME ||
', invalid path; code:' || sqlcode ||
',message:' || sqlerrm);
WHEN UTL_FILE.WRITE_ERROR THEN
raise_application_error(-20503,
'Cannot write to file :' || v_FILENAME ||
', write error; code:' || sqlcode ||
',message:' || sqlerrm);
end;

end Chrdscr;


PL/SQL procedure successfully completed.


2. The XML document created in the directory, after that i have created one table



CREATE TABLE testtab
(id NUMBER,
docs VARCHAR2 (30))
/

Table created.


INSERT INTO testtab VALUES (1, 'chrdscr.xml')
/

1 row created.



3. Context type index also created for the XML document

CREATE INDEX otiind ON testtab (docs)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('DATASTORE otipref')
/

Index created.



SQL> SELECT token_text FROM dr$otiind$i

2 ;



TOKEN_TEXT

----------------------------------------------------------------

1.0

1888

13654
125678.6

888

999999

DATA

DIRECT

ENCODING

UTF

VERSION



TOKEN_TEXT

----------------------------------------------------------------

XML



12 rows selected.



5. then i executed the follwing step


begin
chrdscr.OpenXmlFile('opt/app/log/');
end;



SELECT id FROM mytab
2 WHERE CONTAINS (docs, 'data') > 0
3 /

ID
--------------------------------------------------------------------------------
1

Could you please advice me whether this approch will satify the fuzzy search requirment? your help is very much helpful to implement this.