📄 desc_index.out
字号:
Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 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> -- test if bulk insert rebuilds desc index rightcall SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB1', 0);0 rows inserted/updated/deletedij> select * from tab1 order by c1 desc;C1 |C2 |C3 |C4 |C5 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------100 |11 |9.0 |34sfg |ayupo 76 |2 |-9.86 |yudf |45gd 56 |-3 |6.7 |dfgs |fds 55 |44 |-9.85 |yudd |df89f 34 |66 |1.2 |yty |wer 34 |68 |2.7 |srg |iur 34 |67 |5.3 |rtgd |hds 8 |12 |5.6 |dfg |ghji -78 |45 |-5.6 |jakdsfh |df89g -100 |93 |9.1 |egfh |s6j ij> -- this tests multiple indexes share one conglomerate if they essentially-- are the samecreate table tab2 (c1 int not null primary key, c2 int, c3 int);0 rows inserted/updated/deletedij> -- not unique index, shouldn't share with primary key's indexcreate index i21 on tab2(c1);0 rows inserted/updated/deletedWARNING 01504: The new index is a duplicate of an existing index: xxxxGENERATED-IDxxxx.ij> -- desc index, shouldn't share with primary key's indexcreate index i22 on tab2(c1 desc);0 rows inserted/updated/deletedij> -- this should share with primary key's index, and give a warningcreate unique index i23 on tab2(c1);0 rows inserted/updated/deletedWARNING 01504: The new index is a duplicate of an existing index: xxxxGENERATED-IDxxxx.ij> create index i24 on tab2(c1, c3 desc);0 rows inserted/updated/deletedij> -- this should share with i24's conglomeratecreate index i25 on tab2(c1, c3 desc);0 rows inserted/updated/deletedWARNING 01504: The new index is a duplicate of an existing index: I24.ij> -- no sharecreate index i26 on tab2(c1, c3);0 rows inserted/updated/deletedij> insert into tab2 values (6, 2, 8), (2, 8, 5), (28, 5, 9), (3, 12, 543);4 rows inserted/updated/deletedij> create index i27 on tab2 (c1, c2 desc, c3);0 rows inserted/updated/deletedij> -- no sharecreate index i28 on tab2 (c1, c2 desc, c3 desc);0 rows inserted/updated/deletedij> -- share with i27create index i29 on tab2 (c1, c2 desc, c3);0 rows inserted/updated/deletedWARNING 01504: The new index is a duplicate of an existing index: I27.ij> create index i20 on tab2 (c1, c2 desc, c3);0 rows inserted/updated/deletedWARNING 01504: The new index is a duplicate of an existing index: I27.ij> insert into tab2 values (56, 2, 7), (31, 5, 7), (-12, 5, 2);3 rows inserted/updated/deletedij> select count(distinct conglomeratenumber) from sys.sysconglomerates where tableid = (select tableid from sys.systables where tablename = 'TAB2');1 -----------7 ij> select * from tab2;C1 |C2 |C3 ------------------------------------12 |5 |2 2 |8 |5 3 |12 |543 6 |2 |8 28 |5 |9 31 |5 |7 56 |2 |7 ij> values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'TAB2');1 -----------1 ij> -- see if rebuild indexes correctlycall SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB2', 0);0 rows inserted/updated/deletedij> select count(distinct conglomeratenumber) from sys.sysconglomerates where tableid = (select tableid from sys.systables where tablename = 'TAB2');1 -----------7 ij> select * from tab2;C1 |C2 |C3 ------------------------------------12 |5 |2 2 |8 |5 3 |12 |543 6 |2 |8 28 |5 |9 31 |5 |7 56 |2 |7 ij> update tab2 set c2 = 11 where c3 = 7;2 rows inserted/updated/deletedij> select * from tab2;C1 |C2 |C3 ------------------------------------12 |5 |2 2 |8 |5 3 |12 |543 6 |2 |8 28 |5 |9 31 |11 |7 56 |11 |7 ij> delete from tab2 where c2 > 10 and c2 < 12;2 rows inserted/updated/deletedij> select * from tab2;C1 |C2 |C3 ------------------------------------12 |5 |2 2 |8 |5 3 |12 |543 6 |2 |8 28 |5 |9 ij> values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'TAB2');1 -----------1 ij> -- drop indexesdrop index i22;0 rows inserted/updated/deletedij> drop index i24;0 rows inserted/updated/deletedij> drop index i26;0 rows inserted/updated/deletedij> drop index i28;0 rows inserted/updated/deletedij> drop index i20;ERROR 42X65: Index 'I20' does not exist.ij> select count(distinct conglomeratenumber) from sys.sysconglomerates where tableid = (select tableid from sys.systables where tablename = 'TAB2');1 -----------3 ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB2', 0);0 rows inserted/updated/deletedij> select * from tab2;C1 |C2 |C3 ------------------------------------12 |5 |2 2 |8 |5 3 |12 |543 6 |2 |8 28 |5 |9 ij> values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'TAB2');1 -----------1 ij> drop index i21;ERROR 42X65: Index 'I21' does not exist.ij> drop index i23;ERROR 42X65: Index 'I23' does not exist.ij> drop index i25;ERROR 42X65: Index 'I25' does not exist.ij> drop index i27;0 rows inserted/updated/deletedij> drop index i29;ERROR 42X65: Index 'I29' does not exist.ij> select count(distinct conglomeratenumber) from sys.sysconglomerates where tableid = (select tableid from sys.systables where tablename = 'TAB2');1 -----------2 ij> values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'TAB2');1 -----------1 ij> -- beetle 4974create table b4974 (a BIGINT, b BIGINT, c INT, d CHAR(16), e BIGINT);0 rows inserted/updated/deletedij> create index i4974 on b4974(a, d, c, e);0 rows inserted/updated/deletedij> SELECT b from b4974 t1where (T1.a = 10127 or T1.a = 0) and (T1.d = 'ProductBean' or T1.d = 'CatalogEntryBean') and (T1.e =0 or T1.e = 0);B --------------------ij> rollback;ij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -