I want to create an index in a Oracle DD (Oracle Database 11g Enterprise Edition Release 18.104.22.168.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;
CREATE INDEX fullsearch_description ON T_DESCRIPTION (UPPER(text));
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
*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.
You cannot build a B-tree on a LOB column.
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.