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

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

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

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

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

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

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

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

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

Michael,

<< Tim, is this correct ? >>

Yes, absolutely.

--
Tim Young
Elevate Software
www.elevatesoft.com