![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
![]() |
Tue, May 1 2007 5:52 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
Is CONTAINS case insensitive as with DBISAM or does it depend on the collation? Roy Lambert |
Wed, May 2 2007 11:27 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Wed, May 2 2007 11:44 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Thu, May 3 2007 4:31 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Fri, May 4 2007 3:03 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Fri, May 4 2007 11:30 AM | Permanent Link |
Chris Erdal | "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) |
Mon, May 7 2007 11:22 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Mon, May 7 2007 11:25 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Wed, May 16 2007 10:42 AM | Permanent Link |
Chris Erdal | "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) |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |