dear derby developers, i've got two tables that i join and get good performance with, based on the indicies i've created.
but when i create trivial views on those tables and join with those views instead, performance is bad and optimizer seems to choose a suboptimal plan. by "trivial views" i mean views that just refer to a subset of a single table's columns, and sometime rename columns, but that's all. please see attached "sql.txt" for the precise sequence of sql statements that results in this behaviour, as well as a portion of the derby.log file showing the optimizer choosing a sub-optimal plan. do you think this is a bug? or am i doing something incorrectly? best regards, mike
-- create table "IVYOPPRC", trivial view "opprice", and various indicies create table IVYOPPRC (fd char(8) not null, id int, date char(8) not null, root char(5) not null, suffix char(2) not null, strike int, expdate char(8), cpflag char(1), bid float, offer float, lasttrade char(8), volume int, openint int, specialsettle char(1), iv float, delta float, gamma float, vegakappa float, theta float, optionid int, adjfact int) create view opprice (fd,id,date,root,suffix,strike,expiry,cpflag,bid,offer,lasttradedate,volume,iv,delta,gamma,vega,theta) as select fd,id,date,root,suffix,strike,expdate,cpflag,bid,offer,lasttrade,volume,iv,delta,gamma,vegakappa,theta from IVYOPPRC create unique index I_IVYOPPRC_fd_date_root_suffix_fdd6c298f4891f39273451dbec93941e on IVYOPPRC (fd,date,root,suffix) create index I_IVYOPPRC_fd_ac88386b151a07e4198a4136f4deb963 on IVYOPPRC (fd) create index I_IVYOPPRC_id_97ebf034caf8ba8dd3a7eaeec891cf22 on IVYOPPRC (id) create index I_IVYOPPRC_date_653deea0ce11efba4c169a20fe754add on IVYOPPRC (date) create index I_IVYOPPRC_id_date_9f67db48b3c0dfa8a87bdb2869a9211c on IVYOPPRC (id,date) create index I_IVYOPPRC_id_date_root_suffix_strike_1a44cba5297f911a54474a4696dc5f7e on IVYOPPRC (id,date,root,suffix,strike) create index I_IVYOPPRC_id_date_root_suffix_899993d09a0a2221ceff4f0c12b07305 on IVYOPPRC (id,date,root,suffix) create index I_IVYOPPRC_id_fd_375be1f05f5fec1eaeb36c10bda9b133 on IVYOPPRC (id,fd) create index I_IVYOPPRC_id_fd_root_suffix_strike_4e3160d0fbf02288fabf8ddcf5c5a3f1 on IVYOPPRC (id,fd,root,suffix,strike) create index I_IVYOPPRC_id_fd_root_suffix_8297ec685ddc3040a22cef64348142ae on IVYOPPRC (id,fd,root,suffix) create index I_IVYOPPRC_expdate_strike_cpflag_e2beeea6cb0136bb492a8f213b6c595d on IVYOPPRC (expdate,strike,cpflag) -- populate "IVYOPPRC" with 340378 rows, via CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IVYOPPRC', '<path ommitted>', ' ', null, null, 0) -- create table "IVYSECUR", trivial view "sec", and various indicies create table IVYSECUR (fd char(8) not null, id int not null, cusip char(8), ticker char(6), sic char(4), indexflag char(1), ex int, class char(1), itype char(1), igroup char(3)) create view sec (fd,id,cusip,ticker,indexflag) as select fd,id,cusip,ticker,indexflag from IVYSECUR create unique index I_IVYSECUR_fd_id_3d77ec3e52f45bbe55448221df5fece9 on IVYSECUR (fd,id) create index I_IVYSECUR_cusip_18c02f3b983b57c7777a40fd39494d4c on IVYSECUR (cusip) create index I_IVYSECUR_ticker_bcb18458d8add7a61f40ba1f7712e80e on IVYSECUR (ticker) create index I_IVYSECUR_indexflag_b0eedb977a40d24c8f4ae6d2f76c6560 on IVYSECUR (indexflag) create index I_IVYSECUR_id_ticker_2ddfbe371326a41ecf4c73b89e0a6de2 on IVYSECUR (id,ticker) create index I_IVYSECUR_fd_0d05b8c715b21319bbcb7661ee233b88 on IVYSECUR (fd) -- populate "IVYSECUR" with 42767 rows via CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'IVYSECUR', '<path ommitted>', ' ', null, null, 0) -- select some data from a join on the two tables: takes 154 milliseconds select o.root,o.suffix,o.strike,o.expdate,o.cpflag from IVYOPPRC as o, IVYSECUR as s where s.fd = o.fd and s.id = o.id and s.ticker='OIH' and o.fd >= '20081001' group by o.root,o.suffix,o.strike,o.expdate,o.cpflag order by o.expdate,o.strike,o.cpflag -- select some data from a join on the two trivial views but otherwise the same as before: takes 1,911 milliseconds (why not 154 milliseconds as before?) select o.root,o.suffix,o.strike,o.expiry,o.cpflag from opprice as o, sec as s where s.fd = o.fd and s.id = o.id and s.ticker='OIH' and o.fd >= '20081001' group by o.root,o.suffix,o.strike,o.expiry,o.cpflag order by o.expiry,o.strike,o.cpflag -- also, see "derby.log" for details of what the optimizer does
derby.log
Description: Binary data
