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 ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
