Hi, Could you post a complete, reproducible test case please? A script would be nice, something like this:
create table test(name varchar); create index on test(name); insert into test select x from system_range(1, 2000000); and a Java program with a main method that runs this query: select max(name) from ((select max(name) as best from test where name like ?)); Regards, Thomas On Thursday, December 22, 2011, gonfi wrote: > using h2 1.3.161 > > using spring JdbcTemplate, this query > select max(best) from ((select max(celeblevel) as best from actor > where name like ?)) > with value 'chan, jackie %' > doesn't return (i wated 10 minutes). > > a full table scan (forced by something like "where a='x' or b='x') > takes 18 seconds. table has ~2mio records. > > when using string concatenation then the query returns instantly > select max(best) from ((select max(celeblevel) as best from actor > where name like 'chan, jackie %')) > > it's a reduced version to reproduce the problem. > > the goal would be to write > select max(celeblevel) as best from actor where name like ? or > nameascii like ? > > which must be written as (in order to avoid the table scan for the > 2nd) > select max(best) from ( > (select max(celeblevel) as best from actor where name like ?) > union > (select max(celeblevel) as best from actor where nameascii like ?) > ) > > note: a yet simpler version of the query > select max(celeblevel) as best from actor where name like ? > also returns instantly. > > now i have the choice between not using named params (and thus making > my code possibly vulnerable to sql injection), or running 2 separate > sql queries (and thus changing even more code). or am i missing > something? > > > my table: > > COLUMN_NAME DATA_TYPE PK NULLABLE DEFAULT > AUTOINCREMENT REMARKS JDBC > Type SCALE/SIZE PRECISION POSITION > ID INTEGER YES NO (NEXT VALUE FOR > PUBLIC.SYSTEM_SEQUENCE_7EE1DA42_1DFF_4F1D_84E3_760C8893C4DA) YES > 4 10 > 0 1 > JMDBID INTEGER NO NO 0 NO 4 10 0 > 2 > NAME VARCHAR_IGNORECASE(60) NO NO '' NO 12 > 60 0 3 > NAMEASCII VARCHAR_IGNORECASE(60) NO NO '' NO > 12 60 0 4 > GENDER TINYINT NO YES NO -6 3 0 > 5 > CELEBLEVEL SMALLINT NO YES NO 5 > 5 0 6 > > and the indexes: > > INDEX_NAME UNIQUE PK DEFINITION TYPE > ACTOR_JMDBID NO NO JMDBID ASC NORMAL > ACTOR_NAME NO NO NAME ASC NORMAL > ACTOR_NAMEASCII NO NO NAMEASCII ASC NORMAL > PRIMARY_KEY_3 YES YES ID ASC NORMAL > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To post to this group, send email to > [email protected]<javascript:;> > . > To unsubscribe from this group, send email to > [email protected] <javascript:;>. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
