收藏为你点赞

第七十七章 SQL命令 TRUNCATE TABLE

2021-11-16  本文已影响0人  Cache技术分享

第七十七章 SQL命令 TRUNCATE TABLE

从表中删除所有数据并重置计数器。

大纲

TRUNCATE TABLE [restriction] tablename

参数

描述

TRUNCATE TABLE命令从表中删除所有行，并重置所有表计数器。
可以直接截断表，也可以通过视图截断表。
通过视图截断表会受到删除要求和限制，如CREATE view中所述。

TRUNCATE TABLE重置用于生成RowID字段、IDENTITY字段和SERIAL(%Library.Counter)字段连续整数值的内部计数器。
IRIS为插入到TRUNCATE表后的表中的第一行中的这些字段赋值为1。
对表的所有行执行DELETE操作不会重置这些内部计数器。

TRUNCATE TABLE重置用于在数据插入到流字段时生成流字段OID值的内部计数器。
对表的所有行执行DELETE操作不会重置此内部计数器。

TRUNCATE TABLE总是将%ROWCOUNT本地变量设置为-1;
它没有将%ROWCOUNT设置为删除的行数。

TRUNCATE TABLE不会重置ROWVERSION计数器。

TRUNCATE TABLE禁止提取基表触发器，否则在DELETE处理期间提取基表触发器。
因为TRUNCATE TABLE执行的是带有%NOTRIGGER行为的删除，用户必须被授予%NOTRIGGER权限(使用GRANT语句)才能运行TRUNCATE TABLE。
TRUNCATE TABLE的这方面在功能上是相同的:

DELETE %NOTRIGGER FROM tablename

注意:DELETE命令也可以用来删除表中的所有行。
DELETE提供了比TRUNCATE TABLE更多的功能，包括返回%ROWCOUNT中已删除的行数。
DELETE不会重置内部计数器。

TRUNCATE TABLE为从其他数据库软件迁移代码提供了兼容性。

截断一个表:

如果表被其他进程以EXCLUSIVE模式或SHARE模式锁定，则TRUNCATE TABLE失败。
试图在一个锁定的表上执行TRUNCATE TABLE操作将导致SQLCODE -110错误，并带有%msg，如下所示:
MyStuff' on row with RowID = '3'(其中指定的RowID是表中的第一行)。

如果删除一行会违反外键引用完整性，那么TRUNCATE TABLE将失败。
未删除任何行，因此TRUNCATE TABLE发出SQLCODE -124错误。
这个默认行为是可以修改的，如下所述。

Fast Truncate

如果可能，SQL优化器将执行高效的Fast Truncate表操作。
Fast Truncate操作删除表的范围，而不是单独删除每条记录。
在可能的情况下，快速截断将自动应用。
当无法实现快速截断时，将执行标准的Truncate TABLE操作。

注意:如果没有删除行，或者使用Fast TRUNCATE删除行，则TRUNCATE TABLE不会初始化或设置%ROWID。
因此，应该避免在TRUNCATE表之后使用%ROWID值。

Fast Truncate 限制

快速截断可以应用于标准表或分片表。

不能应用快速截断:

Atomicity

TRUNCATE TABLE不会在自动启动的事务中发生，因此不提供日志记录或回滚选项。

如果需要日志记录和回滚TRUNCATE TABLE选项，则必须显式指定START TRANSACTION，并以显式COMMIT或rollback结束。

这与SET TRANSACTION %COMMITMODE= NONE或0(没有自动事务)相同——调用TRUNCATE TABLE时不会启动任何事务。
失败的TRUNCATE TABLE操作可能会使数据库处于不一致的状态，一些行被删除，一些行没有被删除。
要在此模式中提供事务支持，必须使用START transaction来启动事务，并使用COMMIT或ROLLBACK来结束事务。

分片表的TRUNCATE TABLE总是使用SET TRANSACTION %COMMITMODE NONE执行，即使用户显式地设置了SET TRANSACTION %COMMITMODE EXPLICIT。

限制参数

要使用constraint参数，必须拥有当前名称空间对应的admin-privilege。

指定约束参数限制处理如下:

可以以任何顺序指定多个限制参数。
多个参数由空格分隔。

如果在删除父记录时指定了约束参数，则在删除相应的子记录时将应用相同的约束参数。

TRUNCATE TABLE总是使用隐式的%NOTRIGGER行为执行删除操作，并且需要相应的admin-privilege。

参照完整性

IRIS使用系统范围的配置设置来确定是否执行外键引用完整性检查;
默认值是执行外键引用完整性检查。
可以在系统范围内设置此默认值，如外键引用完整性检查中所述。
要确定当前系统范围的设置，调用$SYSTEM.SQL.CurrentSettings()。

在TRUNCATE TABLE操作期间，对于每个外键引用，都会在引用表中相应的行上获得一个共享锁。
这一行将被锁定，直到事务结束。
这确保了在可能的TRUNCATE表回滚之前不会更改引用的行。

事务锁

IRIS对TRUNCATE TABLE操作执行标准锁定。
唯一的字段值在当前事务期间被锁定。

默认的锁阈值是每个表1000个锁。
这意味着，如果在事务期间从表中删除超过1000个惟一字段值，就会达到锁阈值，IRIS会自动将锁级别从惟一字段值锁提升到表锁。
这允许在事务期间进行大规模删除，而不会溢出锁表。

可以使用$SYSTEM.SQL.Util.GetOption(“LockThreshold”)方法确定当前系统范围的锁阈值。
这个系统范围的锁阈值是可配置的:

需要在“%Admin Manage Resource”中具有“USE”权限才能修改锁定阈值。
IRIS会立即将对锁阈值的任何更改应用到所有当前进程。

Imported SQL代码

ImportDDL("IRIS")和Run()方法不支持TRUNCATE TABLE命令。
在这些方法导入的SQL代码文件中发现的TRUNCATE TABLE命令将被忽略。
这些导入方法确实支持DELETE命令。

示例

下面两个动态SQL示例比较了DELETE和TRUNCATE表。
每个示例都创建一个表，向表中插入行，删除表中的所有行，然后向现在为空的表中插入一行。

第一个示例使用DELETE删除表中的所有记录。
注意，DELETE不会重置RowID计数器:

ClassMethod TruncateTable()
{
    s tcreate = "CREATE TABLE SQLUser.MyStudents1 (StudentName VARCHAR(32),StudentDOB DATE)"
    s tinsert = "INSERT INTO SQLUser.MyStudents1 (StudentName,StudentDOB) "_
                "SELECT Name,DOB FROM Sample.Person WHERE Age <= '21'"
    s tinsert1 = "INSERT INTO SQLUser.MyStudents1 (StudentName,StudentDOB) VALUES ('Bob Jones',60123)"
    s tdelete = "DELETE SQLUser.MyStudents1"
    s tStatement = ##class(%SQL.Statement).%New()
    s qStatus = tStatement.%Prepare(tcreate)
    if qStatus '= 1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    w rset.%StatementTypeName,!

    n %ROWCOUNT,%ROWID
    s qStatus = tStatement.%Prepare(tinsert)
    if qStatus '= 1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    w rset.%StatementTypeName," rowcount ",rset.%ROWCOUNT,!

    s qStatus = tStatement.%Prepare(tdelete)
    if qStatus '= 1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    w rset.%StatementTypeName," rowcount ",rset.%ROWCOUNT,!

    s qStatus = tStatement.%Prepare(tinsert1)
    if qStatus '= 1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    w rset.%StatementTypeName," rowcount ",rset.%ROWCOUNT," RowID ",rset.%ROWID,!
    &sql(DROP TABLE SQLUser.MyStudents1)
}

DHC-APP>d ##class(PHA.TEST.SQLCommand).TruncateTable()
CREATE TABLE
INSERT rowcount 41
DELETE rowcount 41
INSERT rowcount 1 RowID 42

第二个示例使用TRUNCATE TABLE删除表中的所有记录。
注意，%StatementTypeName对TRUNCATE表返回“DELETE”。
注意，TRUNCATE TABLE会重置RowID计数器:

ClassMethod TruncateTable1()
{
    s tcreate = "CREATE TABLE SQLUser.MyStudents2 (StudentName VARCHAR(32),StudentDOB DATE)"
    s tinsert = "INSERT INTO SQLUser.MyStudents2 (StudentName,StudentDOB) "_
    "SELECT Name,DOB FROM Sample.Person WHERE Age <= '21'"
    s tinsert1 = "INSERT INTO SQLUser.MyStudents2 (StudentName,StudentDOB) VALUES ('Bob Jones',60123)"
    s ttrunc = "TRUNCATE TABLE SQLUser.MyStudents2"
    s tStatement = ##class(%SQL.Statement).%New()
    s qStatus = tStatement.%Prepare(tcreate)
    if qStatus '= 1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    w rset.%StatementTypeName,!

    n %ROWCOUNT,%ROWID
    s qStatus = tStatement.%Prepare(tinsert)
    if qStatus '= 1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    w rset.%StatementTypeName," rowcount ",rset.%ROWCOUNT,!

    s qStatus = tStatement.%Prepare(ttrunc)
    if qStatus '= 1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    w rset.%StatementTypeName," (TRUNCATE TABLE) rowcount ",rset.%ROWCOUNT,!

    s qStatus = tStatement.%Prepare(tinsert1)
    if qStatus '= 1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    w rset.%StatementTypeName," rowcount ",rset.%ROWCOUNT," RowID ",rset.%ROWID,!
    //&sql(DROP TABLE SQLUser.MyStudents2)
}
上一篇 下一篇

猜你喜欢

热点阅读