Re: Blob woes

In article <VV_O3.1023$kh.3065@newsfeed.slurp.net>, Rob Wilson <rwilson@ntsource.com> writes >An interesting thing was pointed out to me recently. Let us suppose >we have a database on IDS 7.30UC5. Let us further suppose this >database has one table of the flavor: > >create table tab1 ( > cola char(2), > colb char(36), > data byte, > primary key(cola, colb) >);> >Let us call the primary key index idx_pkey. > >If you run: > >select cola, colb from tab1 WHERE>cola = 'E6' >AND colb >= 'PROG 123456789123456'; > >you get an index read. If you run > >select cola, colb, data from tab1 WHERE>cola = 'E6' >AND colb >= 'PROG 123456789123456'; > Informix cannot use the index to get the blob data since it is not in the index pages. How many rows in the table, how many satisfy cola = 'E6' >AND colb >= 'PROG 123456789123456'; ??? Informix can either a) read all rows, sequential scan, few disk seeks since data read sequentially. Most seeks track to track not across the disk. b) Read one page from index, seek to the data (probably >1 track, hence longer seek time then above), read one row of data (which is the same as a) since it needs to read all of the row since you are selecting all the columns), seek back to the index, read an index page, seek back to the data... Indexs are the best option 95% of the time but not all the time. >you get a sequential scan. All the blobs are less than 1K in size. I >have so far tried the following to avoid the sequential scan: > >Optimizzer directives (they weren't even ignored, they weren't >mentioned in sqexplain.out at all) >Update Statistics (as recommended in the book, as well as others) >Adding an index to just colb (no change) >Adding an index to just cola (no change, none expected) >using just the integer substring portion of colb (no change) >using the maximum value of colb (index path, yay!)> >Has anyone seen this or know a workaround, other than a smaller result >set? > > >-- >Rob Wilson >rwilson@ntsource.com > -- David Williams