Hi there,
Could this sql be better written for better performance please?
SELECT created_by
,created_date
,is_rpl_flg
,last_modified_by
,last_modified_date
,ptnr_id
,ptnr_type
,rpl_update_date
,rpl_update_flg
,rps_deferred_by
,rps_deferred_date
,rps_engine_decision
,rps_screened_by
,rps_screened_date
,sk_loc_id
,sub_org
,sys_date_1
,sys_date_2
,sys_number_1
,sys_number_2
,sys_varchar_1
,sys_varchar_2
,sys_varchar_3
,user_date_1
,user_date_2
,user_number_1
,user_number_2
,user_varchar_1
,user_varchar_2
,user_varchar_3
FROM ems_ptr
WHERE (
(
(ems_ptr.sys_date_1 IS NULL)
OR (ems_ptr.rps_screened_date > ems_ptr.sys_date_1)
)
OR (
(ems_ptr.is_rpl_flg IS NOT NULL)
AND (ems_ptr.sys_date_2 IS NULL)
)
)
AND (
NVL(ems_ptr.sys_varchar_1, 'NULL') IN ('ERROR','NULL')
)
AND (NVL(ems_ptr.is_rpl_flg, 'NULL') NOT IN ('D'));
CREATE TABLE "EMS_PTR"
( "SUB_ORG" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"PTNR_TYPE" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"PTNR_ID" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"IS_RPL_FLG" VARCHAR2(5 BYTE),
"RPS_DEFERRED_BY" VARCHAR2(250 BYTE),
"RPS_DEFERRED_DATE" DATE,
"RPS_ENGINE_DECISION" VARCHAR2(10 BYTE),
"RPS_SCREENED_BY" VARCHAR2(250 BYTE),
"RPS_SCREENED_DATE" DATE,
"SK_LOC_ID" NUMBER(17,0),
"USER_DATE_1" DATE,
"USER_DATE_2" DATE,
"USER_NUMBER_1" NUMBER(25,8),
"USER_NUMBER_2" NUMBER(25,8),
"USER_VARCHAR_1" VARCHAR2(250 BYTE),
"USER_VARCHAR_2" VARCHAR2(250 BYTE),
"USER_VARCHAR_3" VARCHAR2(250 BYTE),
"SYS_DATE_1" DATE,
"SYS_DATE_2" DATE,
"SYS_NUMBER_1" NUMBER(25,8),
"SYS_NUMBER_2" NUMBER(25,8),
"SYS_VARCHAR_1" VARCHAR2(250 BYTE),
"SYS_VARCHAR_2" VARCHAR2(250 BYTE),
"SYS_VARCHAR_3" VARCHAR2(250 BYTE),
"CREATED_BY" VARCHAR2(50 BYTE),
"CREATED_DATE" DATE,
"LAST_MODIFIED_BY" VARCHAR2(250 BYTE),
"LAST_MODIFIED_DATE" DATE,
"RPL_UPDATE_DATE" DATE,
"RPL_UPDATE_FLG" VARCHAR2(250 BYTE),
CONSTRAINT "PKMPTNR_CRPS" PRIMARY KEY ("SUB_ORG", "PTNR_TYPE", "PTNR_ID");
create index SYS_DATE_1_IDX on ems_ptr (SYS_DATE_1);
create index IS_RPL_FLG_IDX on ems_ptr (IS_RPL_FLG);
The execution plan is showing a full table scan, but I'm guessing that's due to the relatively low table row count (table has 750 rows)
SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 879210452
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 710 | 44020 | 16 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMS_PTR | 710 | 44020 | 16 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("EMS_PTR"."SYS_DATE_1" IS NULL OR
"EMS_PTR"."IS_RPL_FLG" IS NOT NULL AND "EMS_PTR"."SYS_DATE_2"
IS NULL OR "EMS_PTR"."RPS_SCREENED_DATE">"EMS_PTR"."SYS_DATE_1"
) AND NVL("EMS_PTR"."IS_RPL_FLG",'NULL')<>'D' AND
(NVL("EMS_PTR"."SYS_VARCHAR_1",'NULL')='NULL' OR
NVL("EMS_PTR"."SYS_VARCHAR_1",'NULL')='ERROR'))
18 rows selected.
As you can see, I did create an index on SYS_DATE_1 and IS_RPL_FLG but neither one is being used.
Appreciate all your input.
Cheers