Thread: index usage in various scenarious

index usage in various scenarious

From

"adam.slachta"

Date:

10 April 2009, 12:03:25

Hello,

QUESTION1: Can somebody clarify in what of the proposed scenarios is the following index used? Any further comment will be greatly appreciated.

QUESTION2: Does any other scenarios when an index is NOT used (and someone might possibly expect it is used) come to your mind? Thank you very much for your time and wish you a happy dayJ

CREATE INDEX idx_margincall_bussdt ON margincall USING btree (businessdate);

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

SCENARIO 1:

select MC.IDTRADINGACCOUNT

from MARGINCALL MC

join DEFAULTINGACCOUNT DA on DA.BUSINESSDATE = MC.BUSINESSDATE and DA.IDTRADINGACCOUNT = MC.IDTRADINGACCOUNT

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

SCENARIO 2:

select MC.IDTRADINGACCOUNT

from MARGINCALL MC

where MC.BUSINESSDATE = '17.11.2008';

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

SCENARIO 3:

select MC.IDTRADINGACCOUNT

from MARGINCALL MC

join DEFAULTINGACCOUNT DA on DA.BUSINESSDATE = MC.BUSINESSDATE and DA.IDTRADINGACCOUNT = MC.IDTRADINGACCOUNT

where MC.BUSINESSDATE = '17.11.2008';

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

SCENARIO 4 + QUESTION:

Is usage of indexes during execution of queries affected by db-links?

E.g.:

select MC.IDTRADINGACCOUNT

from MARGINCALL@DB_LINK_NAME MC

where MC.BUSINESSDATE = '17.11.2008';

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Adam Slachta