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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Jonathan Lewis
Sent: Thursday, October 22, 2015 5:29 PM
To: oracle-l@xxxxxxxxxxxxx
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
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx] on behalf of Deas, Scott [Scott.Deas@xxxxxxx]
Sent: 22 October 2015 21:17
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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.**
