Full Text Oracle

• Oracle, and others, have implemented full-text indexes within the database.

create table content (
        content_id              integer primary key,
        refers_to               references content_raw,
        -- who contributed this and when
        creation_user           not null references users,
        creation_date           not null date,
        modified_date           not null date,
        mime_type               varchar(100) not null,
        one_line_summary        varchar(200) not null,
        body                    clob,
        editorial_status        varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired'))
);

-- create an Oracle Text index (the product used to be called
-- Oracle Context, hence the CTX prefixes on many procedures)

create index content_text 
        on content(body) 
        indextype is ctxsys.context;

-- let's look at opinions on running shoes from 
-- users who registered in the last 30 days, sorting
-- results in order of decreasing relevance

select 
  score(1), 
  content.content_id, 
  content.one_line_summary, 
  users.first_names,
  users.last_name
        from content, users
        where contains(body, 'running shoes', 1) > 0
        and users.registration_date > current_timestamp - interval '30' day
        and content.creation_us
        er = users.user_id
        order by score(1) desc;

--The score(1) function refers to the contains() call

• Oracle provides filters that extract indexable text from .xls, .doc, .pdf and other file types.