Here I have a query based on 2 views.
First View - PREFERRED_MAILING_ADDRESS, is combination of PARTY Details, Relationships, Person Contact, Person Sites and Locations.
Second View - MSP_PARTY_RELATIONSHIP_V, Combination of Party Details and Relationships.
FROM APPS.MSP_PARTY_RELATIONSHIP_V MSP_PARTY_RELATIONSHIP_V, APPS.XXX_PREFERRED_MAILING_ADDRESS XXX_PREFERRED_MAILING_ADDRESS
WHERE ( ( MSP_PARTY_RELATIONSHIP_V.SUBJECT_ID = XXX_PREFERRED_MAILING_ADDRESS.PER_PARTY_ID ) AND
MSP_PARTY_RELATIONSHIP_V.STATUS = XXX_PREFERRED_MAILING_ADDRESS.REL_STATUS ) AND
UPPER(MSP_PARTY_RELATIONSHIP_V.STATUS) = UPPER('A') AND
--UPPER(XXX_PREFERRED_MAILING_ADDRESS.REL_STATUS) = UPPER('A') AND
( UPPER(MSP_PARTY_RELATIONSHIP_V.RELATIONSHIP) = UPPER('Society Member Of') AND
UPPER(MSP_PARTY_RELATIONSHIP_V.OBJECT_PARTY_NAME) LIKE UPPER('Tax%') AND 1 = 1 )
It is taking lot of Time and never get back the result but when I re-write the query (Condition) like below,
--UPPER(MSP_PARTY_RELATIONSHIP_V.STATUS) = UPPER('A') AND
UPPER(XXX_PREFERRED_MAILING_ADDRESS.REL_STATUS) = UPPER('A') AND
It came up within Seconds.I just added PREFERRED_MAILING_ADDRESS view in the condition list.The situation here is I need the query in the Previous style..Please let me know if you need any more information..Thanks in Advance..
previous
Here is the Code for Newly created View:
CREATE OR REPLACE VIEW XXX_PREFERRED_MAILING_ADDRESS as (
SELECT MSP_PERSON_LOCATIONS_V.ADDRESS1,
MSP_PARTY_RELATIONSHIP_V.ATTRIBUTE15,
MSP_PERSON_LOCATIONS_V.CITY,
MSP_PERSON_LOCATIONS_V.COUNTY,
MSP_PERSON_SITES_V.IDENTIFYING_ADDRESS_FLAG,
MSP_PERSON_DETAILS_V.ORIG_SYSTEM_REFERENCE,
MSP_PERSON_DETAILS_V.table_name,
--FOLLOWING TWO LINES ADDED BY KRISHNA ON 02-APR-04 TO GET THE MIDDLE NAME,MAILSTOP
MSP_PERSON_DETAILS_V.PERSON_MIDDLE_NAME,
MSP_PERSON_DETAILS_V.MAILSTOP,
MSP_PERSON_DETAILS_V.PARTY_ATTRIBUTE10,
-- ADDED BY KRISHNA ON 19-MAY-04 TO GET THE MEMEBER JOIN DATE IN preferred mailing address folder
MSP_PERSON_DETAILS_V.party_ATTRIBUTE3,
MSP_PERSON_DETAILS_V.PERSON_FIRST_NAME ,
MSP_PERSON_DETAILS_V.PERSON_LAST_NAME,
MSP_PERSON_DETAILS_V.PARTY_ID PER_PARTY_ID,
MSP_PERSON_DETAILS_V.PARTY_ATTRIBUTE5 PERSON_STATUS,
MSP_PERSON_DETAILS_V.PARTY_ATTRIBUTE7 PERSON_TYPE,
MSP_PERSON_DETAILS_V.PARTY_ATTRIBUTE8 PENDING_ORG_INFO,
MSP_PERSON_DETAILS_V.PARTY_ATTRIBUTE14 PRINT_FLAG,
MSP_PERSON_DETAILS_V.STATUS,
MSP_PERSON_DETAILS_V.PERSON_PRE_NAME_ADJUNCT TITLE,
MSP_PERSON_DETAILS_V.PARTY_ATTRIBUTE11 WEBUSER_ID,
MSP_PERSON_DETAILS_V.email_address,
--FOLLOWING TWO LINES ADDED BY KRISHNA ON 02-APR-04 TO GET THE MIDDLE NAME,MAILSTOP
MSP_PERSON_DETAILS_V.PERSON_MIDDLE_NAME,
MSP_PERSON_DETAILS_V.MAILSTOP,
--FOLLOWING LINES ADDED BY Krishna on 07-Apr-04 to fix the section memebrs count commented following line
AND ( decode(MSP_PERSON_DETAILS_V.PARTY_ATTRIBUTE10
,'Business Relationship',MSP_PARTY_RELATIONSHIP_V.ATTRIBUTE15,1))=( decode(MSP_PERSON_DETAILS_V.PARTY_ATTRIBUTE10
,'Business Relationship','Y',1))
--AND ( MSP_PARTY_RELATIONSHIP_V.ATTRIBUTE15 = 'Y' )
AND ( MSP_PARTY_RELATIONSHIP_V.STATUS = 'A' )
AND ( MSP_PARTY_RELATIONSHIP_V.RELATIONSHIP = 'Employee Of' ) AND
( MSP_PERSON_SITES_V.IDENTIFYING_ADDRESS_FLAG = 'Y' )
)
If each individual view runswell, then you could try ...
Code:
SELECT
MSP_PARTY_RELATIONSHIP_V.END_DATE,
MSP_PARTY_RELATIONSHIP_V.OBJECT_PARTY_NAME,
...
FROM (SELECT * FROM APPS.MSP_PARTY_RELATIONSHIP_V WHERE ROWNUM >= 1) MSP_PARTY_RELATIONSHIP_V,
(SELECT * FROM APPS.XXX_PREFERRED_MAILING_ADDRESS WHERE ROWNUM >=1 )XXX_PREFERRED_MAILING_ADDRESS
WHERE ...
Thanks for your Help but with your idea, it doesn't return any data back. ACtually I want to know why its happenning and what is the solution.
Here is the Query Again:
SELECT MSP_PARTY_RELATIONSHIP_V.END_DATE,
........
FROM APPS.MSP_PARTY_RELATIONSHIP_V MSP_PARTY_RELATIONSHIP_V, APPS.XXX_PREFERRED_MAILING_ADDRESS XXX_PREFERRED_MAILING_ADDRESS
WHERE .....
UPPER(MSP_PARTY_RELATIONSHIP_V.STATUS) = UPPER('A') AND
--UPPER(XXX_PREFERRED_MAILING_ADDRESS.REL_STATUS) = UPPER('A')
.....
...
Above one not getting back but below one getting back data in SECs.
SELECT MSP_PARTY_RELATIONSHIP_V.END_DATE,
........
FROM APPS.MSP_PARTY_RELATIONSHIP_V MSP_PARTY_RELATIONSHIP_V, APPS.XXX_PREFERRED_MAILING_ADDRESS XXX_PREFERRED_MAILING_ADDRESS
WHERE .....
--UPPER(MSP_PARTY_RELATIONSHIP_V.STATUS) = UPPER('A') AND
UPPER(XXX_PREFERRED_MAILING_ADDRESS.REL_STATUS) = UPPER('A')
.....
I am really surprising, Why the query returning data back..Its hanging there only..Please see the attachments for Plan Mehtods for Working and Non-Working Queries..I got this Plan Method from PL/SQL Developer..
Please let me know if you need any more information.
I have seen the plan.. and as i said the plan will obiviously be different.. Its seems me like the qry which is taking time is using all wrong indexes..
You may like to look the codes of views and the availiable indexes and may try out hints ..
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Thanks for your Time. Actually I have the master views of Person_Details_V, Relationship_V, and Person_sites_V. based on this I need to create XXX_PREFERRED_MAILING_ADDRESS after that I need to join the view (XXX) with other views like Person_Details_V, Relationship_V and Person_Sites_V....What do you suggest Giving HINTS to view XXX or Individual Views ?
Please suggest me what kind of material, I may need to go through for achieve this TASK.
Help Need - SQL Tuning in Joining Views
Reply With Quote
Plan Changed
