Optimizer Magic

I support an imaging product from Plexus, XDP, which was built on Informix version 2.11. The answers to many of the ISQL query issues I deal with seem to be attributable to the mysterious workings of the optimizer. A current puzzler: The following 3 queries each execute and return within 2 seconds: 1) select seq_no, scan_date from datatable where (bol_ref = "0004476H") 2) select seq_no, scan_date from datatable where (short_bill = "0004476H") 3) select seq_no, scan_date from datatable where (short_bill = "0004476H") union select seq_no, scan_date from datatable where (bol_ref = "0004476H") The following query executes and returns in 90 seconds: select seq_no, scan_date from datatable where (short_bill = "0004476H") or (bol_ref = "0004476H") The table datatable contains 29 columns, 23 of which have simple indexes (including all columns used in queries above). The only unique index is the one on seq_no. Rowsize of datatable is 410. Data types: Column Type Nulls seq_no serial no scan_date date yes bol_ref char(20) yes short_bill char(10) yes The table contains approximately 126,000 rows of data. A typical tbstat during the execution of the _or_ query: ****** extract of tbstat -a ********** Users address flags pid user tty wait nlocks nreads nwrites c004f418 ------D 589 informix console 0 0 1195 8 c004f470 ------D 0 informix console 0 0 0 0 c004f520 ----R-- 858 informix pty/ttys 0 0 252 0 c004f578 ------- 619 aimsws3 - 0 4 29292 124 c004f628 ------M 871 informix pty/ttys 0 0 4 0 5 active, 20 total Semaphores name address lock wait user bf[6] c0126db8 1 0 c004f520 bf[22] c0127238 1 0 c004f520 bf[45] c01278b0 1 0 c004f520 bf[64] c0127e08 1 0 c004f520 bf[66] c0127e98 1 0 c004f520 bf[104] c0128948 1 0 c004f520 bf[125] c0128f30 1 0 c004f520 bf[155] c01297a0 1 0 c004f520 Locks address hash same wtlist owner lklist type partnum rowid c00682d8 0 0 0 c004f578 c00683b8 HDR+X 1000001 3900 c0068398 0 0 0 c004f578 0 HDR+IX 1000001 0 c00683b8 0 0 0 c004f578 c00683f8 HDR+IX 1000004 0 c00683f8 0 0 0 c004f578 c0068398 HDR+X 1000001 5800 4 active, 20000 total, 2048 hash buckets Buffers address user flags pagenum memaddr pgflags c0126db8 c004f520 0 110cd5 c012d800 1 c0127238 c004f520 0 110cd9 c0135800 1 c01278b0 c004f520 0 110cd6 c0141000 1 c0127e08 c004f520 0 110cd8 c014a800 1 c0127e98 c004f520 0 110cd4 c014b800 1 c0128948 c004f520 0 110cda c015e800 1 c0128f30 c004f520 0 110cdb c0169000 1 c01297a0 c004f520 0 110cd7 c0178000 1 0 modified, 200 total, 64 hash buckets, 2048 buffer size Partitions address next flags ucount partnum physaddr npages nused nextns c0104dbc 0 1 1 1000001 10000e 2505 107 1 c0104ea4 0 1 4 1000004 100011 550 342 3 c0104f8c 0 1 4 1000005 100012 220 172 2 c0105074 0 1 4 1000035 100042 550 398 3 c010515c 0 1 4 1000036 100043 220 200 2 c0105244 0 1 1 1000006 100013 8 6 1 c01056cc 0 1 1 1000038 100045 50000 37275 1 7 active, 500 total, 128 hash buckets Physical Logging buffer bufsize bufused numpages numwrits pages/io c019f000 16 0 5330 431 12.37 phybegin physize phypos phyused %used 1009d6 500 40 0 0.00 Logical Logging buffer bufsize bufused numrecs numpages numwrits pages/io c01a7000 16 0 15844 272 116 2.34 address number flags uniqid begin size used %used c0125a90 1 ----- 0 100bca 250 0 0.00 c0125aac 2 ----- 0 100cc4 250 0 0.00 c0125b38 7 ----- 0 1011aa 250 0 0.00 c0125b54 8 ----- 0 1012a4 250 0 0.00 c0125b70 9 ----- 0 10d9e0 250 0 0.00 c0125b8c 10 U-C-- 482 10dada 250 38 15.20 c0125ba8 11 ----- 0 10dbd4 250 0 0.00 c0125bc4 12 ----- 0 10dcce 250 0 0.00 Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 67691 201043 3447211 98.04 6266 12909 138438 95.47 isamtot open start read write rewrite delete commit rollbk 3240363 21404 40112 1560935 14345 38894 6451 17710 0 ovpartn ovlock ovuser ovbuff usercpu syscpu numckpts flushes 0 0 0 0 404.43 72.08 92 302 bufwaits lokwaits lockreqs deadlks semwaits ckpwaits compress 67 0 820116 0 96 13 1969 ******************************************* The first 6 active partitions are system tables and the 7th is datatable. As you can tell, during most of the query, 8 or 9 buffer semaphores to data pages are typically held at any one time. The pagenums referenced are turbo data pages and are usually in sequence. To me, the query seems to be proceeding as if the columns involved are not indexed. Look forward to any comments! Thanks, Mike Cox Hewlett Packard mjcx@hpuerca.atl.hp.com End of Item 112. 13:02:20