Re: [firebird-support] Firebird optmizer cop-out

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

05.12.2018 19:25, Rudi Feijó rudi.fe...@multidadosti.com.br [firebird-support] 
wrote:
> One last question, does the optimizer actively learns and auto-improves from 
> queries if we 
> set the PLAN manually?

   No. No AI inside.
   Optimizer is using database statistics, including index statistics and you 
must order 
their recalculation manually sometimes, there is no automatic housekeeping.


-- 
   WBR, SD.

RE: [firebird-support] Firebird optmizer cop-out

[Rudi Feijó rudi.fe...@multidadosti.com.br [firebird-support]]

>Join order can be forced by using outer join.
>Using of indexes can be disabled by changing of simple field usage to an 
>expression.
>Preferences between ORDER and SORT plans (more commonly between "first row" 
>and "all 
>rows" strategies) can be changed by using FIRST/ROWS.
>Index statistics recalculation can be done in "a right moment"..



 

Thanks a lot for the information Dimitry.

One last question, does the optimizer actively learns and auto-improves from 
queries if we set the PLAN manually?

I had a slow query that I fiddled a bit the with PLAN, and the execution was 
faster. 
I noticed that after a while, without needing to set the PLAN explicitly, the 
optimizer began using a different and faster PLAN then it was previously using.

 

Re: [firebird-support] Firebird optmizer cop-out

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

05.12.2018 12:37, Rudi Feijó rudi.fe...@multidadosti.com.br [firebird-support] 
wrote:
> Could anyone explain me further how the user can go about to effectively
> pick or influence the optimizers decision?

   Join order can be forced by using outer join.
   Using of indexes can be disabled by changing of simple field usage to an 
expression.
   Preferences between ORDER and SORT plans (more commonly between "first row" 
and "all 
rows" strategies) can be changed by using FIRST/ROWS.
   Index statistics recalculation can be done in "a right moment".

-- 
   WBR, SD.

[firebird-support] Firebird optmizer cop-out

[Rudi Feijó rudi.fe...@multidadosti.com.br [firebird-support]]

>From the docs about the optmizer, there is a small section :

 

Cop-Out

The user can pick or influence the optimizer’s decision. The Firebird
optimizer actually uses all three


Could anyone explain me further how the user can go about to effectively
pick or influence the optimizers decision?
By “pick” I assume it is to use an explicit manual PLAN statement, but what
about “influence”? I know some rdbs like oracle have a HINT statement, but I
haven’t found anything similar in firebird.
And also, can user action “permanently” influence the optimizer in positive
ways in future queries?

 

Atenciosamente,

Rudi Feijó


Multidados Informática Ltda.
*  (11) 2579-8789

*   rudi.fe...@multidadosti.com.br

*   www.multidadosti.com.br 
*   www.whatsappmailing.com.br

 

 



[Non-text portions of this message have been removed]