Home » SQL & PL/SQL » SQL & PL/SQL » SQL Performance
| SQL Performance [message #18846] |
Mon, 18 February 2002 18:33  |
Suparna Saha
Messages: 10
Registered: October 2001
|
Junior Member |
|
|
Hi,
I have written a SQL related with different tables. In this SQL where clause is also complicated. Here I use a VIEW also. Instead of view if I use select statement it will be fast or not. I want to improve performance of this SQL. Following SQL is used
SELECT DISTINCT substr("T_WORK_ORDR_MAST"."APSC_CODE",1,2)||substr("T_WORK_ORDR_MAST"."APSC_CODE",4,3)||"T_WORK_ORDR_MAST"."WKODR_NO" claimid,
"T_WORK_ORDR_MAST"."APSC_CODE" asc_code,
to_char("T_WORK_ORDR_MAST"."WKODR_DATE",'dd/mm/yyyy') claimdate,
substr(("T_CUSTOMER_REGIS"."TITLE_NAME"||' '||"T_CUSTOMER_REGIS"."FIRST_NAME"||' '|| "T_CUSTOMER_REGIS"."LAST_NAME"),1,30) customersurname,
'India' COUNTRY,
substr("T_STATE_MAST"."STATE_NAME",1,30) province,
substr("T_CITY_MAST"."CITY_NAME",1,20) city,
"T_CUSTOMER_REGIS"."PINCODE" post,
substr(("T_CUSTOMER_REGIS"."ADDRESS1" || ' ' || "T_CUSTOMER_REGIS"."ADDRESS2" || ' ' || "T_CUSTOMER_REGIS"."ADDRESS3"),1,100) address ,
"T_CUSTOMER_REGIS"."PHONE_STD1" || ' ' || "T_CUSTOMER_REGIS"."PHONE_NO1" offtele,
"T_CUSTOMER_REGIS"."PHONE_STD2" || ' ' || "T_CUSTOMER_REGIS"."PHONE_NO2" hometele,
'' mobilephone,
'' bp,
"T_CUSTOMER_REGIS"."FAX_NO" fax,
substr("T_CUSTOMER_REGIS"."EMAIL_ADDRESS",1,50) email,
substr("T_CUSTOMER_REGIS"."OTH_COMM_NUM",1,40) other,
to_char("T_WORK_ORDR_MAST"."CALL_DATE",'dd/mm/yyyy') visit_date,
'' visit_time,
'' product,
"V_MODEL"."MODEL" ,
'' aftermodel,
substr("T_WORK_ORDR_MAST"."FDP_NO",4,3)||"T_WORK_ORDR_MAST"."SET_SERIAL_NO" serial,
SUBSTR("T_WORK_ORDR_DETL"."FAULT_CODE",5) defectcode,
'' ishomeservice,
'' orderhomedate,
'' orderhometime,
'' isorder,
substr("T_STATE_MAST"."STATE_NAME",1,10) region,
'' type,
NVL("T_WORK_ORDR_MAST"."CONTRACT_NO",'') warrantycardnbr,
to_char("T_WORK_ORDR_MAST"."PURCHASE_DATE",'dd/mm/yyyy') pur_date,
'' invoincenbr,
'' market,
'' issubmit,
'' technbr,
'' realhomedate,
'' realhometime,
"T_WORK_ORDR_MAST"."SYMP_CODE" symptoncode,
'' delayfee,
'' trafficfee,
'' partfee,
'' laborfee,
'' discount,
"T_WORK_ORDR_MAST"."TOT_CHARGES" totalfee,
'' othercharge,
'' otherdesc,
'' tax,
'' fixcontent,
to_char("T_WORK_ORDR_MAST"."CLOSE_DATE",'dd/mm/yyyy') finishdate,
'' finishtime,
'Z' currentstatus,
'' position,
to_char("T_WORK_ORDR_MAST"."CLOSE_DATE",'dd/mm/yyyy') sendmachinedate,
'' satisfaction,
to_char("T_CRR_TABLE"."GEN_DATE",'mm/dd/yy') processdate,
'' lastchangedate,
'' isprinted,
'' comefrom,
'' islegal,
'' isrefix,
'' iscosam,
'' cosamtime,
'' fixperiod,
'' responseperiod,
'-1' isfcr,
'-1' istechfcr,
'' islabourchanged,
'' ispartchanged,
'' ismodelerror,
'' isserialerror,
'' lastreturndate,
'' pdcode,
'' bgcode,
'' bucode,
'' magcode,
'' segment,
"V_MODEL"."PRODCLASS" chassis,
'PP' factorycode,
'INDIA' factory,
'' defacttype,
"T_REPAIRACT_MAST"."REPACT_CODE" repairtype,
'' inputman,
"T_WORK_ORDR_DETL"."ACTN_TOBE_TAKEN" ccomment
FROM "T_WORK_ORDR_MAST",
"V_MODEL",
"T_CUSTOMER_REGIS",
"T_CITY_MAST",
"T_STATE_MAST",
"T_WORK_ORDR_DETL",
"T_CRR_TABLE",
"T_REPAIRACT_MAST"
WHERE ( "T_WORK_ORDR_MAST"."CRR_NO" IS NOT NULL ) AND
( "T_WORK_ORDR_MAST"."SET_STATUS" = 'G') AND
( "T_WORK_ORDR_MAST"."REPEAT_CALL" ='N') AND
( "T_WORK_ORDR_MAST"."MODEL_NO" = "V_MODEL"."MODEL" ) and
( "T_WORK_ORDR_MAST"."APSC_CODE" = "V_MODEL"."APSCCODE" ) and
( "T_WORK_ORDR_MAST"."WKODR_NO" = "T_WORK_ORDR_DETL"."WKODR_NO" ) and
( "T_WORK_ORDR_MAST"."APSC_CODE" = "T_WORK_ORDR_DETL"."APSC_CODE" ) and
( "T_WORK_ORDR_DETL"."WKODR_SLNO" = (SELECT MAX("T_WORK_ORDR_DETL"."WKODR_SLNO") FROM "T_WORK_ORDR_DETL"
WHERE "T_WORK_ORDR_MAST"."WKODR_NO" = "T_WORK_ORDR_DETL"."WKODR_NO" and
"T_WORK_ORDR_MAST"."APSC_CODE" = "T_WORK_ORDR_DETL"."APSC_CODE" )) and
( "T_WORK_ORDR_MAST"."CUSTOMER_CODE" = "T_CUSTOMER_REGIS"."CUSTOMER_CODE" ) and
( "T_WORK_ORDR_MAST"."APSC_CODE" = "T_CUSTOMER_REGIS"."APSC_CODE" ) and
( "T_CUSTOMER_REGIS"."CITY_CODE" = "T_CITY_MAST"."CITY_CODE" ) and
( "T_CUSTOMER_REGIS"."APSC_CODE" = "T_CITY_MAST"."APSC_CODE" ) and
( "T_CUSTOMER_REGIS"."STATE_CODE" = "T_STATE_MAST"."STATE_CODE" ) and
( "T_CUSTOMER_REGIS"."APSC_CODE" = "T_STATE_MAST"."APSC_CODE" ) and
( "T_WORK_ORDR_MAST"."WKODR_NO" = "T_CRR_TABLE"."WKODR_NO" ) and
( "T_WORK_ORDR_MAST"."APSC_CODE" = "T_CRR_TABLE"."APSC_CODE" ) and
( "T_WORK_ORDR_MAST"."CRR_NO" = "T_CRR_TABLE"."CRR_NO" ) and
( "T_CRR_TABLE"."WKODR_NO" NOT IN
(select C1.wkodr_no
from t_crr_table C1, t_work_ordr_mast W1
where C1.APSC_CODE = W1.APSC_CODE and
C1.WKODR_NO = W1.WKODR_NO and
C1.CRR_NO = W1.CRR_NO and
C1.CRR_NO IS NOT NULL AND
C1.MARK_TAG = 'R'and
W1.APSC_CODE = :s_apsc_code AND
W1.CLOSE_DATE >= :dt_from and
W1.CLOSE_DATE < :dt_to)) and
( "T_REPAIRACT_MAST"."APSC_CODE"="T_WORK_ORDR_DETL"."APSC_CODE") AND
( "T_REPAIRACT_MAST"."REPACT_DESC"="T_WORK_ORDR_DETL"."ACTN_REMARKS") AND
("T_WORK_ORDR_MAST"."CALL_STATUS" IN ('SD','SR','DG')) AND
( "T_CRR_TABLE"."MARK_TAG" <> 'R') AND
(( "T_WORK_ORDR_MAST"."APSC_CODE" = :s_apsc_code ) and
( "T_WORK_ORDR_MAST"."CLOSE_DATE" >= :dt_from ) and
( "T_WORK_ORDR_MAST"."CLOSE_DATE" < :dt_to ) );
V_MODEL is the View. Please mail me any solution. Thanks.
|
|
|
|
| Re: SQL Performance [message #18852 is a reply to message #18846] |
Tue, 19 February 2002 01:24  |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
use of view may decrease performance, but not necessarily.please use the explain plan to analyze the query.make sure u r using right index and index is picked by query and u have analyzed the table,indexes.
good luck!!!
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 10:16:00 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
