Query tuning
Database is 10.2.0.4 on solaris 10.
optimizer mode is all_rows
Please have a look at the following queries and explain plan and suggest any ways to improvise them.
SELECT OSH.ORDER_BASE_GID OB_GID, NVL(MAX(DECODE(BSC.BS_STATUS_CODE_XID,
'SUPPLIER BOOKING SUBMITTED - REV',OSH.EVENT_DATE)),
MAX(DECODE(BSC.BS_STATUS_CODE_XID,'SUPPLIER BOOKING SUBMITTED - PLAN',
OSH.EVENT_DATE))) PLANNED_SUP_BOOKING_SUB,
NVL(MAX(DECODE(BSC.BS_STATUS_CODE_XID,'SUPPLIER BOOKING APPROVED - REV',
OSH.EVENT_DATE)),MAX(DECODE(BSC.BS_STATUS_CODE_XID,'SUPPLIER BOOKING
APPROVED - PLAN',OSH.EVENT_DATE))) PLANNED_SUP_BOOKING_APP,
Please have a look at the following queries and explain plan and suggest any ways to improvise them.
SELECT OSH.ORDER_BASE_GID OB_GID, NVL(MAX(DECODE(BSC.BS_STATUS_CODE_XID,
'SUPPLIER BOOKING SUBMITTED - REV',OSH.EVENT_DATE)),
MAX(DECODE(BSC.BS_STATUS_CODE_XID,'SUPPLIER BOOKING SUBMITTED - PLAN',
OSH.EVENT_DATE))) PLANNED_SUP_BOOKING_SUB,
NVL(MAX(DECODE(BSC.BS_STATUS_CODE_XID,'SUPPLIER BOOKING APPROVED - REV',
OSH.EVENT_DATE)),MAX(DECODE(BSC.BS_STATUS_CODE_XID,'SUPPLIER BOOKING
APPROVED - PLAN',OSH.EVENT_DATE))) PLANNED_SUP_BOOKING_APP,
0