<No Subject Supplied>

David Williams respond: >>>7. OPCOMPIND is ok to use with 7.2x?? I heard this was a showstopp= er. > >> Set it to 0 to allow index usage otherwise you get dynamic hash jo= ins >> and sequential scans. > > Can you tell more about this. (I have OPCOMPIND=3D2) OPTCOMPIND means OPTimizer COMPare the cost of INDicies. OPTCOMPIND=3D0 Online tends to prefer the pre 6.x way (nested loop joins) i.e. index usage. OPTCOMPIND=3D2. Online deciedes whether or not to use an index based = on the cost cmputed by the optimizer and allows the use of Dynamic Hash Joins. This is where both tables to be joined are sequential scanned.= The first table is read into memory and a hash table built from the columns you are joining on. The second table is sequential scanned an= d the join columns are hashed and looked up in the hash table. i.e. bot= h tables to be joined are sequential scanned. Put simply the Online optimizer gets the costs wrong! It tends to prefer these Dynamic Hash Joins. This means sequential scans are done= rather than using indexes. Setting OPTCOMPIND=3D0 means indexes will almost certainly be used wh= ere they should be. Tim wrote: I understand. Thanks. I had heard about this being a bug on HP, or so= me other platform, so have been setting it off. Just wanted more background. My Question: Is this only happen on HP? i use OPCOMPIND=3D2 on AIX 4.2.1. and ODS 7.= 14UD6, should i set it to 0? Thanks. =