📄 desc_index.out
字号:
ij> -- this also tests multiple indexes share one conglomerate if they essentially-- are the sameautocommit off;ij> create table tab1 (c1 int, c2 smallint, c3 double precision, c4 varchar(30), c5 varchar(1024));0 rows inserted/updated/deletedij> insert into tab1 values (8, 12, 5.6, 'dfg', 'ghji');1 row inserted/updated/deletedij> insert into tab1 values (76, 2, -9.86, 'yudf', '45gd');1 row inserted/updated/deletedij> insert into tab1 values (-78, 45, -5.6, 'jakdsfh', 'df89g');1 row inserted/updated/deletedij> insert into tab1 values (56, -3, 6.7, 'dfgs', 'fds');1 row inserted/updated/deletedij> create index i1 on tab1 (c1, c3, c4);0 rows inserted/updated/deletedij> create index i2 on tab1 (c1 desc, c3 desc, c4 desc);0 rows inserted/updated/deletedij> create index i3 on tab1 (c1 desc, c3 asc, c4 desc);0 rows inserted/updated/deletedij> create index i4 on tab1 (c2 desc, c3, c1);0 rows inserted/updated/deletedij> create index i5 on tab1 (c1, c2 desc);0 rows inserted/updated/deletedij> insert into tab1 values (34, 67, 5.3, 'rtgd', 'hds');1 row inserted/updated/deletedij> insert into tab1 values (100, 11, 9.0, '34sfg', 'ayupo');1 row inserted/updated/deletedij> insert into tab1 values (-100, 93, 9.1, 'egfh', 's6j');1 row inserted/updated/deletedij> insert into tab1 values (55, 44, -9.85, 'yudd', 'df89f');1 row inserted/updated/deletedij> insert into tab1 values (34, 68, 2.7, 'srg', 'iur');1 row inserted/updated/deletedij> insert into tab1 values (34, 66, 1.2, 'yty', 'wer');1 row inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);0 rows inserted/updated/deletedij> maximumdisplaywidth 20000;ij> -- should use index i4select c1, c3 from tab1 where c2 > 40 and c3 <= 5.3;C1 |C3 ----------------------------------34 |2.7 34 |5.3 34 |1.2 -78 |-5.6 55 |-9.85 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();1 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Statement Name: nullStatement Text: -- should use index i4select c1, c3 from tab1 where c2 > 40 and c3 <= 5.3Parse 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 (3):Number of opens = 1Rows seen = 5Rows 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: Project-Restrict ResultSet (2): Number of opens = 1 Rows seen = 5 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 Scan ResultSet for TAB1 using index I4 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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, 2} Number of columns fetched=3 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=5 Number of rows visited=7 Scan type=btree Tree height=1 start position: None stop position: >= on first 1 column(s). Ordered null semantics on the following columns: qualifiers:Column[0][0] Id: 1Operator: <=Ordered nulls: falseUnknown return value: falseNegate comparison result: falseij> -- should use index i5select c2, c1 from tab1 where c2 <= 44 and c1 > 55;C2 |C1 -------------------3 |56 2 |76 11 |100 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();1 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Statement Name: nullStatement Text: -- should use index i5select c2, c1 from tab1 where c2 <= 44 and c1 > 55Parse 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 (2):Number of opens = 1Rows seen = 3Rows 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: Index Scan ResultSet for TAB1 using index I5 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 3 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 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=3 Number of rows visited=3 Scan type=btree Tree height=1 start position: > on first 1 column(s). Ordered null semantics on the following columns: stop position: None qualifiers:Column[0][0] Id: 1Operator: <=Ordered nulls: falseUnknown return value: falseNegate comparison result: falseij> -- should use i1select c1, c3, c4 from tab1 order by c1, c3;C1 |C3 |C4 ------------------------------------------------------------------100 |9.1 |egfh -78 |-5.6 |jakdsfh 8 |5.6 |dfg 34 |1.2 |yty 34 |2.7 |srg ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -