📄 partdml.out
字号:
ij> ---- Test partial row access for update and delete--maximumdisplaywidth 2000;ij> drop function getScanCols;ERROR 42Y55: 'DROP FUNCTION' cannot be performed on 'GETSCANCOLS' because it does not exist.ij> drop table basic;ERROR 42Y55: 'DROP TABLE' cannot be performed on 'BASIC' because it does not exist.ij> drop table p;ERROR 42Y55: 'DROP TABLE' cannot be performed on 'P' because it does not exist.ij> CREATE FUNCTION getScanCols(value VARCHAR(32672)) RETURNS VARCHAR (32672) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.StatParser.getScanCols' LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL;0 rows inserted/updated/deletedij> create table p (ccharForBitData char(1) for bit data not null, cdec dec(6,2) not null, unindexed smallint, cchar char(10) not null, constraint pk1 primary key (cchar, ccharForBitData), constraint pk2 unique (cdec));0 rows inserted/updated/deletedij> insert into p values (x'00', 0.0, 11, '00');1 row inserted/updated/deletedij> insert into p values (x'11', 1.1, 22, '11');1 row inserted/updated/deletedij> insert into p values (x'22', 2.2, 33, '22');1 row inserted/updated/deletedij> insert into p values (x'33', 3.3, 44, '33');1 row inserted/updated/deletedij> create table basic (cint int, cchar char(10), ctime time, cdec dec(6,2), ccharForBitData char(1) for bit data, unindexed int);0 rows inserted/updated/deletedij> create index b1 on basic (cchar, ccharForBitData, cint);0 rows inserted/updated/deletedij> create index b2 on basic (ctime);0 rows inserted/updated/deletedij> create index b3 on basic (ctime, cint);0 rows inserted/updated/deletedij> create index b4 on basic (cint);0 rows inserted/updated/deletedij> maximumdisplaywidth 200;ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);0 rows inserted/updated/deletedij> -- the extra 33s are so we can ensure we'll use an index when looking for the othersinsert into basic values (11, '11', TIME('11:11:11'), 1.1, x'11', 11);1 row inserted/updated/deletedij> insert into basic values (22, '22', TIME('22:22:22'), 2.2, x'22', 22);1 row inserted/updated/deletedij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33);1 row inserted/updated/deletedij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33);1 row inserted/updated/deletedij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33);1 row inserted/updated/deletedij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33);1 row inserted/updated/deletedij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33);1 row inserted/updated/deletedij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33);1 row inserted/updated/deletedij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33);1 row inserted/updated/deletedij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33);1 row inserted/updated/deletedij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33);1 row inserted/updated/deletedij> insert into basic values (33, '33', TIME('03:33:33'), 3.3, x'33', 33);1 row inserted/updated/deletedij> -- simple update of each columnupdate basic set cint = cint;12 rows inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------TableScanBit set of columns fetched={0, 1, 2, 4}ij> update basic set cchar = cchar;12 rows inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for BASIC using index B1 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched={0, 1, 2, 3}ij> update basic set ctime = ctime;12 rows inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for BASIC using index B3 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched={0, 1, 2}ij> update basic set cdec = cdec;12 rows inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------TableScanBit set of columns fetched={3}ij> update basic set ccharForBitData = ccharForBitData;12 rows inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for BASIC using index B1 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched={0, 1, 2, 3}ij> update basic set unindexed = unindexed;12 rows inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------TableScanBit set of columns fetched={5}ij> -- confirm the table is okselect ccharForBitData, ctime, cdec, cint, cchar from basic;CCH&|CTIME |CDEC |CINT |CCHAR ----------------------------------------------11 |11:11:11|1.10 |11 |11 22 |22:22:22|2.20 |22 |22 33 |03:33:33|3.30 |33 |33 33 |03:33:33|3.30 |33 |33 33 |03:33:33|3.30 |33 |33 33 |03:33:33|3.30 |33 |33 33 |03:33:33|3.30 |33 |33 33 |03:33:33|3.30 |33 |33 33 |03:33:33|3.30 |33 |33 33 |03:33:33|3.30 |33 |33 33 |03:33:33|3.30 |33 |33 33 |03:33:33|3.30 |33 |33 ij> values (SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'BASIC'));1 -----------1 ij> update basic set cint = cint where cint = 11;1 row inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched=All ij> update basic set cchar = cchar where cint = 11;1 row inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched=All ij> update basic set ctime = ctime where cint = 11;1 row inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched=All ij> update basic set ctime = ctime where cint = 11;1 row inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched=All ij> update basic set cdec = cdec where cint = 11;1 row inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -