Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
Adding Indexes to VIEWS |
Fri, Jul 2 2021 8:02 AM | Permanent Link |
Adam Brett Orixa Systems | 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! |
Sat, Jul 3 2021 3:38 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Aug 4 2021 7:26 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |