Amadeu Cabanilles Amadeu Cabanilles - 1 month ago 21
SQL Question

Oracle: Creating an Index on a Table Containing Partitioned LOB Columns

I want to create an index in a Oracle DD (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
From the Oracle docs I read that to improve the performance of queries, I can create indexes on partitioned LOB columns. For example:

CREATE INDEX index_name
ON table_name (LOB_column_1, LOB_column_2, ...) LOCAL;


I've tried

CREATE INDEX fullsearch_description ON T_DESCRIPTION (UPPER(text));


but I got an error:

Erro

r starting at line : 1 in command -
CREATE INDEX fullsearch_description ON T_DESCRIPTION (UPPER(text))
Error at Command Line : 1 Column : 55
Error report -
SQL Error: ORA-02327: cannot create index on expression with datatype LOB
02327. 00000 - "cannot create index on expression with datatype %s"
*Cause: An attempt was made to create an index on a non-indexable
expression.
*Action: Change the column datatype or do not create the index on an
expression whose datatype is one of VARRAY, nested table, object,
LOB, or REF.

Answer

You cannot build a B-tree on a LOB column. In your case upper(lob) returns lob. And oracle cannot use lob to build index.

You can use somthing like this. Because result of function index is varchar2. But i don't think this solution is useful.

CREATE INDEX fullsearch_description ON T_DESCRIPTION (UPPER(dbms_lob.substr(text,1,1000)));

On lob column you can use domain index. Database Data Cartridge - There are chapters about domain index. And how to implement it. (Using Extensible Indexing, Building Domain Indexes, Defining Operators, Extensible Indexing Interface ,Extensible Optimizer Interface) Oracle provides several implementation of domain indexs. Oracle Text. * These indexing method are very complicated.