Sort Merge
Does anyone know if Informix 7.3x can use a sort merge join? There is some Informix documentation that indicates it at least used to exist. However, the optimizer directives I have found don't seem to support it. I have a situation where I have a one-to-one relation between 500 million row tables that are ordered on the primary key. The queries on these tables join anywhere from 8 to 100 million rows. Ideally, I would like Informix to scan the leaf levels of the unique indexes and merge the equivalent data rows. To date, I have only gotten a nested loop or hash join. The hash join only performs well with a generous allotment of PDQ resources and the nested loop is slow and CPU intensive as it navigates through several levels of index for every row. Covering the query on one of the tables with an index does not help much since IO is not the bottleneck. Any thoughts other than combining the two tables? Jay Buckler
My only suggestion would be to try SET OPTIMIZATION FIRST_ROWS. This will tell the optimizer that you want the data without the delays caused by building a hash table. Perhaps then it will choose a merge join. Art S. Kagel Jay Buckler wrote: > > Does anyone know if Informix 7.3x can use a sort merge join? There is some > Informix documentation that indicates it at least used to exist. However, > the optimizer directives I have found don't seem to support it. > > I have a situation where I have a one-to-one relation between 500 million > row tables that are ordered on the primary key. The queries on these tables > join anywhere from 8 to 100 million rows. Ideally, I would like Informix to > scan the leaf levels of the unique indexes and merge the equivalent data > rows. To date, I have only gotten a nested loop or hash join. The hash > join only performs well with a generous allotment of PDQ resources and the > nested loop is slow and CPU intensive as it navigates through several levels > of index for every row. Covering the query on one of the tables with an > index does not help much since IO is not the bottleneck. Any thoughts other > than combining the two tables? > > Jay Buckler