Materialized view redefinition [message #365320] |
Thu, 11 December 2008 08:06  |
skave
Messages: 2 Registered: December 2008
|
Junior Member |
|
|
Hi all,
I need redefine a Materialized View (add some columns). I try it with:
drop materialized view TCPIP preserve table;
ALTER TABLE TCPIP ADD (SUM_IP_OUT_OF_SEQ NUMBER)
/
ALTER TABLE TCPIP ADD (SUM_IP_OUT_OF_SEQ NUMBER)
/
and than create a new MV:
CREATE MATERIALIZED VIEW TCPIP
ON PREBUILT TABLE USING NO INDEX NEVER REFRESH ENABLE QUERY REWRITE as
SELECT ...
But with Oracle error: ORA-12060: shape of prebuilt table does not match definition query
I check the new MV definition, it is contain the 2 added columns.
What can be wrong?
Thank you for advance
|
|
|
|
Re: Materialized view redefinition [message #365330 is a reply to message #365321] |
Thu, 11 December 2008 09:05   |
skave
Messages: 2 Registered: December 2008
|
Junior Member |
|
|
When I use "ON PREBUILT TABLE", I can not use "BUILD IMMEDIATE" or "BUILD DEFERRED". I checked it in create materialized view syntax. I checked also the number of columns, column types in the prebuilt table and in the MV creational query. It seems to be ok.
|
|
|
|
|
Re: Materialized view redefinition [message #566308 is a reply to message #566307] |
Wed, 12 September 2012 09:33   |
John Watson
Messages: 8965 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Kevin, is the MV based on an aggregation? If so, it will have an extra hidden column and an index, see COLUMN_NAME HIDDEN DATA_DEFAULT in DBA_TAB_COLS.
Only a guess if that can cause the problem, I haven't tested.
|
|
|
Re: Materialized view redefinition [message #566309 is a reply to message #566308] |
Wed, 12 September 2012 09:51  |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Seems there are multiple issues. In the end I needed two things:
WITH REDUCED PRECISION
and
to CAST() every column to the necessary definition. What a pain in the butt.
Here is the scenario for those who need the detail:
The prebuilt table I want.
create table kev_VIEW_TABLE_LOAD_VALUES
(
TABLE_ID
, TABLE_ROW_ID
, TABLE_ROW_SORT_ORDER
, TABLE_COLUMN_ID
, TABLE_COLUMN_VALUE
, TABLE_COLUMN_SRTORDR
,constraint kev_VIEW_TABLE_LOAD_VALUES_pk primary key (TABLE_ID,TABLE_COLUMN_ID,TABLE_ROW_ID)
)
organization index
as
select
TABLE_ID
, TABLE_ROW_ID
, TABLE_ROW_SORT_ORDER
, TABLE_COLUMN_ID
, TABLE_COLUMN_VALUE
, TABLE_COLUMN_SRTORDR
from RATE_PROC.VIEW_TABLE_LOAD_VALUES
/
Rename it in a weak attempt at naming sense.
rename kev_view_table_load_values to kev_mv_view_table_load_values
/
'
This fails. Notice this is the exact same query so why does it fail?
create materialized view km21378.kev_mv_VIEW_TABLE_LOAD_VALUES
on prebuilt table
with reduced precision
--enable query rewrite
as
select
TABLE_ID
, TABLE_ROW_ID
, TABLE_ROW_SORT_ORDER
, TABLE_COLUMN_ID
, TABLE_COLUMN_VALUE
, TABLE_COLUMN_SRTORDR
from RATE_PROC.VIEW_TABLE_LOAD_VALUES
/
This works. Notice use of WITH REDUCED PRECISION and use of CAST().
create materialized view km21378.kev_mv_VIEW_TABLE_LOAD_VALUES
on prebuilt table
with reduced precision
--enable query rewrite
as
select
cast(TABLE_ID as varchar2(4)) table_id
, cast(TABLE_ROW_ID as varchar2(8)) table_row_id
, cast(TABLE_ROW_SORT_ORDER as number(8)) TABLE_ROW_SORT_ORDER
, cast(TABLE_COLUMN_ID as varchar2(4)) TABLE_COLUMN_ID
, cast(TABLE_COLUMN_VALUE as varchar2(40)) TABLE_COLUMN_VALUE
, cast(TABLE_COLUMN_SRTORDR as number(4)) TABLE_COLUMN_SRTORDR
from RATE_PROC.VIEW_TABLE_LOAD_VALUES
/
I wonder if rewrite will work given the casting?
Kevin
|
|
|