Як працювати з картотекою (набором даних з короткими описами)?

… У відділі «Що сталося за день» нонпарель було надруковано:
Потрапив під коня.
Вчора на площі Свердлова попав під коня
візника № 8974 громадянин О. Бендер.
Потерпілий відбувся легким переляком.

І. Ільф, Є. Петров. Дванадцять стільців 


Анотація


Вбудована в СУБД Oracle текстова пошукова машина Oracle Text здатна не тільки виконувати повнотекстовий пошук в документах (як що знаходяться всередині БД, так і поза нею), але й здійснювати пошук за розділами коротких описів типу практикуються в каталогах. Для цього застосовується різновид CTXSYS.CTXCAT предметного індексу і оператор CATSEARCH. У статті показані приклади їх застосування. Статтю зручно розглядати як продовження публікувалися раніше «Oracle: працювати з текстовими документами дуже просто» і «Текстові документи в Oracle: різноманітність джерел, форматів, запитів».


Введення


Крім повнотекстового пошуку в документах, обсяг яких може бути дуже великий, вбудована в СУБД Oracle текстова машина («Oracle Text») окремо забезпечує можливість пошуку інформації для одного спеціального випадку. Мова йде про пошук в наборі записів, що містять короткі текстові описи. Подібний пристрій даних добре відомо за традиційними картотеками, поступово йдуть у минуле, але ще зустрічається в успадкованих інститутах, таких як бібліотеки. Там картотеки організовані у вигляді довгастих шухлядок, де зберігаються картки з коротким описом одиниць зберігання та зазначенням місця розташування. Більш сучасний приклад того самого пристрою даних – канали новин RSS, додали своєю появою помітний імпульс динаміці інформації в інтернеті. Сама ж фірма Oracle використовує для іменування такого пристрою даних метафору «каталогу», утвореного записами з описами, крім інших реквізитів, об'єктів каталогізації.


Якщо подібну картотеку / каталог промоделювати таблиці в БД Oracle, сама собою виникає думка проіндексувати поля з такими описами предметним (DOMAIN) індексом типу CTXSYS.CONTEXT і скористатися для запитів до даних можливостями оператора CONTAINS. Однак якщо опису короткі (одна – два рядки), це неекономно: індекс виходить надмірно великим, а можливості запитів надлишковими. Для такого випадку та запропонований спеціальний тип предметного індексу: CTXSYS.CTXCAT. Він дає одночасно і велику по відношенню до повнотекстовому індексу компактність, й простіший (в основному варіанті) мова запитів, забезпечений спеціальним оператором CATSEARCH.


Нижче розглядається приклад організації в БД «картотеки», побудови предметного індексу типу CTXSYS.CTXCAT і складання запитів до даних.


Вихідні дані та план дій


В якості вихідних даних візьмемо реальну стрічку новин «Oracle Technology Network Headlines» за адресою з утриманням на момент написання статті:



Документ має загальний опис каналу новин (розмітка title, link, description, copywrite і т. д.) та перерахування відомостей про окремі новинах елементами title:


<title>


   <link>…</link>


   <description>…</description>


   <category>…</category>


  …


</title>


План подальших дій такий:


– Перенесемо ці дані в БД в таблицю з полями LINK, DESCRIPTION і PUBTIME, дані в яких описують кожну конкретну новина.


– Побудуємо «каталожний» індекс типу CTXSYS.CTXCAT по полю DESCRIPTION.


– Наведемо приклади запитів.


Для кращого розуміння суті сам приклад буде використовувати ряд технічних спрощень і вихідних припущень. Зокрема, зверніть увагу, що (а) вихідні дані представлені в форматі RSS 2.0, і в статті не враховується можливість існування в інших каналах новин інших форматів RSS з іншої розміткою; (б) у конкретному каналі новин елемент description невеликий за обсягом і краще підходить під «короткий зміст », ніж ніж елемент title.


Крім того, деяких цілей, переслідуваних далі, можна досягти іншими шляхами. Так, перше, що спадає на думку – програмування, якого далі, проте, я свідомо уникаю.


Для зручності занесемо адресу каналу в змінну SQL * Plus:


VARIABLE url VARCHAR2 ( 1000 )


EXECUTE: url: = "http://www.oracle.com/technology/syndication/rss_otn_news.xml"


Якщо звернення в інтернет буде здійснюватися через наближений (proxy) сервер, потрібно попередньо видати щось на кшталт:


EXECUTE UTL_HTTP.SET_PROXY ("http:// ім'я: пароль @ адреса: порт")


Завантаження даних в БД


Завантаження даних про новини буде здійснюватися за допомогою функції за формою, а по суті – процедури, DBMS_XMLSTORE.INSERTXML. Вона вимагає привести дані для завантаження до наступного вигляду:


<ROWSET>


<ROW>


<link> …</ link> <description> …</ description> <pubDate> …</ pubDate>


</ROW>


<ROW>


<link> …</ link> <description> …</ description> <pubDate> …</ pubDate>


</ROW>



</ROWSET> 


Це буде зроблено за допомогою перетворення XQUERY, але для того, щоб не ускладнювати його формулу, я використовую наступний трюк. Створимо таблицю з полями LINK, DESCRIPTION і PUBDATE, а завантаження зробимо в однотипну похідну таблицю (view), імена стовпців яких зберігають регістр літер відповідних позначок у вихідному документі. Об'єкти для зберігання і для завантаження:


CREATE TABLE otnnews (


  link        VARCHAR2 ( 4000 )


, description VARCHAR2 ( 4000 )


, pubdate     VARCHAR2 ( 30 )


); 


CREATE VIEW otnnews_view ( “link”, “description”, “pubDate” )


AS SELECT * FROM otnnews



У наступному блоці на PL / SQL спочатку оператор SELECT бере з інтернету документ XML і перетворює його в необхідний вид, а подальша серія викликів підпрограм пакету DBMS_XMLSTORE організовує занесення даних (Елемент title) з отриманого документа XML в таблицю OTNNEWS_VIEW; фактично – в OTNNEWS:


VARIABLE rowsnumber NUMBER


DECLARE


  xmltypedoc    XMLTYPE;


  updatecontext DBMS_XMLSTORE.CTXTYPE;


BEGIN


SELECT


  XMLQUERY (


  “<ROWSET> {


     for $a in /rss/channel/item


return <ROW> {$ a / link, $ a / description, $ a / pubDate} </ ROW>


   }


   </ROWSET>”


  PASSING HTTPURITYPE ( :url ).GETXML ( )


  RETURNING CONTENT


  )


INTO xmltypedoc


FROM dual


;


updatecontext := DBMS_XMLSTORE.NEWCONTEXT ( “CTX.OTNNEWS_VIEW” );


DBMS_XMLSTORE.CLEARUPDATECOLUMNLIST ( updatecontext );


DBMS_XMLSTORE.SETUPDATECOLUMN ( updatecontext, “link” );


DBMS_XMLSTORE.SETUPDATECOLUMN ( updatecontext, “description” );


DBMS_XMLSTORE.SETUPDATECOLUMN ( updatecontext, “pubDate” );


:rowsnumber := DBMS_XMLSTORE.INSERTXML ( updatecontext, xmltypedoc );


DBMS_XMLSTORE.CLOSECONTEXT ( updatecontext );


END;


/


У таблиці OTNNEWS з'явилося 11 записів. Ось, наприклад, дати новин:


CTX> SELECT pubdate FROM otnnews;


PUBDATE


——————————


Fri, 29 Jun 2007 21:18:44 GMT


Mon, 25 Jun 2007 22:23:05 GMT


Tue, 19 Jun 2007 16:18:01 GMT


Tue, 12 Jun 2007 15:26:52 GMT


Fri, 01 Jun 2007 16:24:42 GMT


Fri, 01 Jun 2007 16:23:00 GMT


Fri, 25 May 2007 15:37:53 GMT


Fri, 25 May 2007 15:34:52 GMT


Tue, 15 May 2007 23:11:50 GMT


Tue, 15 May 2007 23:09:37 GMT


Fri, 01 Jun 2007 16:16:47 GMT


11 rows selected. 


Створення індексу


У простому випадку індекс створюється просто:


CREATE INDEX otnnews_idx


ON otnnews ( description )


INDEXTYPE IS CTXSYS.CTXCAT


;


Ось які структури БД і сегменти зберігання з'явилися в результаті:


CTX> COLUMN object_name  FORMAT A30


CTX> COLUMN object_type  FORMAT A30


CTX> COLUMN segment_name FORMAT A30


CTX> COLUMN segment_type FORMAT A30


CTX> SELECT object_name, object_type FROM user_objects ORDER BY 2, 1;


OBJECT_NAME                    OBJECT_TYPE


—————————— ——————————


DR$OTNNEWS_IDX$R               INDEX


DR$OTNNEWS_IDX$X               INDEX


OTNNEWS_IDX                           INDEX


DR$OTNNEWS_IDX$I                TABLE


OTNNEWS                                     TABLE


DR$OTNNEWS_IDXTC               TRIGGER


OTNNEWS_VIEW                           VIEW


CTX> SELECT segment_name, segment_type FROM user_segments ORDER BY 2, 1;


SEGMENT_NAME                   SEGMENT_TYPE


—————————— ——————————


DR$OTNNEWS_IDX$R               INDEX


DR$OTNNEWS_IDX$X               INDEX


DR$OTNNEWS_IDX$I               TABLE


OTNNEWS                        TABLE


Очевидно, що формально індекс типу CTXSYS.CTXCAT компактніше індексу типу CTXSYS.CONTEXT: він реалізований не з чотирьох, а з трьох службових таблиць, до того ж не мають полів LOB. Ще одна відмінність: створення «Каталожного» індексу пов'язане з автоматичним появою критичної процедури (у нашому випадку DR $ OTNNEWS_IDXTC) виду AFTER EACH ROW INSERT OR UPDATE, пов'язаної з базовою таблицею (все це легко встановити в якості самостійного вправи). Наявність цієї критичної процедури дає підставу припустити, що ще одна відмінність «каталожного» індексу від повнотекстового в тому, що він автоматично коригується при зміні даних у таблиці. Це припущення легко перевіряється.


Запити


Оператором для пред'явлення запиту (формально до таблиці, а фактично – до індексу), є CATSEARCH. На відміну від CONTAINS, він може вживатися в SQL тільки в складі умовного вираження. В основному застосуванні він допускає власні позначення операторів запиту (логічні AND, OR і NOT, що позначаються через «пропуск», / і -; * для довільної підстановки і висновку в лапки). Тим не менш, синтаксис, застосовуваний у CONTAINS, теж допускається, але шляхом певних хитрощів, через які ця можливість тут не розглядається.


Видамо:


SELECT description FROM otnnews WHERE CATSEARCH (description, & 1, & 2)> 0


.


SAVE catsearch


COLUMN description FORMAT A75 TRUNCATED


SET VERIFY OFF


Приклади запитів:


CTX> @catsearch “”java”” NULL


DESCRIPTION


————————————————– ————————-


Get an introduction to using Oracle Data Integrator, Java-based middleware


CTX> @catsearch “”java / web”” NULL


DESCRIPTION


————————————————– ————————-


Get an introduction to using Oracle Data Integrator, Java-based middleware


Learn how to secure PHP-based Web applications via database-based authentic


CTX> @catsearch “”(java / web) – php”” null


DESCRIPTION


————————————————– ————————-


Get an introduction to using Oracle Data Integrator, Java-based middleware


У вигляді вправиможна видати наступні команди і порівняти результати запитів:


VARIABLE query1 VARCHAR2 ( 1000 )


VARIABLE query2 VARCHAR2 ( 1000 )


EXECUTE :query1 := “oracle *developer”


EXECUTE :query2 := “oracle *developer


@catsearch :query1 NULL


@catsearch :query2 NULL 


Індекс по декількох полях


Особливістю «каталожного» індексу є те, що він дозволяє враховувати додаткові, крім основного, стовпці. В якості такого стовпця розглянемо дату новини; цей же реальний приклад заодно покаже і деякі «яри», не завжди помітні на «папері» фірмової документації Oracle.


Формат представлення дати новини очевидний з показаних вище вихідного документа XML і відповіді на запит до таблиці. На жаль, він не годиться для нашої мети – включити поле PUBDATE до складу індексованих, – І з двох причин: (а) текст PUBDATE занадто довгий і (б) він не допускає порівняння значень часу як рядків тексту. Це можна розглядати як обмеження мови запитів оператора CATSEARCH та індексу типу CTXSYS.CTXCAT, які дозволяють використовувати тільки додаткові стовпці типу «число» і «рядок», та й то, в другому випадку не дуже довга. Тому щоб продемонструвати індексування з кількох полях нам доведеться цей стовпець переробити. Як спрощення вважатимемо, що дати новин завжди наводяться щодо Гринвіцького часового поясу (як це є в наявних даних), а далі й зовсім не будемо приймати часовий пояс до уваги.


Видалимо старий індекс і додамо новий стовпець таблиці:


DROP INDEX otnnews_idx;


ALTER TABLE otnnews ADD ( pubtime VARCHAR2 ( 30 ) );


UPDATE otnnews


SET pubtime = TO_CHAR (


TO_TIMESTAMP_TZ (pubdate, "Dy, DD Mon YYYY HH24: MI: SS TZD")


              , “YYYY:MM:DD:HH24”


              )


;


Облік у додаткових полів оформляється через механізм параметрів індексу, а необхідні параметри заводяться послідовністю викликів системних процедур:


BEGIN


   CTX_DDL.CREATE_INDEX_SET ( “otntab_fields” );


   CTX_DDL.ADD_INDEX ( “otntab_fields“, “pubtime” );


— CTX_DDL.ADD_INDEX ( “otntab_fields”, “…” ); – якщо треба, і інші стовпці


END;


/


Сформована «перевагу» OTNTAB_FIELDS вкажемо параметром новому індексу:


CREATE INDEX otnnews_idx


ON otnnews ( description )


INDEXTYPE IS CTXSYS.CTXCAT


PARAMETERS ( “index set OTNTAB_FIELDS” )


;


В якості вправи, Для створеного індексу можна влаштувати перевірку типу виконувалася вище для «простого» «каталожного» індексу і переконатися в тому, що додавання додаткових стовпців індексації дещо ускладнює технічну організацію індексу.


Перевірка запитами


Перший запит нічим не відрізняється від одного з попередніх ні за формою, ні по результату:


CTX> @catsearch ““java / web“” NULL


DESCRIPTION


————————————————– ————————-


Get an introduction to using Oracle Data Integrator, Java-based middleware


Learn how to secure PHP-based Web applications via database-based authentic


Наступний запит показує можливість сформувати критерій впорядкування, що включає звернення до додаткового індексованому стовпцю, в третьому аргументі оператора CATSEARH:


CTX> @catsearch “”java / web”” “”order by pubtime desc“”


DESCRIPTION


————————————————– ————————-


Learn how to secure PHP-based Web applications via database-based authentic


Get an introduction to using Oracle Data Integrator, Java-based middleware


Наступний запит показує можливість сформувати умовний вираз, що включає звернення до додаткового індексованому стовпцю, в третьому аргументі оператора CATSEARH:


CTX> VARIABLE texpr VARCHAR2 ( 256 )


CTX> BEGIN


  2  :texpr := TO_CHAR ( SYSDATE – 35, “YYYY:MM:DD:HH24” );


  3  :texpr := “pubtime < “”” // :texpr // “”””;


  4  DBMS_OUTPUT.PUT_LINE ( :texpr );


  5* END;


CTX> /


pubtime < “2007:05:31:15”


PL/SQL procedure successfully completed.


CTX> @catsearch “”java / web”” :texpr


DESCRIPTION


————————————————– ————————-


Get an introduction to using Oracle Data Integrator, Java-based middleware


На жаль формулювання виразів для третього аргументу CATSEARCH можуть бути тільки дуже простими; зокрема, на відміну від виразів SQL, вони не терплять звернень до функцій. Тим не менш, можливо об'єднання фрази впорядкування і фільтруючого умовного вираження.


Вправа. Перевірити це, сформувавши уточнення запиту наступним значенням третього аргументу CATSEARCH: «order by pubtime desc AND pubtime <" 2007:05:31:15 "».


Реакція СУБД на помилки роботи з каталожними індексом (а в прикладах вище їх не було) може виснажити програміста, до чого треба бути готовим.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*