I don't often add Indexes to Views, so I don't have much experience of this.

I have a View with a CLOB field I would like to index. The view is very straight forward: 3 columns, 1 of which is a CLOB

If I run this SQL:

CREATE TEXT INDEX "tiContent" ON "System_FrameworkSchema" ("Content")
INDEXED WORD LENGTH 20
WORD GENERATOR "Default"

The SQL runs fine, but when I query the data with a SELECT I get this error:

ElevateDB Error #100 There is an error in the metadata for the index column in the index tiContent (BLOB columns cannot be included in a non-text index)

This error is weird as the column is not a BLOB, and the index being created is a text-index! Also the error only occurs when I run a SELECT, not when I create the Index.

If I run this SQL:

CREATE INDEX "tiContent" ON "System_FrameworkSchema" ("Content")

The SQL does not even run but returns:

ElevateDB Error #100 There is an error in the metadata for the index column in the index tiContent (BLOB columns cannot be included in a non-text index).

My assumption was that I could index Views in exactly the same way I index tables, but it seems that there are some limitations. I can't see this explained anywhere (but I may not have looked well enough).

* Are there limits on Index creation on Views, and what are they?
* What is the sensible way to add a Text index to this type of data?

With a text index on this data in a table form I get a result in about 1 second, whereas without the text index the result can take 20 minutes to return!

Adam


Running 2.34 b3 (ansi in this instance). Trying in EDBManager I created  a view

CREATE VIEW "Testing" AS
SELECT
"JobNo",
"AccountNo",
"CustomerNo",
"ConsumerNotes"

FROM
AJobTickets
VERSION 1.00


and tried to create a text index

CREATE TEXT INDEX "tiContent" ON "Testing" ("ConsumerNotes")
INDEXED WORD LENGTH 20
WORD GENERATOR "Default"

this generated an error

ElevateDB Error #902 An error occurred with the view Testing (You cannot create an index on an updateable view)


Can you show the code creating the view so I can try a bit nearer to what you're doing?


I also tried creating the text index on the table referenced in the view and this is used on SELECTs for the view


Roy Lambert

Adam,

<< I don't often add Indexes to Views, so I don't have much experience of this.

I have a View with a CLOB field I would like to index. The view is very straight forward: 3 columns, 1 of which is a CLOB

If I run this SQL:

CREATE TEXT INDEX "tiContent" ON "System_FrameworkSchema" ("Content")
INDEXED WORD LENGTH 20
WORD GENERATOR "Default"

The SQL runs fine, but when I query the data with a SELECT I get this error:

ElevateDB Error #100 There is an error in the metadata for the index column in the index tiContent (BLOB columns cannot be included in a non-text index)

This error is weird as the column is not a BLOB, and the index being created is a text-index! Also the error only occurs when I run a SELECT, not when I create the Index. >>

Hmm, I'll have to check this out further.  I specifically disabled the creation of text indexes for views in the EDB Manager, but I'm not quite sure why yet.

Let me get back to you on this,

Tim Young
Elevate Software
www.elevatesoft.com