1、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, OracleWorld, San Francisco, Sep 10 2003,European Molecular Biology Laboratory (EMBL),International network of research institutes dedicated to
2、 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
3、 genetics - Hinxton, EBI: bioinformatics,European Bioinformatics Institute (EBI),Mandateto 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
4、 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 bioinformat
5、ics.,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
6、people- 100 Database developers, software engineers- DBA - 4- Systems - 6Environment- OS: Tru64 ES45s, Solaris- Storage: SAN storage, NetApp NFS- 350-cpu linux compute farm,Major public databases at the EBI,DNA sequences - EMBL Nucleotide Sequence Database protein sequences - SWISS-PROT, TrEMBL, Int
7、erpro, CluSTr genome annotation - Ensembl protein structures - MSD microarrays - ArrayExpress literature - MEDLINE, patents, fulltextenzymes - IntEnz protein interactions - IntAct immunogenetics - IMGT, HLAintegration - Integr8, EnsMart,Oracle Production Instances,Statistics,26 million nucleotide se
8、quences (25b bases) 1 million protein sequences 200 complete genomes (+viruses, organelles) 28,000 genes in human genome 10,000 protein 3D 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
9、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,espacenet,ID AX067
10、464 standard; DNA; PRO; 100848 BP. XX AC AX067464; XX SV AX067464.1 XX DT 24-JAN-2001 (Rel. 66, Created) DT 24-JAN-2001 (Rel. 66, Last updated, Version 1) XX DE Sequence 39 from Patent WO0078968. XX KW . XX OS Moraxella catarrhalis OC Bacteria; Proteobacteria; gamma subdivision; OC Moraxellaceae; Mo
11、raxella. XX RN 1 RA Lagace R.E., Patterson C., Berg K.L.; RT “Nucleotide sequences of moraxella catarrhalis genome“; RL Patent number WO0078968-A/39, 28-DEC-2000. RL Incyte Genomics, Inc. (US). XX FH Key Location/Qualifiers FH FT source 1100848 FT /db_xref=“taxon:480“ FT /organism=“Moraxella catarrh
12、alis“,Searches based on bibliographic data in patent documentsFull 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 bur
13、ied in free text resources enable indexing and intelligent searching of full-text literature,Areas of improvement for public text resources,improve text retrieval functionality improve and add text corpora use of thesauri and ontologies (UMLS, SNOMED, GO, GOBO) interfaces,Text corpora,MEDLINE Full-t
14、ext literature AGRICOLA Biotech patent abstracts Biotech patent full-text OMIM The web = public searchable services,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/jitterb
15、ug files RDBMS (ORACLE and MySQL, 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/MeSH, GO Stemming and wild-carding Multiple language support (for patent
16、 literature) Document clustering functionality Search refinenement, set operators Ranking of results (relevance, date),Search engine evaluation criteria,Weighting of search Scaleability of indexing, searching Load balancing on multiple nodes Parallel processing Incremental and off-line indexing Ease
17、 of use of APIs, documentation API languages, C/Java/Perl Interoperabilty with SRS Market strength of vendor Availability on multiple unix platforms esp. Tru64, Linux and Solaris Technical support Licence costs and flexibility,Text search/extraction systems,Altavista Verity Inktomi ASPseek Google Au
18、tonomy Thunderstone Excalibur Fulcrum SPSS/LexiQuest Stratify/”Purple Yogi” Dolphinsearch Quiver Oracle Text,X-Mine - “Opus” Diogene incellico - “Cell Entity Browser” Collexis Alma PharmDM Linguamatics Inxight ClearForest APRSmartLogik,Search engine implementation,Verity K2 - Red Hat Linux, 200-cpu
19、pc farm - MEDLINE XML parser built Oracle Text - Oracle 9iR2 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 o
20、f 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,94033980 8219565 .1051-04434 51993 Sep-Oct ,Possible Approaches
21、,Fast, efficient, Domain standard Lack of text query functions,Fast, efficient for simple query Pre-processing required, hard to maintain, lack of text query functions, requesting multiple joins for more info,Efficient, support text query Return data as XML, post-processing required,Efficient text q
22、uery, scaleable, industry standard Return data as XML, post-processing required,Oracle XML-DB,Oracle Text,Verity K2,Normalised relational tables,SRS,MEDLINE/Patent XML,Why Oracle XML DB for Medline ?,Oracle 9iR2 embedded XML features with DBMSXMLType datatype LOB storage - maintains original XML byt
23、e 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
24、XMLSchema support,Why Oracle Text for Medline ?,Oracle 9i embedded Text features with DBMSPowerful 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 ex
25、pressions 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 co
26、mmon 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: = XMLSchema,Register XMLSchema, creat
27、e table,begin dbms_xmlschema.registerschema( http:/www3.ebi.ac.uk/internal/Services/medline/ medlinecitation_Types.xsd,xdburitype(/public/ medlinecitation_Types.xsd).getClob(), TRUE,TRUE,FALSE,TRUE); end;,Load data,SQL*Loader We use a Java application, JDBC - need to control updates, deletions. sync
28、hronize context index exec ctx_ddl.sync_index(title_ind,40M); Complete MEDLINE: - 1 day to load, 1 day for context indexing Updates - 10 mins,Actual MEDLINE instances EBI,9iR2 in Production - MEDLINE + patent abstracts- updated twice per week - used for in-house reference - CLOB storage of XMLType f
29、ield - partitioned (by date) context index of XMLType9iR2 in development - using structured object-relational storage - indexing fields, inc. context indextype for titles, abstracts - no partioning,MED production schema,Main Table: MedlineCitation,Table is partitioned into 8,XMLtype Column is regist
30、ered with XMLSchema, locally context type indexed,select m.MedlineCitation.getClobVal() AS MedXML from Medlinecitation m where pmid=8219565;,-return a full XML document:94033980 8219565 .1051-04434 51993 Sep-OctTranscatheter manipulation of asymmetrically opened titanium Green field filters.687-90 ,
31、Select a part of XML document,select extract (MedlineCitation, /MedlineCitation/Article/Journal/JournalIssue).getStringVal() ”JournalIssue” from Medlinecitation where pmid= 11194419;,4042000Nov,Select just Abstract text,select extractValue (MedlineCitation, /MedlineCitation/Article/Abstract/Abstract
32、Text).getStringVal() “AbstractText” from Medlinecitation where pmid=8219565;,AbstractText - 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 tissu
33、e, 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 a
34、n 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 PMID number primary key, MedlineID number, ArticleTitle VARCHAR2(1500),
35、 Volume VARCHAR2(55), Issue VARCHAR2(55), StartPage VARCHAR2(55), EndPage VARCHAR2(55), MedlinePgn VARCHAR2(100), PubYear number, ISSN VARCHAR2(9), NlmUniqueId VARCHAR2(25), AuthorListCompleteYN VARCHAR2(1),AuthorPMID number foreign key,LastName VARCHAR2(255),Initials VARCHAR2(255),Suffix VARCHAR2(2
36、5),CollectiveName VARCHAR2(1000), Affiliation VARCHAR2(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.contextparameters(storage med_storage lexer med_lex stoplist med_STOPLI
37、ST section group autogroupmemory 400M);or just text fields: Create index Journal_Abstract_Index on MEDLINE_CITATION_TABLE c (extractValue(val(m), /MedlineCitation/Article/Abstract/AbstractText) indextype is ctxsys.context;,Improvements S-R storage - index specific fields,create unique index MEDLINE_
38、PMID_INDEX on MEDLINE_CITATION_TABLE m(extractValue(value(m),/MedlineCitation/PMID) create index AUTHOR_LASTNAME_INDEX on ARTICLE_AUTHOR_TABLE a(a.“LastName“) create index JOURNAL_ISSN_INDEX on MEDLINE_CITATION_TABLE c( extractValue(value(c),/MedlineCitation/Article/Journal/ISSN) ) create index JOUR
39、NAL_VOLUME_INDEX on MEDLINE_CITATION_TABLE c(extractValue(value(c),/MedlineCitation/Article/Journal/JournalIssue/Volume),extractValue(value(c),/MedlineCitation/Article/Journal/JournalIssue/Issue) create index PAGINATION_INDEX on MEDLINE_CITATION_TABLE c( extractValue(value(c),/MedlineCitation/Articl
40、e/Pagination/MedlinePgn),Improvements - create a view,create or replace view Citation as select to_Number(extractValue(value(m),/MedlineCitation/PMID) PMID, to_Number(extractValue(value(m),/MedlineCitation/MedlineID) MedlineId, extractValue(value(m),/MedlineCitation/Article/Journal/ISSN) ISSN, extra
41、ctValue(value(m),/MedlineCitation/Article/Journal/JournalIssue/Volume) Volume, extractValue(value(m),/MedlineCitation/Article/Journal/JournalIssue/Issue) Issue, extractValue(value(m),/MedlineCitation/Article/Pagination/MedlinePgn) MedlinePgn from MEDLINE_CITATION_TABLE m;,Typical query becomes simpl
42、e & fast,select PMID, MedlineId from Citation where Volume=12 and ISSN=1040-4651 and MedlinePgn like 1-%;PMID MEDLINEID- -10634903 20102627Elapsed: 00: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
43、pmid, title FROM citation WHERE CONTAINS(abstract,Drosophila AND ABOUT(adh)0;,Gene Ontology Annotation (GOA),GO - controlled vocabularies for: - molecular function - biological process - cellular component GO consortium www.geneontology.org GOA - associations of GO terms to gene products - 6009 Go t
44、erms assigned to 570,000 protein sequences - ongoing, combination of electronic and manual methods,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),
