Do you want to Search Something?

Those who opened the gates

Wednesday, December 10, 2014

The query which made me a mere spectator of Time


This SQL query takes it too long to bring the data

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