Recently, I heard a guy cried that the DB2 for iSeriese does not use index he created. Then, I asked him if he has check the size of table. The answer is NO!
So, it seems that some developers do not aware that a RDBMS' query optimizer may not use index though indexes are created to optimize SQL query speed. In fact, DBMS normally have its own algorithm to decide when to use index scan or table scan in a SQL query. In general, DBMS should use index scan instead of full table scan only when size of index is smaller than size of table. Otherwise, there is no point for DBMS to use index. This is true for DB2, MySQL, Oracle, MS SQL etc.
How to create index or combined index and how many index should be created for a table is big topic. But, we shall know that index may not be always used when SQL query optimizer thinks it is not worthy. This is most likely true for a new launched application, which has not received/created many data in tables. So, do not cry that DBMS is suck that it does not use indexes. Check your table size first. Then, check your DBMS configuration. For example, MySQL has configurable threshold value, which determine when query optimizer can use index instead of full table scan. But, you can not force SQL query optimizer to use index. The most we can do is configuring table to prefer index scan than table scan.
Here is a more detailed discussion about "magic number".
Hi Yiyu Jia,
ReplyDeleteDo you know why when i selected a column with BLOB datatype, all my access path was kill by system.
Following each access path name in the list is a reason code which explains why the access path was not used. A reason code of 0 indicates that the access path was used to implement the query.
1 - Access path was not in a valid state. The system invalidated the access path.
I had tested all possible method such as reorg, create a new table (blank table) & index, the run Visual Explain to see the log. It still the same. If my sql statement exclude that field then my log appear as normal (will use the primary key/index).
4 - The cost to use this access path, as determined by the optimizer, was higher than the cost associated with the chosen access method.
Do you have any idea on setting up the optimizer for BLOB?