This shows you the differences between two versions of the page.
— |
kurs:regex_bei_oracle_9 [2014/09/10 21:22] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | <code> | ||
+ | set echo on | ||
+ | col comp_name for a30 | ||
+ | spool textinstall.log | ||
+ | Rem ======================================================================= | ||
+ | Rem Start of Text loading | ||
+ | Rem ======================================================================= | ||
+ | EXECUTE dbms_registry.loading('CONTEXT', 'Oracle Text'); | ||
+ | Rem dr0csys.sql <CTXSYS_password> <CTXSYS_TS_NAME> <TEMP_TS_NAME> | ||
+ | start ?/ctx/admin/dr0csys ctxsys DRSYS TEMP | ||
+ | |||
+ | *** NOTE: We assume that DRSYS tablespace already exists, if not create a | ||
+ | *** tablespace for Oracle Text data dictionary tables, for example: | ||
+ | CREATE TABLESPACE drsys DATAFILE '/oradata/NIC9/drsys01.dbf' SIZE 40m; | ||
+ | |||
+ | REM ======================================================================== | ||
+ | REM Install CTXSYS objects | ||
+ | REM ======================================================================== | ||
+ | connect CTXSYS/ctxsys | ||
+ | start ?/ctx/admin/dr0inst /oracle/product/9.2.0/ctx/lib/libctxx9.so | ||
+ | start ?/ctx/admin/defaults/drdefus.sql | ||
+ | |||
+ | REM ======================================================================== | ||
+ | REM Upgrade CTXSYS to the latest patchset version | ||
+ | REM ======================================================================== | ||
+ | connect SYS/password as SYSDBA | ||
+ | start ?/ctx/admin/ctxpatch.sql | ||
+ | select comp_name, version, status from dba_registry; | ||
+ | spool off | ||
+ | exit; | ||
+ | |||
+ | vorher | ||
+ | |||
+ | CREATE INDEX per_NAME_CTX ON personen | ||
+ | ( per_name ) indextype is ctxsys.context | ||
+ | </code> | ||
+ | |||
+ | ersetzt | ||
+ | |||
+ | <code> | ||
+ | select * | ||
+ | from personen | ||
+ | where ( | ||
+ | upper(per_name) like '% '|| p_suchname ||' %' | ||
+ | or upper(per_name) like p_suchname ||' %' | ||
+ | or upper(per_name) like '% '|| p_suchname | ||
+ | or upper(per_name) = p_suchname | ||
+ | </code> | ||
+ | |||
+ | mit dem deutlich performanteren | ||
+ | |||
+ | <code> | ||
+ | select * | ||
+ | from personen | ||
+ | where ( | ||
+ | contains(per_name, p_suchname)>0 | ||
+ | </code> |