>SELECT * FROM >TABLE_2 T2 > INNER JOIN >TABLE_1 T1 > ON > T2.TABLE_1_ID = T1.ID > >After executing this query I am getting such plan: > >PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2)) > >Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? And why I am >getting error when i try to enforce it with: >PLAN JOIN (T1 INDEX (PK_TABLE_1), T2 INDEX (FK_TABLE_2))
Admittedly, my way of "plan thinking" is far from the way things are implemented, but still - it normally helps me understand why the optimizer behaves as it does. A simple query like yours I typically consider from the first table to the last in the plan. Hence, I'd start with T1. SELECT * FROM TABLE_1 T1 Here, there's no limiting JOIN, WHERE or anything similar that can be used to limit the number of rows to return (T2 isn't involved yet, hence any clause referring to that tuple cannot be considered). So, the quickest and only sensible option (at least unless you want to slow things down) is to go NATURAL on T1. Then, we add T2 INNER JOIN TABLE_2 T2 ON T2.TABLE_1_ID = T1.ID Here, you already have T1 available, and using an index for T2.TABLE_1_ID will help reducing the potential number of records to look up considerably. Is there no way to utilize the index for T1.ID? Sure there is, you can start with T2 rather than T1. However, then you would start with SELECT * FROM TABLE_2 T2 which cannot use an index. Adding INNER JOIN TABLE_1 T1 ON T2.TABLE_1_ID = T1.ID would, however, benefit from using the index for T1.ID. Is there really no way to use an index for both fields? No, not unless you add a WHERE (or ON) clause that limits the result set through including one or more constants, e.g. AND T1.ID between 2 and 4 Having said that, it is only on rather big tables that it is bad to see NATURAL on the first tuple in the plan (well, in more complex queries - e.g. involving subselects - it may not be the first tuple). And I think you would normally use a proper WHERE clause on big tables, although it is of course thinkable to e.g. want to have a query that should find all persons being a citizen of both London and Paris (millions of persons in both tables, but only a very small subset being part of both). Such rare queries are normally slowish, sometimes even after optimization. HTH, Set
