Problem with view's performance cost
558027Jan 24 2007 — edited Jan 25 2007
Hi, I have a disconcerting problem with view's performance cost
Cost of select on the view (7792985252581660) is great that the cost of the "select" with which the view was defined (48094)
shoudn't be the same performance cost between them??
I defined
CREATE OR REPLACE VIEW family_liquidation AS
SELECT PCFFAMILYGROUP.PCFFAMILYGROUPID
,PCFFAMILYGROUP.PCFFAMILYGROUPCOMPOUNDID
,PCFFAMILYGROUP.PCFDISTRIBUTIONCENTERID
,PCFDISTRIBUTIONCENTER.MUNICIPALITYID
,PCFDISTRIBUTIONCENTER.description distributionCenter
,MUNICIPALITY.DEPARTMENTID
,MUNICIPALITY.DESCRIPTION municipalityDescription
,MUNICIPALITY.ZONECODE ZONECODE
,beneficiary.LASTNAME
,beneficiary.FIRSTNAME
,beneficiary.PERSONALIDENTIFICATIONNUMBER
,SUM(cba) cba
,SUM(cba)* getENGELCOEFFICIENT cbt
,SUM (active_beneficiary_VIEW.active) members
,SUM(disability) disability
,SUM(pregnant) pregnant
,SUM(income) income
,SUM(less14) less14
,SUM(more65) more65
,COUNT(DISTINCT(active_beneficiary_VIEW.MUNICIPALITYID)) municipality_amount
,city.MUNICIPALITYID AS titular_municipality
,titular.beneficiaryId titularId
FROM PCFFAMILYGROUP
JOIN ALLOCATIONUNIT ON (PCFFAMILYGROUP.PCFFAMILYGROUPID = ALLOCATIONUNIT.ALLOCATIONUNITID AND (ALLOCATIONUNIT.STATEID=1 OR ALLOCATIONUNIT.STATEID=4))
JOIN SPBENEFICIARY ON (SPBENEFICIARY.ALLOCATIONUNITID = PCFFAMILYGROUP.PCFFAMILYGROUPID )
LEFT JOIN active_beneficiary_VIEW ON (SPBENEFICIARY.BENEFICIARYID = active_beneficiary_VIEW.beneficiaryid)
JOIN PCFDISTRIBUTIONCENTER ON(PCFDISTRIBUTIONCENTER.PCFDISTRIBUTIONCENTERID=PCFFAMILYGROUP.PCFDISTRIBUTIONCENTERID)
JOIN MUNICIPALITY ON (PCFDISTRIBUTIONCENTER.MUNICIPALITYID = MUNICIPALITY.MUNICIPALITYID)
JOIN PCFBENEFICIARY ON (PCFFAMILYGROUP.PCFFAMILYGROUPID = PCFBENEFICIARY.PCFFAMILYGROUPID AND PCFBENEFICIARY.PCFTITULARRELATIONID=1)
JOIN SPBENEFICIARY sp ON (SP.SOCIALPROGRAMPBENEFICIARYID = PCFBENEFICIARY.PCFBENEFICIARYID )
LEFT JOIN beneficiary titular ON ((titular.STATEID =1 OR titular.STATEID = 4)and titular.BENEFICIARYID = sp.beneficiaryID)
JOIN beneficiary ON (beneficiary.BENEFICIARYID = sp.beneficiaryID)
LEFT JOIN House ON (beneficiary.HOUSEDATA = HOUSE.HOUSEID)
LEFT JOIN location ON (house.ADDRESS = location.LOCATIONID)
LEFT JOIN CITY ON (location.CITYID = city.CITYID)
GROUP BY PCFFAMILYGROUP.PCFFAMILYGROUPID
,PCFFAMILYGROUP.PCFFAMILYGROUPCOMPOUNDID
,PCFFAMILYGROUP.PCFDISTRIBUTIONCENTERID
,PCFDISTRIBUTIONCENTER.MUNICIPALITYID
,PCFDISTRIBUTIONCENTER.description
,MUNICIPALITY.DEPARTMENTID
,MUNICIPALITY.DESCRIPTION
,MUNICIPALITY.ZONECODE
,beneficiary.LASTNAME
,beneficiary.FIRSTNAME
,beneficiary.PERSONALIDENTIFICATIONNUMBER
,city.MUNICIPALITYID
,titular.beneficiaryId;
cost of "SELECT * FROM family_liquidation" is 7792985252581660 and cost of
SELECT PCFFAMILYGROUP.PCFFAMILYGROUPID
,PCFFAMILYGROUP.PCFFAMILYGROUPCOMPOUNDID
,PCFFAMILYGROUP.PCFDISTRIBUTIONCENTERID
,PCFDISTRIBUTIONCENTER.MUNICIPALITYID
,PCFDISTRIBUTIONCENTER.description distributionCenter
,MUNICIPALITY.DEPARTMENTID
,MUNICIPALITY.DESCRIPTION municipalityDescription
,MUNICIPALITY.ZONECODE ZONECODE
,beneficiary.LASTNAME
,beneficiary.FIRSTNAME
,beneficiary.PERSONALIDENTIFICATIONNUMBER
,SUM(cba) cba
,SUM(cba)* getENGELCOEFFICIENT cbt
,SUM (active_beneficiary_VIEW.active) members
,SUM(disability) disability
,SUM(pregnant) pregnant
,SUM(income) income
,SUM(less14) less14
,SUM(more65) more65
,COUNT(DISTINCT(active_beneficiary_VIEW.MUNICIPALITYID)) municipality_amount
,city.MUNICIPALITYID AS titular_municipality
,titular.beneficiaryId titularId
FROM PCFFAMILYGROUP
JOIN ALLOCATIONUNIT ON (PCFFAMILYGROUP.PCFFAMILYGROUPID = ALLOCATIONUNIT.ALLOCATIONUNITID AND (ALLOCATIONUNIT.STATEID=1 OR ALLOCATIONUNIT.STATEID=4))
JOIN SPBENEFICIARY ON (SPBENEFICIARY.ALLOCATIONUNITID = PCFFAMILYGROUP.PCFFAMILYGROUPID )
LEFT JOIN active_beneficiary_VIEW ON (SPBENEFICIARY.BENEFICIARYID = active_beneficiary_VIEW.beneficiaryid)
JOIN PCFDISTRIBUTIONCENTER ON(PCFDISTRIBUTIONCENTER.PCFDISTRIBUTIONCENTERID=PCFFAMILYGROUP.PCFDISTRIBUTIONCENTERID)
JOIN MUNICIPALITY ON (PCFDISTRIBUTIONCENTER.MUNICIPALITYID = MUNICIPALITY.MUNICIPALITYID)
JOIN PCFBENEFICIARY ON (PCFFAMILYGROUP.PCFFAMILYGROUPID = PCFBENEFICIARY.PCFFAMILYGROUPID AND PCFBENEFICIARY.PCFTITULARRELATIONID=1)
JOIN SPBENEFICIARY sp ON (SP.SOCIALPROGRAMPBENEFICIARYID = PCFBENEFICIARY.PCFBENEFICIARYID )
LEFT JOIN beneficiary titular ON ((titular.STATEID =1 OR titular.STATEID = 4)and titular.BENEFICIARYID = sp.beneficiaryID)
JOIN beneficiary ON (beneficiary.BENEFICIARYID = sp.beneficiaryID)
LEFT JOIN House ON (beneficiary.HOUSEDATA = HOUSE.HOUSEID)
LEFT JOIN location ON (house.ADDRESS = location.LOCATIONID)
LEFT JOIN CITY ON (location.CITYID = city.CITYID)
GROUP BY PCFFAMILYGROUP.PCFFAMILYGROUPID
,PCFFAMILYGROUP.PCFFAMILYGROUPCOMPOUNDID
,PCFFAMILYGROUP.PCFDISTRIBUTIONCENTERID
,PCFDISTRIBUTIONCENTER.MUNICIPALITYID
,PCFDISTRIBUTIONCENTER.description
,MUNICIPALITY.DEPARTMENTID
,MUNICIPALITY.DESCRIPTION
,MUNICIPALITY.ZONECODE
,beneficiary.LASTNAME
,beneficiary.FIRSTNAME
,beneficiary.PERSONALIDENTIFICATIONNUMBER
,city.MUNICIPALITYID
,titular.beneficiaryId;
is 48094
Thanks very much in advance. Martin