CERN European Organization for Nuclear Research Administrative Support

CERN – European Organization for Nuclear Research Administrative Support - Advanced Information Systems Introduction to Oracle inter. Media -Text By Derek Mathieson (AS-IDS)

The Problem l Find ! W O L X by keyword Y SELECT cod FROM bud_codes WHERE desc LIKE ‘%EDH%’; S O SELECT doc_id FROM edh_docs WHERE UPPER(short_desc) LIKE ‘%PRINTER%’; O T CERN AIS f 1

The Solution Oracle inter. Media-Text CERN AIS

Searching with inter. Media-Text l Rewritten query using inter. Media- Text. SELECT doc_id FROM edh_docs WHERE CONTAINS( short_desc, ‘%PRINTER%’, 1) > 0; CERN AIS d 3, d 4, d 5

Query Operators l Algebraic l Dictionary l Other CERN AIS

Algebraic Query Operators l AND, OR, NOT, MINUS Example: l monitor NOT flat l AS-IDS OR AS-SAS CERN AIS d 6

Dictionary Query Operators l ABOUT(subatomic particles) matches text on the subject of physics l Broader, Narrower, Related or Preferred Term BT(dog) Matches ‘dog’, ‘mammal, ‘animal’ CERN AIS d 7

Dictionary Query Operators l Stem stem(sing) matches ‘sing’ ‘sung’ or ‘sang’ l Synonym SYN(tiger) matches ‘tiger’ ‘cat’, etc. l Translated Term TR(chien) matches ‘chien’ or ‘dog’ CERN AIS

Other Query Operators l fuzzy, soundex ? apply matches ‘apply’ ‘apple’ ‘applied’ ‘April’ l Wildcards math%, %day, %th%, _ing CERN AIS name

Other Query Operators l NEAR((word 1, [, order]]) word 2, . . . ) [, max_span NEAR((monday, tuesday, wednesday), 20, TRUE) l WITHIN <TITLE>Workflow</TITLE> Business process automation… workflow WITHIN TITLE Derek WITHIN AUTHOR WITHIN BOOK CERN AIS

Sorting by Relevance SELECT doc_id FROM edh_docs WHERE CONTAINS( short_desc, ‘DOG, CAT, MOUSE’, 1) > 0; CERN AIS

Sorting by Relevance SELECT doc_id FROM edh_docs WHERE CONTAINS( short_desc, ‘DOG, CAT, MOUSE’, 1) > 0 ORDER BY SCORE(1) DESC; CERN AIS

Sorting by Relevance SELECT /*+ FIRST_ROWS */ doc_id FROM edh_docs WHERE CONTAINS( short_desc, ‘DOG, CAT, MOUSE’, 1) > 0 ORDER BY SCORE(1) DESC; CERN AIS acid

Creating Indexes l Now built in to Oracle kernel l NOT automatically updated after DML – Manually refreshed – ctxsrv process create index edh_docs_idx on edh_docs( short_desc ) indextype is ctxsys. context CERN AIS

Data Sources l Column data (VARCHAR, CLOB, etc. ) l Detail Table l External File l URL CERN AIS

Data Types l Text – ASCII, HTML, XML, … l Microsoft – RTF, Word, Works, Power. Point, Excel, Access, … l Other – PDF, Word. Perfect, Lotus 1 -2 -3, Mac. Write, Quattro. Pro, d. BASE, … l Over CERN AIS 160 different File Formats!

Applications l EDH Search Screens – Supplier by keyword, or partial address – Budget Code by description – Document by short description – Document by full text? What about YOUR application? CERN AIS

Thank You For More Information Browse to: http: //technet. oracle. com /training/products/intermedia/listing. htm CERN AIS