RE: Bitmap index costing - how to influence
Date: Mon, 30 Mar 2009 13:23:09 +0400
Message-ID: <17182849A17B3C44AE01780E7B519C82026608EA_at_WISE.netcracker.com>
It's a documentation bug 5160959 THE HINT OPT_PARAM SHOULD BE DOCUMENTED <https://metalink2.oracle.com/metalink/plsql/f?p=130:15:4647418408099426 43::::p15_database_id,p15_docid,p15_show_header,p15_show_help,p15_black_ frame,p15_font:Bug,5160959,1,0,1,helvetica> , and explaination is in Note 377333.1 OPT_PARAM Hint in 10g R2.
<https://metalink2.oracle.com/metalink/plsql/f?p=130:14:4647418408099426 43::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_ frame,p14_font:NOT,377333.1,1,1,1,helvetica> so it's safe and supported since 10gR2.
Thanks,
Timur Akhmadeev
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling
Sent: Monday, March 30, 2009 1:02 AM
To: oracle-l_at_freelists.org
Cc: info_at_sqltools-plusplus.org
Subject: Re: Bitmap index costing - how to influence
The opt_param hint is documented, albeit only in the Oracle 11i SQL Reference Manual even though it is also available in 10g (R2 at least, haven't tried it in 10.1.x ):
"The OPT_PARAM hint lets you set an initialization parameter for the duration of the current query only. This hint is valid only for the following parameters: OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING, and STAR_TRANSFORMATION_ENABLED."
At 06:02 AM 3/29/2009, Randolf Geist wrote:
> Why use undocumented OPT_PARAM when INDEX_COMBINE is documented and
> provided?
Greg,
thanks for making that point. I generally second that one shouldn't use undocumented parameters/features if possible. Unfortunately Oracle doesn't provide (yet) an officially documented way to influence parameters like OICA / OIC on statement level, that's why I suggested it in this particular case to limit the effects of this parameter as much as possible.
There are obviously many different possibilities how the index usage in this particular case could be hinted/forced, but the INDEX_COMBINE and the OPTIMIZER_INDEX_COST_ADJ suggestions provide quite different means, so it depends what the objective is in the particular situation.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com <http://www.centrexcc.com/>
The information transmitted herein is intended only for the person or entity to which it is addressed and may contain confidential, proprietary and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 30 2009 - 04:23:09 CDT