Home » SQL & PL/SQL » SQL & PL/SQL » Materialized view fast refresh is working slow compared to complete refresh
Materialized view fast refresh is working slow compared to complete refresh [message #413627] Thu, 16 July 2009 15:52 Go to next message
ans22hul
Messages: 2
Registered: July 2009
Junior Member
Hi ALL,
We have created a materialized view using fast refresh option. when i am changing record in one of the base table and fast refreshing it, its taking 431 sec while complete refresh is taking 45sec.

we also analyzed all the tables used in the query. Deleted all the entries from materialized view log table and also executed ALTER MATERIALIZED VIEW LOG SHRINK SPACE command. still refresh is slow.

MV Query:

create materialized view TEST_INDUS
REFRESH FAST
AS
SELECT crc.alert_list_item_id Alert_List_Item_id,
CRC.TRANSACTION_ID,
e.entity_desc Party_Legal_Name,
--IND_LKS.ENTITY_CODE Gdr_Party_Id,
cg.entity_code Gdr_Party_Id,
r.rating ORR,
r.rating_order,
c.country_name Country_name,
c.country_code Country_Code,
crc.action_reason Alert_List_add_reason,
crc.action_authorizedby Alert_List_AuthorizedBy,
crc.action_effective_date Alert_List_Eff_date,
CRC.INDUSTRY INDUSTRY_CODE,
CRC.COMMENTS,
CRC.is_active,
sec.DESCRIPTION Industry,
g.geographic_area_code Region_Code,
g.geographic_area_description Region_Name,
CRC.Alert_List_Type Alert_List_Type,
CG.Mother_Code Credit_Group_GDR_ID,
CG.CG_NAME CRedit_Group_Name,
crc.rowid rd0,
c.rowid rd1,
g.rowid rd2,
e.rowid rd3,
sec.rowid rd4,
lnk.rowid rd5,
R.rowid rd6,
cpk.rowid rd7,
cg.rowid rd8
FROM GEM.AIG_CRC_ALERT_LIST_ITEMS crc,
GEM.AIG_ENTITY_INDUSTRY_LINKS LNK,
GEM.AIG_CRC_PARTITION_KEY cpk,
GEM.aig_entity CG,
GEM.rating r,
GEM.entity e,
GEM.country c,
GEM.geographic_area g,
GEM.industry_sector sec
WHERE LNK.PARTITION_KEY = CPK.PK_RD
AND LNK.Partition_Key = trim(Cg.Partition_Key)
AND trim(cg.partition_key) = r.partition_key
AND r.partition_key = e.partition_key
AND e.partition_key = c.partition_key
AND c.partition_key = g.partition_key
AND g.partition_key = sec.partition_key
AND LNK.INDUSTRY_SECTOR LIKE crc.INDUSTRY||'%'
AND LNK.INDUSTRY_SECTOR_TYPE = 'CI'
AND nvl(e.attribute_3,'#') NOT LIKE 'EXPI%'
AND UPPER(e.status) <> 'DELETED'
AND LNK.Entity_Code = CG.Mother_Code
AND cg.rating = r.rating
AND r.agency_code = 'AIG ORR'
AND cg.entity_code = e.entity_code
AND e.country_code = c.country_code
AND c.geographic_area = g.geographic_area_code
AND crc.industry = sec.code
AND sec.attribute_1 = 'CI'
AND crc.alert_add_type ='I'
AND ((crc.action_type IN ('A','M') AND CRC.IS_ACTIVE='Y') OR (crc.action_type ='R'))
AND ((crc.active_to_date >= cpk.current_date-60) OR (crc.active_to_date IS NULL))
AND ((crc.country = c.country_code) OR (NVL(crc.country,'ALL') = 'ALL'))
AND (( r.rating_order BETWEEN DECODE(CRC.ORR_RANK_START,'ALL',NULL,CRC.ORR_RANK_START) AND DECODE(CRC.ORR_RANK_END,'ALL',NULL,CRC.ORR_RANK_END)) OR (NVL(crc.orr_rank_start,'ALL') = 'ALL'))
AND ((crc.region_code = c.geographic_area) OR (NVL(crc.region_code,'ALL')= 'ALL'))





Re: Materialized view fast refresh is working slow compared to complete refresh [message #413629 is a reply to message #413627] Thu, 16 July 2009 16:57 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
SELECT crc.alert_list_item_id        alert_list_item_id,
       crc.transaction_id,
       e.entity_desc                 party_legal_name,
       --IND_LKS.ENTITY_CODE Gdr_Party_Id,
       cg.entity_code gdr_party_id,
       r.rating                      orr,
       r.rating_order,
       c.country_name                country_name,
       c.country_code                country_code,
       crc.action_reason             alert_list_add_reason,
       crc.action_authorizedby       alert_list_authorizedby,
       crc.action_effective_date     alert_list_eff_date,
       crc.industry                  industry_code,
       crc.comments,
       crc.is_active,
       sec.description               industry,
       g.geographic_area_code        region_code,
       g.geographic_area_description region_name,
       crc.alert_list_type           alert_list_type,
       cg.mother_code                credit_group_gdr_id,
       cg.cg_name                    credit_group_name,
       crc.rowid                     rd0,
       c.rowid                       rd1,
       g.rowid                       rd2,
       e.rowid                       rd3,
       sec.rowid                     rd4,
       lnk.rowid                     rd5,
       r.rowid                       rd6,
       cpk.rowid                     rd7,
       cg.rowid                      rd8
FROM   gem.aig_crc_alert_list_items crc,
       gem.aig_entity_industry_links lnk,
       gem.aig_crc_partition_key cpk,
       gem.aig_entity cg,
       gem.rating r,
       gem.entity e,
       gem.country c,
       gem.geographic_area g,
       gem.industry_sector sec
WHERE  lnk.partition_key = cpk.pk_rd
       AND lnk.partition_key = Trim(cg.partition_key)
       AND Trim(cg.partition_key) = r.partition_key
       AND r.partition_key = e.partition_key
       AND e.partition_key = c.partition_key
       AND c.partition_key = g.partition_key
       AND g.partition_key = sec.partition_key
       AND lnk.industry_sector LIKE crc.industry
                                    ||'%'
       AND lnk.industry_sector_type = 'CI'
       AND Nvl(e.attribute_3,'#') NOT LIKE 'EXPI%'
       AND Upper(e.status) <> 'DELETED'
       AND lnk.entity_code = cg.mother_code
       AND cg.rating = r.rating
       AND r.agency_code = 'AIG ORR'
       AND cg.entity_code = e.entity_code
       AND e.country_code = c.country_code
       AND c.geographic_area = g.geographic_area_code
       AND crc.industry = sec.code
       AND sec.attribute_1 = 'CI'
       AND crc.alert_add_type = 'I'
       AND ((crc.action_type IN ('A','M')
             AND crc.is_active = 'Y')
             OR (crc.action_type = 'R'))
       AND ((crc.active_to_date >= cpk.current_date - 60)
             OR (crc.active_to_date IS NULL))
       AND ((crc.country = c.country_code)
             OR (Nvl(crc.country,'ALL') = 'ALL'))
       AND ((r.rating_order BETWEEN Decode(crc.orr_rank_start,'ALL',NULL,
                                                              crc.orr_rank_start) AND Decode(crc.orr_rank_end,'ALL',NULL,
                                                                                                              crc.orr_rank_end))
             OR (Nvl(crc.orr_rank_start,'ALL') = 'ALL'))
       AND ((crc.region_code = c.geographic_area)
             OR (Nvl(crc.region_code,'ALL') = 'ALL')) 


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post EXPLAIN_PLAN

[Updated on: Thu, 16 July 2009 17:18]

Report message to a moderator

Re: Materialized view fast refresh is working slow compared to complete refresh [message #413701 is a reply to message #413627] Fri, 17 July 2009 02:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have a look at metalink doc 420040.1
Re: Materialized view fast refresh is working slow compared to complete refresh [message #414128 is a reply to message #413627] Mon, 20 July 2009 07:59 Go to previous message
ans22hul
Messages: 2
Registered: July 2009
Junior Member
We have chnaged little bit query also..
modified query:

SELECT LNK.INDUSTRY_SECTOR,
LNK.PRIMARY_FLAG,
crc.alert_list_item_id Alert_List_Item_id,
CRC.TRANSACTION_ID,
e.entity_desc Party_Legal_Name,
--IND_LKS.ENTITY_CODE Gdr_Party_Id,
cg.entity_code Gdr_Party_Id,
r.rating ORR,
r.rating_order,
c.country_name Country_name,
c.country_code Country_Code,
crc.action_reason Alert_List_add_reason,
crc.action_authorizedby Alert_List_AuthorizedBy,
crc.action_effective_date Alert_List_Eff_date,
CRC.INDUSTRY INDUSTRY_CODE,
CRC.COMMENTS,
CRC.is_active,
sec.DESCRIPTION Industry,
g.geographic_area_code Region_Code,
g.geographic_area_description Region_Name,
CRC.Alert_List_Type Alert_List_Type,
CG.Mother_Code Credit_Group_GDR_ID,
CG.CG_NAME CRedit_Group_Name,
CASE WHEN (CRC.ALERT_LIST_ITEM_ID = EXCl.ALERT_LIST_ITEM_ID AND CRC.TRANSACTION_ID = EXCl.TRANSACTION_ID AND EXCL.IS_ACTIVE = 'Y') THEN 'Y'
ELSE
'N'
END EXCL_FLAG,
crc.rowid rd0,
c.rowid rd1,
g.rowid rd2,
e.rowid rd3,
sec.rowid rd4,
lnk.rowid rd5,
R.rowid rd6,
cpk.rowid rd7,
cg.rowid rd8,
excl.rowid rd9
FROM GEM.AIG_CRC_ALERT_LIST_ITEMS crc,
GEM.AIG_CRC_ALERT_LIST_EXCL EXCL,
GEM.AIG_ENTITY_INDUSTRY_LINKS LNK,
GEM.AIG_CRC_PARTITION_KEY cpk,
GEM.aig_entity CG,
GEM.rating r,
GEM.entity e,
GEM.country c,
GEM.geographic_area g,
GEM.industry_sector sec
WHERE LNK.PARTITION_KEY = CPK.PK_RD
AND LNK.Partition_Key = trim(Cg.Partition_Key)
AND trim(cg.partition_key) = r.partition_key
AND r.partition_key = e.partition_key
AND e.partition_key = c.partition_key
AND c.partition_key = g.partition_key
AND g.partition_key = sec.partition_key
AND LNK.INDUSTRY_SECTOR LIKE crc.INDUSTRY||'%'
AND LNK.INDUSTRY_SECTOR_TYPE = 'CI'
AND nvl(e.attribute_3,'#') NOT LIKE 'EXPI%'
AND UPPER(e.status) <> 'DELETED'
AND LNK.Entity_Code = CG.Mother_Code
AND cg.rating = r.rating
AND r.agency_code = 'AIG ORR'
AND cg.entity_code = e.entity_code
AND e.country_code = c.country_code
AND c.geographic_area = g.geographic_area_code
AND crc.industry = sec.code
AND sec.attribute_1 = 'CI'
AND crc.alert_add_type ='I'
AND e.entity_code = excl.gdr_party_id(+)
AND excl.is_active(+) = 'Y'
AND excl.ACTION_ADD_TYPE(+) = 'I'
AND ((crc.action_type IN ('A','M') AND CRC.IS_ACTIVE='Y') OR (crc.action_type ='R'))
AND ((crc.active_to_date >= cpk.current_date-60) OR (crc.active_to_date IS NULL))
AND ((crc.country = c.country_code) OR (NVL(crc.country,'ALL') = 'ALL'))
AND ((crc.region_code = c.geographic_area) OR (NVL(crc.region_code,'ALL')= 'ALL'))
AND (( r.rating_order BETWEEN DECODE(CRC.ORR_RANK_START,'ALL',NULL,CRC.ORR_RANK_START) AND DECODE(CRC.ORR_RANK_END,'ALL',NULL,CRC.ORR_RANK_END)) OR (NVL(crc.orr_rank_start,'ALL') = 'ALL'))
AND (CASE WHEN LENGTH(LNK.INDUSTRY_SECTOR) = 8 AND LNK.PRIMARY_FLAG IS NULL THEN 1
ELSe (CASE WHEN LENGTH(LNK.INDUSTRY_SECTOR) <> 8 AND LNK.PRIMARY_FLAG = 'Y' THEN
1 ELSE 0 END)
END) = 1


EXPLAIN PLAN :
******************************************************************



SELECT STATEMENT, GOAL = ALL_ROWS Cost=1159 Cardinality=1 Bytes=572
HASH JOIN OUTER Cost=1159 Cardinality=1 Bytes=572
NESTED LOOPS Cost=1157 Cardinality=1 Bytes=538
NESTED LOOPS Cost=1156 Cardinality=1 Bytes=472
NESTED LOOPS Cost=1156 Cardinality=1 Bytes=431
NESTED LOOPS Cost=1156 Cardinality=1 Bytes=386
NESTED LOOPS Cost=1150 Cardinality=1 Bytes=288
HASH JOIN Cost=1147 Cardinality=8 Bytes=1640
PARTITION RANGE ALL Cost=27 Cardinality=432 Bytes=17280
TABLE ACCESS FULL Object owner=GEM Object name=RATING Cost=27 Cardinality=432 Bytes=17280
HASH JOIN Cost=1119 Cardinality=246 Bytes=40590
NESTED LOOPS Cost=23 Cardinality=345 Bytes=27945
TABLE ACCESS FULL Object owner=GEM Object name=AIG_CRC_PARTITION_KEY Cost=2 Cardinality=1 Bytes=33
PARTITION RANGE ITERATOR Cost=21 Cardinality=345 Bytes=16560
TABLE ACCESS FULL Object owner=GEM Object name=AIG_ENTITY_INDUSTRY_LINKS Cost=21 Cardinality=345 Bytes=16560
PARTITION RANGE ALL Cost=1046 Cardinality=721315 Bytes=60590460
TABLE ACCESS FULL Object owner=GEM Object name=AIG_ENTITY Cost=1046 Cardinality=721315 Bytes=60590460
PARTITION RANGE ITERATOR Cost=1 Cardinality=1 Bytes=83
TABLE ACCESS BY LOCAL INDEX ROWID Object owner=GEM Object name=ENTITY Cost=1 Cardinality=1 Bytes=83
INDEX UNIQUE SCAN Object owner=GEM Object name=PK_ENTITY Cost=1 Cardinality=1
TABLE ACCESS FULL Object owner=GEM Object name=AIG_CRC_ALERT_LIST_ITEMS Cost=6 Cardinality=1 Bytes=98
PARTITION RANGE ITERATOR Cost=0 Cardinality=1 Bytes=45
TABLE ACCESS FULL Object owner=GEM Object name=COUNTRY Cost=0 Cardinality=1 Bytes=45
PARTITION RANGE ITERATOR Cost=0 Cardinality=1 Bytes=41
TABLE ACCESS FULL Object owner=GEM Object name=GEOGRAPHIC_AREA Cost=0 Cardinality=1 Bytes=41
PARTITION RANGE ITERATOR Cost=0 Cardinality=1 Bytes=66
TABLE ACCESS FULL Object owner=GEM Object name=INDUSTRY_SECTOR Cost=0 Cardinality=1 Bytes=66
TABLE ACCESS FULL Object owner=GEM Object name=AIG_CRC_ALERT_LIST_EXCL Cost=2 Cardinality=1 Bytes=34
Previous Topic: Error codes
Next Topic: SQL using my 32-bit driver does not work on server with 64-bit driver (merged 2)
Goto Forum:
  


Current Time: Mon Dec 05 19:22:28 CST 2016

Total time taken to generate the page: 0.09803 seconds