index usage in various scenarious

["adam.slachta" <adam.slachta@xxxxxxxxx>]

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

 

 

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

SCENARIO 2:

select MC.IDTRADINGACCOUNT

          from MARGINCALL MC

where MC.BUSINESSDATE = '17.11.2008';

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

 

 

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

 

 

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