User Tools

Site Tools


kurs:regex_bei_oracle_9
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 

ersetzt

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 

mit dem deutlich performanteren

   select *
   from   personen
   where  (
              contains(per_name,         p_suchname)>0 
kurs/regex_bei_oracle_9.txt · Last modified: 2014/09/10 21:22 (external edit)