Tim

Is CONTAINS case insensitive as with DBISAM or does it depend on the collation?

Roy Lambert

Roy,

<< Is CONTAINS case insensitive as with DBISAM or does it depend on the
collation? >>

It depends upon the collation.  Also, unlike DBISAM, you must match the
collation of an existing text index correctly, or else you will get an
error.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>It depends upon the collation. Also, unlike DBISAM, you must match the
>collation of an existing text index correctly, or else you will get an
>error.

Is that covered in the manual? If not can you expand a little bit (or preferably an awful lot)

Roy Lambert

Roy,

<< Is that covered in the manual? If not can you expand a little bit (or
preferably an awful lot) >>

Sure, the whole bit minus the index collation is covered here under
"Performing a Text Index Search":

http://www.elevatesoft.com/edb1sql_text_indexing.htm

while the index selection/collation information is covered here:

http://www.elevatesoft.com/edb1sql_optimizer.htm

Although, I could probably add some of the collation information to the text
indexing topic also.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


My aren't the manuals getting pretty

1. for http://www.elevatesoft.com/edb1sql_optimizer.htm you need to sort the page width out - the lines are way to long.
2. If I understand correctly for an ordinary comparison I can use something like

field collate ansi_ci = 'smith'

to force case insensitive even if the column was created with the ANSI collation (I've seen an example somewhere for the syntax but can't find it now)

but for CONTAINS I HAVE to use whatever the collation was when the index was created.

Is that right?

Roy Lambert

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
news:758B8E24-A268-4D27-B2B7-378E22C9D383@news.elevatesoft.com:

> while the index selection/collation information is covered here:
>
> http://www.elevatesoft.com/edb1sql_optimizer.htm
>

Tim,

This makes very interesting reading.

Just one comment:

> SELECT *
> FROM Employee
> WHERE (LastName = 'Smith') and (FirstName = 'John')
>
> Logically you would assume that ElevateDB can use the one index
> available for the enforcement of the primary key constraint in order to
> optimize the entire WHERE clause. Unfortunately, this is not the case,
> and instead ElevateDB will only use the index used for the primary key
> constraint for optimizing the LastName expression and resort to reading
> the resultant rows in order to evaluate the FirstName expression.

It would not seem unreasonable to hope that this could be a little
improved by a compromise:

given that it is impossible to constrain via the secondary fields in a
compound index, rather than resorting to a sequential scan of the data
could there not be a sequential scan of the selected part of the index
for any other fields that happen to be present in that index?

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.02 build 1)

Roy,

<< 1. for http://www.elevatesoft.com/edb1sql_optimizer.htm you need to sort
the page width out - the lines are way to long. >>

Yep, it was missing a closed tag.

<< 2. If I understand correctly for an ordinary comparison I can use
something like

field collate ansi_ci = 'smith'

to force case insensitive even if the column was created with the ANSI
collation (I've seen an example somewhere for the syntax but can't find it
now) >>

Yes.  The example that you saw was in the Optimizer topic, just further
down.

<< but for CONTAINS I HAVE to use whatever the collation was when the index
was created. >>

No, CONTAINS works the exact same way.  You can force a collation just like
with the equals (=) operator.

--
Tim Young
Elevate Software
www.elevatesoft.com

Chris,

<< It would not seem unreasonable to hope that this could be a little
improved by a compromise:

given that it is impossible to constrain via the secondary fields in a
compound index, rather than resorting to a sequential scan of the data could
there not be a sequential scan of the selected part of the index for any
other fields that happen to be present in that index? >>

The hard part is determining under which conditions all of the above is
true.  The actual code involved in doing the index scan, etc. is trivial.
Don't worry - EDB will have this capability shortly.  DBISAM couldn't do it
at all because of the way that character strings were compared in indexes.
EDB doesn't have this architectural issue.  It's one of the reasons, for
example, why EDB already has a LIKE operator that always uses an available
index, if it can find one.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
news:93AC39EF-ED4D-4040-9688-003F985B0932@news.elevatesoft.com:

> The hard part is determining under which conditions all of the above
> is true.  The actual code involved in doing the index scan, etc. is
> trivial. Don't worry - EDB will have this capability shortly.

Good news!

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.02 build 1)