Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Update the DELETE documentation to discuss the truncation optimization. |
|---|---|
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
dcd3325dd31de0463d57d740b7f91d0b |
| User & Date: | drh 2009-04-30 16:00:31 |
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
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>
|