select * from s_evt_act where QUOTE_ID is not null
and TODO_CD in ('Email - Inbound','Email -
Outbound','Communication','BCP Request','PCN Request','PriceResponse')
Generate Explain Plan using F10 in SQL Developer
We
can see that the Table Access is happening for S_EVT_ACT for query QUOTE_ID IS
NOT NULL
The
Cost comes around 11200
Solution:
Create an Index
CREATE INDEX
"SIEBEL"."S_EVT_ACT_F85_X" ON
"SIEBEL"."S_EVT_ACT"
(
"QUOTE_ID" DESC,
"CREATED" DESC,
"TODO_CD" DESC
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE
STATISTICS STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "SIEBDATA" ;
Modify the above SQL to use the newly created INDEX as
a hint(in Blue Font)
select /*+ index(S_EVT_ACT
S_EVT_ACT_F85_X) */ * from s_evt_act where QUOTE_ID is not null
and TODO_CD in ('Email -
Inbound','Email - Outbound','Communication','BCP Request','PCN
Request','PriceResponse')
Check the Explain Plan by clicking F10
You
can see that the Cost is reduced by leaps and bounds 552
Performance
is improved.But you can further tune...think think think:)
Use
the following SQL as per need to compute statistics occasionaly
alter
index "SIEBEL"."S_EVT_ACT_F85_X" compute statistics
Have a great day
Have a great day
