Používáte dokumenty JSON a nevíte, co hledáte? 23c Prohledávání indexů k záchraně

Oracle má výkonné možnosti pro zpracování JSON. Má také flexibilní možnosti pro fulltextové vyhledávání, jako je vyhledávání klíčových slov, fráze nebo blízké vyhledávání. Uvidíme, jak se tyto možnosti setkávají v indexu vyhledávání JSON, aby poskytovaly výkonné funkce fulltextového vyhledávání optimalizovaným způsobem pro všechny vaše dokumenty JSON.

Co jsou textové indexy?

Textový index ve své základní podobě umožňuje vytvořit slovní index na textovém poli v databázi. V tabulce je pak možné vyhledávat pole obsahující konkrétní slova nebo fráze.

Řekněme, že máme tabulku emp, která obsahuje některé podrobnosti o zaměstnancích:

create table emp(name varchar2(40), salary number, qualifications varchar2(200));
insert into emp values ('John', 1500, 'PhD in physics, Msc Math');
commit;

V 23c bychom vytvořili textový index na této tabulce pomocí:

create search index emp_qual on emp(qualifications);

V dřívějších verzích bychom udělali:

create index emp_qual on emp(qualifications) indextype is ctxsys.context;

Po vytvoření mého textového indexu jej mohu prohledávat pomocí dotazu CONTAINS, například:

select * from emp where contains(qualifications, 'physics') > 0;

To je nejjednodušší příklad, jak používat textový index. Toto hledání hledá slovo „physics“ někde v poli kvalifikace. Dotaz by mohl být mnohem složitější – až se za chvíli podíváme na JSON_TEXTCONTAINS, uvidíme několik pokročilejších příkladů.

Co je zvláštního na indexu vyhledávání?

Na první pohled bychom si mohli myslet, že bychom mohli provést vyhledávání podřetězců na poli kvalifikace a hledat slovo „physics“. Ale vyhledávání podřetězců je poměrně omezené

• Nemohou používat index
• Rozlišují velká a malá písmena
• Interpunkce a mezery ovlivní vyhledávání
• Nemůžeme provádět oolean vyhledávání, jako je AND, OR nebo NOT v rámci samotného vyhledávání podřetězců, museli bychom provádět více vyhledávání, což by bylo velmi neefektivní

Mít rejstřík vyhledávání znamená, že každé slovo v našem textu má svou vlastní položku rejstříku, takže jsme schopni rychle najít odkazy na jednotlivá slova nebo fráze nebo logické kombinace slov, aniž bychom museli skenovat původní text.

Co jsou indexy JSON?

Nyní řekněme, že data o našich zaměstnancích byla uložena jako JSON a potřebujeme vyhledávat pomocí rozpětí platů. Pro malý počet řádků bude úplné skenování JSON dostatečně rychlé, zvláště pokud používáme binární datový typ JSON v 21c/23c.

create table empj(empdata json);

insert into empj values ('{ "name":"john", "salary":1500, "qualifications": "PhD in physics, Msc Math"}');

insert into empj values ('{ "name":"bobby", "salary":900,
"qualifications": "Msc Math", "hobbies": "physics" }');

commit;

select * from empj e where e.empdata.salary.number() > 1000;

Ale co když máme v tabulce JSON velmi velký počet řádků? V takovém případě budeme chtít vytvořit index prvku platu JSON:

vytvořit index emp_salary on empj e(e.empdata.salary.number());

Ale … co když nevíme, jaká pole musíme hledat? Nebo to víme, ale chceme v těchto polích vyhledávat podle slov? V konečném důsledku jsou data JSON flexibilní a nemají statickou definici schématu, která by vyhovovala všem, jako dříve naše relační tabulka.

Oba tyto problémy jsou vyřešeny pomocí indexu vyhledávání JSON. Index vyhledávání JSON indexuje všechna data v objektu JSON, aniž byste museli předem deklarovat jakékoli datové typy nebo dokonce znát atributy ve vašich dokumentech. Všechno je to JSON. Nejen, že indexuje vaše dokumenty JSON tím nejefektivnějším způsobem, ale také poskytuje možnosti fulltextového vyhledávání s tímto indexem.

OK… Potřebuji vyhledávací index JSON. Jak ho vytvořím?

Vytvoření indexu vyhledávání JSON je tak snadné:

create search index emp_search on empj(empdata) for json;

S tímto indexem vyhledávání JSON jste zabili dvě mouchy jednou ranou: Za prvé, máte index pro „normální“ vyhledávání JSON, jako je náš předchozí příklad platů, jak je vidět v plánu vysvětlení:

explain plan for select * from empj e where e.empdata.salary.number() > 1000;

select * from table(dbms_xplan.display);

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |  4114 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPJ       |     1 |  4114 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | EMP_SEARCH |       |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Řádek s Id=2 ukazuje, že pro vyhledávání používáme „index domény“ – to je náš index vyhledávání JSON, který se používá k urychlení přístupu k poli platu 2 – i když jsme nespecifikovali, že bychom to chtěli konkrétně indexovat. pole.

Za druhé, váš index vyhledávání JSON také umožňuje fulltextové vyhledávání ve všech polích řetězců v rámci JSON. Udělali bychom to pomocí operátoru JSON_TEXTCONTAINS, který jako argumenty přebírá název sloupce, cestu JSON, kde hledat, a výraz fulltextového vyhledávání. Například:

select e.empdata.name from empj e
where json_textcontains(empdata, '$', 'physics');

NAME
--------------------------------------------------------------------------------
"john"
"bobby"

„$“ v něm představuje kořen nebo základ dokumentu JSON (je to „výraz cesty JSON“, pokud chcete vyhledat podrobnější informace) a znamená, že bychom měli hledat slovo „physics“ kdekoli v dokumentu JSON. Pokud bychom chtěli prohledat určitou část dokumentu, řekněme „kvalifikace“, mohli bychom to vyjádřit jako cestu:

vyberte e.empdata.name z empj e
kde json_textcontains(empdata, ‚$.kvalifikace‘, ‚fyzika‘);

NAME
--------------------------------------------------------------------------------
"john"

To je síla indexů vyhledávání JSON. V závislosti na vašem „vyhledávacím prostoru“ – celý dokument nebo konkrétní atribut – index vyhledávání JSON urychluje váš požadavek.

Mohli jsme to udělat s jednoduchým operátorem rovnosti JSON a několika zástupnými znaky, ale u velké sbírky by to nebylo rychlé. A rozhodně jsme nemohli provést složitější vyhledávání jako:

select e.empdata.name, e.empdata.qualifications from empj e
where json_textcontains(empdata, '$.qualifications', 'physics AND msc math');

NAME      QUALIFICATIONS
_________ _____________________________
"john"    "PhD in physics, Msc Math"

Meaning "the qualifications field contains the single word "physics" and the contiguous phrase "msc math". Nor could we do a 'fuzzy' search such as:

select e.empdata.name, e.empdata.qualifications from empj e
where json_textcontains(empdata, '$.qualifications', 'fuzzy(phisiks'));

NAME      QUALIFICATIONS
_________ _____________________________
"john"    "PhD in physics, Msc Math"

Very useful if you're unsure of your spelling, or that of whoever created the JSON in the first place. The JSON search index comes to the rescue and you’ll find what you’re looking for!

Můžeme dokonce provést hodnocení relevance v 23c pomocí JSON_TEXTCONTAINS. Pojďme do naší tabulky přidat několik dalších dokumentů JSON:

insert into empj values ('{ "name":"bill", "salary":1000, "qualifications": "Math professor"}');

insert into empj values ('{ "name":"mike", "salary":2000, "qualifications": "Physics student"}');

commit;

Budeme muset vydat COMMIT a počkat dvě nebo tři sekundy, než se náš index aktualizuje, pak můžeme spustit dotaz se SCORE. Všimněte si dalšího posledního argumentu pro JSON_TEXTCONTAINS – to je číslo, které spojuje funkci SCORE() s tímto konkrétním JSON_TEXTCONTAINS.

select score(1), e.empdata.name, e.empdata.qualifications from empj e
where json_textcontains(empdata, '$.qualifications', 'math ACCUM physics', 1)
order by score(1) desc;

Operátor ACCUM garantuje, že pokud budou nalezeny oba výrazy, bude záznam vyšší, než když bude nalezen pouze jeden výraz. Termínů může být více než dva a vyšší počet nalezených termínů bude mít vždy vyšší skóre. Výše uvedený dotaz nám tedy dává:

SCORE(1)    NAME       QUALIFICATIONS
___________ __________ _____________________________
         52 "john"     "PhD in physics, Msc Math"
          2 "bobby"    "Msc Math"
          2 "bill"     "Math professor"
          2 "mike"     "Physics student"

Absolutní hodnota skóre není tak důležitá – není to něco, co bychom obecně ukazovali uživateli. Místo toho se používá, jako zde, k řazení výsledků podle jejich relevance. S pomocí indexů vyhledávání JSON snadno najdete, co hledáte.

A to je jen začátek. S indexy vyhledávání Oracle JSON (a indexy Oracle Text obecně) toho můžete objevit mnohem víc. Syntaxe vyhledávacího dotazu použitá v JSON_TEXTCONTAINS je stejná jako pro operátor Oracle Text CONTAINS.

Zdroj: Oracle