Re: star transformation not chosen because of bind variables

From: Tanel Poder <change_to_my_first_name_at_integrid.info>
Date: Sun, 28 Sep 2003 20:24:45 +0300
Message-ID: <3f77195e_1@news.estpak.ee>

"Jim Reesman" <jimreesman_at_yahoo.com> wrote in message news:ef9d9e.0309251232.7d247cad_at_posting.google.com...
> In the following listing, the first query has 2 literal values in the
> where clause. It results in a star transformation being chosen and the
> plan and response time are good. The second query has simply had the 2
> literals replaced with bind variables. Despite including the
> STAR_TRANSFORMATION hint, the CBO chooses the full scan and index
> range scans (it does so seemingly without regard to _any_ hints - eg.
> FACT () ).
>
> How can I get the second query to use the first execution plan? I'm
> using 9.2.

Hi!

Check the following link for "Star Tranformation Restrictions" http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/schemas.htm#11975

Star transformation is not supported for tables with any of the following characteristics:
* Queries that contain bind variables

So, do not use bind variables in your query. In DW you probably aren't running your query that often anyway that you'd get parsing problems...

Btw, autotrace and explain plan aren't the best tools for analyzing execution plans, especially when you're dealing with complex queries involving rewrites, transformations and recursive execution plans. 10046 trace+tkprof is the real thing, even v$sql_plan doesn't help you with recursive plans.

Tanel. Received on Sun Sep 28 2003 - 12:24:45 CDT