CODEWITHSUNDEEP
oracle-databaseplsqloracle12coracle-text
Toolkit
Toolkit

Reputation: 11119

How to build Oracle Text index on Index Organized Table?

I need a large table ordered by SOME_NUMERIC_PARAM with text field:

CREATE TABLE HR.IOT_TEST
(
  ID VARCHAR2(30 CHAR),
  SOME_NUMERIC_PARAM NUMBER,
  TEXTS CLOB,
  CONSTRAINT PK_IOT PRIMARY KEY (ID, SOME_NUMERIC_PARAM) ENABLE
)
ORGANIZATION INDEX;

I want to build a Text search index on that CLOB: 

CREATE INDEX IOT_TEST_IND ON HR.IOT_TEST
(
  TEXTS
)
INDEXTYPE IS CTXSYS.CONTEXT;

But I get this error:

Table HR.IOT_TEST created.

SQL Error: ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine
ORA-29960: line 1, 
DRG-11303: secondary indexes are not supported by this indextype

What am I missing? How can I use Oracle Text and Index Organized Table?

Upvotes: 1

Views: 605

Answers (1)

APC
APC

Reputation: 146239

"How can I use Oracle Text and Index Organized Table"

You can't. An index-organized table is basically just an index (there is a table object but it's stub). We can't build indexes on indexes, it's just a limitation of the database architecture.

But why do you want to do this? There's no value in building this table with organization index. There are limited use cases for IOT; mainly they are useful for things like reference data look ups, which usually have a key-meaning structure: we always look up description by the key, there's no other access path and we normally use both the columns in our SQL.

Any index with a CLOB does not seem to fit that class of use case. The fact that you want to search the text independently confirms it. You need to build a regular heap table for your CLOB. Then you can build a Text index.

I'm guessing that you want to combine a full text search with other filters. If so, you should check out the CTXCAT index type. This is a different kind of Text index, one which allows us to build a compound index of regular columns and free-text search enabled columns. So you could run a query that to find values of TEXTS containing the word 'bicycle' where the number value is greater than some value like this:

select from hr.txt_test 
where CATSEARCH(texts, 'bicycle', 'some_numeric_param > 23')> 0;

Another advantage of CTXCAT indexes are that they are transactional, so we don't have to maintain them with background jobs. The main disadvantage is that they are not really suited to large chunks of text. So maybe they don't suit your situation. Find out more.

"I need a large table ordered by SOME_NUMERIC_PARAM with text field "

I presume you ruled out Partitioning (because of licensing?). As you're on 12c there is a possibility that Attribute Clustering could help you. This is "is a table-level directive that clusters data in close physical proximity based on the content of certain columns" (from the docs). However, Attribute Clustering only works when populating the table through direct-path insert operations, data movement, or table creation: normal DML statements don't apply it.

" Again kinda ebay style where people start with something very common like samsung and have few sorting options "

So the thing about sites like eBay, Google and many other totems of scalability is that they invest an lot of effort in caching, indexing and specialized data structures. In many ways they are like data warehouses, with heavily denormalized data structures. So a lot of eBay searches filter results not on from full-text searches of the items' descriptions but by filtering and matching on dimension like structures: initially not even database tables but in-memory caches.

Common search patterns are cache and presented as drop-down suggestions. They even handle typos that way. Type smasu into the eBay search box and you get the same suggested searches as if you had typed samsu i.e. a list of currently desirable Samsung phones (and not laptops or refrigerators). That's bespoke. You're not going to be able to that with Oracle Text out of the box.

So it comes back to, how much like eBay do you really want to be? It's easy enough to use such sites as short hand for features, it's a different magnitude of task to implement equivalent functionality.

Upvotes: 2

Related Questions