hi ,
below query taking lot of time,can anybody help me tune the query.My db version is oracle 9.2
explain plan for select MRH_MRN,DECODE(MRH_SEX,'M','MALE','FEMALE') AS SEX, trim((mrh_sur_name||' '||mrh_first_name||' '||mrh_middle_name)) as MEMNAME, decode(nvl(mrh_fellow_status_yn,'333'),'Y','FCA','ACA')
AS ACA_FCA, DECODE(MRH_RESI_STATUS,'I',MRH_PROF_ADDR_LINE_1,'A',MRH_RES_ADDR_LINE_1) AS L_ADD1, DECODE(MRH_RESI_STATUS,'I',MRH_PROF_ADDR_LINE_2,'A',MRH_RES_ADDR_LINE_2) AS L_ADD2, DECODE(MRH_RESI_STATUS,'I',MRH_PROF_ADDR_LINE_3,'A',MRH_RES_ADDR_LINE_3) AS L_ADD3, DECODE(MRH_RESI_STATUS,'I',MRH_PROF_ADDR_LINE_4,'A',
MRH_RES_ADDR_LINE_4) AS L_ADD4, DECODE(MRH_RESI_STATUS,'I',a.city_name,'A',C.CITY_NAME) AS L_CITY, DECODE(MRH_RESI_STATUS,'I',MRH_PROF_ZIP_POSTAL_CODE,'A',MRH_RES_ZIP_POSTAL_CODE) AS L_PIN, DECODE
(MRH_RESI_STATUS,'I',b.cou_name,'A',D.COU_NAME) as L_Country, DECODE(MRH_RESI_STATUS,'I','NOT APPLICABLE',MRH_PROF_ADDR_LINE_1) AS R_ADD1, DECODE(MRH_RESI_STATUS,'I',' ',MRH_PROF_ADDR_LINE_2)
AS R_ADD2, DECODE(MRH_RESI_STATUS,'I',' ',MRH_PROF_ADDR_LINE_3) AS R_ADD3, DECODE(MRH_RESI_STATUS,'I',' ',MRH_PROF_ADDR_LINE_4) AS R_ADD4, DECODE(MRH_RESI_STATUS,'I',' ','A',A.CITY_NAME) AS R_CITY, DECODE(MRH_RESI_STATUS,'I',' ','A',
MRH_PROF_ZIP_POSTAL_CODE) AS R_PIN, DECODE(MRH_RESI_STATUS,'I',' ','A',B.COU_NAME) as R_Country, decode(nvl(mrh_mem_sub_status,'555'),'26','EXPIRED','') as sub_status, decode(nvl(mrh_mem_status,'777'),'1','ACTIVE',
'2','REMOVED') as mem_status,mrh_resi_status, DECODE(MRH_COP_STATUS,'1',DECODE(MRH_COP_TYPE ,'13','FULLTIME-COP','1','FULLTIME-COP', '12','PARTTIME-COP','2','PARTTIME-COP'),'NOT HOLDING COP')
AS COP_STATUS, TO_CHAR(MRH_ENROL_DT,'RRRR') AS ASSO_YR,TO_CHAR(MRH_FELLOW_DT,'RRRR') AS FELLOW_YR from om_mem_reg_head,om_city A,om_country B,om_city C,om_country D where mrh_doc_status=5 and mrh_prof_city_code=A.City_code(+) and mrh_prof_cou_code=
B.cou_code(+) and mrh_res_city_code=C.City_code(+) and mrh_res_cou_code=D.cou_code(+) and trim((mrh_sur_name||' '||mrh_first_name||''||mrh_middle_name)) like upper('%%') ORDER BY trim((mrh_sur_name||' '||mrh_first_name||' '||mrh_middle_name))
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
|
--------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 2863 | 684K| | 201
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
6 |
| 1 | SORT ORDER BY | | 2863 | 684K| 1496K| 201
6 |
| 2 | NESTED LOOPS OUTER | | 2863 | 684K| | 190
0 |
| 3 | NESTED LOOPS OUTER | | 2863 | 637K| | 190
0 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 4 | HASH JOIN OUTER | | 2863 | 589K| | 190
0 |
| 5 | HASH JOIN OUTER | | 2863 | 545K| | 189
1 |
| 6 | TABLE ACCESS FULL| OM_MEM_REG_HEAD | 2863 | 500K| | 188
2 |
| 7 | TABLE ACCESS FULL| OM_COUNTRY | 677 | 10832 | |
4 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 8 | TABLE ACCESS FULL | OM_COUNTRY | 677 | 10832 | |
4 |
| 9 | INDEX UNIQUE SCAN | CITY_CODE_PK | 1 | 17 | |
|
| 10 | INDEX UNIQUE SCAN | CITY_CODE_PK | 1 | 17 | |
|
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---
Note: cpu costing is off, PLAN_TABLE' is old version
18 rows selected.
SQL>
Edited by: user00726 on Jan 23, 2009 3:53 AM