Hi

In my Application ( not DB structure , my application ) all string  fields
are Case Insensitive.

for my all string sono upper.



now... in db structure...


i have all indexes ( string ) set CASE-INS = True

( ASC-ANSI-CASEINS )


for active the index optimize when i execute a query like this

SELECT * FROM MYCITY  WHERE CITY = 'LAS VEGAS'
( INDEX : CITY;STREET )

what i must do for to have a fast query result ?

Mauro


What does the execution plan say?

Roy Lambert [Team Elevate]

> Roy Lambert [Team Elevate]

my table have : 82'675 records.




================================================================================
SQL Query (Executed by ElevateDB 2.02 Build 3)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly
the
same as the SQL that was originally entered.  However, none of the
differences
alter the execution results in any way.
================================================================================

SELECT ALL "CAP"."PROVINCIA" AS "PROVINCIA", "CAP"."COMUNE" AS "COMUNE",
"CAP"."CAP" AS "CAP", "CAP"."TIPO" AS "TIPO", "CAP"."VIA" AS "VIA",
"CAP"."NUMERI
CIVICI" AS "NUMERI CIVICI" FROM "CAP" WHERE "COMUNE" = 'LOS ANGELES'

Source Tables
-------------

CAP: 82675 rows

Result Set
----------

The result set was sensitive
The result set consisted of zero or more rows
The result set was ordered using the table's natural order

Filtering
---------

The following filter condition was applied to the Query table:

"COMUNE" =  'LOS ANGELES' [Row scan (cap): 82675 rows, 16535000 bytes
estimated
cost]

================================================================================
510 row(s) returned in 8,127 secs
================================================================================

I have all indexes with Case Insensitive set on , but this is not enough for
turn on optimize engine


the query scan all records.

Mauro

Two things:


1. Can you post the column definitions for the table as well please.

2. What happens if you specify the collation eg
        "COMUNE" collate ansi_ci =  'LOS ANGELES'

Roy Lambert [Team Elevate]

"Roy Lambert" <roy.lambert@skynet.co.uk> ha scritto nel messaggio
news:1247EBCA-188B-4C0C-A1EC-F3A03102A459@news.elevatesoft.com...
> Mauro
>
> Two things:
>
>
> 1. Can you post the column definitions for the table as well please.
not easy with edb...

my table cap is :

6 FIELDS VARCHAR - ANSI - NULLABLE = TRUE  - GENERATED = NO - COMPUTED = NO



> 2. What happens if you specify the collation eg
> "COMUNE" collate ansi_ci = 'LOS ANGELES'

SELECT * FROM CAP WHERE COMUNE  collate ansi_ci  = 'LOS ANGELES'
work well !  - 1 second for query

PLAN :

Filtering
---------
The following filter condition was applied to the Query table:

"COMUNE" COLLATE "ansi_ci" = 'VERBANIA' [Index scan (cap.COMUINDI):, 160
keys,
28672 bytes estimated cost]

--------------------
scan only 160 records. not more all db.



Question :

If i convert all Fields string in : from ANSI to ANSI_CI for have fast query
, are there any problems ?

Mauro


Only that the fields are case insensitive so 'Los Angeles' would be the same as 'LOS ANGELES'. I've set all my VARCHAR fields to case insensitive.

Roy Lambert [Team Elevate]

>I've set all my VARCHAR fields to case insensitive.

Thank Roy   

Mauro,

You should check out this technical article ("Collations and Comparisons")
on this subject:

http://www.elevatesoft.com/articles?action=view&articleno=2

--
Tim Young
Elevate Software
www.elevatesoft.com