Documentation Source Text

Check-in [dcd3325dd3]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update the DELETE documentation to discuss the truncation optimization.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: dcd3325dd31de0463d57d740b7f91d0bf318c5f1
User & Date: drh 2009-04-30 16:00:31.000
Context
2009-05-01
10:45
Add text describing file locking and the database header cookies (file and schema versions) to fileformat.html. (check-in: ca6db0c14f user: dan tags: trunk)
2009-04-30
16:00
Update the DELETE documentation to discuss the truncation optimization. (check-in: dcd3325dd3 user: drh tags: trunk)
2009-04-29
14:33
Update trigger documentation. (check-in: 1179f9425f user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/lang.in.
980
981
982
983
984
985
986


987
988
989
990
991
992
993
994
995
996
997
998
999

1000








1001
1002
1003
1004
1005
1006
1007
</p>

<p>Without a WHERE clause, all rows of the table are removed.
If a WHERE clause is supplied, then only those rows that match
the expression are removed.</p>

<tcl>hd_fragment trucateopt {truncate optimization}</tcl>


<p>When the WHERE is omitted from a DELETE statement and the table
being deleted has no triggers,
SQLite uses an optimization to erase the entire table content
without having to visit each row of the table individual.
This "truncate" optimization makes the delete run much faster.
Prior to SQLite [version 3.6.5], the truncate optimization
also meant that the [sqlite3_changes()] and
[sqlite3_total_changes()] interfaces
and the [count_changes pragma]
will not actually return the number of deleted rows.  
That problem has been fixed as of [version 3.6.5].

<p>The truncate optimization can be disabled for all queries by recompiling

SQLite with the [SQLITE_OMIT_TRUNCATE_OPTIMIZATION] compile-time switch.</p>









<p>If SQLite is compiled with the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT]
compile-time option, then the syntax of the DELETE statement is
extended by the addition of optional ORDER BY and LIMIT clauses:</p>

<tcl>BubbleDiagram delete-stmt-limited</tcl>








>
>












|
>

>
>
>
>
>
>
>
>







980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
</p>

<p>Without a WHERE clause, all rows of the table are removed.
If a WHERE clause is supplied, then only those rows that match
the expression are removed.</p>

<tcl>hd_fragment trucateopt {truncate optimization}</tcl>
<h3>The Truncate Optimization</h3>

<p>When the WHERE is omitted from a DELETE statement and the table
being deleted has no triggers,
SQLite uses an optimization to erase the entire table content
without having to visit each row of the table individual.
This "truncate" optimization makes the delete run much faster.
Prior to SQLite [version 3.6.5], the truncate optimization
also meant that the [sqlite3_changes()] and
[sqlite3_total_changes()] interfaces
and the [count_changes pragma]
will not actually return the number of deleted rows.  
That problem has been fixed as of [version 3.6.5].

<p>The truncate optimization can be permanently disabled for all queries
by recompiling
SQLite with the [SQLITE_OMIT_TRUNCATE_OPTIMIZATION] compile-time switch.</p>

<p>The truncate optimization can also be disabled at runtime using
the [sqlite3_set_authorizer()] interface.  If an authorizer callback
returns [SQLITE_IGNORE] for an [SQLITE_DELETE] action code, then
the DELETE operation will proceed but the truncate optimization will
be bypassed and rows will be deleted one by one.</p>

<h3>Use Of LIMIT</h3>

<p>If SQLite is compiled with the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT]
compile-time option, then the syntax of the DELETE statement is
extended by the addition of optional ORDER BY and LIMIT clauses:</p>

<tcl>BubbleDiagram delete-stmt-limited</tcl>