Як працювати з картотекою (набором даних з короткими описами)?, Інші СУБД, Бази даних, статті

… В відділі «Що сталося за день» нонпареллю було надруковано:
Потрапив під коня.
Вчора на площі Свердлова попав під коня
візника № 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>

*

*