Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Ameena.

Asked: February 20, 2004 - 4:20 pm UTC

Last updated: April 12, 2011 - 3:59 pm UTC

Version: 8.1.7.4.0

Viewed 1000+ times

You Asked

Hi Tom,

I'm a regular visitor of your site and learns a lot through the different types of real time issues.

Well, my application has lot of materialized views and we usually refresh them through jobs running during night.

Some times the refresh fail giving unable to extend rollback segment.

My question is ,is there any way to commit the transaction after certain number of records.

there are around 5 mviews and each has around 100,8674 of records.

I would really appreciate any suggestions from your side.


Thanks

Ameena

and Tom said...

You really just need to size your RBS for the size of the jobs you are performing.

I can 100% assure you that the time you spent looking at this issue, the time spent correcting the failed jobs already cost your company many many many times more than the cost of attaching the appropriate amount of storage on your system.

No, MV's cannot "partially commit", that would not only not fix the problem (could still run out of space) but would introduce an entirely new and truly bad problem -- that of a PARTIALLY (eg: inconsistent, incorrect, not "whole") MV. An MV that contains data that never ever existed in your database at any point in time.




Rating

  (19 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Suggentions for tuning MV query...

Balaji, February 21, 2004 - 11:52 am UTC

Tuning this query...
Hi Tom,

I need you suggestion and your great input in tuning this query.

Question on tuning this query...

Count of records per table.
---------------------------
CCRT_CALENDAR_DIM - 7304 rows
PORTFOLIOS_DIM - 17 rows
ACCOUNT_MONTHLY_FACT - 39691552 rows PARTITIONED BY MONTH (has 38 partitions)

This create mv script (attached below) takes 2 hrs 30 minutes and I am doin a
COMPLETE REFRESH since I am using ANALYTICAL FUNCTIONS, I cannot use FAST REFRESH option.

Can you throw me some light? Guide me what are all the parameters that I should look into.

Wht type of indexes will do the trick, How to re-write the sql in a much betterway.

I need/share your inputs/guidance and all your experience in tunning. All I need an example to start with.
So that I can start tuning.

How to start and proceed from here... Steps that are to be considered while identifying the bottle neck.

I need your suggestions to get into a good solution.

This is test data 39 million. The actual size of ACCOUNT_MONTHLY_FACT is 520 million rows.

CREATE MATERIALIZED VIEW MV_CREDIT_MEASURES AS
SELECT
B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
(CASE WHEN GROUPING_ID
(B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
TRIM(D.CAL_QTR_STR),
TRIM(C.CAL_MONTH_STR),
A.MONTHS_ON_BOOK) = 0 THEN
TRIM(D.CAL_QTR_STR)
WHEN GROUPING_ID
(B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
TRIM(D.CAL_YEAR_STR),
TRIM(C.CAL_MONTH_STR),
A.MONTHS_ON_BOOK) = 0 THEN
TRIM(D.CAL_YEAR_STR)
ELSE
TRIM(D.CAL_MONTH_STR)
END) VINTAGE_PERIOD,
TRIM(C.CAL_MONTH_STR) REPORT_PERIOD,
A.MONTHS_ON_BOOK,
SUM(INT_AND_FEES) INT_AND_FEES,
SUM(OPENING_BAL) OPENING_BAL,
SUM(CLOSING_BAL) CLOSING_BAL,
SUM(NUM_ACCOUNTS_OPEN) NUM_ACCOUNTS_OPEN,
SUM(NUM_ACCOUNTS_VOLUNTARY_ATTR) NUM_ACCOUNTS_VOLUNTARY_ATTR,
SUM(NUM_ACCOUNTS_INVOLUNTARY_ATTR) NUM_ACCOUNTS_INVOLUNTARY_ATTR,
SUM(PRINCIPAL_CHGOFF) PRINCIPAL_CHGOFF,
SUM(GROSS_CHGOFF) GROSS_CHGOFF,
SUM(NET_CHGOFF) NET_CHGOFF,
SUM(TWO_PLUS_CLOSING_BAL) TWO_PLUS_CLOSING_BAL,
SUM(THREE_PLUS_CLOSING_BAL) THREE_PLUS_CLOSING_BAL,
SUM(FOUR_PLUS_CLOSING_BAL) FOUR_PLUS_CLOSING_BAL
FROM (SELECT
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
A.MONTHS_ON_BOOK,
A.VINTAGE_DATE,
A.MONTH_END_DATE,
(CASE WHEN A.DELINQUENCY_STATUS_ID <> 10 THEN
NVL(A.PURCHASE_FIN_CHG, 0) + NVL(A.CASH_ADV_FIN_CHG,0) + NVL(A.ANNUAL_FEE, 0) +
NVL(A.FRONT_END_FEE ,0) + NVL(A.INSURANCE_FEE, 0) + NVL(A.INTERCHANGE_FEE, 0) +
NVL(A.LATE_FEE, 0) + NVL(A.OTHER_FEE, 0) + NVL(A.OVERLIMIT_FEE, 0)
ELSE 0
END) INT_AND_FEES,
LAG(CASE WHEN A.DELINQUENCY_STATUS_ID <> 10 THEN NVL(A.MTD_CLOSING_BAL,0) ELSE 0
END)
OVER (PARTITION BY
A.ACCOUNT_ID
ORDER BY
A.MONTH_END_DATE)
OPENING_BAL,
(CASE WHEN A.DELINQUENCY_STATUS_ID <> 10 THEN
NVL(A.MTD_CLOSING_BAL, 0)
ELSE 0
END) CLOSING_BAL,
(CASE WHEN A.DELINQUENCY_STATUS_ID <> 10 THEN
1
ELSE 0
END) NUM_ACCOUNTS_OPEN,
(CASE WHEN A.ACCOUNT_STATUS_ID = 1 THEN
1
ELSE 0
END) NUM_ACCOUNTS_VOLUNTARY_ATTR,
(CASE WHEN A.ACCOUNT_STATUS_ID = 2 THEN
1
ELSE 0
END) NUM_ACCOUNTS_INVOLUNTARY_ATTR,
(CASE WHEN A.DELINQUENCY_STATUS_ID = 10 THEN
NVL(A.PURCHASE_CHGOFF, 0) + NVL(A.CASH_ADV_CHGOFF, 0)
ELSE 0
END) PRINCIPAL_CHGOFF,
(CASE WHEN A.DELINQUENCY_STATUS_ID = 10 THEN
NVL(A.TOTAL_CHGOFF, 0)
ELSE 0
END) GROSS_CHGOFF,
(CASE WHEN A.DELINQUENCY_STATUS_ID = 10 THEN
NVL(A.TOTAL_CHGOFF, 0) - NVL(A.TOTAL_RECOVERY_AMT, 0) -
(NVL(A.PURCHASE_FIN_CHG_CHGOFF, 0) + NVL(A.CASH_ADV_FIN_CHG_CHGOFF, 0) +
NVL(A.MISC_CHGOFF, 0))
ELSE 0
END) NET_CHGOFF,
(CASE WHEN A.DELINQUENCY_STATUS_ID NOT IN (0, 1, 10) THEN
NVL(A.MTD_CLOSING_BAL, 0)
ELSE 0
END) TWO_PLUS_CLOSING_BAL,
(CASE WHEN A.DELINQUENCY_STATUS_ID NOT IN (0, 1, 2, 10) THEN
NVL(A.MTD_CLOSING_BAL, 0)
ELSE 0
END) THREE_PLUS_CLOSING_BAL,
(CASE WHEN A.DELINQUENCY_STATUS_ID NOT IN (0, 1, 2, 3, 10) THEN
NVL(A.MTD_CLOSING_BAL, 0)
ELSE 0
END) FOUR_PLUS_CLOSING_BAL
FROM ACCOUNT_MONTHLY_FACT A) A,
PORTFOLIOS_DIM B,
CCRT_CALENDAR_DIM C,
CCRT_CALENDAR_DIM D
WHERE A.PORTFOLIO_ID = B.PORTFOLIO_ID
AND A.MONTH_END_DATE = C.CAL_DAY_DT
AND A.VINTAGE_DATE = D.CAL_DAY_DT
GROUP BY GROUPING SETS (
(B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
TRIM(D.CAL_MONTH_STR),
TRIM(C.CAL_MONTH_STR),
A.MONTHS_ON_BOOK),
(B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
TRIM(D.CAL_QTR_STR),
TRIM(C.CAL_MONTH_STR),
A.MONTHS_ON_BOOK),
(B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
TRIM(D.CAL_YEAR_STR),
TRIM(C.CAL_MONTH_STR),
A.MONTHS_ON_BOOK)
);

Thanks in advance.

Cheers.
Balaji

Tom Kyte
February 21, 2004 - 1:36 pm UTC

I saw this on the other page.

basically -- no suggestions. Indexing won't help here. big pga_aggregate_target (in 9i and up) or hash area size in 8i and before might.

look at the query -- every row in every table must by definition be touched, joined, aggregated, analyzed.

parallel query
sufficient ram

would be things to look at (besides things like "why would I need to trim strings, why is the data stored with trailing blanks"

Thanks for the Inputs tom

Balaji, February 21, 2004 - 5:50 pm UTC

HI Tom,

"I saw this on the other page." - Sorry I posted twice.

The DB is 9i. I came see these HINTS. When to you these hints? (I know that you always say trust the CBO and never use hints). Still I need to know for my understanding.

/*+ ORDERED use_hash(AD) USE_NL(SM A2) */
/*+ MERGE(A) */
/*+ EXPAND_GSET_TO_UNION */
/*+ FULL(A) PARALLEL(A, 8) */

I tried using /*+ FULL(A) PARALLEL(A, 8) */.
MV which were running for 18 minutes came down to 3 minutes.

When I Execute/Refresh MV parallely... The MV which ran for 3 minutes runs for 1 hour. Can you tell me the reason? I Don't have the DBA access to get the TRACE files. Weekend no one works.

Please give me some inputs.

Thanks in advance.
Balaji

Tom Kyte
February 22, 2004 - 8:52 am UTC

hints are documented, you can read about them in the performance guide.

with the level detail here "one mv went from 18 to 3, another went from 3 to 60, why" -- no, no one can say.

Thanks for the valuable inputs

Ameena Vasim, February 26, 2004 - 3:45 pm UTC

Hi tom Thanks for your time and valuable inputs for my question on materialized view .

MV CONSIDER FRESH OPTION

Balaji, March 02, 2004 - 4:55 pm UTC 

Hi Tom,

I got some doubts in Materialized View Consider Fresh Option.

Its works with FAST REFRESH OPTION and NOT with COMPLETE REFRESH.

This is my test case,

CREATE table test 
(time_id number, num number) 
PARTITION BY RANGE (time_id)
(
PARTITION month1 VALUES LESS THAN (121999),
PARTITION month2 VALUES LESS THAN (122000),
PARTITION month3 VALUES LESS THAN (122001),
PARTITION month4 VALUES less than (maxvalue)
);

alter table test add primary key(time_id, num);

insert into test values('121998', 1);
insert into test values('121999', 2);
insert into test values('122000', 3);
--insert into test values('121998', 4);
--insert into test values('121999', 5);
commit;

create MATERIALIZED VIEW log on test with primary key INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW test_mv  PARALLEL
PARTITION BY RANGE (time_id)
(
PARTITION month1 VALUES LESS THAN (121999),
PARTITION month2 VALUES LESS THAN (122000),
PARTITION month3 VALUES LESS THAN (122001),
PARTITION month4 VALUES less than (maxvalue)
)
REFRESH FAST
AS
SELECT time_id, num FROM test;

select PARTITION_NAME, PARTITION_POSITION, NUM_ROWS from dba_tab_partitions where table_name = 'TEST_MV';

1) Suppose the oldest month is to be removed from the table. 
ALTER TABLE test DROP/TRUNCATE PARTITION month1;

--IF YOU INSERT/UPDATE AFTER DROP/TRUNCATE A PARTITION, 
    THEN YOU HAVE TO RUN THIS COMMAND BEFORE YOUR INSERT/UPDATE OPERATION "ALTER MATERIALIZED VIEW test_mv CONSIDER FRESH".

RESULTS:
--------
a) from table after dropping month1 partition.

select * from test;
   TIME_ID        NUM
---------- ----------
    121999          2
    122000          3

select * from test_mv;
   TIME_ID        NUM
---------- ----------
    121999          2
    121998          1
    122000          3
    
b) from table after inserting new record 129000, 9

ONCE YOU DROP THE PARTITION AND TRY TO INSERT... YOU GET THE FOLLOWING ERROR. 
IN THIS CASE, YOU HAVE TO REBUILD THE INDEX TO BRING THE INDEX STATE USABLE.

  1*  insert into test values(129000, 9)
10:38:50 SQL> /
 insert into test values(129000, 9)
*
ERROR at line 1:
ORA-01502: index 'SAKSOFT.SYS_C006525' or partition of such index is in unusable state


Elapsed: 00:00:00.20
10:38:50 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
10:38:54 SQL> alter index SYS_C006525 rebuild;

Index altered.

Elapsed: 00:00:00.60
10:39:11 SQL> insert into test values(129000, 9);

1 row created.

Elapsed: 00:00:00.20
10:39:18 SQL> commit;


select * from test;
   TIME_ID        NUM
---------- ----------
    121999          2
    122000          3
    129000          9

c) from mview
  select * from test_mv
   TIME_ID        NUM
---------- ----------
    121999          2
    121998          1
    122000          3

2) Use CONSIDER FRESH to declare that the materialized view has been refreshed. 
ALTER MATERIALIZED VIEW test_mv CONSIDER FRESH;

3) Execute the mview refresh
EXECUTE DBMS_MVIEW.REFRESH('TEST_MV', 'F',   '', TRUE, FALSE,0,0,0,FALSE);

select * from test
   TIME_ID        NUM
---------- ----------
    121999          2
    122000          3
    129000          9

select * from test_mv
   TIME_ID        NUM
---------- ----------
    129000          9
    122000          3
    121999          2
    121998          1
    
******************************************************************************************
Test REFRESH with UPDATING some records.

13:37:56 saksoft:ccdw>   select * from test;

   TIME_ID        NUM
---------- ----------
    121999          2
    122000          3
    129000          9

3 rows selected.

Elapsed: 00:00:00.00
13:42:26 saksoft:ccdw> insert into test values('121998', 4);

1 row created.

Elapsed: 00:00:00.00
13:44:28 saksoft:ccdw> commit;

Commit complete.

Elapsed: 00:00:00.00
13:44:30 saksoft:ccdw> select * from test;

   TIME_ID        NUM
---------- ----------
    121998          4
    121999          2
    122000          3
    129000          9

4 rows selected.

Elapsed: 00:00:00.00
13:44:34 saksoft:ccdw> EXECUTE DBMS_MVIEW.REFRESH('TEST_MV', 'F',   '', TRUE, FALSE,0,0,0,FALSE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.00
13:44:54 saksoft:ccdw> select * from test_mv;

   TIME_ID        NUM
---------- ----------
    129000          9
    121998          1
    121999          2
    122000          3
    121998          4

5 rows selected.

Elapsed: 00:00:03.01
13:45:04 saksoft:ccdw> update test set time_id = 111999 where num = 4;

1 row updated.

Elapsed: 00:00:00.00
13:46:07 saksoft:ccdw> commit;

Commit complete.

Elapsed: 00:00:00.00
13:46:10 saksoft:ccdw> select * from test;

   TIME_ID        NUM
---------- ----------
    111999          4
    121999          2
    122000          3
    129000          9

4 rows selected.

STATS BEFORE REFRESH OF THE MVIEW.

  1* select * from test_mv
13:47:19 saksoft:ccdw> /

   TIME_ID        NUM
---------- ----------
    121998          1
    121999          2
    122000          3
    129000          9
    121998          4

5 rows selected.

13:47:20 saksoft:ccdw>  EXECUTE DBMS_MVIEW.REFRESH('TEST_MV', 'F',   '', TRUE, FALSE,0,0,0,FALSE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.02
13:48:35 saksoft:ccdw> l
  1* select * from test_mv
13:48:37 saksoft:ccdw> /

   TIME_ID        NUM
---------- ----------
    121998          1
    111999          4
    121999          2
    122000          3
    129000          9

Thanks in Advance.

Tom Kyte
March 02, 2004 - 7:17 pm UTC

when I'm taking questions, this looks like a good one (i guess - i really didn't read all of the way through). it really isn't a followup on the original thread here at all and is quite "large"

MV CONSIDER FRESH

Jegan, March 03, 2004 - 9:09 am UTC

Hi Tom,
Thanks for the reply. Sorry Tom I don't know how to start a new thread. Since I have post a question on MV in this thread I though I can use the same thread.

In my earlier post I asked abt the performance issue.

Now I am facing a different issue.

Here we have a FACT table with 550 million rows partitioned by YEAR_MONTH. We have 36 partitions for 36 months. If I used a MV to generate the summary tables, I need the whole data from DAY one till DAY. Say, my FACT table will hold just 36 months of data (JAN 2000 till DEC 2002). Every month end the old data is moved into the HISTORY table from the FACT table (MONTHS_BETWEEN CURRENTMONTH YEAR is greater than 36 months).

If I have a FAST REFRESH I am able to hold the DATA in my MVs that doesn't exist in the FACT table (i.e.) The data that has been archived from the FACT table. I have pasted an example in the previous post which work fine with FAST REFRESH. Since I am using Analytical functions I am not able to use FAST REFRESH option.

All I need is to retain the data in the summary table even if they are not present in the FACT table.

FACT
----
2000FEB
2000MAR
...
2003JAN
MV
--
2000JAN
2000FEB
2000MAR
...
2003JAN

Hope I have explained clearly.

Jegan

Tom Kyte
March 03, 2004 - 10:13 am UTC

when I take new questions -- it'll be on the home page.

MV's can only reflect what is in the base tables. they cannot have "more" data then the base table. they do not work that way.

MV CONSIDER FRESH

Jegan, March 03, 2004 - 9:14 am UTC

Hi Tom,
Sorry again. In my previuos post is missed an important stuff.

"If I have a FAST REFRESH I am able to hold the DATA in my MVs that doesn't exist
in the FACT table (i.e.) The data that has been archived from the FACT table. I
have pasted an example in the previous post which work fine with FAST REFRESH.
Since I am using Analytical functions I am not able to use FAST REFRESH option."

In the above paragraph, I missed to say that CONSIDER FRESH OPTION works with FAST REFRESH and NOT with COMPLETE FRESH. Please Throw some ideas.

Thanks

MV CONSIDER FRESH

Jegan, March 04, 2004 - 2:04 pm UTC

Hi Tom,
Sorry to trouble you again
[Quote]when I take new questions -- it'll be on the home page.[/Quote]
Thanks for the Info.

[Quote]MV's can only reflect what is in the base tables. they cannot have "more" data
then the base table. they do not work that way. [/Quote]

CONSIDER FRESH OPTION works WITH Materialized View FAST REFRESH. All I need to know how Materialized View FAST REFRESH option can hold data that doesn't exist in the master table? (I think it should fetch from the Materialized View Logs). Now I am in a dengerous position when I will loose the DATA of my previous months if I do a COMPLETE REFRESH. I need some ideas/input/expert advice from you (My small brain doesn't work).

Need your input on this.
My example in my previous post shows FAST REFRESH WORKS WITH CONSIDER FRESH OPTIOn.

Thanks a million

Jegan

Tom Kyte
March 04, 2004 - 2:41 pm UTC

MV's are not intended or designed or support what you are trying to do. sorry -- they just don't "do that"

You'll have to "do it yourself", the consider fresh is so you can manually drop partitions from "master" and "mv" and tell the database "hey, consider the mv fresh, i did the work to ensure they are in sync"

It is not in support of what you are after.

MV CONSIDER FRESH

Jegan, March 04, 2004 - 4:50 pm UTC

Thank you very much Tom.

Even I came to this conclusion long time back. I told my guys to convert this into a table (Anyway I goin to convert the MVs to tables). But, I just wanna get a confirm word from my Guru.

Thanks for all your help and inputs.

Is that possible to move a partion before refresh and add the same after the refresh? (Looks foolish. Will take lots of maintenance activity.). I will try this and revert with the testcase.

Thanks You.

Reragds,
Jegan

Tom Kyte
March 04, 2004 - 5:37 pm UTC

don't try to fake out the MV -- it doesn't work that way.

You would be able to

a) have your partitioned detail table
b) have a partitioned MV on that detail table

when you goto roll the window (purge the old data) you would:

a) alter table detail drop partition
b) alter table MV EXCHANGE partition, to convert the full partition into a full table and convert an empty table into an empty partition
c) alter table MV drop partition and tell us to "consider it fresh"
d) add that full table as a partition to another history table

MV CONSIDER FRESH OPTION

Jegan, March 04, 2004 - 5:56 pm UTC

Hi Tom,
Thank you very much for your support.

My Detail table and MVs are partitioned.

I will do a test as you said and revert with the results tomorrow.

Thank you very much for your support and help.

Regards,
Jegan

Alternative To materialized view

Ameena Vasim, April 02, 2004 - 1:45 pm UTC

Hi tom ,

We have a scenario where we use materialized view to make the reports faster. This is a complex materialized view and could not be fast refreshed, is there any other way to replace the materialized view and still maintain the performance. This particular report has lots of calculated formulaes , I tried making a regular view but still it's not fast.

Tom Kyte
April 02, 2004 - 3:06 pm UTC

well, a materialized view runs the query once and lets you query the answer over and over.

a 'real' view runs the query each and every time you ask the question.


There are way to 'replace' the MV -- indexing strategies, physical design, using analytic functions when appropriate (eg: knowing everything you can about what you have access to)

Alternative To materialized view

Ameena Vasim, April 08, 2004 - 11:18 am UTC

With continuation of my previous question, I have a situation here where the requirement is to remove materialized view and get the real time data.

The following SQL is from a report whcih has a normal view DCX_BOM_RECON_FACT_T based on another view DCX_PIECE_COST_V_TEMP , earlier DCX_PIECE_COST_V_TEMP was a materialized view which is changed to a normal view.

This report takes lot of time , I tried tuning the SQL of individual views , individually after adding the hints the views run fast but when I see the explain plan of the report SQL the hint doesn't seem to work.

the two tables dcx_piece_cost_fact , dcx_papercar_usage_dim on which the view is based are partitioned tables .

THE REPORT SQL
_______________________________________________________

SELECT
Nvl(PriorTrackedVehicleDim.DCX_TRACKED_VEHICLE, DCX_PLATFORM_DIM.DCX_TRACKED_VEHICLE),
DCX_BOM_RECON_FACT_T.PAST_DCX_TRACKED_VEHICLE,
DCX_BOM_RECON_FACT_T.CUR_DCX_SNAPSHOT,
DCX_BOM_RECON_FACT_T.CUR_DCX_TRACKED_VEHICLE,
DCX_BOM_RECON_FACT_T.PAST_DCX_REV,
DCX_BOM_RECON_FACT_T.CUR_DCX_REV,
DCX_BOM_RECON_FACT_T.PAST_QTY,
DCX_BOM_RECON_FACT_T.PAST_DCX_REPORT_MGMT_UID,
DCX_BOM_RECON_FACT_T.PAST_DCX_PO_COST,
DCX_BOM_RECON_FACT_T.PAST_DCX_PART_NUMBER,
DCX_BOM_RECON_FACT_T.PAST_DCX_EXT_ITEM_TARGET,
DCX_BOM_RECON_FACT_T.PAST_DCX_EXT_ITEM_STATUS,
DCX_BOM_RECON_FACT_T.PAST_DCX_EXT_DEPT_TARGET,
DCX_BOM_RECON_FACT_T.PAST_DCX_EXT_DEPT_STATUS,
DCX_BOM_RECON_FACT_T.PAST_DCX_ASSESS_PROB,
DCX_BOM_RECON_FACT_T.PAST_DCX_ASSESSMENT,
DCX_BOM_RECON_FACT_T.CUR_QTY,
DCX_BOM_RECON_FACT_T.CUR_DCX_REPORT_MGMT_UID,
DCX_BOM_RECON_FACT_T.CUR_DCX_PO_COST,
DCX_BOM_RECON_FACT_T.CUR_DCX_EXT_ITEM_TARGET,
DCX_BOM_RECON_FACT_T.CUR_DCX_EXT_ITEM_STATUS,
DCX_BOM_RECON_FACT_T.CUR_DCX_EXT_DEPT_TARGET,
DCX_BOM_RECON_FACT_T.CUR_DCX_EXT_DEPT_STATUS,
DCX_BOM_RECON_FACT_T.CUR_DCX_ASSESS_PROB,
DCX_BOM_RECON_FACT_T.CUR_DCX_ASSESSMENT,
DCX_BOM_RECON_FACT_T.TYPE,
DCX_BOM_RECON_FACT_T.CUR_DCX_PART_NUMBER,
DCX_PLATFORM_DIM.DCX_VEHICLE_FAMILY,
DCX_PLATFORM_DIM.DCX_TRACKED_VEHICLE,
DCX_BOM_RECON_FACT_T.PAST_DCX_SNAPSHOT,
DCX_MANAGEMENT_DIM.DCX_BASE_DEPT,
DCX_BASE_DEPT_DIM.DCX_DIR_ENG_AREA,
nvl(PriorBaseDeptDim.DCX_BASE_DEPT, DCX_BASE_DEPT_DIM.DCX_BASE_DEPT),
NVL(PriorBaseDeptDim.DCX_BASE_DEPT_NAME, DCX_BASE_DEPT_DIM.DCX_BASE_DEPT_NAME),
nvl(PriorBaseDeptDim.DCX_MGR_FNAME, DCX_BASE_DEPT_DIM.DCX_MGR_FNAME),
nvl(PriorBaseDeptDim.DCX_MGR_LNAME, DCX_BASE_DEPT_DIM.DCX_MGR_LNAME),
nvl(PriorMgtDim.DCX_SUB_DEPT, DCX_MANAGEMENT_DIM.DCX_SUB_DEPT),
nvl(PriorMgtDim.DCX_SUB_DEPT_NAME, DCX_MANAGEMENT_DIM.DCX_SUB_DEPT_NAME),
PriorSnapshot.DCX_SNAPSHOT,
PriorTrackedVehicleDim.DCX_VEHICLE_FAMILY,
PriorTrackedVehicleDim.DCX_MODEL_YEAR,
DCX_PLATFORM_DIM.DCX_MODEL_YEAR,
PriorTrackedVehicleDim.DCX_LAST_EBOM_REFRESH_DT,
DCX_PLATFORM_DIM.DCX_LAST_EBOM_REFRESH_DT,
PriorBaseDeptDim.DCX_BASE_DEPT,
nvl(DCX_BOM_RECON_FACT_T.PAST_DCX_PART_NUMBER,DCX_BOM_RECON_FACT_T.CUR_DCX_PART_NUMBER),
'Dept: '||nvl(PriorMgtDim.DCX_SUB_DEPT, DCX_MANAGEMENT_DIM.DCX_SUB_DEPT)||'
'||'VSC: '||nvl(PriorMgtDim.DCX_VSC, DCX_MANAGEMENT_DIM.DCX_VSC),
DCX_PART_DIM.DCX_PART_DESCR,
DCX_PLATFORM_DIM.DCX_DISPLAY_NAME,
Nvl(PriorTrackedVehicleDim.DCX_DISPLAY_NAME, DCX_PLATFORM_DIM.DCX_DISPLAY_NAME)
FROM
DCX_PLATFORM_DIM,
DCX_PLATFORM_DIM PriorTrackedVehicleDim,
DCX_BOM_RECON_FACT_T,
DCX_MANAGEMENT_DIM,
DCX_BASE_DEPT_DIM,
DCX_BASE_DEPT_DIM PriorBaseDeptDim,
DCX_MANAGEMENT_DIM PriorMgtDim,
DCX_SNAPSHOT_DIM PriorSnapshot,
DCX_PART_DIM,
DCX_SNAPSHOT_DIM
WHERE
( DCX_BASE_DEPT_DIM.DCX_BASE_DEPT=DCX_MANAGEMENT_DIM.DCX_BASE_DEPT )
AND ( DCX_BOM_RECON_FACT_T.CUR_DCX_SNAPSHOT=DCX_PLATFORM_DIM.DCX_SNAPSHOT
and DCX_BOM_RECON_FACT_T.CUR_DCX_TRACKED_VEHICLE=DCX_PLATFORM_DIM.DCX_TRACKED_VEHICLE )
AND ( DCX_PART_DIM.DCX_PART_NUMBER=DCX_BOM_RECON_FACT_T.CUR_DCX_PART_NUMBER )
AND ( DCX_MANAGEMENT_DIM.DCX_MANAGEMENT_UID=DCX_BOM_RECON_FACT_T.CUR_DCX_REPORT_MGMT_UID )
AND ( DCX_BOM_RECON_FACT_T.CUR_DCX_SNAPSHOT=DCX_SNAPSHOT_DIM.DCX_SNAPSHOT )
AND ( DCX_BOM_RECON_FACT_T.PAST_DCX_SNAPSHOT=PriorSnapshot.DCX_SNAPSHOT )
AND ( DCX_BOM_RECON_FACT_T.PAST_DCX_SNAPSHOT=PriorTrackedVehicleDim.DCX_SNAPSHOT
and DCX_BOM_RECON_FACT_T.PAST_DCX_TRACKED_VEHICLE=PriorTrackedVehicleDim.DCX_TRACKED_VEHICLE )
AND ( PriorMgtDim.DCX_MANAGEMENT_UID=DCX_BOM_RECON_FACT_T.PAST_DCX_REPORT_MGMT_UID )
AND ( PriorMgtDim.DCX_BASE_DEPT=PriorBaseDeptDim.DCX_BASE_DEPT )
AND (
( DCX_PLATFORM_DIM.DCX_TRACKED_VEHICLE IN
('030905160008795098') OR 'ALL' IN
('030905160008795098') )
AND ( DCX_SNAPSHOT_DIM.DCX_SNAPSHOT IN ('Current') )
AND ( PriorSnapshot.DCX_SNAPSHOT IN
('3:45 pm - JB (08/22/2003)') )
AND (( PriorMgtDim.DCX_SUB_DEPT IN
('1020') OR 'ALL' IN
('1020') )
OR ( ('VP-'||PriorBaseDeptDim.DCX_VP_LNAME || ', ' || PriorBaseDeptDim.DCX_VP_FNAME ) IN
('NA') or
('MGR-'||PriorBaseDeptDim.DCX_MGR_LNAME || ', ' || PriorBaseDeptDim.DCX_MGR_FNAME || ' (' ||
PriorBaseDeptDim.DCX_BASE_DEPT || ')') IN
('NA') or
('DIR-'||PriorBaseDeptDim.DCX_DIR_LNAME || ', ' || PriorBaseDeptDim.DCX_DIR_FNAME IN
('NA') ) ))
AND (( DCX_MANAGEMENT_DIM.DCX_SUB_DEPT IN
('1020') OR 'ALL'
IN ('1020') )
OR ( ('VP-'||DCX_BASE_DEPT_DIM.DCX_VP_LNAME || ', ' || DCX_BASE_DEPT_DIM.DCX_VP_FNAME )
IN ('NA') or ('MGR-'||
DCX_BASE_DEPT_DIM.DCX_MGR_LNAME || ', ' || DCX_BASE_DEPT_DIM.DCX_MGR_FNAME || ' (' ||
DCX_BASE_DEPT_DIM.DCX_BASE_DEPT || ')')
IN ('NA')
or
('DIR-'||DCX_BASE_DEPT_DIM.DCX_DIR_LNAME || ', ' || DCX_BASE_DEPT_DIM.DCX_DIR_FNAME IN
('NA') ) ))
AND PriorSnapshot.DCX_SNAPSHOT != ('N')
AND ( PriorTrackedVehicleDim.DCX_TRACKED_VEHICLE IN
('030905160008795098') OR 'ALL' IN
('030905160008795098') )
AND DCX_PLATFORM_DIM.DCX_MODEL_YEAR != ('N')
AND DCX_SNAPSHOT_DIM.DCX_SNAPSHOT != ('N')
)
UNION ALL
SELECT
DCX_PLATFORM_DIM.DCX_TRACKED_VEHICLE,
null,
cur.DCX_SNAPSHOT,
cur.DCX_TRACKED_VEHICLE,
null,
cur.DCX_REV,
to_number(null),
to_number(null),
to_number(null),
null,
to_number(null),
to_number(null),
to_number(null),
to_number(null),
null,
to_number(null),
cur.QTY,
cur.DCX_REPORT_MGMT_UID,
cur.DCX_PO_COST,
cur.DCX_EXT_ITEM_TARGET,
cur.DCX_EXT_ITEM_STATUS,
cur.DCX_EXT_DEPT_TARGET,
cur.DCX_EXT_DEPT_STATUS,
cur.DCX_ASSESS_PROB,
cur.DCX_ASSESSMENT,
'Design',
cur.DCX_PART_NUMBER,
DCX_PLATFORM_DIM.DCX_VEHICLE_FAMILY,
DCX_PLATFORM_DIM.DCX_TRACKED_VEHICLE,
cur.DCX_SNAPSHOT,
DCX_MANAGEMENT_DIM.DCX_BASE_DEPT,
DCX_BASE_DEPT_DIM.DCX_DIR_ENG_AREA,
DCX_BASE_DEPT_DIM.DCX_BASE_DEPT,
DCX_BASE_DEPT_DIM.DCX_BASE_DEPT_NAME,
DCX_BASE_DEPT_DIM.DCX_MGR_FNAME,
DCX_BASE_DEPT_DIM.DCX_MGR_LNAME,
DCX_MANAGEMENT_DIM.DCX_SUB_DEPT,
DCX_MANAGEMENT_DIM.DCX_SUB_DEPT_NAME,
null,
null,
null,
DCX_PLATFORM_DIM.DCX_MODEL_YEAR,
to_date(null),
DCX_PLATFORM_DIM.DCX_LAST_EBOM_REFRESH_DT,
null,
cur.DCX_PART_NUMBER,
'Dept: '||DCX_MANAGEMENT_DIM.DCX_SUB_DEPT||' '||'VSC: '||DCX_MANAGEMENT_DIM.DCX_VSC,
DCX_PART_DIM.DCX_PART_DESCR,
DCX_PLATFORM_DIM.DCX_DISPLAY_NAME,
DCX_PLATFORM_DIM.DCX_DISPLAY_NAME
FROM
DCX_PLATFORM_DIM,
DCX_PIECE_COST_V_TEMP cur,
DCX_MANAGEMENT_DIM,
DCX_BASE_DEPT_DIM,
DCX_PART_DIM,
DCX_SNAPSHOT_DIM
WHERE
( DCX_BASE_DEPT_DIM.DCX_BASE_DEPT=DCX_MANAGEMENT_DIM.DCX_BASE_DEPT )
AND ( cur.DCX_SNAPSHOT=DCX_PLATFORM_DIM.DCX_SNAPSHOT
and cur.DCX_TRACKED_VEHICLE=DCX_PLATFORM_DIM.DCX_TRACKED_VEHICLE )
AND ( DCX_PART_DIM.DCX_PART_NUMBER=cur.DCX_PART_NUMBER )
AND ( DCX_MANAGEMENT_DIM.DCX_MANAGEMENT_UID=cur.DCX_REPORT_MGMT_UID )
AND ( cur.DCX_SNAPSHOT=DCX_SNAPSHOT_DIM.DCX_SNAPSHOT )
AND (
( DCX_PLATFORM_DIM.DCX_TRACKED_VEHICLE IN
('030905160008795098') OR 'ALL' IN
('030905160008795098') )
AND ( DCX_SNAPSHOT_DIM.DCX_SNAPSHOT IN ('Current') )
AND (( DCX_MANAGEMENT_DIM.DCX_SUB_DEPT IN
('1020') OR 'ALL'
IN ('1020') )
OR ( ('VP-'||DCX_BASE_DEPT_DIM.DCX_VP_LNAME || ', ' || DCX_BASE_DEPT_DIM.DCX_VP_FNAME ) IN
('NA')
or ('MGR-'||DCX_BASE_DEPT_DIM.DCX_MGR_LNAME || ', ' || DCX_BASE_DEPT_DIM.DCX_MGR_FNAME
|| ' (' || DCX_BASE_DEPT_DIM.DCX_BASE_DEPT || ')') IN
('NA') or
('DIR-'||DCX_BASE_DEPT_DIM.DCX_DIR_LNAME || ', ' || DCX_BASE_DEPT_DIM.DCX_DIR_FNAME IN
('NA') ) ))
)
AND ( NVL (cur.qty, 0) <> 0
AND ( NVL (cur.dcx_ext_dept_target, 0) <> 0
OR NVL (cur.dcx_ext_item_target, 0) <> 0
OR NVL (cur.dcx_ext_item_status, 0) <> 0
OR NVL (cur.dcx_ext_dept_status, 0) <> 0
OR NVL (cur.dcx_assessment, 0) <> 0
OR NVL (cur.dcx_po_cost, 0) <> 0
)
)
AND NOT EXISTS ( SELECT 1
FROM DCX_PIECE_COST_V_TEMP past
WHERE past.dcx_part_number = cur.dcx_part_number
AND past.dcx_report_mgmt_uid = cur.dcx_report_mgmt_uid
AND ( past.DCX_TRACKED_VEHICLE IN
('030905160008795098') )
AND ( past.DCX_SNAPSHOT IN
('030905160008795098') )
)
UNION ALL
SELECT
null,
DCX_PLATFORM_DIM.DCX_TRACKED_VEHICLE,
null,
null,
past.DCX_REV,
null,
past.QTY,
past.DCX_REPORT_MGMT_UID,
past.DCX_PO_COST,
past.DCX_PART_NUMBER,
past.DCX_EXT_ITEM_TARGET,
past.DCX_EXT_ITEM_STATUS,
past.DCX_EXT_DEPT_TARGET,
past.DCX_EXT_DEPT_STATUS,
past.DCX_ASSESS_PROB,
past.DCX_ASSESSMENT,
to_number(null),
to_number(null),
to_number(null),
to_number(null),
to_number(null),
to_number(null),
to_number(null),
null,
to_number(null),
'Design',
null,
null,
null,
past.DCX_SNAPSHOT,
null,
null,
DCX_BASE_DEPT_DIM.DCX_BASE_DEPT,
DCX_BASE_DEPT_DIM.DCX_BASE_DEPT_NAME,
DCX_BASE_DEPT_DIM.DCX_MGR_FNAME,
DCX_BASE_DEPT_DIM.DCX_MGR_LNAME,
DCX_MANAGEMENT_DIM.DCX_SUB_DEPT,
DCX_MANAGEMENT_DIM.DCX_SUB_DEPT_NAME,
past.DCX_SNAPSHOT,
DCX_PLATFORM_DIM.DCX_VEHICLE_FAMILY,
DCX_PLATFORM_DIM.DCX_MODEL_YEAR,
null,
DCX_PLATFORM_DIM.DCX_LAST_EBOM_REFRESH_DT,
to_date(null),
DCX_MANAGEMENT_DIM.DCX_BASE_DEPT,
past.DCX_PART_NUMBER,
'Dept: '||DCX_MANAGEMENT_DIM.DCX_SUB_DEPT||' '||'VSC: '||DCX_MANAGEMENT_DIM.DCX_VSC,
DCX_PART_DIM.DCX_PART_DESCR,
null,
DCX_PLATFORM_DIM.DCX_DISPLAY_NAME
FROM
DCX_PLATFORM_DIM,
DCX_PIECE_COST_V_TEMP past,
DCX_MANAGEMENT_DIM,
DCX_BASE_DEPT_DIM,
DCX_PART_DIM,
DCX_SNAPSHOT_DIM
WHERE
( DCX_BASE_DEPT_DIM.DCX_BASE_DEPT=DCX_MANAGEMENT_DIM.DCX_BASE_DEPT )
AND ( past.DCX_SNAPSHOT=DCX_PLATFORM_DIM.DCX_SNAPSHOT
and past.DCX_TRACKED_VEHICLE=DCX_PLATFORM_DIM.DCX_TRACKED_VEHICLE )
AND ( DCX_PART_DIM.DCX_PART_NUMBER=past.DCX_PART_NUMBER )
AND ( DCX_MANAGEMENT_DIM.DCX_MANAGEMENT_UID=past.DCX_REPORT_MGMT_UID )
AND ( past.DCX_SNAPSHOT=DCX_SNAPSHOT_DIM.DCX_SNAPSHOT )
AND (
( DCX_PLATFORM_DIM.DCX_TRACKED_VEHICLE IN
('030905160008795098')
OR 'ALL' IN ('030905160008795098') )
AND ( DCX_SNAPSHOT_DIM.DCX_SNAPSHOT IN ('Current') )
AND (( DCX_MANAGEMENT_DIM.DCX_SUB_DEPT IN ('1020') OR 'ALL' IN
('1020') )
OR ( ('VP-'||DCX_BASE_DEPT_DIM.DCX_VP_LNAME || ', ' || DCX_BASE_DEPT_DIM.DCX_VP_FNAME ) IN
('NA')
or ('MGR-'||DCX_BASE_DEPT_DIM.DCX_MGR_LNAME || ', ' || DCX_BASE_DEPT_DIM.DCX_MGR_FNAME || ' (' ||
DCX_BASE_DEPT_DIM.DCX_BASE_DEPT || ')') IN ('NA') or
('DIR-'||DCX_BASE_DEPT_DIM.DCX_DIR_LNAME || ', ' || DCX_BASE_DEPT_DIM.DCX_DIR_FNAME IN
('NA') ) ))
)
AND ( NVL (past.qty, 0) <> 0
AND ( NVL (past.dcx_ext_dept_target, 0) <> 0
OR NVL (past.dcx_ext_item_target, 0) <> 0
OR NVL (past.dcx_ext_item_status, 0) <> 0
OR NVL (past.dcx_ext_dept_status, 0) <> 0
OR NVL (past.dcx_assessment, 0) <> 0
OR NVL (past.dcx_po_cost, 0) <> 0
)
)
AND NOT EXISTS ( SELECT 1
FROM DCX_PIECE_COST_V_TEMP cur
WHERE cur.dcx_part_number = past.dcx_part_number
AND cur.dcx_report_mgmt_uid = past.dcx_report_mgmt_uid
AND ( cur.DCX_TRACKED_VEHICLE IN
('030905160008795098') )
AND ( cur.DCX_SNAPSHOT IN
('Current') )
)
ORDER BY
33,
37,
46 DESC


____________________________________________________

First View

OLAP.DCX_BOM_RECON_FACT_T

CREATE OR REPLACE VIEW dcx_bom_recon_fact_t (
type,
past_qty,
cur_qty,
cur_dcx_tracked_vehicle,
cur_dcx_snapshot,
cur_dcx_part_number,
cur_dcx_report_mgmt_uid,
cur_dcx_ext_dept_target,
cur_dcx_ext_item_target,
cur_dcx_ext_item_status,
cur_dcx_ext_dept_status,
cur_dcx_assess_prob,
cur_dcx_assessment,
cur_dcx_po_cost,
cur_dcx_rev,
past_dcx_tracked_vehicle,
past_dcx_snapshot,
past_dcx_part_number,
past_dcx_report_mgmt_uid,
past_dcx_ext_dept_target,
past_dcx_ext_item_target,
past_dcx_ext_item_status,
past_dcx_ext_dept_status,
past_dcx_assess_prob,
past_dcx_assessment,
past_dcx_po_cost,
past_dcx_rev )
AS
(SELECT 'Design' TYPE, past.qty past_qty, cur.qty cur_qty,
cur.dcx_tracked_vehicle cur_dcx_tracked_vehicle,
cur.dcx_snapshot cur_dcx_snapshot,
cur.dcx_part_number cur_dcx_part_number,
cur.dcx_report_mgmt_uid cur_dcx_report_mgmt_uid,
cur.dcx_ext_dept_target cur_dcx_ext_dept_target,
cur.dcx_ext_item_target cur_dcx_ext_item_target,
cur.dcx_ext_item_status cur_dcx_ext_item_status,
cur.dcx_ext_dept_status cur_dcx_ext_dept_status,
cur.dcx_assess_prob cur_dcx_assess_prob,
cur.dcx_assessment cur_dcx_assessment,
cur.dcx_po_cost cur_dcx_po_cost, cur.dcx_rev cur_dcx_rev,
past.dcx_tracked_vehicle past_dcx_tracked_vehicle,
past.dcx_snapshot past_dcx_snapshot,
past.dcx_part_number past_dcx_part_number,
past.dcx_report_mgmt_uid past_dcx_report_mgmt_uid,
past.dcx_ext_dept_target past_dcx_ext_dept_target,
past.dcx_ext_item_target past_dcx_ext_item_target,
past.dcx_ext_item_status past_dcx_ext_item_status,
past.dcx_ext_dept_status past_dcx_ext_dept_status,
past.dcx_assess_prob past_dcx_assess_prob,
past.dcx_assessment past_dcx_assessment,
past.dcx_po_cost past_dcx_po_cost, past.dcx_rev past_dcx_rev
FROM dcx_piece_cost_v_temp past,
dcx_piece_cost_v_temp cur
WHERE past.dcx_report_mgmt_uid = cur.dcx_report_mgmt_uid
AND past.dcx_part_number = cur.dcx_part_number
AND ( ( NVL (past.dcx_rev, 0) = NVL (cur.dcx_rev, 0)
AND NVL (past.qty, 0) <> NVL (cur.qty, 0)
AND ( NVL (cur.qty, 0) <> 0
AND ( NVL (cur.dcx_ext_dept_target, 0) <> 0
OR NVL (cur.dcx_ext_item_target, 0) <> 0
OR NVL (cur.dcx_ext_item_status, 0) <> 0
OR NVL (cur.dcx_ext_dept_status, 0) <> 0
OR NVL (cur.dcx_assessment, 0) <> 0
OR NVL (cur.dcx_po_cost, 0) <> 0
)
OR ( NVL (past.qty, 0) <> 0
AND ( NVL (past.dcx_po_cost, 0) <> 0
OR NVL (past.dcx_ext_dept_target, 0) <> 0
OR NVL (past.dcx_assessment, 0) <> 0
OR NVL (past.dcx_ext_dept_status, 0) <> 0
OR NVL (past.dcx_ext_item_status, 0) <> 0
OR NVL (past.dcx_ext_item_target, 0) <> 0
)
)
)
)
OR ( NVL (cur.dcx_rev, 0) <> NVL (past.dcx_rev, 0)
AND ( ( NVL (cur.qty, 0) <> NVL (past.qty, 0)
AND ( NVL (cur.qty, 0) <> 0
OR NVL (past.qty, 0) <> 0
)
)
OR NVL (cur.dcx_ext_dept_target, 0) <>
NVL (past.dcx_ext_dept_target, 0)
OR NVL (cur.dcx_ext_item_target, 0) <>
NVL (past.dcx_ext_item_target, 0)
OR NVL (cur.dcx_ext_item_status, 0) <>
NVL (past.dcx_ext_item_status, 0)
OR NVL (cur.dcx_ext_dept_status, 0) <>
NVL (past.dcx_ext_dept_status, 0)
OR NVL (
NVL (cur.dcx_assessment, cur.dcx_ext_dept_status),
0
) <> NVL (
NVL (
past.dcx_assessment,
past.dcx_ext_dept_status
),
0
)
OR NVL (cur.dcx_po_cost, 0) <> NVL (past.dcx_po_cost, 0)
)
)
)
UNION ALL
SELECT CASE
WHEN ( ( NVL (cur.dcx_ext_dept_target, 0) <>
NVL (past.dcx_ext_dept_target, 0)
OR NVL (cur.dcx_ext_dept_status, 0) <>
NVL (past.dcx_ext_dept_status, 0)
)
AND ( NVL (cur.dcx_ext_item_target, 0) <>
NVL (past.dcx_ext_item_target, 0)
OR NVL (cur.dcx_ext_item_status, 0) <>
NVL (past.dcx_ext_item_status, 0)
)
AND ( NVL (
NVL (
cur.dcx_assessment,
cur.dcx_ext_dept_status
),
0
) <> NVL (
NVL (
past.dcx_assessment,
past.dcx_ext_dept_status
),
0
)
OR NVL (cur.dcx_po_cost, 0) <>
NVL (past.dcx_po_cost, 0)
)
) THEN 'Both Value'
WHEN ( NVL (cur.dcx_ext_item_target, 0) <>
NVL (past.dcx_ext_item_target, 0)
OR NVL (cur.dcx_ext_item_status, 0) <>
NVL (past.dcx_ext_item_status, 0)
OR NVL (
NVL (cur.dcx_assessment, cur.dcx_ext_dept_status),
0
) <> NVL (
NVL (
past.dcx_assessment,
past.dcx_ext_dept_status
),
0
)
) THEN 'Item Value'
WHEN ( NVL (cur.dcx_ext_dept_target, 0) <>
NVL (past.dcx_ext_dept_target, 0)
OR NVL (cur.dcx_ext_dept_status, 0) <>
NVL (past.dcx_ext_dept_status, 0)
OR NVL (cur.dcx_po_cost, 0) <> NVL (past.dcx_po_cost, 0)
) THEN 'Dept Value'
ELSE 'Bad Coding Error'
END
AS TYPE,
past.qty past_qty, cur.qty cur_qty,
cur.dcx_tracked_vehicle cur_dcx_tracked_vehicle,
cur.dcx_snapshot cur_dcx_snapshot,
cur.dcx_part_number cur_dcx_part_number,
cur.dcx_report_mgmt_uid cur_dcx_report_mgmt_uid,
cur.dcx_ext_dept_target cur_dcx_ext_dept_target,
cur.dcx_ext_item_target cur_dcx_ext_item_target,
cur.dcx_ext_item_status cur_dcx_ext_item_status,
cur.dcx_ext_dept_status cur_dcx_ext_dept_status,
cur.dcx_assess_prob cur_dcx_assess_prob,
cur.dcx_assessment cur_dcx_assessment,
cur.dcx_po_cost cur_dcx_po_cost, cur.dcx_rev cur_dcx_rev,
past.dcx_tracked_vehicle past_dcx_tracked_vehicle,
past.dcx_snapshot past_dcx_snapshot,
past.dcx_part_number past_dcx_part_number,
past.dcx_report_mgmt_uid past_dcx_report_mgmt_uid,
past.dcx_ext_dept_target past_dcx_ext_dept_target,
past.dcx_ext_item_target past_dcx_ext_item_target,
past.dcx_ext_item_status past_dcx_ext_item_status,
past.dcx_ext_dept_status past_dcx_ext_dept_status,
past.dcx_assess_prob past_dcx_assess_prob,
past.dcx_assessment past_dcx_assessment,
past.dcx_po_cost past_dcx_po_cost, past.dcx_rev past_dcx_rev
FROM dcx_piece_cost_v_temp past,
dcx_piece_cost_v_temp cur
WHERE past.dcx_report_mgmt_uid = cur.dcx_report_mgmt_uid
AND past.dcx_part_number = cur.dcx_part_number
AND NVL (past.dcx_rev, 0) = NVL (cur.dcx_rev, 0)
AND ( NVL (cur.qty, 0) = NVL (past.qty, 0)
AND ( NVL (cur.qty, 0) <> 0
OR NVL (past.qty, 0) <> 0
)
)
AND ( NVL (cur.dcx_ext_item_target, 0) <>
NVL (past.dcx_ext_item_target, 0)
OR NVL (cur.dcx_ext_item_status, 0) <>
NVL (past.dcx_ext_item_status, 0)
OR NVL (cur.dcx_ext_dept_target, 0) <>
NVL (past.dcx_ext_dept_target, 0)
OR NVL (cur.dcx_ext_dept_status, 0) <>
NVL (past.dcx_ext_dept_status, 0)
OR NVL (NVL (cur.dcx_assessment, cur.dcx_ext_dept_status), 0) <>
NVL (NVL (past.dcx_assessment, past.dcx_ext_dept_status), 0)
OR NVL (cur.dcx_po_cost, 0) <> NVL (past.dcx_po_cost, 0)
))
/


-- End of DDL Script for View OLAP.DCX_BOM_RECON_FACT_T
___________________________________________________________

Second View

DCX_PIECE_COST_V_TEMP

CREATE OR REPLACE VIEW dcx_piece_cost_v_temp (
qty,
dcx_tracked_vehicle,
dcx_snapshot,
dcx_part_number,
dcx_report_mgmt_uid,
dcx_ext_dept_target,
dcx_ext_item_target,
dcx_ext_item_status,
dcx_ext_dept_status,
dcx_assess_prob,
dcx_assessment,
dcx_po_cost,
dcx_rev )
AS
(
SELECT SUM (pcf.dcx_ext_quantity) as qty, pcf.dcx_tracked_vehicle as dcx_tracked_vehicle,
pcf.dcx_snapshot as dcx_snapshot, pcf.dcx_part_number as dcx_part_number,
pcf.dcx_report_mgmt_uid as dcx_report_mgmt_uid ,

SUM (
CASE
WHEN pcf.dcx_foreign = 'Y'
OR pud.dcx_uom = 'AR' THEN 0
ELSE pcf.dcx_ext_dept_target
END
)
as dcx_ext_dept_target,

SUM (
CASE
WHEN pcf.dcx_foreign = 'Y'
OR pud.dcx_uom = 'AR'
OR SUBSTR (pud.dcx_lineup_code, 1, 2) =
'02'
OR SUBSTR (pud.dcx_lineup_code, 1, 2) =
'35' THEN 0
ELSE pcf.dcx_ext_item_target
END
)
as dcx_ext_item_target,

SUM (
CASE
WHEN pcf.dcx_foreign = 'Y'
OR pud.dcx_uom = 'AR'
OR SUBSTR (pud.dcx_lineup_code, 1, 2) =
'02'
OR SUBSTR (pud.dcx_lineup_code, 1, 2) =
'35' THEN 0
ELSE pcf.dcx_ext_item_status
END
)
as dcx_ext_item_status,

SUM (
CASE
WHEN pcf.dcx_foreign = 'Y'
OR pud.dcx_uom = 'AR' THEN 0
ELSE pcf.dcx_ext_dept_status
END
)
as dcx_ext_dept_status,

MAX (dcx_assess_prob) dcx_assess_prob,

SUM (
CASE
WHEN pcf.dcx_foreign = 'Y'
OR pud.dcx_uom = 'AR' THEN 0
ELSE NVL (
pcf.dcx_assessment,
pcf.dcx_ext_dept_status
)
END
)
as dcx_assessment,

SUM (pcf.dcx_po_cost*pcf.dcx_ext_quantity) as dcx_po_cost, MAX (pcf.dcx_rev) as dcx_rev

FROM dcx_piece_cost_fact pcf, dcx_papercar_usage_dim pud
where pcf.dcx_papercar_usage_uid = pud.dcx_papercar_usage_uid
GROUP BY pcf.dcx_tracked_vehicle,
pcf.dcx_snapshot,
pcf.dcx_part_number,
pcf.dcx_report_mgmt_uid
)
/


-- End of DDL Script for View OLAP.DCX_PIECE_COST_V_TEMP
_________________________________________________________

I'm sorry if this is weird way of asking a question but any help would really be appreciated.
Thanks a lot
Ameena____________________________________________________

Tom Kyte
April 08, 2004 - 3:20 pm UTC

impressively long but -- what could you expect me to do with it?

you and your coworkers have made the strategic decision to query the live tables directly to answser the answer -- instead of querying the answer itself (which might be a little stale -- the staleness of which is controlled by you).

You will wait for the answer to appear. Other than looking at the model and saying 'could i write a more efficient query'.......

The EXPAND_GSET_TO_UNION Hint

Jerry, July 07, 2005 - 3:11 am UTC

Hi Tom,

Thank you for your service to the Oracle world community.

I have a question regarding the EXPAND_GSET_TO_UNION Hint that is mentioned in a post above. I looked up the Performance Tuning Guide where it is mentioned that using this hint causes Oracle to
1. break up a "grouping sets" query into multiple queries with Union All,
2. evaluate whether any of those multiple queries can be rewritten using materialized views and if yes then it is.
3. Any of those queries that are not rewritten are "recombined" into a grouping sets query.

I also happened to read the Oracle-By-Example article at (</code> http://www.oracle.com/technology/obe/obe9ir2/obe-dwh/rba/rba.htm <code>) where it is indicated (with examples) that a Grouping Sets or Rollup query is always better than a Union ALL.

Now my question is :
Is the cost of performing the above 3 Steps(i.e multiple re-writes of the query) when the hint is specified, so much less than just executing with the efficient plan generated by the grouping sets without the Hint.

In other words I don't really see the usefulness of the hint .

Would appreciate your opinion on this. ( I currently don't have access to an Oracle environment so am unable to test this out but it is more of a theoretical/concepts question).

Thanks

MV Consider fresh

martina, May 27, 2009 - 6:32 am UTC

Hello Tom,

Are you still of the opinion of post

MV CONSIDER FRESH March 4, 2004 - 4pm ?

If we drop a partition of the basetable and say "consider fresh" we might loose current data for the MV.

so we had the same idea like jegan had,

this was your answer then:

don't try to fake out the MV -- it doesn't work that way.

You would be able to

a) have your partitioned detail table
b) have a partitioned MV on that detail table

when you goto roll the window (purge the old data) you would:

a) alter table detail drop partition
b) alter table MV EXCHANGE partition, to convert the full partition into a full table and convert
an empty table into an empty partition
c) alter table MV drop partition and tell us to "consider it fresh"
d) add that full table as a partition to another history table


instead of your d) we would like to exchange the partition back into the MV.

a second question: where do i see what materialized views are based on a table?

thank you!
martina
Tom Kyte
May 27, 2009 - 11:31 am UTC

would I still say "don't fake it out", yes

ops$tkyte%ORA10GR2> create table t1 ( x int primary key, y int );

Table created.

ops$tkyte%ORA10GR2> create table t2 ( a int primary key, b int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create materialized view mv
  2  as
  3  select *
  4    from t1, t2
  5   where t1.x = t2.a
  6  /

Materialized view created.

ops$tkyte%ORA10GR2> select * from USER_MVIEW_DETAIL_RELATIONS;

OWNER                          MVIEW_NAME
------------------------------ ------------------------------
DETAILOBJ_OWNER                DETAILOBJ_NAME                 DETAILOBJ
------------------------------ ------------------------------ ---------
DETAILOBJ_ALIAS
------------------------------
OPS$TKYTE                      MV
OPS$TKYTE                      T1                             TABLE
T1

OPS$TKYTE                      MV
OPS$TKYTE                      T2                             TABLE
T2



http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1100.htm#i1582007

Tuning MVIEW

Atul, January 28, 2010 - 5:17 am UTC

Hello Tom,

I im in process of tuning MVIEW which goes like this

==
CREATE MATERIALIZED VIEW XXADS_PIPELINE_ORDERS_28_MV
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS with ItemInfo as (
select
msi.organization_id,
msi.inventory_item_id,
msi.segment1 as ItemNumber,
msi.primary_unit_of_measure,
mic.category_id,
mc.structure_id,
mc.segment1 as ProductType,
mc.segment2 as ProductLine,
mc.segment3 as ProductSize,
mc.segment4 as ProductSeries,
mc.segment5 as ProductBrand
from apps.mtl_system_items msi
join apps.mtl_item_categories mic
on msi.organization_id = mic.organization_id
and msi.inventory_item_id = mic.inventory_item_id
join apps.mtl_categories mc
on mic.category_id = mc.category_id
join apps.mtl_category_sets mcs
on mc.structure_id = mcs.structure_id
where mcs.category_set_name = ''Inventory''
)
select trunc(sysdate) created_date, apps.XXADS_COMMON_UTILS_INT.Get_Region_From_Shipto(oolh.ship_to_org_id,ooha.attribute1,
oolh.sold_to_org_id,decode(oola.ship_from_org_id,648,''NON-US'',''US'')) Rgn,
mcb.segment1 Product_Type,
ooha.order_number order_number,
mcb.segment2 Type,
mcb.segment3 Dia,
rc.customer_name customer_name,
jrs.name Salesperson,
jrs.salesrep_number,
mp.organization_code as warehouse,
trunc(oolh.hist_creation_date) Cancelled_Dt,
trunc(ooha.booked_date) Entry_Dt,
trunc(oolh.schedule_ship_date) Ship_Dt,
ItemInfo.ItemNumber Product,
oola.ordered_quantity Qty_Ord,
--apps.XXADS_COmmon_utils_int.get_lb_conversion_rate(muom.unit_of_measure,msi.primary_unit_of_measure,msi.inventory_item_id) * oola.ordered_quantity Ordered_WeightOLD,
apps.XXADS_COmmon_utils_int.get_lb_conversion_rate(muom.unit_of_measure,msi.primary_unit_of_measure,msi.inventory_item_id, ''Y'', oola.Ship_From_Org_ID) *
oola.ordered_quantity Ordered_Weight,
oolh.latest_cancelled_quantity Cancelled_Qty,
--apps.XXADS_COmmon_utils_int.get_lb_conversion_rate(muom.unit_of_measure,msi.primary_unit_of_measure,msi.inventory_item_id) * oolh.latest_cancelled_quantity Cancelled_WeightOLD,
apps.XXADS_COmmon_utils_int.get_lb_conversion_rate(muom.unit_of_measure,msi.primary_unit_of_measure,msi.inventory_item_id, ''Y'', oola.Ship_From_Org_ID) *
oolh.latest_cancelled_quantity Cancelled_Weight,
decode(ooha.transactional_curr_code,''CAD'',
oolh.unit_selling_price*(
select gdr.conversion_rate
from apps.gl_daily_rates gdr
where gdr.from_currency = ''CAD''
AND trunc(ooha.booked_date) = gdr.conversion_date
),oolh.unit_selling_price) Unit_Price,
CASE WHEN (select msi.unit_weight from apps.mtl_system_items_b msi where oolh.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID and
oolh.ship_from_org_id = msi.organization_id)=0 THEN 0
ELSE (decode(ooha.transactional_curr_code,''CAD'',
(apps.XXADS_COmmon_utils_int.get_lb_conversion_rate(muom.unit_of_measure,msi.primary_unit_of_measure,msi.inventory_item_id,''N'') * oolh.unit_selling_price)*(select gdr.conversion_rate from apps.gl_daily_rates gdr
where gdr.from_currency = ''CAD'' AND trunc(ooha.booked_date) = gdr.conversion_date),
apps.XXADS_COmmon_utils_int.get_lb_conversion_rate(muom.unit_of_measure,msi.primary_unit_of_measure,msi.inventory_item_id,''N'') * oolh.unit_selling_price))/(select msi.unit_weight from
apps.mtl_system_items_b msi where oolh.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID and oolh.ship_from_org_id = msi.organization_id) END Yield
, orv.reason_code as Reason_Code,
orv.reason as Reason,
orv.comments as Comments,
oola.line_number as Line_Number,
ooha.ORG_ID as Operating_Unit,
fu.user_name as CSR
from apps.oe_order_lines_history oolh,
apps.oe_order_headers_all ooha,
apps.oe_order_lines_all oola,
apps.mtl_item_categories mic,
apps.mtl_categories_b mcb,
apps.oe_transaction_types_tl ottt,
apps.oe_transaction_types_all otta,
apps.mtl_system_items msi,
apps.mtl_units_of_measure muom ,
apps.ra_customers rc,
apps.jtf_rs_salesreps jrs,
apps.qp_price_lists_v qp,
apps.mtl_Parameters mp,
ItemInfo,
apps.oe_reasons_v orv,
apps.fnd_user fu
where 1=1
and trunc(oolh.hist_creation_date) > trunc(apps.XXADS_COMMON_UTILS_INT.Get_Nth_Working_Day(SYSDATE, ''XX_PIPELIN'', -2)) -- 2nd last business day
and trunc(oolh.hist_creation_date) <=trunc(apps.XXADS_COMMON_UTILS_INT.Get_Nth_Working_Day(SYSDATE, ''XX_PIPELIN'', -1)) -- 1st last business day
--and to_char(oolh.hist_creation_date, ''YYYY-MM-DD'') = ''2009-09-03''
and trunc(oolh.hist_creation_date) <> trunc(oola.creation_date)
and trunc(oolh.hist_creation_date) <> trunc(ooha.booked_date) -- exclude lines created and cancelled on same day or booked and cancelled on same day
and ooha.booked_date is not null
and oolh.hist_type_code = ''CANCELLATION''
and oolh.line_id = orv.entity_id
and orv.reason_type_code = ''CANCEL_CODE''
and orv.entity_code = ''LINE''
and oolh.reason_id = orv.reason_id
and oolh.Hist_Created_By = fu.user_id
and oolh.latest_cancelled_quantity > 0
AND oola.header_id = ooha.header_id
and oolh.header_id = ooha.header_id
and oolh.line_id = oola.line_id
and oolh.INVENTORY_ITEM_ID = mic.inventory_item_id
and oolh.ship_from_org_id = mic.ORGANIZATION_ID

and mic.category_set_id = 1
and mic.category_id=mcb.category_id
and oola.ship_from_org_id = mp.organization_id
and ooha.ORG_ID IN (''133'',''132'',''1006'',''1007'',''989'',''990'')
and ooha.order_type_id = ottt.transaction_type_id
and ottt.name not like ''%Internal%''
and ottt.transaction_type_id = otta.transaction_type_id
and nvl(otta.end_date_active,sysdate+1) > sysdate
and nvl(otta.attribute10,''N'') = ''Y'' -- do not return credit memos
and oolh.inventory_item_id = msi.inventory_item_id
and oolh.ship_from_org_id = msi.organization_id
and oolh.order_quantity_uom = muom.uom_code
and ooha.sold_to_org_id = rc.customer_id
and jrs.salesrep_id(+) = oola.salesrep_id
and jrs.org_id(+) = oola.org_id
and ooha.price_list_id = qp.price_list_id
and qp.name <> ''Conversion Pricelist''
and oola.ship_from_org_id = ItemInfo.organization_id
and oola.inventory_item_id = ItemInfo.inventory_item_id
===

And Exaplain Plan is below

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-----------------------------------------

| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)|

--------------------------------------------------------------------------------
-----------------------------------------

| 0 | SELECT STATEMENT |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 | 680 | 539 (1)|

| 1 | TABLE ACCESS BY INDEX ROWID | GL_DAILY_RATES
| 1 | 19 | 6 (0)|

| 2 | INDEX SKIP SCAN | GL_DAILY_RATES_U1
| 1 | | 5 (0)|

| 3 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_
B | 1 | 13 | 3 (0)|


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 4 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_
B_U1 | 1 | | 2 (0)|

| 5 | TABLE ACCESS BY INDEX ROWID | GL_DAILY_RATES
| 1 | 19 | 6 (0)|

| 6 | INDEX SKIP SCAN | GL_DAILY_RATES_U1
| 1 | | 5 (0)|

| 7 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_
B | 1 | 13 | 3 (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 8 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_
B_U1 | 1 | | 2 (0)|

| 9 | NESTED LOOPS |
| 1 | 680 | 539 (1)|

| 10 | NESTED LOOPS |
| 1 | 667 | 538 (1)|

| 11 | NESTED LOOPS |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 | 642 | 537 (1)|

| 12 | NESTED LOOPS |
| 1 | 635 | 537 (1)|

| 13 | NESTED LOOPS |
| 1 | 601 | 536 (1)|

| 14 | NESTED LOOPS |
| 1 | 587 | 535 (1)|


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 15 | NESTED LOOPS |
| 1 | 575 | 534 (1)|

| 16 | NESTED LOOPS |
| 1 | 567 | 533 (1)|

| 17 | HASH JOIN |
| 1 | 542 | 532 (1)|

| 18 | NESTED LOOPS |
| 1 | 531 | 525 (1)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 19 | NESTED LOOPS |
| 1 | 522 | 524 (1)|

| 20 | NESTED LOOPS OUTER |
| 1 | 509 | 522 (1)|

| 21 | NESTED LOOPS |
| 1 | 471 | 521 (1)|

| 22 | NESTED LOOPS |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 | 453 | 519 (1)|

| 23 | NESTED LOOPS |
| 1 | 438 | 517 (1)|

| 24 | NESTED LOOPS |
| 1 | 419 | 515 (1)|

| 25 | NESTED LOOPS |
| 1 | 371 | 513 (1)|


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 26 | NESTED LOOPS |
| 1 | 334 | 512 (1)|

| 27 | NESTED LOOPS |
| 1 | 317 | 511 (1)|

| 28 | NESTED LOOPS |
| 1 | 309 | 510 (1)|

| 29 | NESTED LOOPS |
| 1 | 280 | 508 (1)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 30 | NESTED LOOPS |
| 1 | 233 | 506 (1)|

| 31 | HASH JOIN |
| 1 | 161 | 503 (1)|

| 32 | MERGE JOIN CARTESIAN |
| 1 | 113 | 5 (0)|

| 33 | NESTED LOOPS |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 | 55 | 2 (0)|

| 34 | INDEX RANGE SCAN | FND_LOOKUP_TYPES_
U1 | 1 | 26 | 2 (0)|

| 35 | INDEX UNIQUE SCAN | FND_LOOKUP_TYPES_
TL_U1 | 1 | 29 | 0 (0)|

| 36 | BUFFER SORT |
| 1 | 58 | 5 (0)|


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 37 | TABLE ACCESS BY INDEX ROWID| FND_LOOKUP_VALUES
| 1 | 58 | 3 (0)|

| 38 | INDEX RANGE SCAN | FND_LOOKUP_VALUES
_U1 | 1 | | 2 (0)|

| 39 | TABLE ACCESS FULL | OE_REASONS
| 13009 | 609K| 498 (1)|

| 40 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_LINES_HI
STORY | 1 | 72 | 3 (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 41 | INDEX RANGE SCAN | OE_ORDER_LINES_HI
STORY_N1 | 1 | | 2 (0)|

| 42 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_HEADERS_
ALL | 1 | 47 | 2 (0)|

| 43 | INDEX UNIQUE SCAN | OE_ORDER_HEADERS_
U1 | 1 | | 1 (0)|

| 44 | TABLE ACCESS BY INDEX ROWID | OE_TRANSACTION_TY

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
PES_TL | 1 | 29 | 2 (0)|

| 45 | INDEX RANGE SCAN | OE_TRANSACTION_TY
PES_TL_U1 | 1 | | 1 (0)|

| 46 | TABLE ACCESS BY INDEX ROWID | OE_TRANSACTION_TY
PES_ALL | 1 | 8 | 1 (0)|

| 47 | INDEX UNIQUE SCAN | OE_TRANSACTION_TY
PES_ALL_U1 | 1 | | 0 (0)|


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 48 | TABLE ACCESS BY INDEX ROWID | QP_LIST_HEADERS_B
| 1 | 17 | 1 (0)|

| 49 | INDEX UNIQUE SCAN | QP_LIST_HEADERS_B
_PK | 1 | | 0 (0)|

| 50 | TABLE ACCESS BY INDEX ROWID | QP_LIST_HEADERS_T
L | 1 | 37 | 1 (0)|

| 51 | INDEX UNIQUE SCAN | QP_LIST_HEADERS_T
L_PK | 1 | | 0 (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 52 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_LINES_AL
L | 1 | 48 | 2 (0)|

| 53 | INDEX UNIQUE SCAN | OE_ORDER_LINES_U1
| 1 | | 1 (0)|

| 54 | INDEX RANGE SCAN | MTL_ITEM_CATEGORI
ES_U1 | 1 | 19 | 2 (0)|

| 55 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
B | 1 | 15 | 2 (0)|

| 56 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_
B_U1 | 1 | | 1 (0)|

| 57 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_
B | 1 | 18 | 2 (0)|

| 58 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_
B_U1 | 1 | | 1 (0)|


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 59 | TABLE ACCESS BY INDEX ROWID | JTF_RS_SALESREPS
| 1 | 38 | 1 (0)|

| 60 | INDEX UNIQUE SCAN | JTF_RS_SALESREPS_
U1 | 1 | | 0 (0)|

| 61 | INDEX RANGE SCAN | MTL_ITEM_CATEGORI
ES_U1 | 2 | 26 | 2 (0)|

| 62 | TABLE ACCESS BY INDEX ROWID | MTL_CATEGORIES_B
| 1 | 9 | 1 (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 63 | INDEX UNIQUE SCAN | MTL_CATEGORIES_B_
U1 | 1 | | 0 (0)|

| 64 | TABLE ACCESS FULL | MTL_CATEGORY_SETS
_B | 12 | 132 | 6 (0)|

| 65 | TABLE ACCESS BY INDEX ROWID | MTL_CATEGORY_SETS
_TL | 1 | 25 | 1 (0)|

| 66 | INDEX UNIQUE SCAN | MTL_CATEGORY_SETS

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
_TL_U1 | 1 | | 0 (0)|

| 67 | TABLE ACCESS BY INDEX ROWID | MTL_PARAMETERS
| 1 | 8 | 1 (0)|

| 68 | INDEX UNIQUE SCAN | MTL_PARAMETERS_U1
| 1 | | 0 (0)|

| 69 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNTS
| 1 | 12 | 1 (0)|


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 70 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_
U1 | 1 | | 0 (0)|

| 71 | TABLE ACCESS BY INDEX ROWID | MTL_UNITS_OF_MEAS
URE_TL | 1 | 14 | 1 (0)|

| 72 | INDEX UNIQUE SCAN | MTL_UNITS_OF_MEAS
URE_TL_U2 | 1 | | 0 (0)|

| 73 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES
| 1 | 34 | 1 (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 74 | INDEX UNIQUE SCAN | HZ_PARTIES_U1
| 1 | | 0 (0)|

| 75 | INDEX UNIQUE SCAN | MTL_CATEGORIES_TL
_U1 | 1 | 7 | 0 (0)|

| 76 | TABLE ACCESS BY INDEX ROWID | MTL_CATEGORIES_B
| 1 | 25 | 1 (0)|

| 77 | INDEX UNIQUE SCAN | MTL_CATEGORIES_B_

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
U1 | 1 | | 0 (0)|

| 78 | TABLE ACCESS BY INDEX ROWID | FND_USER
| 1 | 13 | 1 (0)|

| 79 | INDEX UNIQUE SCAN | FND_USER_U1
| 1 | | 0 (0)|

--------------------------------------------------------------------------------
-----------------------------------------


I am not sure from where to start,could you please suggest


How to choose between FAST or COMPLETE refresh ?

Max, February 11, 2010 - 8:06 am UTC

Hi,

I currently have several materialized views which are "simple" copies of master tables (of a remote db) containing 1 to 5 millions lines each. They are refreshed in COMPLETE mode.

Do you have a rule of thumb for determining in which cases the FAST mode will be faster (depending on the % of data in the table that has changed, number of insert/update/delete between 2 refresh...) ?

Greetings,
Max
Tom Kyte
February 16, 2010 - 9:48 am UTC

Not really - because you can always change the % by increasing or decreasing the period of refreshing - refresh more often - percentage of rows likely decreases, increase and the opposite.

This is something you should benchmark, in your environment, using your network. Things to consider;

o if you fast refresh you need materialized view logs, that'll make every transaction on the source system do more work, generate more undo, more redo, take a bit longer.

o if you fast refresh, the refresh process will be more "complex", involves more procedural work - but could be significantly LESS work than a full refresh (no full scan, no 5,000,000 rows over the network)

o fast refreshes are always transactional (undo, redo, insert/update/delete). complete can skip redo/undo (can use truncate+direct path - but data disappears during refresh)

o by default a complete refresh (in 10g and above) will be an expensive DELETE+INSERT of 5,000,000 rows.


I have no rule of thumb, I would consider benchmarking to see what the expense of adding the materialized view log would be on existing applications and whether the incremental (fast) refresh is less resource intensive in my environment.

Can materialized views be refreshed automatically?

Douglas Alan, February 18, 2010 - 1:17 pm UTC

Can materialized views be refreshed automatically?

Well, let me elaborate a bit, as I know they can be, either on a schedule
or "on commit", BUT if we refresh on a schedule, we might end up using
stale data when we query the materialized view directly, and if we
refresh "on commit" we will probably slow down the commits.

What I have in mind, instead, is having our materialized views refreshed
automatically, but this refreshing need not occur until the materialized
view is queried AND it has stale data.

Alternatively, the materialized view could be refreshed in the background
as soon as it becomes stale. In this case, any queries made on the
materialized views would have to block until the refresh has completed.

I haven't been able to find any documentation, however, saying that
either of these automatic refreshing approaches is possible in Oracle.

The reason that I would like to do things this way, if possible, is that
we cannot tolerate at all the use of stale data. And also, we likely
cannot live with commits on the underlying data being slowed down. On the
other hand, we can live with queries waiting for fresh data.

One suggestion might be to have all our code explicitly test an MV for
staleness before using it, and refresh it if need be. But this is not so
convenient, and is error-prone.

Another suggestion might be for us to write any programs that modify the
underlying tables to also refresh dependent materialized views and to
also disable them somehow until the MVs are finished being refreshed. We
do something of the sort already, but sometimes someone manually modifies
a table and forgets to refresh dependent MVs, and we end up using stale
data to much gnashing of teeth.

A more automatic and foolproof mechanism would be much preferable.

Thanks,
|>ouglas

Tom Kyte
February 18, 2010 - 7:39 pm UTC

... What I have in mind, instead, is having our materialized views refreshed
automatically, but this refreshing need not occur until the materialized
view is queried AND it has stale data.
...


sorry, does not exist and I haven't seen a business case for it - the query response time would vary so widely (if you have nothing to update, fast, if you have a 1,000,000 rows to update - pretty darn slow) that the materialized view would be considered widely unpredictable. It would not work in real life.


... The reason that I would like to do things this way, if possible, is that
we cannot tolerate at all the use of stale data. ...

then you either

a) use on commit OR
b) do not use a materialized view

they do not support the concepts you describe.




.. A more automatic and foolproof mechanism would be much preferable. ...

sorry, but if you ask me, it is already automatic (either on commit - transactional or on demand, completely automatic and 'foolproof').


In any sort of system were updates were happening at the same time as queries against the materialized views whose base tables are updated - what you propose would make the response times of the materialized views so unpredicable as to render them "something to be feared by developers and end users all over"

MV

A reader, February 18, 2010 - 11:31 pm UTC


Re: Can materialized views be refreshed automatically?

Douglas Alan, February 23, 2010 - 5:56 pm UTC

Thanks for the speedy and informative reply!

> sorry, does not exist and I haven't seen a business case for it

I can certainly understand that Oracle wouldn't want to implement a
complex feature that might only be desired by a tiny fraction of its
customers, but it seems to me as if there *is* a decent business case
for the feature. At least for us. (Again, I don't mean to imply that
this business case is significant enough that Oracle should run out
and implement it.)

We have a scientific data warehouse, where some of the tables have
hundreds of millions of rows. We also have views that aggregate
information in some of these large tables. Querying against these
views can take a very long time at the moment, because they apparently
have to be materialized into temp tablespace every time they are used.

Some of our science needs these aggregated views, but a lot of it
doesn't. If we used "on commit", then new data would take longer to be
made available to the apps that need the non-aggregated data.

> sorry, but if you ask me, it is already automatic (either on commit
> - transactional or on demand, completely automatic and 'foolproof').

I agree that "on commit" is foolproof, but I'm not sure that we could
tolerate the slow-down on writes. The automatic, on-demand approach is
not fool-proof for us, since someone might see stale data.

> In any sort of system were updates were happening at the same time
> as queries against the materialized views whose base tables are
> updated - what you propose would make the response times of the
> materialized views so unpredictable as to render them "something to
> be feared by developers and end users all over"

I certainly agree that it might be a feature that could cause some
people trouble. Then again, so can many useful features. E.g.,
demand-paging on operating systems has a similar gotcha if the system
starts thrashing.

For us, in this case, it's true that the feature I was looking for
might cause queries on our aggregation views to take an unpredictable
amount of time. Sometimes the query would be fast and sometimes it
would be slow. On the other hand, right now these queries are *always*
slow. So it's not clear that in this case *predictable* is *better*.

I am, of course, always open to the possibility that there is an even
better (or perhaps merely nearly-as-good) solution to our problem than
what I was looking for, and that I am just not aware of it. If this is
the case, I am all ears!

Thanks again,
|>ouglas

Tom Kyte
March 01, 2010 - 8:37 am UTC

the only approaches I see are

a) refresh on commit
b) refresh on a schedule and accept that sometimes the data is stale, within the tolerance defined by you as specified by your refresh period.

sorry.

alternative of MV

a reader, April 10, 2011 - 12:13 am UTC

Hi Tom,
could you please give me some other alternative of metralized view.somehow there is one scenario where i cannot use MV.what are all other options apart from MV available in oracle.


Tom Kyte
April 12, 2011 - 3:59 pm UTC

you would sort of need to describe what you need to do, completely, in order to offer a solution.

this is like asking "what can i use as an alternative to an index". The only reasonable answer is "it depends". tell us what you are trying to accomplish.

and why you cannot use a materialized view - that is, what restrictions do you have.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.