|
|
Compacting your database
From NeoWiki
| Revision as of 10:13, 22 October 2009 (edit) James3359 (Talk | contribs) (This is a new page(!) with instructions about how to use SQL commands to compact a database.) ← Previous diff |
Current revision (23:02, 22 October 2009) (edit) (undo) Sardisson (Talk | contribs) (minor stylistic tweaks) |
||
| (One intermediate revision not shown.) | |||
| Line 1: | Line 1: | ||
| - | In the database engine embedded in Base, when records are deleted they are unlinked in the actual tables. The space used by them is not automatically freed and the indexes can be become fragmented over time. | + | In the database engine embedded in Base, when records are deleted they are unlinked in the actual tables. The space used by them is not automatically freed and the indexes can be become fragmented over time. This leads to a degradation of performance. |
| - | + | ==Reclaiming Space and Repacking Indexes== | |
| There are two ways to reclaim this space and repack the indexes. | There are two ways to reclaim this space and repack the indexes. | ||
| - | The first is to execute the command | + | The first is to execute the SQL command<br> |
| - | <tt>SHUTDOWN COMPACT</tt> | + | <tt>SHUTDOWN COMPACT</tt><br> |
| The disadvantage of this is that the database engine is, well shutdown. So the Base file would need to be closed and reopened. | The disadvantage of this is that the database engine is, well shutdown. So the Base file would need to be closed and reopened. | ||
| - | The other is to execute this command | + | The other is to execute this SQL command<br> |
| - | <tt>CHECKPOINT DEFRAG</tt> | + | <tt>CHECKPOINT DEFRAG</tt><br> |
| In this case the engine is also shutdown to active connections, the space used by deleted records reclaimed and the indexes rebuilt. The engine is then automatically restarted. | In this case the engine is also shutdown to active connections, the space used by deleted records reclaimed and the indexes rebuilt. The engine is then automatically restarted. | ||
| - | Either of these commands can be entered by hand in the SQL window. | + | Either of these commands can be entered by hand in the SQL window. |
| - | To enter the command by hand begin in your main database window. In the {{menu|Tools}} menu choose {{menu|SQL…}}. This brings up an {{Window|Execute SQL Statement}} window. Type the command into the window and then click {{button|Execute}}. | + | ===Entering the SQL Commands=== |
| + | |||
| + | To enter the command by hand, begin in your main database window. In the {{menu|Tools}} menu, choose {{menu|SQL…}}. This brings up an {{Window|Execute SQL Statement}} window. Type the command into the window and then click {{button|Execute}}. | ||
| + | |||
| + | ==References== | ||
| + | * [http://www.oooforum.org/forum/viewtopic.phtml?t=61295 OpenOffice.org Forum] | ||
| [[Category:NeoOffice]][[Category:Tips_and_Hints]] | [[Category:NeoOffice]][[Category:Tips_and_Hints]] | ||
Current revision
In the database engine embedded in Base, when records are deleted they are unlinked in the actual tables. The space used by them is not automatically freed and the indexes can be become fragmented over time. This leads to a degradation of performance.
Reclaiming Space and Repacking Indexes
There are two ways to reclaim this space and repack the indexes.
The first is to execute the SQL command
SHUTDOWN COMPACT
The disadvantage of this is that the database engine is, well shutdown. So the Base file would need to be closed and reopened.
The other is to execute this SQL command
CHECKPOINT DEFRAG
In this case the engine is also shutdown to active connections, the space used by deleted records reclaimed and the indexes rebuilt. The engine is then automatically restarted.
Either of these commands can be entered by hand in the SQL window.
Entering the SQL Commands
To enter the command by hand, begin in your main database window. In the Tools menu, choose SQL…. This brings up an Execute SQL Statement window. Type the command into the window and then click Execute.
