Den 15.08.2015 06:14, skrev 'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]: > Hello Set > > Why writing: PP.STATUS_CODE || '' will improve the SELECT, can you > explain me? > As far I know, adding '' is for use NATURAL in the PLAN, why the use > of NATURAL will do a better response time? > Thanks in advance. > > Greetings. > Walter Sure Walter.
Generally, NATURAL is the quickest way to go through all records in a table, whereas indexes are quicker if you're only considering a few of them. As I expected, the threshold when an index becomes sensible for a particular query, is below 44% (I think I've said before that I don't know where the threshold is, just guessed somewhere between 5 and 20 % (it's purely a wild guess, I know nothing about the Fb code and have not done any measurements)). However, going from 2 hours 45 minutes to 12 minutes is simply too much of an improvement if the only change in the plan is whether this particular index is used or not. Although Fb 2.5 is considerably better than 1.5, Fb 1.5 wasn't that bad in utilizing indexes. Did the plan change in some other way, or was the only difference that this index was replaced by natural? I cannot explain your improvement. Fb 1.5 (and before) has problems with many duplicates in an index, but that problem manifest itself with update and delete, not select. That particular problem is also simple to fix, just add your primary key field to the end of the index. Another problem with Fb 1.5 is that it sometimes have problems to choose which index to use if you have to identical indexes, but you haven't indicated that you have that. Set
