Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 11 total |
Text Indexing find specific phrase ? |
Thu, Feb 7 2008 7:42 PM | Permanent Link |
Charalabos Michael | Hello,
I'm bit confused about how to make EDB find specific phrase on a CLOB. How to configure it to give me the records which they contain "My word" and those which contain "My" or "Word" or "My Hello World" etc ... The CLOB may contain: "My", "My 1", "Word", ";My Word1" etc Thank you in advance! -- Charalabos Michael - [Creation Power] - http://www.creationpower.gr |
Fri, Feb 8 2008 3:45 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Charalabos
This is one that I'm going to be getting to. My best thoughts for a phrase are: clob contains 'my word' and field like '%my word%' I'm expecting ElevateDB to first extract the records with 'my' and 'word' in them (in any order and with any separation) and then apply the LIKE to pick out just those that have the phrase from that subset. If not you might just as well use LIKE only. I haven't done any tests yet so I don't know. For any of the words in a list you have to construct a set of OR's clob contains 'my' or clob contains 'word' If you want it to include word1 then its clob contains 'word*' Roy Lambert |
Fri, Feb 8 2008 7:56 AM | Permanent Link |
Charalabos Michael | Hello Roy,
> clob contains 'my word' and field like '%my word%' Well, field like "%my word%" does the job but does it take advantage of the Text Indexing of the CLOB field ? -- Charalabos Michael - [Creation Power] - http://www.creationpower.gr |
Fri, Feb 8 2008 9:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Charalabos
>> clob contains 'my word' and field like '%my word%' > >Well, field like "%my word%" does the job but does it >take advantage of the Text Indexing of the CLOB field ? No, but as I said I'm hoping CONTAINS does Well I know it does, what I don't know is if it produces a subset which is then scanned for the LIKE or not. If not its a waste of space in this case if it does the query will fly (at least by comparison with a brute force scan of the entire table). Roy Lambert |
Fri, Feb 8 2008 10:37 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Charalabos
Now you prompted me - here's what happens SELECT ALL ... FROM "eln" WHERE "_message" CONTAINS 'John David Group' AND "_message" LIKE '%John David Group%' Source Tables ------------- eln: 4868 rows Result Set ---------- The result set was insensitive and read-only The result set consisted of zero or more rows Filtering --------- The following filter condition was applied to the eln table: "_message" CONTAINS 'John David Group' [Index scan: 1150 keys, 20480 bytes estimated cost] AND "_message" LIKE '%John David Group%' [Row scan: 1150 rows, 570400 bytes estimated cost] ================================================================================ 4 row(s) returned in 0.406 secs ================================================================================ SELECT ALL ... FROM "eln" WHERE "_message" LIKE '%John David Group%' Source Tables ------------- eln: 4868 rows Result Set ---------- The result set was insensitive and read-only The result set consisted of zero or more rows Filtering --------- The following filter condition was applied to the eln table: "_message" LIKE '%John David Group%' [Row scan: 4868 rows, 2414528 bytes estimated cost] ================================================================================ 4 row(s) returned in 3.969 secs ================================================================================ Roy Lambert |
Fri, Feb 8 2008 10:42 AM | Permanent Link |
Charalabos Michael | Hello Roy,
> SELECT ALL .. FROM "eln" WHERE "_message" CONTAINS 'John David Group' > AND "_message" LIKE '%John David Group%' > > Source Tables ------------- eln: 4868 rows Result Set ---------- The > result set was insensitive and read-only The result set consisted of > zero or more rows > > Filtering --------- The following filter condition was applied to the > eln table: > > "_message" CONTAINS 'John David Group' [Index scan: 1150 keys, 20480 > bytes estimated cost] AND "_message" LIKE '%John David Group%' [Row > scan: 1150 rows, 570400 bytes estimated cost] > > ================================================================================ > 4 row(s) returned in 0.406 secs > ================================================================================ > > > SELECT ALL .. FROM "eln" WHERE "_message" LIKE '%John David Group%' > > Source Tables ------------- eln: 4868 rows Result Set ---------- The > result set was insensitive and read-only The result set consisted of > zero or more rows Filtering --------- The following filter condition > was applied to the eln table: > > "_message" LIKE '%John David Group%' [Row scan: 4868 rows, 2414528 > bytes estimated cost] > > ================================================================================ > 4 row(s) returned in 3.969 secs > ================================================================================ Wow! so, using CONTAINS and LIKE are actually take advantage of the index if they used together! Thank you very much Roy! -- Charalabos Michael - [Creation Power] - http://www.creationpower.gr |
Fri, Feb 8 2008 11:22 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Charalabos
You're welcome. Question for Tim - if I wanted to create a function say PHRASE that produced the same result is that possible in a way that would take account of the indices? Roy Lambert |
Fri, Feb 8 2008 12:53 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Question for Tim - if I wanted to create a function say PHRASE that produced the same result is that possible in a way that would take account of the indices? >> Unfortunately, no. Functions like that and aggregated functions are something I want to add in a future release however. As long as the function can be assumed to be scalar, it should work if a column or columns from the same table are being passed into the function. Then the engine can at least scan just an index on the given column instead of reading the rows in the table. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Feb 8 2008 7:55 PM | Permanent Link |
Charalabos Michael | Hello Tim,
>> SELECT ALL .. FROM "eln" WHERE "_message" CONTAINS 'John David Group' >> AND "_message" LIKE '%John David Group%' >> >> Source Tables ------------- eln: 4868 rows Result Set ---------- The >> result set was insensitive and read-only The result set consisted of >> zero or more rows >> >> Filtering --------- The following filter condition was applied to the >> eln table: >> >> "_message" CONTAINS 'John David Group' [Index scan: 1150 keys, 20480 >> bytes estimated cost] AND "_message" LIKE '%John David Group%' [Row >> scan: 1150 rows, 570400 bytes estimated cost] >> >> ================================================================================ >> >> 4 row(s) returned in 0.406 secs >> ================================================================================ > > Wow! so, using CONTAINS and LIKE are actually take > advantage of the index if they used together! > > Thank you very much Roy! Tim, is this correct ? Thank you -- Charalabos Michael - [Creation Power] - http://www.creationpower.gr |
Mon, Feb 11 2008 3:07 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Michael,
<< Tim, is this correct ? >> Yes, absolutely. -- Tim Young Elevate Software www.elevatesoft.com |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, May 15, 2024 at 08:40 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |