MEDLINE in Oracle XML-DB and Oracle Text Peter

MEDLINE in Oracle XML-DB and Oracle Text Peter Stoehr Head of Database Operations European Bioinformatics Institute (EBI) www. ebi. ac. uk Oracle Life Sciences, Oracle. World, San Francisco, Sep 10 2003

European Molecular Biology Laboratory (EMBL) International network of research institutes dedicated to research in molecular biology Treaty organisation funded by 16 member states Headquarters established in Heidelberg in 1974 research programmes in cell biology, developmental biology, instrumentation, gene expression etc Outstations Hamburg and Grenoble: structural biology Monterotondo: mouse genetics Hinxton, EBI: bioinformatics

European Bioinformatics Institute (EBI) Mandate to ensure the growing body of data and information from molecular biology and genome research is placed in the public domain and is accessible freely to all facets of the scientific community in ways that promote scientific progress and global competitiveness to support academic research as well as biotech, agricultural, chemical and pharmaceutical industries The EBI builds, develops and publishes databases and information services relevant to molecular biology, as well as conducting research in bioinformatics.

About EBI § Located in Hinxton, Cambridge, England (since 1993) § Non-profit organisation - part of EMBL § Started as EMBL Data Library in 1980 § Centre for research and services in bioinformatics § Three branches: Services, Research, Industry § Funding mainly from EMBL and EU

EBI Resources Personnel - 220 people - ~100 Database developers, software engineers - DBA - 4 - Systems - 6 Environment - OS: Tru 64 ES 45 s, Solaris - Storage: SAN storage, Net. App NFS - 350 -cpu linux compute farm

Major public databases at the EBI § § § DNA sequences protein sequences genome annotation protein structures microarrays literature - EMBL Nucleotide Sequence Database - SWISS-PROT, Tr. EMBL, Interpro, Clu. STr - Ensembl - MSD - Array. Express - MEDLINE, patents, fulltext § enzymes - Int. Enz § protein interactions - Int. Act § immunogenetics - IMGT, HLA § integration - Integr 8, Ens. Mart

Oracle Production Instances Database Instance cpu Oracle size tables EMBL PRDB 1 4 8. 1. 7. 2 350 300 SVA ERD 4 8. 1. 7. 2 550 6 SWISS-PROT PRDB 1 4 8. 1. 7. 2 8 125 Interpro IPRO 4 8. 1. 7. 2 8 130 Clustr CLUSTR 4 8. 1. 7. 2 40 24 MSD 280 475 MEDLINE 4 9. 2. 0. 3 120 10 GO IPRO 4 8. 1. 7. 2 1 15 +10 … … …

Statistics 26 million nucleotide sequences (25 b bases) 1 million protein sequences 200 complete genomes (+viruses, organelles) 28, 000 genes in human genome 10, 000 protein 3 D structures 2500 journals with sequences 12 million MEDLINE citations, 4500 journals 130, 000 biotech patent documents 550, 000 web hits per day, www. ebi. ac. uk

EBI interest in text resources • provide links from factual databases to full-text literature (journals, patents…) • mine for information relevant to factual database annotation. • most scientific information buried in free text resources • enable indexing and searching of full-text literature

ID XX AC XX SV XX DT DT XX DE XX KW XX OS OC OC XX RN RA RT RL RL XX FH FH FT FT FT AX 067464 standard; DNA; PRO; 100848 BP. espacenet AX 067464; AX 067464. 1 24 -JAN-2001 (Rel. 66, Created) 24 -JAN-2001 (Rel. 66, Last updated, Version 1) Sequence 39 from Patent WO 0078968. . Moraxella catarrhalis Bacteria; Proteobacteria; gamma subdivision; Moraxellaceae; Moraxella. [1] Lagace R. E. , Patterson C. , Berg K. L. ; "Nucleotide sequences of moraxella catarrhalis genome"; Patent number WO 0078968 -A/39, 28 -DEC-2000. Incyte Genomics, Inc. (US). Key Location/Qualifiers source 1. . 100848 /db_ xref="taxon: 480" /organism=" Moraxella catarrhalis" Searches based on bibliographic data in patent documents Full text documents - PDF files

EBI interest in text resources • provide access from factual databases to full-text literature (journals, patents…) • mine for information relevant to factual database annotation. • most scientific information buried in free text resources • enable indexing and intelligent searching of fulltext literature

Areas of improvement for public text resources • improve text retrieval functionality • improve and add text corpora • use of thesauri and ontologies (UMLS, SNOMED, GOBO) • interfaces

Text corpora • MEDLINE • Full-text literature • AGRICOLA • Biotech patent abstracts • Biotech patent full-text • OMIM • The web • => public searchable services

Search engine evaluation criteria Speed of searches Speed of indexing Ability to search multiple data sources and formats, MEDLINE in XML EMBL/SWISS-PROT-type structured files websites Word, PDF and text, email/jitterbug files RDBMS (ORACLE and My. SQL, Postgres) Ability to handle large database/collections Text query functions Natural language Boolean operators Phrase searches Proximity searches Use of synonyms, ontologies, thesauri esp. UMLS/Me. SH, GO Stemming and wild-carding Multiple language support (for patent literature) Document clustering functionality Search refinenement, set operators Ranking of results (relevance, date) Weighting of search Scaleability of indexing, searching Load balancing on multiple nodes Parallel processing Incremental and off-line indexing Ease of use of APIs, documentation API languages, C/Java/Perl Interoperabilty with SRS Market strength of vendor Availability on multiple unix platforms esp. Tru 64, Linux and Solaris Technical support Licence costs and flexibility

Text search/extraction systems Altavista Verity Inktomi ASPseek Google Autonomy Thunderstone Excalibur Fulcrum SPSS/Lexi. Quest Stratify/”Purple Yogi” Dolphinsearch Quiver Oracle Text X-Mine - “Opus” Diogene incellico - “Cell Entity Browser” Collexis Alma Pharm. DM Linguamatics Inxight Clear. Forest APRSmart. Logik

Search engine implementation Verity K 2 - Red Hat Linux, 200 -cpu pc farm - MEDLINE XML parser built Oracle Text - Oracle 9 i. R 2 implementation - text indexing of titles and abstract - XML DB - Oracle Life Sciences Initiative - MEDLINE + weekly updates implemented - tuning, performance analysis - in use for internal sequence DB maintenance

MEDLINE • National Library of Medicine (NLM) Bethesda • 530 XML files, following NLM DTD • ~ 12 million citations published in over 4500 biomedical journals • Daily updates

MEDLINE record view in SRS

Patent abstract record view in SRS

Patent abstract in XML

Medline in XML <!DOCTYPE Medline. Citation. Set PUBLIC "-//NLM//DTD NLM Medline, 1 st November 2002//EN” "http: //www. nlm. nih. gov/databases/dtd/nlmmedline_021101. dtd> <Medline. Citation. Set> <Medline. Citation Owner="NLM” Status="Completed"> <Medline. ID>94033980</Medline. ID> <PMID>8219565</PMID> …. . . <Journal> <ISSN>1051 -0443</ISSN> <Journal. Issue> <Volume>4</Volume> <Issue>5</Issue> <Pub. Date> <Medline. Date>1993 Sep-Oct</Medline. Date> </Pub. Date> </Journal. Issue> </Journal> … </Medline. Citation> …………. . <Medline. Citation. Set>

Possible Approaches MEDLINE/Patent XML SRS Fast, efficient, Domain standard Lack of text query functions Verity K 2 Efficient text query, scaleable, industry standard Return data as XML, post -processing required Normalised relational tables Fast, efficient for simple query Pre-processing required, hard to maintain, lack of text query functions, requesting multiple joins for more info Oracle Text Efficient, support text query Return data as XML, postprocessing required Oracle XML-DB

Why Oracle XML DB for Medline ? Oracle 9 i. R 2 embedded XML features with DBMS • XMLType datatype LOB storage - maintains original XML byte for byte - can use an Oracle text index, support Xpath queries - flexible when schemas change object-relational storage - better performance, index specific fields - access to SQL features (constraints, indices etc) - DOM fidelity (ordering, namespaces, inhertitance…) - piecewise XML element update • XMLSchema support

Why Oracle Text for Medline ? Oracle 9 i embedded Text features with DBMS • Powerful and extensive text functions - wildcards, boolean, stemming, proximity searches, NLP linguistic features, pattern matching, ‘soundex’ • XML specific operators, HASPATH, INPATH etc, to support XPATH like expressions • Relevance ranking • Multi-lingual features • Extensions to SQL*Plus • Management of thesauri • Classification (CTXRULE indextype) • Unsupervised document Clustering • Documentation pretty good

Oracle RDBMS, XML DB and Text One product range - Cost (already purchased and used RDBMS) - lower complexity – common administration, training, backup, replication, RAC etc - lower latency of development/deployment - no incompatible product updates, gateways etc - greater performance for mixed queries

Prepare XMLSchema from DTD • NLM MEDLINE DTD • XML-Spy • To use Oracle CLOB type: <xs: schema xmlns: xs=http: //www. w 3. org/2001/XMLSchema xmlns: xdb=http: //xmlns. oracle. com/xdb element. Form. Default="qualified" xdb: store. Varray. As. Table="true"> <xs: element name="Abstract" xdb: SQLType="CLOB"/> • => XMLSchema

Register XMLSchema, create table begin dbms_xmlschema. registerschema( ’http: //www 3. ebi. ac. uk/internal/Services/medline/ medlinecitation_Types. xsd’, xdburitype(’/public/ medlinecitation_Types. xsd’). get. Clob(), TRUE, FALSE, TRUE); end;

Load data • SQL*Loader • We use a Java application, JDBC - need to control updates, deletions. • synchronize context index exec ctx_ddl. sync_index(‘title_ind’, ’ 40 M’); • Complete MEDLINE: - 1 day to load, 1 day for context indexing • Updates - 10 mins

Actual MEDLINE instances @EBI • 9 i. R 2 in Production - MEDLINE + patent abstracts- updated twice per week - used for in-house reference - CLOB storage of XMLType field - partitioned (by date) context index of XMLType • 9 i. R 2 in development - using structured object-relational storage - indexing fields, inc. context indextype for titles, abstracts - no partioning

MED production schema

Main Table: Medline. Citation #PMID Medline. Citation Pub. Year dummy number XMLType number clob Table is partitioned into 8 XMLtype Column is registered with XMLSchema, locally context type indexed

select m. Medline. Citation. get. Clob. Val() AS Med. XML from Medlinecitation m where pmid=8219565; --return a full XML document: <Medline. Citation Owner="NLM” Status="Completed"> <Medline. ID>94033980</Medline. ID> <PMID>8219565</PMID> …. . . <Article> <Journal> <ISSN>1051 -0443</ISSN> <Journal. Issue> <Volume>4</Volume> <Issue>5</Issue> <Pub. Date> <Medline. Date>1993 Sep-Oct</Medline. Date> </Pub. Date> </Journal. Issue> </Journal> <Article. Title>Transcatheter manipulation of asymmetrically opened titanium Green field filters. </Article. Title> <Pagination> <Medline. Pgn>687 -90</Medline. Pgn> </Pagination>…… <Article> … </Medline. Citation>

Select a part of XML document select extract (Medline. Citation, '/Medline. Citation/Article/Journal. Issue'). get. String. Val() ”Journal. Issue” from Medlinecitation where pmid= 11194419; <Journal. Issue> <Volume>40</Volume> <Issue>4</Issue> <Pub. Date> <Year>2000</Year> <Month>Nov</Month> </Pub. Date> </Journal. Issue>

Select just Abstract text select extract. Value (Medline. Citation, '/Medline. Citation/Article/Abstract. Text'). get. String. Val() “Abstract. Text” from Medlinecitation where pmid=8219565; Abstract. Text ----------------------------------------A case of amebic meningoencephalitis recognized in an adult Zambian is described. This is the first authenticated case from Africa. The morphologic features of the organism, its ability to form cysts in tissue, and the granulomatous tissue response denote that the ameba is an hartmannellid rather than a Naegleria. Free -living amebas of the family Hartmannellidae have not been incriminated before as a cause of primary amebic meningoencephalitis in man. To our knowledge this is the only case where such an ameba was responsible for fulminating meningoencephalitis. The presence of the amebas in a cellulocutaneous abdominal lesion sugges ts hematogenous dissemination.

Improvements - can create additional relational tables Citation Author PMID number primary key, Medline. ID number, Article. Title VARCHAR 2(1500), Volume VARCHAR 2(55), Issue VARCHAR 2(55), Start. Page VARCHAR 2(55), End. Page VARCHAR 2(55), Medline. Pgn VARCHAR 2(100), Pub. Year number, ISSN VARCHAR 2(9), Nlm. Unique. Id VARCHAR 2(25), Author. List. Complete. YN VARCHAR 2(1) PMID number foreign key, Last. Name VARCHAR 2(255), Initials VARCHAR 2(255), Suffix VARCHAR 2(25), Collective. Name VARCHAR 2(1000), Affiliation VARCHAR 2(1000), Rank number

Improvements – use O-R storage Can ‘context’ index whole XML table … create index MEDLINE_CITATION_INDEX on MEDLINE_CITATION_TABLE x (value(x)) indextype is ctxsys. context parameters(’storage med_storage lexer med_lex stoplist med_STOPLIST section group autogroup memory 400 M’); or just text fields: Create index Journal_Abstract_Index on MEDLINE_CITATION_TABLE c (extract. Value(val(m), ’/Medline. Citation/Article/Abstract. Text’)) indextype is ctxsys. context;

Improvements S-R storage - index specific fields create unique index MEDLINE_PMID_INDEX on MEDLINE_CITATION_TABLE m (extract. Value(value(m), '/Medline. Citation/PMID')) create index AUTHOR_LASTNAME_INDEX on ARTICLE_AUTHOR_TABLE a (a. "Last. Name") create index JOURNAL_ISSN_INDEX on MEDLINE_CITATION_TABLE c ( extract. Value(value(c), '/Medline. Citation/Article/Journal/ISSN') ) create index JOURNAL_VOLUME_INDEX on MEDLINE_CITATION_TABLE c (extract. Value(value(c), '/Medline. Citation/Article/Journal. Issue/Volume'), extract. Value(value(c), '/Medline. Citation/Article/Journal. Issue/Issue')) create index PAGINATION_INDEX on MEDLINE_CITATION_TABLE c ( extract. Value(value(c), '/Medline. Citation/Article/Pagination/Medline. Pgn'))

Improvements - create a view create or replace view Citation as select to_Number(extract. Value(value(m), '/Medline. Citation/PMID')) PMID, to_Number(extract. Value(value(m), '/Medline. Citation/Medline. ID')) Medline. Id, extract. Value(value(m), '/Medline. Citation/Article/Journal/ISSN') ISSN, extract. Value(value(m), '/Medline. Citation/Article/Journal. Issue/Volume') Volume, extract. Value(value(m), '/Medline. Citation/Article/Journal. Issue/Issue') Issue, extract. Value(value(m), '/Medline. Citation/Article/Pagination/Medline. Pgn') Medline. Pgn from MEDLINE_CITATION_TABLE m;

Typical query becomes simple & fast select PMID, Medline. Id from Citation where Volume='12' and ISSN='1040 -4651' and Medline. Pgn like '1 -%'; PMID MEDLINEID ---------- 10634903 20102627 Elapsed: 00: 00. 35

Text query examples SELECT score, pmid, title FROM citation WHERE CONTAINS(abstract, ’gene NEAR expression’, 1) >0 ORDER BY score(1) DESC; SELECT pmid, title FROM citation WHERE CONTAINS(abstract, ’Drosophila AND ABOUT(adh)’)>0;

Next steps • lexical functionality of Oracle Text • thesauri (UMLS, GO, SNOMED) • scaleability (linux RAC ? ) • concept extraction, classification, clustering • application to database curation • interfaces: web services, GUI

Acknowledgements Leader: Peter Stoehr, Weimin Zhu DBA: Muruli Rao, Olalekan Oyewole Developer: Lichun Wang Oracle Support: Mark Drake (XML) Roger Ford (Text)