Re: Really slow SQL query - Pleas help a newbie before i get cremated on the bonfire tomorrow!

pl/sql is always slower than straight sql. otn.oracle.com do a search in the docs on 'explain plan' you need to learn this tool.

MARTINJAMESYOUNG_at_HOTMAIL.COM (Martin Young) wrote in message news:<cf938d26.0311040537.6fd3dd54_at_posting.google.com>...
> Hi i am new to SQL and PL SQL.
>
> The database we use for our occupational health department is Oracle
> 8.1.7.5.
>
> There is a View called CASS.OPASRESTRICTIONS_VIEW
>
> the table data is held as follows
>
> Name Null? Type
> ----------------------------------------- --------
> -----------------------
> PIN NOT NULL VARCHAR2(10)
> SID VARCHAR2(15)
> NI VARCHAR2(10)
> SURNAME NOT NULL VARCHAR2(40)
> FORENAMES NOT NULL VARCHAR2(40)
> DOB NOT NULL DATE
> GENDER NOT NULL NUMBER(38)
> TITLE NUMBER(38)
> ADDRESS1 VARCHAR2(50)
> ADDRESS2 VARCHAR2(50)
> ADDRESS3 VARCHAR2(50)
> ADDRESS4 VARCHAR2(50)
> POSTCODE VARCHAR2(15)
> HOMETELEPHONE VARCHAR2(20)
> WORKTELENO VARCHAR2(20)
> WORKEMAIL VARCHAR2(50)
> NATIONALITY NUMBER(38)
> ETHNICORIGIN NUMBER(38)
> OCCUPATION NUMBER(38)
> EMPLOYMENTSTATUS NUMBER(38)
> AREAOFWORKCODE VARCHAR2(10)
> TYPEOFWORK NUMBER(38)
> BUSINESSUNITCODE VARCHAR2(10)
> SHIFT NUMBER(38)
> SUPERVISOR VARCHAR2(30)
> SUPERVISORPIN VARCHAR2(10)
> SUPERVISOREMAIL VARCHAR2(50)
> DEPARTMENTCODE VARCHAR2(10)
> NOKNAME VARCHAR2(50)
> NOKADDRESS1 VARCHAR2(50)
> NOKADDRESS2 VARCHAR2(50)
> NOKADDRESS3 VARCHAR2(50)
> NOKADDRESS4 VARCHAR2(50)
> NOKPOSTCODE VARCHAR2(15)
> NOKRELATIONSHIP NUMBER(38)
> NOKTELEPHONENUMBER VARCHAR2(20)
> NOTES VARCHAR2(2000)
> COSTCENTRE VARCHAR2(10)
> FULLNAME VARCHAR2(82)
> L_P__PIN NOT NULL VARCHAR2(10)
> L_P__NI_NUMBER VARCHAR2(10)
> L_P__SECONDARY_IN VARCHAR2(15)
> L_P__SURNAME NOT NULL VARCHAR2(40)
> P__PREVIOUS_SURNAME VARCHAR2(40)
> L_P__FORENAMES NOT NULL VARCHAR2(40)
> D_L_P__DATE_OF_BIRTH NOT NULL DATE
> P__GENDER VARCHAR2(255)
> P__TITLE VARCHAR2(255)
> P__ADDRESS_1 VARCHAR2(50)
> P__ADDRESS_2 VARCHAR2(50)
> P__TOWN VARCHAR2(50)
> P__COUNTY VARCHAR2(50)
> P__BUSINESS_UNIT VARCHAR2(255)
> P__DEPARTMENT VARCHAR2(255)
> P__NATIONALITY VARCHAR2(255)
> P__ETHNIC_ORIGIN VARCHAR2(255)
> P__AREA_OF_WORK VARCHAR2(255)
> P__ZONE VARCHAR2(255)
> P__OCCUPATION VARCHAR2(255)
> P__SHIFT VARCHAR2(255)
> P__TYPE_OF_WORK VARCHAR2(255)
> P__EMPLOYMENT_STATUS VARCHAR2(255)
> P__NOK_RELATIONSHIP VARCHAR2(255)
> DATEOFRECALL DATE
> TIMEOFRECALL DATE
> STARTDATE DATE
> FINISHDATE DATE
> EPISODETYPE NUMBER(38)
> BUSINESSUNIT NUMBER(38)
> DEPARTMENT NUMBER(38)
> AREAOFWORK NUMBER(38)
> ZONE NUMBER(38)
> S__BUSINESS_UNIT VARCHAR2(255)
> S__DEPARTMENT VARCHAR2(255)
> S__AREA_OF_WORK VARCHAR2(255)
> S__ZONE VARCHAR2(255)
> D_S__DATE_OF_RECALL DATE
> T_S__TIME_OF_RECALL DATE
> S__EPISODE_TYPE VARCHAR2(255)
> D_S__EPISODE_START_DATE DATE
> D_S__EPISODE_FINISH_DATE DATE
> EPISODEUNIQUEKEY VARCHAR2(30)
> APPSUNIQUEKEY VARCHAR2(30)
> D_S__APPOINTMENT_DATE DATE
> T_S__APPOINTMENT_TIME DATE
> TIMEIN DATE
> TIMEOUT DATE
> EVENTTYPE VARCHAR2(50)
> DUEDATE DATE
> ATTENDANCEDATE DATE
> SEENBY NUMBER(38)
> OUTCOME NUMBER(38)
> OHLOCATION NUMBER(38)
> EXTERNAL_REFERRAL NUMBER(38)
> EXT_REFERRAL_DATE DATE
> COMMENTS VARCHAR2(2000)
> USERA NUMBER(38)
> USERC NUMBER(38)
> DATEA VARCHAR2(19)
> DATEC VARCHAR2(19)
> COST NUMBER(8,2)
> D_S__ATTENDANCE_DATE DATE
> S__EVENT_TYPE VARCHAR2(50)
> D_S__DUE_DATE DATE
> T_S__TIME_IN DATE
> T_S__TIME_OUT DATE
> S__SEEN_BY VARCHAR2(255)
> S__OUTCOME VARCHAR2(255)
> S__OH_LOCATION VARCHAR2(255)
> S__EXTERNAL_REFERRAL VARCHAR2(255)
> D_S__EXTERNAL_REFERRAL_DATE DATE
> S__COMMENTS VARCHAR2(2000)
> RESTRICTIONUNIQUEKEY VARCHAR2(30)
>
> The s__event_type field holds the restriction description e.g.
> Restriction applied: Special Needs
> Restriction lifted: Lifting and handling
>
> Pin is Payroll Number 534473 (always six digits) and not null
>
> I have created a concatonated field using PIN and S__Event type to
> include in a self join this is because there is no other fields that i
> can join by to produce the correct result
>
> I am running the following code on from my Networked PC.
>
> --------------------------------------------------------------------------------
> SELECT
> DISTINCT
> a.FULLNAME,
> A.PIN||trim(' 'FROM SUBSTR( a.S__EVENT_TYPE, 21)),
> a.PIN,
> trim(' ' FROM SUBSTR( a.S__EVENT_TYPE,21)) AS Restriction_Type,
> trim(' ' FROM SUBSTR(a.EVENTTYPE,13,1)) AS State,
> a.COSTCENTRE,
> TO_CHAR(a.D_S__ATTENDANCE_DATE,'DD/MM/YY'),
> TO_CHAR(a.D_S__DUE_DATE,'DD/MM/YY')
>
> FROM CASS.OPASRESTRICTIONS_VIEW A
> WHERE
> NOT EXISTS (SELECT DISTINCT B.PIN||trim(' ' FROM SUBSTR(
> B.S__EVENT_TYPE, 21)) FROM CASS.OPASRESTRICTIONS_VIEW B WHERE B.pin
> <>'1' AND
> B.EVENTTYPE LIKE 'Rest%' AND
> A.PIN||trim(' ' FROM SUBSTR( a.S__EVENT_TYPE, 21)) = B.PIN||trim('
> ' FROM SUBSTR( B.S__EVENT_TYPE, 21)) AND
> trim(' ' FROM SUBSTR(B.EVENTTYPE,13,1)) LIKE 'L') AND
> pin <>'1' AND
> a.EVENTTYPE LIKE 'Rest%';

distinct is costly. Do NOT use it inside a sub-query, just do

select 1
from stuff.... exists ONLY returns true or false. not much else i can say until i see your execution plan. see my message on learning the expkain plan. all you have to do is go to $ORACLE_HOME/rdbms/admin and execute utlexplan.sql(spelling might be off).

the set autotrace on
then execute query

post the results. Please try to format it. I cant read it if its messy.

>
> --------------------------------------------------------------------------------
>
> The code above is used to select all the applied restrictions from the
> View and look up using a not exists statement in the where clause so
> that it only displays restrictions that are applied but not lifted
>
>
> Is there an easier way of doing this?
>
> I have looked up recordsets and the possibility of using an
> IF-THEN-ELSE but i don't really understand how to use them. I assume i
> need two one for applied and one for lifted restrictions.
>
> Any help at all is appreciated as i'm on my own on this one!!!
>
> Many thanks
> Martin
Received on Wed Nov 05 2003 - 11:15:04 CST