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