Re: Frag elimination Bug ? Workaround ?

David Williams <djw@smooth1.demon.co.uk> wrote: >In article <F122B1778C85B6D6004166D7852565F5.004166D6852565F5@notes- >inet1.harland.net>, Barry Leb <bleb@harland.net> writes >>Has anyone seen this problem and do you know of a workaround? >> >>Sun 6000e, Solaris 2.6 >>Informix 7.24.uc4 >> >>Fragmenting a table across 58 dbspaces.(carrying 13 mos of weekly data >>which will roll-off each weekl). Fragmenting by expression on the >>first column which is a date. This is what happens: >> > Exactly what are the fragment expressions? fragment by expression inv_dte = '3/31/1997' in dbs01 inv_dte = '4/7/1997 in dbs02 . . . etc Tried it with and without a remainder. >>select * from table_a>>returns correct results, seq scans all frags >> >>select * from table_a where inv_dte = '03/31/97'>>returns no rows found, seq scans frag 58 >> >>select * from table_a where inv_dte = '04/07/97'>>returns no rows found, seq scans frag 58 >> >>select * from table_a where inv_dte in ('03/31/97', '04/07/97')>>returns correct results, seq scans frags 1,2 >> >>select * from table_a where inv_dte in ('03/31/97', '03/31/97')>>returns correct results, seq scans frag 1 >> >>select * from table_a where inv_dte in ('03/31/97')>>returns no rows found, seq scans frag 58 >> >>Have tried these queries with and without indexes getting same >>results. PDQ settings also have no effect. >> >>Any suggestions? > Try update statistics high on the inv_dte column. Did that, no difference. > What is the output from set explain on. Basically, it does a serial scan on fragment 58 when the data is located in fragment 1. > Check under $INFORMIXDIR/release/en_us/0333/ONLINE* > are there any known problems. > Is 7.24.uc5 out yet? No. >>Barry Leb >> >>barryleb@atl.mindspring.com >> >-- >David Williams >Maintainer of the Informix FAQ > Primary site (Beta Version) http://www.smooth1.demon.co.uk > Official site http://www.iiug.org/techinfo/faq/faq_top.html >I see you standin', Standin' on your own, It's such a lonely place for you, For >you to be If you need a shoulder, Or if you need a friend, I'll be here >standing, Until the bitter end... >So don't chastise me Or think I, I mean you harm... >All I ever wanted Was for you To know that I care Barry Leb barryleb@atl.mindspring.com