5.6 TRUNCATE TABLE
The TRUNCATE TABLE statement deletes all the rows in a table, often much
faster than the equivalent set-oriented DELETE statement.
<truncate_table> ::= TRUNCATE TABLE [ <owner_name> "." ] <table_name>
TRUNCATE TABLE comes in two versions: fast and slow. The fast form is
used if two requirements are met: First, there must be no non-empty child
tables, and second, the TRUNCATE_WITH_AUTO_COMMIT database option
must be 'ON' (the default).
The first requirement means that the table being truncated cannot partici
-
pate as the parent in any foreign key relationship where the child table contains
any rows; there can be child tables, but they have to be empty for the fast form
of TRUNCATE TABLE to be used.
The second requirement, that TRUNCATE_WITH_AUTO_COMMIT must
be 'ON', is a bit confusing. It means that if the first requirement is met,
TRUNCATE TABLE will perform a COMMIT when it is finished deleting
rows. It also means, again only if the first requirement is met and if a transac-
tion is already in progress before TRUNCATE TABLE is executed, that a
COMMIT will be issued before it starts deleting rows. If the first requirement is
not met, TRUNCATE TABLE will not issue either COMMIT even if
TRUNCATE_WITH_AUTO_COMMIT is 'ON'.
The difference between fast and slow is striking. In one test, the fast ver-
sion of TRUNCATE TABLE took 10 seconds to delete 50M of data in 30,000
rows. Both the slow version of TRUNCATE TABLE and the DELETE state-
ment took four and a half minutes to do the same thing.
The fast version of TRUNCATE TABLE gets its speed from the fact that it
takes several shortcuts. The first shortcut, which is also taken by the slow ver-
sion, is that TRUNCATE TABLE does not fire any delete triggers. If you have
critical application logic in a delete trigger, it won’t get executed, and you may
want to use another method to delete data.
This doesn’t mean TRUNCATE TABLE bypasses foreign key checking; on
the contrary, if you attempt to remove a row that is a parent in a foreign key
relationship, the TRUNCATE TABLE statement will fail. That’s true even if
you coded ON DELETE CASCADE; the TRUNCATE TABLE operates as if
you had specified ON DELETE RESTRICT, and you cannot use it to cascade
deletes from parent to child tables. By definition, of course, the fast version of
TRUNCATE TABLE won’t violate referential integrity because if there are any
child tables they must be empty; otherwise the fast version isn’t used.
Note: If a child table is non-empty, but contains only NULL values in the for
-
eign key columns, it won’t prevent TRUNCATE TABLE from executing successfully
because there will be no referential integrity violations. It will, however, prevent
the fast version of TRUNCATE TABLE from being used simply because the child
table is non-empty. This combination of circumstances means that a setting of
TRUNCATE_WITH_AUTO_COMMIT of 'ON' will not be honored, and TRUNCATE
TABLE will not issue any commits.
190 Chapter 5: Deleting
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The second shortcut, also taken by both the slow and fast forms of TRUNCATE
TABLE, is that the individual deleted rows are not written to the transaction log
file; just a record of the TRUNCATE TABLE command itself. This means that
TRUNCATE TABLE should not be used on a table that is being uploaded via
MobiLink if you want the deleted rows to be included in the upload stream.
MobiLink determines which rows to upload by examining the transaction log,
and rows deleted via TRUNCATE TABLE will be missed. For more informa
-
tion about MobiLink, see Chapter 7, “Synchronizing.”
The third shortcut is only taken by the fast version of TRUNCATE TABLE.
It does not acquire locks on the individual deleted rows but instead places an
exclusive lock on the entire table. In most cases this will cause fewer problems
for concurrency because the alternatives, DELETE or slow TRUNCATE
TABLE, run slower and acquire locks on every row.
The fourth shortcut, also only taken by the fast version of TRUNCATE
TABLE, is that extra space in the database file is not allocated for the rollback
and checkpoint logs.
Note: If you delete and re-insert all the rows in a large table, using DELETE
or the slow version of TRUNCATE TABLE, it is entirely possible for the database
file to double or even triple in size because of all the space required to hold the
rollback and checkpoint logs. For more information on these logs, see Section
9.11, “Logging and Recovery.”
Tip:
If you are willing to commit the change after deleting all the rows in a
large table, and you want to avoid having the database file grow in size, execute
explicit COMMIT and CHECKPOINT statements immediately after the DELETE or
TRUNCATE TABLE. These statements will increase the chances that the database
engine will be able to reuse or release the extra database file space that may
have been allocated to accommodate the rollback and checkpoint logs during
the deletion operation. In the case of a fast TRUNCATE TABLE, an explicit
COMMIT is not necessary but it will do no harm, and it’s sometimes hard to pre
-
dict if you’re going to get the fast or slow version. The same is true of the explicit
CHECKPOINT; it may not be necessary because the database engine may
decide on its own that it’s time to do a CHECKPOINT, but in that case an extra
CHECKPOINT will do no harm.
Note: CHECKPOINT statements can be expensive. Generally speaking,
explicit CHECKPOINT statements are not required in application programs
because the server does a good job of scheduling checkpoints to minimize their
impact on performance. An explicit CHECKPOINT should never be used without
careful consideration, especially in a busy multi-user environment.
Following is a table that shows how the actions performed by TRUNCATE
TABLE depend on whether there are any rows in a child table, the
TRUNCATE_WITH_AUTO_COMMIT setting, and whether or not a database
transaction is already in progress. Note that of the eight combinations, only two
result in the fast version of TRUNCATE TABLE being used. Also note that in
two of the combinations, TRUNCATE_WITH_AUTO_COMMIT is 'ON' but no
commits are performed.
Chapter 5: Deleting
191
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Non-empty
child TRUNCATE_WITH Transaction
table? _AUTO_COMMIT in progress? TRUNCATE TABLE Actions
========= ============= ============ ===========================================
Yes 'ON' Yes slow TRUNCATE
Yes 'ON' No BEGIN TRAN, slow TRUNCATE
Yes 'OFF' Yes slow TRUNCATE
Yes 'OFF' No BEGIN TRAN, slow TRUNCATE
No 'ON' Yes COMMIT, BEGIN TRAN, fast TRUNCATE, COMMIT
No 'ON' No BEGIN TRAN, fast TRUNCATE, COMMIT
No 'OFF' Yes slow TRUNCATE
No 'OFF' No BEGIN TRAN, slow TRUNCATE
Note: This book assumes that the CHAINED database option is set to 'ON',
and that is why BEGIN TRAN (short for BEGIN TRANsaction) operations are
shown in the table above. The chained mode of operation means that any data
manipulation operation like INSERT, UPDATE, DELETE, and TRUNCATE TABLE
will implicitly start a database transaction if one isn’t already started, and that
transaction will not normally end until an explicit COMMIT or ROLLBACK is
issued. Some commands, such as CREATE TABLE and the fast version of
TRUNCATE TABLE, will perform a COMMIT as a side effect. For more informa
-
tion about transactions, see Section 9.3.
Here is an example that demonstrates how TRUNCATE TABLE works; first,
two tables are created and one row is inserted into each:
CREATE TABLE t1 (
key_1 UNSIGNED INTEGER NOT NULL PRIMARY KEY,
non_key_1 INTEGER NOT NULL );
CREATE TABLE t2 (
key_1 UNSIGNED INTEGER NOT NULL PRIMARY KEY,
non_key_1 INTEGER NOT NULL );
INSERT t1 VALUES ( 1, 1 );
INSERT t2 VALUES ( 22, 22 );
COMMIT;
In the first test, TRUNCATE_WITH_AUTO_COMMIT is explicitly set to 'ON',
the row in table t2 is updated, TRUNCATE TABLE is executed against table t1,
and a ROLLBACK statement is executed:
SET EXISTING OPTION PUBLIC.TRUNCATE_WITH_AUTO_COMMIT = 'ON';
UPDATE t2 SET non_key_1 = 999;
TRUNCATE TABLE t1;
ROLLBACK;
After those statements are executed, t1 is empty and the value of t2.non_key_1
is 999; the TRUNCATE TABLE performed before-and-after COMMIT opera
-
tions and the ROLLBACK statement was completely ignored, as is shown by
the corresponding entries in the transaction log:
BEGIN TRANSACTION
UPDATE DBA.t2
SET non_key_1=999
WHERE key_1=22
COMMIT WORK
BEGIN TRANSACTION
192 Chapter 5: Deleting
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
truncate table t1
COMMIT WORK
If TRUNCATE_WITH_AUTO_COMMIT is 'OFF' the result is completely dif
-
ferent; the ROLLBACK reverses the effects of the UPDATE and TRUNCATE
TABLE statements, and the two tables contain the original rows:
SET EXISTING OPTION PUBLIC.TRUNCATE_WITH_AUTO_COMMIT = 'OFF';
UPDATE t2 SET non_key_1 = 999;
TRUNCATE TABLE t1;
ROLLBACK;
Here is what the transaction log looks like when
TRUNCATE_WITH_AUTO_COMMIT is 'OFF':
BEGIN TRANSACTION
UPDATE DBA.t2
SET non_key_1=999
WHERE key_1=22
truncate table t1
ROLLBACK WORK
Not only is TRUNCATE TABLE often faster than DELETE when you want to
delete all the rows, you can also use it to speed up the deletion of large numbers
of rows even when you want to preserve some of them. A three-step technique
can be used: First, copy the rows you want to save into a temporary table, then
truncate the original table, and finally copy the saved rows back.
Here is an example of a table that was filled with 160M of data in 100,000
rows as part of a comparison of TRUNCATE TABLE with DELETE:
CREATE TABLE t1 (
key_1 INTEGER NOT NULL PRIMARY KEY,
inserted_date DATE NOT NULL DEFAULT CURRENT DATE,
blob LONG VARCHAR );
The following set-oriented DELETE took about one minute to delete 99.9% of
the rows:
DELETE t1
WHERE inserted_date < DATEADD ( DAY, -7, CURRENT DATE );
The following three statements performed exactly the same function in less than
half the time (27 seconds):
SELECT *
INTO #t1
FROM t1
WHERE inserted_date >= DATEADD ( DAY, -7, CURRENT DATE );
TRUNCATE TABLE t1;
INSERT t1
SELECT *
FROM #t1;
Note: If the server crashes (because of a power failure, for example) immedi
-
ately after the TRUNCATE TABLE in the example above, but before the final
INSERT t1 finishes and a COMMIT is done, you will need to restore the database
from a backup to recover the rows you want to keep. That’s because the rows
only exist in the temporary table and they won’t be there after recovery.
Chapter 5: Deleting
193
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
For more information about the SELECT INTO method of creating and filling a
temporary table, see Section 1.15.2.3, “SELECT INTO #table_name.” For more
information about using INSERT to copy data from one table to another, see
Section 2.2.3, “INSERT Select All Columns.”
Note: Performance tests described in this book are not intended to be
“benchmark quality,” just reasonably fair comparisons of different techniques.
The test above, for example, was run on a 933MHz Intel CPU with 512M of
cache running Windows 2000, and the sa_flush_cache procedure was called
before each test to ensure fairness.
5.7 Chapter Summary
This chapter described how to code simple DELETE statements that delete one
or more rows from a single table and explained how a DELETE involving a
multi-table join works. The full syntax of the set-oriented DELETE was
described, followed by the cursor-oriented DELETE WHERE CURRENT OF
and the TRUNCATE TABLE statement.
The next chapter turns to the subject of application logic written in SQL,
with a discussion of cursor fetch loops.
194 Chapter 5: Deleting
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 6
Fetching
6.1 Introduction
This chapter starts with an example of a cursor loop involving cursor
DECLARE, OPEN, FETCH, and CLOSE statements as well as DELETE
WHERE CURRENT OF. This example is shown in both SQL and C using
embedded SQL and comes with a step-by-step explanation of how it works.
The next five sections describe the syntax of the three formats of the cursor
DECLARE statement followed by the OPEN, CLOSE, and FETCH statements.
The last section describes the cursor FOR loop, which can be used to simplify
programming.
6.2 Cursor FETCH Loop
A cursor loop is a mechanism to deal with a multi-row result set one row at a
time. Depending on the cursor type, it is possible to move forward and back-
ward one or more rows, to move to a row at a specific position, and to update or
delete the current row. Cursor loops are often used in application programs,
either explicitly in the code or implicitly by the programming environment; for
example, a call to the PowerBuilder DataWindow Retrieve function might look
like a single operation but behind the scenes a cursor loop is used to fill the
DataWindow buffer.
A cursor loop may also be coded inside a SQL stored procedure or other
SQL programming block. It is constructed from several different SQL state
-
ments: some variable DECLARE statements, a WHILE loop, and statements to
DECLARE, OPEN, FETCH, and CLOSE a cursor. The following is an example
of a typical SQL cursor loop; this example is written to be short and simple
while at the same time serving a useful purpose: to delete old rows from a table,
limiting the total number of deletions to 1000 rows for each run and executing a
COMMIT after every 100 deletions.
BEGIN
DECLARE @key_1 INTEGER;
DECLARE @non_key_1 VARCHAR ( 100 );
DECLARE @last_updated TIMESTAMP;
DECLARE @SQLSTATE VARCHAR(5);
DECLARE @loop_counter INTEGER;
DECLARE c_fetch NO SCROLL CURSOR FOR
SELECT TOP 1000
t1.key_1,
t1.non_key_1,
195
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
t1.last_updated
FROM t1
WHERE t1.last_updated < DATEADD ( MONTH, -6, CURRENT DATE )
ORDER BY t1.last_updated
FOR UPDATE;
OPEN c_fetch WITH HOLD;
FETCH c_fetch INTO
@key_1,
@non_key_1,
@last_updated;
SET @SQLSTATE = SQLSTATE;
SET @loop_counter = 0;
WHILE @SQLSTATE = '00000' LOOP
SET @loop_counter = @loop_counter + 1;
MESSAGE STRING ( 'Deleting ',
@loop_counter, ', ',
@key_1, ', "',
@non_key_1, '", ',
@last_updated ) TO CONSOLE;
DELETE t1 WHERE CURRENT OF c_fetch;
IF MOD ( @loop_counter, 100)=0THEN
COMMIT;
MESSAGE STRING ( 'COMMIT after ', @loop_counter, ' rows.' ) TO CONSOLE;
END IF;
FETCH c_fetch INTO
@key_1,
@non_key_1,
@last_updated;
SET @SQLSTATE = SQLSTATE;
END LOOP;
CLOSE c_fetch;
COMMIT;
MESSAGE STRING ( 'Final COMMIT after ', @loop_counter, ' rows.' ) TO CONSOLE;
END;
In the example above, the first three local variables — @key_1, @non_key_1,
and @last_updated — are required to receive the column values returned by the
cursor SELECT via the FETCH statements. The @SQLSTATE variable is used
for checking the current state of execution, and @loop_counter is used to deter
-
mine when to do a COMMIT.
The cursor DECLARE statement gives a name to the cursor, c_fetch, and
uses the NO SCROLL keywords to indicate that the code won’t be moving
backward in the result set so SQL Anywhere is free to perform some kinds of
optimization. The SELECT retrieves rows that are at least six months old, sorts
them so the oldest rows appear first, and limits the number of rows returned to
1000. The FOR UPDATE keywords tell SQL Anywhere that the rows being
retrieved may be changed; in this case, they are going to be deleted.
The OPEN statement starts the process by actually executing the SELECT
defined in the cursor DECLARE. The WITH HOLD keywords tell SQL
196 Chapter 6: Fetching
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Anywhere to hold the cursor open when a COMMIT is executed rather than
implicitly closing the cursor.
The first FETCH statement retrieves the first row in the result set and cop
-
ies the column values into the three local variables. The subsequent SET
statement copies the value of SQLSTATE into the local variable @SQLSTATE.
This kind of assignment is good practice because many SQL statements change
SQLSTATE and this code only cares about the value set by the FETCH.
The WHILE statement starts the loop and runs it until there are no more
rows; at that point @SQLSTATE will contain '02000'. The first MESSAGE
statement inside the loop displays the current row.
The DELETE statement deletes the current row. For more information
about the DELETE WHERE CURRENT OF cursor statement, see Section 5.5.
The IF statement after the DELETE shows how to use the MOD function to
determine when multiples of 100 rows have been reached. MOD divides the
first parameter by the second and returns the remainder; when the first parame
-
ter is exactly divisible by the second, the remainder is zero, so MOD
( @loop_counter, 100)=0when @loop_counter is 100, 200, 300, and so on.
The next FETCH statement returns the second or later rows and fills in the
three local variable with new column values. Eventually this FETCH will set
SQLSTATE to '02000' for “row not found.” After the loop ends, the cursor is
closed and final COMMIT and MESSAGE statements are executed.
Here are the last few lines of MESSAGE output from the cursor loop
above:
Deleting 998, 9003, "", 1979-05-11 10:04:07.389
Deleting 999, 9002, "", 1979-05-12 10:04:07.389
Deleting 1000, 9001, "", 1979-05-13 10:04:07.389
COMMIT after 1000 rows.
Done after 1000 rows.
Here is the same loop again, this time coded as a standalone C program using
embedded SQL:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "sqldef.h"
EXEC SQL INCLUDE SQLCA;
int main() {
EXEC SQL BEGIN DECLARE SECTION;
long key_1;
char non_key_1 [ 101 ];
char last_updated [ 24 ];
EXEC SQL END DECLARE SECTION;
char copy_SQLSTATE[6];
long loop_counter;
ldiv_t loop_counter_ldiv;
db_init( &sqlca );
EXEC SQL CONNECT USING 'ENG=test6;DBN=test6;UID=DBA;PWD=SQL';
EXEC SQL DECLARE c_fetch NO SCROLL CURSOR FOR
SELECT TOP 1000
Chapter 6: Fetching
197
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
t1.key_1,
t1.non_key_1,
DATEFORMAT ( t1.last_updated, 'yyyy-mm-dd hh:nn:ss.sss' )
FROM t1
WHERE t1.last_updated < DATEADD ( MONTH, -6, CURRENT DATE )
ORDER BY t1.last_updated
FOR UPDATE;
EXEC SQL OPEN c_fetch WITH HOLD;
EXEC SQL FETCH c_fetch INTO
:key_1,
:non_key_1,
:last_updated;
strcpy ( copy_SQLSTATE, SQLSTATE );
loop_counter = 0;
while ( strcmp ( copy_SQLSTATE, "00000" ) ==0){
loop_counter = loop_counter + 1;
printf ( "Deleting %d, %d, '%s', %s\n",
loop_counter,
key_1,
non_key_1,
last_updated );
EXEC SQL DELETE t1 WHERE CURRENT OF c_fetch;
loop_counter_ldiv = ldiv ( loop_counter, 100L );
if ( loop_counter_ldiv.rem ==0){
EXEC SQL COMMIT;
printf ( "COMMIT after %d rows.\n", loop_counter );
}
EXEC SQL FETCH c_fetch INTO
:key_1,
:non_key_1,
:last_updated;
strcpy ( copy_SQLSTATE, SQLSTATE );
} // while
EXEC SQL CLOSE c_fetch;
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
db_fini ( &sqlca );
printf ( "Done after %d rows.\n", loop_counter );
return(0);
} // main
Note: This book doesn’t cover embedded SQL in any great detail. The exam
-
ple above has been included because cursor fetch loops are very common in
applications using various forms of embedded SQL statements, and the C ver
-
sion is representative of embedded SQL syntax found in other development
environments, even PowerBuilder.
The next sections discuss the syntax of each component of a cursor fetch loop in
detail.
198 Chapter 6: Fetching
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
6.2.1 DECLARE CURSOR FOR Select
A cursor may be defined as a select, as a USING clause referencing a string
variable that contains a select, or as a procedure CALL.
<declare_cursor> ::= <declare_cursor_for_select>
| <declare_cursor_using_select>
| <declare_cursor_for_call>
Here is the syntax for the first format:
<declare_cursor_for_select> ::= DECLARE <cursor_for_select>
<cursor_for_select> ::= <cursor_name>
[ <cursor_type> ]
CURSOR FOR
<select>
<cursor_name> ::= <identifier> defined in a cursor DECLARE or FOR command
<identifier> ::= see <identifier> in Chapter 1, “Creating”
<cursor_type> ::= NO SCROLL asensitive
| DYNAMIC SCROLL asensitive; default
| SCROLL value-sensitive, keyset-driven
| INSENSITIVE insensitive
| SENSITIVE sensitive
<select> ::= [ <with_clause> ]
<query_expression>
[ <order_by_clause> ]
[ <for_intent_clause> ]
<for_intent_clause> ::= FOR READ ONLY
| FOR UPDATE
<with_clause> ::= see <with_clause> in Chapter 3, “Selecting”
<query_expression> ::= see <query_expression> in Chapter 3, “Selecting”
<order_by_clause> ::= see <order_by_clause> in Chapter 3, “Selecting”
The various clauses of a cursor DECLARE control the two main stages in the
life cycle of a cursor: The WITH clause, the query expression, and the ORDER
BY clause specify what the cursor result set looks like when the OPEN state-
ment is executed, and the <cursor_type> and <for_intent_clause> specify how
the result set behaves as it is subsequently fetched and processed in the cursor
loop.
Even though the cursor DECLARE statement contains many elements that
specify executable behavior, it is not itself an executable statement. Each cursor
DECLARE must appear at the beginning of the BEGIN block before any exe
-
cutable statements. More than one cursor may be declared and used within one
block, but each cursor name must be unique within that block.
The WITH clause, query expression, and ORDER BY clause are all
described in Chapter 3, “Selecting.”
The <cursor_type> indirectly specifies defaults for the following three cur
-
sor attributes:
n
Scrollability controls the order in which rows can be fetched; in particular,
it controls whether an earlier row can be fetched again after a later row has
been fetched.
n
Updatability controls whether or not UPDATE WHERE CURRENT OF
and DELETE WHERE CURRENT OF statements can be used with this
cursor, as well as the PUT statement in embedded SQL. Note that
UPDATE, DELETE, and INSERT statements that operate directly on the
underlying tables, without referring to the cursor by name, are always pos
-
sible whether or not the cursor is updatable.
Chapter 6: Fetching
199
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét