case-insensitivity and Oracle

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

case-insensitivity and Oracle

Bart van den Eijnden-4
Hi list,

to get back to Dave's and my own thread on case-sensitivity from a few weeks ago, it seems RC4 already does queries case-insensitive, at least the Oracle driver, see the following SQL:

 919903 [FINE] org.geotools.data.jdbc.JDBC1DataStore - About to execute query: SELECT "OGR_FID", "ORA_GEOMETRY", "ID", "KLASSE", "OPDATUM", "VVDATUM", "PERCNR", "GEMCODE", "SECTIE", "NUMMER", "PERCEEL" FROM "DCMRPERCELEN" WHERE UPPER("GEMCODE") LIKE UPPER('abb00%')

The unfortunate thing is that the UPPER approach costs a lot of performance, on my test query it takes 8 seconds with the UPPER, and less than half a second without. The solution is to create function based indexes with upper, that speeds it up back to half a second. But it should be documented somewhere I guess.

So make sure you create function-based indexes, e.g.:

CREATE INDEX SYSTEM.dcmrpercelen_gemcode_idx ON SYSTEM.DCMRPERCELEN
(upper(GEMCODE))
LOGGING
NOPARALLEL;

Best regards,
Bart

Bart van den Eijnden
Syncera IT Solutions
Postbus 270
2600 AG  DELFT

tel.nr.: 015-7512436
email: [hidden email]


-------------------------------------------------------
This SF.Net email is sponsored by the JBoss Inc.
Get Certified Today * Register for a JBoss Training Course
Free Certification Exam for All Training Attendees Through End of 2005
Visit http://www.jboss.com/services/certification for more information
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Reply | Threaded
Open this post in threaded view
|

Re: case-insensitivity and Oracle

Chris Holmes-2
Quoting Bart van den Eijnden <[hidden email]>:

> Hi list,
>
> to get back to Dave's and my own thread on case-sensitivity from a
> few weeks ago, it seems RC4 already does queries case-insensitive, at
> least the Oracle driver, see the following SQL:
>
>  919903 [FINE] org.geotools.data.jdbc.JDBC1DataStore - About to
> execute query: SELECT "OGR_FID", "ORA_GEOMETRY", "ID", "KLASSE",
> "OPDATUM", "VVDATUM", "PERCNR", "GEMCODE", "SECTIE", "NUMMER",
> "PERCEEL" FROM "DCMRPERCELEN" WHERE UPPER("GEMCODE") LIKE
> UPPER('abb00%')
>
> The unfortunate thing is that the UPPER approach costs a lot of
> performance, on my test query it takes 8 seconds with the UPPER, and
> less than half a second without. The solution is to create function
> based indexes with upper, that speeds it up back to half a second.
> But it should be documented somewhere I guess.
>
> So make sure you create function-based indexes, e.g.:
>
> CREATE INDEX SYSTEM.dcmrpercelen_gemcode_idx ON SYSTEM.DCMRPERCELEN
> (upper(GEMCODE))
> LOGGING
> NOPARALLEL;

Could you doc it for us?
http://docs.codehaus.org/display/GEOSDOC/Oracle+DataStore  Perhaps in a
little section called 'hints and tricks' or something, and there's
probably other info in the docs that could go in that section.  And add
anything else that you've figured out as well.  The page is a wiki, so
you just need to sign up (link on the corner of the page), and start
editing.

best regards,

Chris

>
> Best regards,
> Bart
>
> Bart van den Eijnden
> Syncera IT Solutions
> Postbus 270
> 2600 AG  DELFT
>
> tel.nr.: 015-7512436
> email: [hidden email]
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by the JBoss Inc.
> Get Certified Today * Register for a JBoss Training Course
> Free Certification Exam for All Training Attendees Through End of
> 2005
> Visit http://www.jboss.com/services/certification for more
> information
> _______________________________________________
> Geoserver-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>




----------------------------------------------------------
This mail sent through IMP: https://webmail.limegroup.com/


-------------------------------------------------------
This SF.Net email is sponsored by the JBoss Inc.
Get Certified Today * Register for a JBoss Training Course
Free Certification Exam for All Training Attendees Through End of 2005
Visit http://www.jboss.com/services/certification for more information
_______________________________________________
Geoserver-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geoserver-users