Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 21 2007
Added on Jan 24 2007
2 comments
253 views