Home » RDBMS Server » Server Administration » Materialized aggregate view index (DB11.2.0.3)
Materialized aggregate view index [message #558928] Wed, 27 June 2012 08:43 Go to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
When you create a MAV, you automatically get a hidden column and an index. Does anyone know what it is for? Can you think of a way to use it? Here's an example,
drop user jon cascade;
grant dba to jon identified by jon;
conn jon/jon

create table emp as select * from scott.emp;
create materialized view mv1 enable query rewrite as
select deptno,sum(sal) from emp group by deptno;

select object_name,object_type from user_objects;
select index_name,column_name from user_ind_columns where table_name='MV1';
select column_name,hidden_column from user_tab_cols where table_name='MV1';

select deptno,"SUM(SAL)",sys_nc00003$ from mv1;

Re: Materialized aggregate view index [message #558939 is a reply to message #558928] Wed, 27 June 2012 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Knowing that the hidden column definition is 'SYS_OP_MAP_NONNULL("DEPTNO")', maybe the followings will help:
Optimizing Materialized Views Part III: Manual Refresh Mechanisms
A Quick Materialized View Performance Note

Regards
Michel

[Updated on: Wed, 27 June 2012 10:42]

Report message to a moderator

Re: Materialized aggregate view index [message #559048 is a reply to message #558939] Thu, 28 June 2012 04:27 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Thank you for replying, I hope I'm beginning to understand what is going on.
The hidden and indexed column is a virtual column, defined in dba_tab_cols.data_default. The function that creates the virtual column is sys_op_map_nonnull, which is not documented, that converts nulls into a form that can be compared:
orcl> select * from dual where null=null;

no rows selected

orcl> select * from dual where sys_op_map_nonnull(null)=sys_op_map_nonnull(null);

D
-
X

orcl>
The purpose of all this is to facilitate fast refresh from MV logs, in the case where the aggregating column is nullable. I can't imagine a circumstance where you would want to aggregate on a nullable column, but without this an index couldn't be used to locate the matching row.
I realize that questions of this nature are often of no immediate practical value, but I find that reverse engineering Oracle features is the best way to learn how to exploit them.
Previous Topic: restart of the database service causes ORA-12514: TNS:listener does not currently know of service re
Next Topic: what is wrong in the code
Goto Forum:
  


Current Time: Thu Mar 28 03:59:04 CDT 2024