Downloaded 54 times
Uploaded byScott Wesley
Oracle Text in APEX
The document outlines a customized Oracle training workshop offered by Sage Computing Services, specifically focusing on Oracle Text in APEX and advanced indexing techniques. It includes details on architecture, setup procedures, maintenance, and various indexing considerations while providing sample SQL commands and demonstrations for practical application. Additionally, it refers to resources and contact information for further inquiries related to the training and sessions.
Oracle Text in APEX
- 1. SAGE Computing Services CustomisedOracle Training Workshops and Consulting Oracle Text in Apex Advanced Indexing Techniques Integrated with Application Express Scott Wesley Systems Consultant & Trainer
- 2. Agenda • Introduction • Architecture •Fundamentals • Considerations • Setting Up • Samples • Index Maintenance • Visualisation • New Features
- 3.
- 4. the law isstrangling creativity http://www.ted.com/talks/larry_lessig_says_the_law_is_strangling_creativity.html http://presentationzen.blogs.com/presentationzen/2005/10/the_lessig_meth.html
- 5. Identity 2.0 –Dick Hardt http://identity20.com/media/OSCON2005/
- 6. who’s the Dickon your site
- 7.
- 8. so today’s goingto be more like this
- 9.
- 10. after I showa few pictures
- 11.
- 12.
- 15.
- 23. Why use OracleApplication Express?
- 24. Why use OracleText?
- 26. What is OracleText?
- 27.
- 29.
- 31.
- 33.
- 34. Class Description Datastore Howare your documents stored? Filter How can the documents be converted to plain text? Lexer What language is being indexed? Wordlist How should stem and fuzzy queries be expanded? Storage How should the index data be stored? Stop List What words or themes are not to be indexed? Section Group How are documents sections defined?
- 35.
- 36. CREATE INDEX ctx_nameON my_names(name) INDEXTYPE IS ctxsys.context PARAMETERS ('DATASTORE CTXSYS.DEFAULT_DATASTORE');
- 37. SQL> SELECT SCORE(1),name 2 FROM my_names 3 WHERE CONTAINS(name, 'fuzzy(john,,,weight)', 1) > 0 4 ORDER BY SCORE(1) DESC; SCORE(1) NAME ---------- ---------------------------------------- 100 John 100 John 70 Jon 70 Jon 63 Joan 63 Joan 52 Jong 48 Jona 8 rows selected.
- 38.
- 39.
- 40.
- 41.
- 42. Pointers to objectson file system
- 43.
- 44. Pointers to objectson the intertube
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52. asynchronous index &table data
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61. similar to typicalindex paradigm
- 62.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74. bypassing rows -images
- 75.
- 76.
- 77. fuzzy matching &stemming
- 78.
- 80.
- 81. query performance andstorage of LOBs
- 82.
- 83.
- 84. GRANT ctxapp TOausoug;
- 85. create & deleteindexing preferences
- 86. use Oracle TextPL/SQL supplied packages
- 87. 1* select grantee,owner, table_name, privilege from dba_tab_privs where table_name = 'CTX_DDL' SQL> / GRANTEE OWNER TABLE_NAME PRIVILEGE -------------------- ------------ ------------------------------ -------------------- CTXAPP CTXSYS CTX_DDL EXECUTE APEX_040000 CTXSYS CTX_DDL EXECUTE APEX_030200 CTXSYS CTX_DDL EXECUTE AUSOUG CTXSYS CTX_DDL EXECUTE XDB CTXSYS CTX_DDL EXECUTE 5 rows selected.
- 88. PLS-00201: identifier "string"must be declared
- 89. CTX PL/SQL Packages GRANTEXECUTE ON CTXSYS.CTX_CLS TO ausoug; GRANT EXECUTE ON CTXSYS.CTX_DDL TO ausoug; GRANT EXECUTE ON CTXSYS.CTX_DOC TO ausoug; GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO ausoug; GRANT EXECUTE ON CTXSYS.CTX_QUERY TO ausoug; GRANT EXECUTE ON CTXSYS.CTX_REPORT TO ausoug; GRANT EXECUTE ON CTXSYS.CTX_THES TO ausoug; GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO ausoug;
- 90. Using URL Datastorein 11g CREATE ROLE apex_url_datastore_role; GRANT apex_url_datastore_role TO APEX_040000 WITH ADMIN OPTION; GRANT apex_url_datastore_role TO ausoug; EXEC ctxsys.ctx_adm.set_parameter ('file_access_role' ,'APEX_URL_DATASTORE_ROLE');
- 91. Demonstrations Script Description Ctx_blobs.sql Import& index a range of documents Ctx_bfiles.sql Import & index BFILE pointers Ctx_urls.sql Index & search URL references Ctx_dict.sql Index & search English dictionary words Ctx_views.sql Index view SQL text for impact analysis Ctx_apex_files.sql Duplicate and search Apex file repository Ctx_apex_backups.sql Hunt through your (automated) Apex app backups Ctx_names.sql Basic name filter options Ctx_products.sql Multiple column searches Ctx_category.sql Attribute based searching Ctx_classify.sql Classify documents into categories
- 112.
- 113.
- 115.
- 116. ALTER INDEX ctx_surname REBUILDPARAMETERS ('resume memory 10m');
- 117.
- 118. EXEC ctx_ddl.recreate_index_online ('ctx_surname', 'replacelexer sw_lexer');
- 119.
- 120. ALTER INDEX ctx_surname REBUILDPARAMETERS('replace lexer sw_lexer') ONLINE;
- 121.
- 122. create table ausoug.my_stats(stats clob); declare x clob := null; begin for r_rec in (select * from ctxsys.ctx_indexes where idx_owner = 'AUSOUG' and idx_type = 'CONTEXT') loop ctx_report.index_stats(r_rec.idx_name,x); insert into ausoug.my_stats values (x); end loop; commit; dbms_lob.freetemporary(x); end; /
- 123.
- 128. SQL> select count(*) 2from all_views 3 where owner = 'CTXSYS'; COUNT(*) ---------- 58
- 129.
- 130. DML operations ona CONTEXT index
- 131.
- 132.
- 133.
- 134.
- 135.
- 136.
- 138.
- 139.
- 140.
- 141. each entry containslist of documents
- 142. DOG - DOC1DOC3 DOC5 DOG - DOC7 DOG - DOC9 DOG - DOC11
- 143.
- 144.
- 145. Object of InterestNum Rows Table Size Index size Dictionary 150k 7 27 Documents 28 34 1.5 Names 27k 1 6 Views 2k 7 2 BFiles 4 Product 1 URL 1
- 146.
- 147.
- 148.
- 149.
- 151.
- 152.
- 154.
- 155.
- 156. SAGE Computing Services CustomisedOracle Training Workshops and Consulting Question time Presentations are available from our website: http://www.sagecomputing.com.au enquiries@sagecomputing.com.au scott.wesley@sagecomputing.com.au http://triangle-circle-square.blogspot.com