11.04.2012 1:49, unordained wrote:

> select 1 from bt_dchronexpl inner join bt_ref on bt_ref.file_num =
> bt_dchronexpl.filenumber;
> --> PLAN JOIN (BT_DCHRONEXPL NATURAL, BT_REF INDEX (IX_BT_REF_FILE_NUM_ASC))
> -- so it CAN use an index, but why not both? just the size imbalance?

It's impossible to use two indices for a single condition. If you search
for some value by its key you should have that key already retrieved.
Just think more about it and you should get the idea.

> select 1 from bt_dchronexpl full outer join bt_ref on bt_ref.file_num =
> bt_dchronexpl.filenumber
> --> PLAN JOIN (BT_REF NATURAL, BT_DCHRONEXPL NATURAL)
> -- why no index at all?

FULL OUTER JOIN could never use indices due to its implementation, as
both tables should be accessed twice with different rules. It's improved
in FB3.

> select 1 from bt_dchronexpl right join bt_ref on bt_ref.file_num starts
> with
> bt_dchronexpl.filenumber;
> --> PLAN JOIN (BT_REF NATURAL, BT_DCHRONEXPL NATURAL)
> -- why not a reversed looping lookup?

Because LEFT/RIGHT JOIN always dictates the join order, from the left
side to the right side.

> select 1 from bt_dchronexpl left|inner|right join bt_ref on bt_ref.file_num
> starts with bt_dchronexpl.filenumber
> PLAN JOIN (BT_DCHRONEXPL INDEX(IX_DCHRONEXPL_FILENUM), BT_REF INDEX
> (IX_BT_REF_FILE_NUM_ASC))
> --> ERROR: index IX_DCHRONEXPL_FILENUM cannot be used in the specified plan

See my first comment.


Dmitry