Re: [Firebird-devel] AutoOptimize

[Arno B. <fbd...@ab...>]

Hi,

>   //Fetch all the index names - also this part isn't needed.
>   ibtMain.SQL.Text:='SELECT I.RDB$INDEX_NAME FROM RDB$INDICES I WHERE I.RDB$INDEX_INACTIVE=0 AND RDB$UNIQUE_FLAG=0';
> //do only for active indexes and not unique ones

Why only not unique ones?

> 1. Add in firebird.conf ChangesToOptimize (=0 disabled) and
> TimeToOptimize (see UnflushedWrites engine). (Which is the number of Changes to reach and the amount of time from the
> last change to pass in order to start the automatic optimization)

> 2. Add in the idx structure an int called, le't say 'modifications'
> which at creation time is 0 and last_optimize_time and a thread
> doOptimize. Set to this the IDLE_PRIORITY_CLASS,
> THREAD_PRIORITY_BELOW_NORMAL
> 3. In IDX_modifiy, if everything is ok and the index isn't unique then
> using a similar code with that from UnflushedWrites (see CCH_flush from
> cch.cpp), or use timers for the delay or WM_TIMER (on Windows). dunno for Linux.
>     <execute the doOptimize thread which will cal inside the
> IDX_statistics with the appropiate values >

It's not interesting to track modifications of index.
All indexes which belong to the same table should update the statistics simultaneously else the statistics become 
"unbalanced" compared to each other.

Tracking modifications (U/D/I) of a table (or data-pages updates) would be enough.
Using that number to calculate the percent that has changed of the table x should prepare the "update statistics batch 
for table x" in the scheduler.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
  http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features:
  http://www.firebirdsql.org
  http://www.firebirdsql.info

Support list for Interbase and Firebird users:
  fir...@ya...

Nederlandse firebird nieuwsgroep:
  news://newsgroups.firebirdsql.info