📄 staleplans.out
字号:
Number of pages visited=1 Number of rows qualified=1 Number of rows visited=2 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers:Noneij> commit;ij> -- Now do the same thing with a table that has many rowscreate table t2 (c1 int, c2 int, c3 varchar(255));0 rows inserted/updated/deletedij> commit;ij> create index t2x on t2(c1);0 rows inserted/updated/deletedij> commit;ij> insert into t2 values (1, 1, PADSTRING('abc',255));1 row inserted/updated/deletedij> insert into t2 select c1 + 1, c2 + 1, c3 from t2;1 row inserted/updated/deletedij> insert into t2 select c1 + 2, c2 + 2, c3 from t2;2 rows inserted/updated/deletedij> insert into t2 select c1 + 4, c2 + 4, c3 from t2;4 rows inserted/updated/deletedij> insert into t2 select c1 + 8, c2 + 8, c3 from t2;8 rows inserted/updated/deletedij> insert into t2 select c1 + 16, c2 + 16, c3 from t2;16 rows inserted/updated/deletedij> insert into t2 select c1 + 32, c2 + 32, c3 from t2;32 rows inserted/updated/deletedij> insert into t2 select c1 + 64, c2 + 64, c3 from t2;64 rows inserted/updated/deletedij> insert into t2 select c1 + 128, c2 + 128, c3 from t2;128 rows inserted/updated/deletedij> insert into t2 select c1 + 256, c2 + 256, c3 from t2;256 rows inserted/updated/deletedij> insert into t2 select c1 + 512, c2 + 512, c3 from t2;512 rows inserted/updated/deletedij> commit;ij> -- Make sure row count from inserts is flushed outselect count(c1) from flusher;1 -----------64 ij> prepare s2 as 'select count(c1 + c2) from t2 where c1 = 1';ij> execute s2;1 -----------1 ij> -- Expect this to use indexvalues SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();1 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Statement Name: nullStatement Text: select count(c1 + c2) from t2 where c1 = 1Parse Time: 0Bind Time: 0Optimize Time: 0Generate Time: 0Compile Time: 0Execute Time: 0Begin Compilation Timestamp : nullEnd Compilation Timestamp : nullBegin Execution Timestamp : nullEnd Execution Timestamp : nullStatement Execution Plan Text: Project-Restrict ResultSet (5):Number of opens = 1Rows seen = 1Rows filtered = 0restriction = falseprojection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0Source result set: Scalar Aggregate ResultSet: Number of opens = 1 Rows input = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Key Optimization = false Source result set: Project-Restrict ResultSet (4): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Index Row to Base Row ResultSet for T2: Number of opens = 1 Rows seen = 1 Columns accessed from heap = {0, 1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T2 using index T2X at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=2 Number of rows qualified=1 Number of rows visited=2 Scan type=btree Tree height=2 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers:Noneij> commit;ij> -- Change the row count a little bitinsert into t2 values (1025, 1025, PADSTRING('abc',255));1 row inserted/updated/deletedij> insert into t2 values (1026, 1026, PADSTRING('abc',255));1 row inserted/updated/deletedij> insert into t2 values (1027, 1027, PADSTRING('abc',255));1 row inserted/updated/deletedij> insert into t2 values (1028, 1028, PADSTRING('abc',255));1 row inserted/updated/deletedij> insert into t2 values (1029, 1029, PADSTRING('abc',255));1 row inserted/updated/deletedij> insert into t2 values (1030, 1030, PADSTRING('abc',255));1 row inserted/updated/deletedij> insert into t2 values (1031, 1031, PADSTRING('abc',255));1 row inserted/updated/deletedij> insert into t2 values (1032, 1032, PADSTRING('abc',255));1 row inserted/updated/deletedij> insert into t2 values (1033, 1033, PADSTRING('abc',255));1 row inserted/updated/deletedij> insert into t2 values (1034, 1034, PADSTRING('abc',255));1 row inserted/updated/deletedij> commit;ij> -- Change the data so a table scan would make more sense.-- Use a qualifier to convince TableScanResultSet not to-- update the row count in the store (which would make it-- hard for this test to control when recompilation takes-- place).update t2 set c1 = 1 where c1 > 0;1034 rows inserted/updated/deletedij> -- Make sure row count from inserts is flushed outselect count(c1) from flusher;1 -----------64 ij> -- Execute 11 more times, the plan should not changeexecute s2;1 -----------1034 ij> execute s2;1 -----------1034 ij> execute s2;1 -----------1034 ij> execute s2;1 -----------1034 ij> execute s2;1 -----------1034 ij> execute s2;1 -----------1034 ij> execute s2;1 -----------1034 ij> execute s2;1 -----------1034 ij> execute s2;1 -----------1034 ij> execute s2;1 -----------1034 ij> execute s2;1 -----------1034 ij> -- Expect this to use tables scan, as the above update has basically made-- all the rows in the table be equal to "1", thus using the index does not-- help if all the rows are going to qualify.values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();1 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Statement Name: nullStatement Text: select count(c1 + c2) from t2 where c1 = 1Parse Time: 0Bind Time: 0Optimize Time: 0Generate Time: 0Compile Time: 0Execute Time: 0Begin Compilation Timestamp : nullEnd Compilation Timestamp : nullBegin Execution Timestamp : nullEnd Execution Timestamp : nullStatement Execution Plan Text: Project-Restrict ResultSet (4):Number of opens = 1Rows seen = 1Rows filtered = 0restriction = falseprojection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0Source result set: Scalar Aggregate ResultSet: Number of opens = 1 Rows input = 1034 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Key Optimization = false Source result set: Project-Restrict ResultSet (3): Number of opens = 1 Rows seen = 1034 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for T2 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1034 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1} Number of columns fetched=2⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -