Home » SQL & PL/SQL » SQL & PL/SQL » Materialized view redefinition (Oracle 10g)
Materialized view redefinition [message #365320] Thu, 11 December 2008 08:06 Go to next message
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 #365321 is a reply to message #365320] Thu, 11 December 2008 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-12060: shape of prebuilt table does not match definition query
 *Cause:  The number of columns or the type or the length semantics of a
          column in the prebuilt table did not match the materialized
          view definition query.
 *Action: Reissue the SQL command using BUILD IMMEDIATE, BUILD DEFERRED, or
          ensure that the prebuilt table matches the materialized view
          definition query.

Regards
Michel
Re: Materialized view redefinition [message #365330 is a reply to message #365321] Thu, 11 December 2008 09:05 Go to previous messageGo to next message
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 #365331 is a reply to message #365330] Thu, 11 December 2008 09:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It seems to be ok.

If you say so but it seems Oracle does not agree with you.

Regards
Michel
Re: Materialized view redefinition [message #566307 is a reply to message #365320] Wed, 12 September 2012 09:22 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
SKAVE, you ever get an answer to this. I am running into same error.

DESCRIBE of the prebuilt table and the MVIEW created using the original query, are the same so I am dumb-founded as to what is wrong.

I think something else is happening under the covers that Oracle is not telling me but I have not clue what it is.

Kevin
Re: Materialized view redefinition [message #566308 is a reply to message #566307] Wed, 12 September 2012 09:33 Go to previous messageGo to next message
John Watson
Messages: 6580
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 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: Formatting output file (3 Merged)
Next Topic: to get data from all similar tables (2 Merged)
Goto Forum:
  


Current Time: Thu Dec 08 10:23:24 CST 2016

Total time taken to generate the page: 0.09555 seconds