Home » RDBMS Server » Performance Tuning » about PCT in mv_capabilities_table.
about PCT in mv_capabilities_table. [message #166694] Fri, 07 April 2006 11:11 Go to next message
srikanth.raju
Messages: 1
Registered: April 2006
Location: TAMIL NADU
Junior Member
Hi,

we have creatyed an Mv for fast refresh and its taking more time than complete refresh .

I found in MV_CAPABILITY_TABLE, for the particular mv,capability_name for PCT is N and in our detail table , the column which is partitioned is not used in the select statement of the Mv.PCT_TABLE shows the message as "no parttition_key or PMARKER in the select list of MV".
If i recreate the MV by having the partition key in the select statement of MV, will this improve the performance of the fast refresh and i also want to know performance gets affected because of PCT.

thanks
srikanth
Re: about PCT in mv_capabilities_table. [message #166802 is a reply to message #166694] Sat, 08 April 2006 19:29 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
PCT is Partition Change Tracking (I think). It means that when you TRUNCATE, ADD, DROP, EXCHANGE partitions in the base table, the materialized view can still be refreshed. Without PCT, you would need to full-refresh the MV.

If you can still perform a fast refresh with PCT disabled, it seems likely that you are not performing any of these operations.

The inability to perform PCT refresh does not affect INSERT/UPDATE/DELETE.

Take a look at the the Data Warehousing Guide, it has some suggestions for tuning fast-refresh MVs, such as indexing the MV Log, indexing the ROWIDs in the MV for USING ROWID mv's, refreshing between direct path load and conventional operations, and more.

Ross Leishman
Previous Topic: Database speed up
Next Topic: List vs Range Partitioning
Goto Forum:
  


Current Time: Fri Mar 29 00:21:01 CDT 2024