Home » SQL & PL/SQL » SQL & PL/SQL » view to function (oracle 10g)
view to function [message #429115] Mon, 02 November 2009 03:49 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
How to convert a view with select statements having more than 10-13 union all statements to a stored function for optimisation
Re: view to function [message #429116 is a reply to message #429115] Mon, 02 November 2009 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you think it will be an optimsation?
Using cursors you will hide the logic to the optimizer and it will be able to optimize.

Regards
Michel

[Updated on: Mon, 02 November 2009 03:55]

Report message to a moderator

Re: view to function [message #429117 is a reply to message #429115] Mon, 02 November 2009 03:58 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can't optimize a view by converting it to a function by some general means.

It might be possible in some very specific circumstances, but that would depend on the data the view is pulling.

For example, I had a view where a somewhat complicated join with calculations on a date-based VAT table took place, there it made sense to convert the view into an Pipelined Table Function. But that was a very special case. In 99.99% of cases it wouldn't make any sense.

Re: view to function [message #429118 is a reply to message #429116] Mon, 02 November 2009 03:59 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi my view is like this and now wants to change it to function,any ideas on this.
CREATE OR REPLACE FORCE VIEW EMRVWTODAYSTASKLISTCOUNT (EMR_TRANSACTION_ID, TASK_ID, TASK_NAME, APPOINTMENT_DATE, PATIENT_FIRSTNAME, PATIENT_LASTNAME, CONSULTANT_ID, ROLE_NAME, LOCATION_ID, ID_VALUE) AS 
 SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME,     
	 '1/1/1900' AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL 
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID 
INNER JOIN EMRPATIENTSMASTER EPM ON ET.PATIENT_ID = EPM.PATIENT_ID 
WHERE   ET.TASK_ID IN (4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 18, 19, 20, 21, 22, 23, 24, 28, 
29, 30, 31, 32, 33, 58, 59, 60, 63, 64, 65, 66, 67, 96, 110, 113, 115, 116, 117, 118, 119, 120, 121, 123, 125, 150)
UNION ALL
SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME,     
	 TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL 
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID 
INNER JOIN EMRPATIENTSMASTER EPM ON ET.PATIENT_ID = EPM.PATIENT_ID
INNER JOIN EMRAppDetailsSuppHistory EA ON ET.ID_VALUE = EA.APPOINTMENT_ID
WHERE   ET.TASK_ID IN (9)
UNION ALL
SELECT     ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, TO_CHAR(EED.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE,                       
                      EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EED.CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID,  ET.ID_VALUE
FROM        EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM, EMRTranscriptionMapping ETM, EMREncounterDetails EED, 
                      EMRTranscriptionData ETD
WHERE     ETL.TASK_ID = ET.TASK_ID AND ET.ID_VALUE = ETM.TRANSCRIPTION_ID AND ETM.ENCOUNTER_ID = EED.ENCOUNTER_ID AND 
          ETD.IS_VERIFIED = 0 AND ETD.TRANSCRIPTION_ID = ETM.TRANSCRIPTION_ID AND EED.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID = 15
UNION ALL
SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME, 
        Case When Nvl(EED.APPOINTMENT_ID,'0')='0' then 
                      '1/1/1900'  
                      ELSE TO_CHAR(EED.APPOINTMENT_DATE,'DD/MM/YYYY')  
                      END AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL 
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID 
INNER JOIN EMRPATIENTSMASTER EPM ON ET.PATIENT_ID = EPM.PATIENT_ID
INNER JOIN EMRENCOUNTERDETAILS EED ON ET.ID_VALUE = EED.ENCOUNTER_ID
WHERE   ET.TASK_ID IN (16,17,25,26,27,153)
UNION ALL
SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME, 
        TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY')  AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL 
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID 
INNER JOIN EMRPATIENTSMASTER EPM ON ET.PATIENT_ID = EPM.PATIENT_ID
INNER JOIN EMRAppointmentDetails EA ON ET.ID_VALUE = EA.APPOINTMENT_ID  
WHERE   ET.TASK_ID IN (28)
UNION ALL
SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME, 
        TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY')  AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL 
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID 
INNER JOIN EMRPATIENTSMASTER EPM ON ET.PATIENT_ID = EPM.PATIENT_ID
INNER JOIN EMRAppDetailsSuppHistory EA ON ET.ID_VALUE = EA.APPOINTMENT_ID  
WHERE   ET.TASK_ID IN (112)
UNION ALL
SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME,     
	 '1/1/1900' AS APPOINTMENT_DATE, '' AS PATIENT_FIRSTNAME, '' AS PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, NULL AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID
INNER JOIN USERS US ON ET.USER_LOGIN = US.USER_LOGIN
WHERE  ET.TASK_ID IN (34, 61, 62, 73, 109)
UNION ALL
SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME,     
	 '1/1/1900' AS APPOINTMENT_DATE, '' AS PATIENT_FIRSTNAME, '' AS PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, NULL AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID
WHERE  ET.TASK_ID IN (124, 126, 147, 154) AND ET.ROLE_NAME IS NOT NULL
UNION ALL
SELECT  ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME,
       '1/1/1900' AS APPOINTMENT_DATE, '' AS PATIENT_FIRSTNAME, '' AS PATIENT_LASTNAME,  
       CON.CONSULTANT_ID AS CONSULTANT_ID, ET.ROLE_NAME ,  AL.LOCATION_ID AS LOCATION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE  
FROM  EMRTransactions ET inner join EMRTaskListLkup ETL on  ETL.TASK_ID = ET.TASK_ID
       INNER JOIN EMRDISEASEALERTS AL ON AL.DISEASE_ALERT_ID=ET.ID_VALUE
       LEFT JOIN EMRCONSULTANTDETAILS CON ON ET.USER_LOGIN = CON.CONSULTANT_ID  
       WHERE   ET.TASK_ID IN (122)
UNION ALL
SELECT  ET.EMR_TRANSACTION_ID, ET.TASK_ID, TASK_NAME,   
	TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,   
        ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM EMRTransactions ET
        INNER JOIN EMRTASKLISTLKUP ETL ON ET.TASK_ID = ETL.TASK_ID
	INNER JOIN EMRAppointmentDetails EA ON ET.ID_VALUE = EA.APPOINTMENT_ID 
	INNER JOIN EMRPatientsMaster EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
        INNER JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_STATUS_ID 
WHERE   ET.TASK_ID IN (1, 2, 3)
UNION ALL
SELECT     ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, 
	EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE
FROM         EMRACTransactions ET 
	INNER JOIN EMRTaskListLkup ETL ON ETL.TASK_ID = ET.TASK_ID
	INNER JOIN EMRPatientsMaster EPM ON ET.PATIENT_ID = EPM.PATIENT_ID
        INNER JOIN EMRENCOUNTERDETAILS EED ON EED.ENCOUNTER_ID = ET.ID_VALUE
        INNER JOIN EMRCONSULTANTDETAILS ECD ON EED.CONSULTANT_ID = ECD.CONSULTANT_ID
	LEFT OUTER JOIN EMRAppointmentDetailsHistory EA ON EED.APPOINTMENT_ID = EA.APPOINTMENT_ID AND EA.APPOINTMENT_STATUS_ID = 2
WHERE ET.TASK_ID IN (152)
/ 


Re: view to function [message #429120 is a reply to message #429118] Mon, 02 November 2009 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is not clear in "trying to write this with a function will lead to worst performances"?

Regards
Michel
Re: view to function [message #429122 is a reply to message #429120] Mon, 02 November 2009 04:15 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yep. There is nothing in there where rewriting it as a function might be of any help.
Re: view to function [message #429126 is a reply to message #429122] Mon, 02 November 2009 04:25 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Thomas,
help me in approaching with function just show me some tips in doing so.will be helpful for me in showing steps in creating a function
Re: view to function [message #429127 is a reply to message #429126] Mon, 02 November 2009 04:30 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
A function won't do any good. It will only slow it down even more. And "some tips" are already in the link I posted.

[Updated on: Mon, 02 November 2009 04:31]

Report message to a moderator

Re: view to function [message #429129 is a reply to message #429127] Mon, 02 November 2009 04:31 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Then any other alternative approach.please suggest me
Re: view to function [message #429130 is a reply to message #429129] Mon, 02 November 2009 04:32 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Read the Sticky Post in the performance tuning forum for standard performance tuning steps.
Re: view to function [message #429131 is a reply to message #429129] Mon, 02 November 2009 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Approch to what? To optimize?
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
- Tuning High-Volume SQL Wiki page

Then provide the required and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: view to function [message #429144 is a reply to message #429115] Mon, 02 November 2009 05:44 Go to previous messageGo to next message
idris.ali
Messages: 34
Registered: June 2008
Location: Hyderabad
Member
Hi,

Some of the Select statements in your View have same join conditions just a different logic for APPOINTMENT_DATE. You may have only one query for all such selects and have a function say CALC_APPOINTMENT_DATE to calculate appointment date based on your logic.

Ex: For the 2nd to 5th sqls in your view can be replaced with one query

Thanks,
Idris

[Updated on: Mon, 02 November 2009 05:47]

Report message to a moderator

Re: view to function [message #429151 is a reply to message #429144] Mon, 02 November 2009 06:02 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
HI ali,
can you please give me the function in doing so please
Re: view to function [message #429172 is a reply to message #429115] Mon, 02 November 2009 07:23 Go to previous message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
Will you stop going on about functions - no function is going to help you here.

This problem is almost identical to one of your earlier threads:
view to be split into multiple views

It's a different view but the same issue.
And so the same advise.
Merge all the unioned select statements that you can.
idris.ali has already pointed some out.

Get rid of all the unnecessary duplicate statements.
Then if after doing that you still have performance issues, have a read of the links Michel posted and then post all the required information so we can take a look.

EDIT: typo

[Updated on: Mon, 02 November 2009 07:24]

Report message to a moderator

Previous Topic: To increment column value before insert
Next Topic: error_message : ORA-06508: PL/SQL: could not find program unit being called
Goto Forum:
  


Current Time: Wed Dec 07 18:24:45 CST 2016

Total time taken to generate the page: 0.07725 seconds