Oracle-L: Partition Pruning and Hash Joins in 8i

From: Larry Elkins <elkinsl_at_flash.net>
Date: Wed, 06 Aug 2003 20:54:24 -0800
Message-ID: <F001.005C952F.20030806205424@fatcity.com>

Listers,

8.1.7.4. Anyone use the _subquery_pruning_cost_factor and _subquery_pruning_reduction hidden parameters to make the CBO a bit more aggressive about using recursive SQL and the TBL$OR$IDX$PART$NUM internal function to get partition pruning on hash joins (for example, when the value(s) used for partition elimination are coming from a dimension, and the fact is partitioned, and a hash join is used)? Metalink Note 179518.1 talks a bit about this (for those that haven't seen the behavior).

We get the behavior on a handful of hash joins, where he will go ahead and do the recursive SQL on a dimension to get the distinct partition key values for the fact table. But we have many cases where the CBO doesn't, where the cost of that recursive SQL wouldn't be that bad and we would like to see it happen. So I've been experimenting with those parameters. There's not a lot out there on those parameters.

Anyway, my main question is, for those that may have used those parameters, have you seen any ill side effects or bugs, partition pruning related or not? Do they impact anything else besides the pruning capabilities during hash joins? I intend to test quite thoroughly but am always a bit leery of using undocumented parameters. And yes, we will get support involved before we would even think of using them in a production DB, but you know how support is about providing info on undocumented parameters.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Larry Elkins
INET: elkinsl_at_flash.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Aug 06 2003 - 23:54:24 CDT