RE: Star Transformation Issue
Date: Fri, 23 Oct 2015 19:49:40 +0000
Message-ID: <C1FB7BA65B13C542B2CB1CE5DB8F74AF1B611D71_at_NC2PWEX501.us.ad.lfg.com>
Thanks everyone, it looks like we were missing the NOT NULL constraints on our foreign key columns, which I've since created. Luckily, star transformation is kicking in, even with the ANSI SQL, so even though it's not pretty, it's working. I'm cautiously optimistic that things are working correctly now, but I'm still doing lots of testing, so thanks again!
Thanks,
Scott
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Thursday, October 22, 2015 5:29 PM
To: oracle-l_at_freelists.org
Subject: RE: Star Transformation Issue
There's no obvious reason why you should be losing star transformation - I've modelled some of your key details: novalidate PKs, non-unique index on PKs, complete absence of FKs, ANSI syntax with the bits you might expect to put in the WHERE clause in the ON clause. I still get a star transformation.
The only anomaly I can see in your query is that you've got a predicate on the fact table (partition column ?) in every ON clause, and I'm wondering if this might have had an odd effect on the optimizer.
The other thought is to check that every partiiton of every local index is valid - maybe some odd behaviour appears if Oracle has to mix table expansion (different indexing strategies) with star transformation.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org] on behalf of Deas, Scott [Scott.Deas_at_lfg.com] Sent: 22 October 2015 21:17
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: Star Transformation Issue
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.**
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-lReceived on Fri Oct 23 2015 - 21:49:40 CEST