Re: Star Transformation Issue

maybe something too simple:
Have you tried the hint?
Why star_transformation_enabled at temp_disable? Only makes it complicated.

Probably you should not put conditions in a on clause that have nothing to do with the Join. Put them in the where clause. E.g.

         AND DIM_PS.START_DATE <= to_date('03/06/2015','mm/dd/yyyy')

         AND DIM_PS.END_DATE >= to_date('03/06/2015','mm/dd/yyyy'))

regards

Lothar
On 22.10.2015 22:17, Deas, Scott wrote:
>
> All,
>
> I’m trying to enable star transformation in an 11.2.0.4.5 EE RAC
> database (running on AIX 6.1).
>
> We have bitmap indexes on FK (with RELY option) on our fact tables,
> and non-validated PKs on our Dimension tables. Fact table is a daily
> partitioned table, with approx. 6,811,000,000 rows (each partition has
> on average 32,000,000 rows).
>
> SQL> show parameter optimizer
>
> NAME TYPE VALUE
>
> ------------------------------------ ----------- -------------
>
> _optimizer_cartesian_enabled boolean FALSE
>
> optimizer_capture_sql_plan_baselines boolean FALSE
>
> optimizer_dynamic_sampling integer 2
>
> optimizer_features_enable string 11.2.0.4
>
> optimizer_index_caching integer 0
>
> optimizer_index_cost_adj integer 100
>
> optimizer_mode string ALL_ROWS
>
> optimizer_secure_view_merging boolean TRUE
>
> optimizer_use_invisible_indexes boolean FALSE
>
> optimizer_use_pending_statistics boolean FALSE
>
> optimizer_use_sql_plan_baselines boolean TRUE
>
> SQL> show parameter star
>
> NAME TYPE VALUE
>
> ------------------------------------ -----------
> ------------------------------
>
> _always_star_transformation boolean TRUE
>
> dg_broker_start boolean FALSE
>
> fast_start_io_target integer 0
>
> fast_start_mttr_target integer 0
>
> fast_start_parallel_rollback string HIGH
>
> log_archive_start boolean FALSE
>
> star_transformation_enabled string TEMP_DISABLE
>
> Query (not my query, I’m just the DBA…) :
>
> SELECT
>
> DIM_PS.PLAN_SPONSOR_NAME,
>
> DIM_P.PLAN_CODE,
>
> DIM_MT.MONEY_TYPE_CODE,
>
> DIM_MT.MONEY_TYPE_NAME,
>
> DIM_F.FUND_CODE,
>
> DIM_F.FUND_NAME,
>
> DIM_F.FUND_TICKER_SYMBOL,
>
> DIM_P.PLAN_LEGAL_NAME,
>
> sum(FACT_PFS.TOTAL_BALANCE) as TOTAL_BALANCE
>
> FROM
>
> DIM_F
>
> INNER JOIN FACT_PFS
>
> ON (FACT_PFS.FUND_KEY=DIM_F.FUND_KEY
>
> AND FACT_PFS.PRICE_DATE <= to_date('03/06/2015','mm/dd/yyyy')
>
> AND DIM_F.START_DATE <= to_date('03/06/2015','mm/dd/yyyy')
> AND DIM_F.END_DATE >= to_date('03/06/2015','mm/dd/yyyy'))
>
> INNER JOIN DIM_PART
>
> ON (FACT_PFS.PARTICIPANT_KEY=DIM_PART.PARTICIPANT_KEY
>
> AND FACT_PFS.PRICE_DATE <= to_date('03/06/2015','mm/dd/yyyy'))
>
> INNER JOIN DIM_P
>
> ON (FACT_PFS.PLAN_KEY=DIM_P.PLAN_KEY
>
> AND FACT_PFS.PRICE_DATE <= to_date('03/06/2015','mm/dd/yyyy')
>
> AND DIM_P.START_DATE <= to_date('03/06/2015','mm/dd/yyyy')
>
> AND DIM_P.END_DATE >= to_date('03/06/2015','mm/dd/yyyy')
> AND DIM_P.PLAN_CODE IN ('UTX-003'))
>
> INNER JOIN DIM_MT
>
> ON (FACT_PFS.MONEY_TYPE_KEY=DIM_MT.MONEY_TYPE_KEY
>
> AND FACT_PFS.PRICE_DATE <= to_date('03/06/2015','mm/dd/yyyy')
>
> AND DIM_MT.START_DATE <= to_date('03/06/2015','mm/dd/yyyy')
>
> AND DIM_MT.END_DATE >= to_date('03/06/2015','mm/dd/yyyy'))
>
> INNER JOIN DIM_PS
>
> ON (FACT_PFS.PLAN_SPONSOR_KEY=DIM_PS.PLAN_SPONSOR_KEY
>
> AND FACT_PFS.PRICE_DATE <= to_date('03/06/2015','mm/dd/yyyy')
>
> AND DIM_PS.START_DATE <= to_date('03/06/2015','mm/dd/yyyy')
>
> AND DIM_PS.END_DATE >= to_date('03/06/2015','mm/dd/yyyy'))
>
> GROUP BY
>
> DIM_PS.PLAN_SPONSOR_NAME,
>
> DIM_P.PLAN_CODE,
>
> DIM_MT.MONEY_TYPE_CODE,
>
> DIM_MT.MONEY_TYPE_NAME,
>
> DIM_F.FUND_CODE,
>
> DIM_F.FUND_NAME,
>
> DIM_F.FUND_TICKER_SYMBOL,
>
> DIM_P.PLAN_LEGAL_NAME;
>
> The query is not using star transformation, and when running a 10053
> trace, I can see the message “ST: not valid since query block has less
> than 3 tables”. Obviously the query has more than 3 tables (7 tables
> actually), but what I’m seeing is that the optimizer is re-writing the
> query into two separate query blocks, and then saying that neither
> qualify for star transformation :
>
> ST: not valid since query block has less than 3 tables
>
> discarded since not big enough
>
> discarded since not big enough
>
> …
>
> ST: not valid since query block has less than 3 tables
>
> discarded since not big enough
>
> discarded since not big enough
>
> Any way to bypass this re-write so we can see if star transformation
> can be used on this query?
>
> Thanks,
> Scott
>
> Notice of Confidentiality: **This E-mail and any of its attachments
> may contain
> Lincoln National Corporation proprietary information, which is
> privileged, confidential,
> or subject to copyright belonging to the Lincoln National Corporation
> family of
> companies. This E-mail is intended solely for the use of the
> individual or entity to
> which it is addressed. If you are not the intended recipient of this
> E-mail, you are
> hereby notified that any dissemination, distribution, copying, or
> action taken in
> relation to the contents of and attachments to this E-mail is strictly
> prohibited
> and may be unlawful. If you have received this E-mail in error, please
> notify the
> sender immediately and permanently delete the original and any copy of
> this E-mail
> and any printout. Thank You.**
>

-- 





--
http://www.freelists.org/webpage/oracle-l