Re: slow DSS large dataset query, help

a_blonde@mindless.com wrote: > > ---- you wrote: > > Instead of doing the "radical" drop of indexes, force a hash join by adding > > "+0" to the equijoin criteria: > > > > select > > A.c1, > > A.c2, > > A.c3, > > C.c1 > > from > > Table_A A, > > Table_B B, > > Table_C C > > where > > month(A.create_timestamp) = 11 and > > year(A.create_timestamp) = 1995 and > > A.c1 = (B.c1 + 0 )and > > B.c2 = (C.c2 + 0 ) and > > B.c3 = (C.c3 + 0 ) > > Sure Dude, but why not use a supported method? Like: > > select {+USE_HASH(Table_A/BUILD)} > A.c1, > A.c2, > A.c3, > C.c1 > from > Table_A A, > Table_B B, > Table_C C > > or whatever. This gets my vote. You can choose which table to build the hash table on with the BUILD keyword. Before version 7.3 there used to be an environment variable you could set to force a hash join. Something like HASH_JOIN=FORCE. But I don't have the details with me. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock http://www.informix.com |//////// /| | mailto:mdstock@mydas.freeserve.co.uk |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+