📄 distinctelimination.out
字号:
ij> -- test distinct eliminationset isolation to rr;0 rows inserted/updated/deletedij> -- eliminate distincts based on a uniqueness condition-- create tablescreate table one(c1 int, c2 int, c3 int, c4 int, c5 int);0 rows inserted/updated/deletedij> create unique index one_c1 on one(c1);0 rows inserted/updated/deletedij> create table two(c1 int, c2 int, c3 int, c4 int, c5 int);0 rows inserted/updated/deletedij> create unique index two_c1c3 on two(c1, c3);0 rows inserted/updated/deletedij> create table three(c1 int, c2 int, c3 int, c4 int, c5 int);0 rows inserted/updated/deletedij> create unique index three_c1 on three(c1);0 rows inserted/updated/deletedij> create table four(c1 int, c2 int, c3 int, c4 int, c5 int);0 rows inserted/updated/deletedij> create unique index four_c1c3 on four(c1, c3);0 rows inserted/updated/deletedij> CREATE TABLE "APP"."IDEPT" ("DISCRIM_DEPT" VARCHAR(32), "NO1" INTEGER NOT NULL, "NAME" VARCHAR(50), "AUDITOR_NO" INTEGER, "REPORTTO_NO" INTEGER, "HARDWAREASSET" VARCHAR(15), "SOFTWAREASSET" VARCHAR(15));0 rows inserted/updated/deletedij> -- primary/uniqueALTER TABLE "APP"."IDEPT" ADD CONSTRAINT "PK_IDEPT" PRIMARY KEY ("NO1");0 rows inserted/updated/deletedij> insert into one values (1, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into one values (2, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into one values (3, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into one values (4, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into one values (5, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into one values (6, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into one values (7, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into one values (8, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into two values (1, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into two values (1, 1, 2, 1, 1);1 row inserted/updated/deletedij> insert into two values (1, 1, 3, 1, 1);1 row inserted/updated/deletedij> insert into two values (2, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into two values (2, 1, 2, 1, 1);1 row inserted/updated/deletedij> insert into two values (2, 1, 3, 1, 1);1 row inserted/updated/deletedij> insert into two values (3, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into two values (3, 1, 2, 1, 1);1 row inserted/updated/deletedij> insert into two values (3, 1, 3, 1, 1);1 row inserted/updated/deletedij> insert into three values (1, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into three values (2, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into three values (3, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into three values (4, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into three values (5, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into three values (6, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into three values (7, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into three values (8, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into four values (1, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into four values (1, 1, 2, 1, 1);1 row inserted/updated/deletedij> insert into four values (1, 1, 3, 1, 1);1 row inserted/updated/deletedij> insert into four values (2, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into four values (2, 1, 2, 1, 1);1 row inserted/updated/deletedij> insert into four values (2, 1, 3, 1, 1);1 row inserted/updated/deletedij> insert into four values (3, 1, 1, 1, 1);1 row inserted/updated/deletedij> insert into four values (3, 1, 2, 1, 1);1 row inserted/updated/deletedij> insert into four values (3, 1, 3, 1, 1);1 row inserted/updated/deletedij> insert into idept values ('Dept', 1, 'Department1', null, null, null, null);1 row inserted/updated/deletedij> insert into idept values ('HardwareDept', 2, 'Department2', 25, 1, 'hardwareaset2', null);1 row inserted/updated/deletedij> insert into idept values ('HardwareDept', 3, 'Department3', 25, 2, 'hardwareaset3', null);1 row inserted/updated/deletedij> insert into idept values ('SoftwareDept', 4, 'Department4', 25, 1, null, 'softwareasset4');1 row inserted/updated/deletedij> insert into idept values ('SoftwareDept', 5, 'Department5', 30, 4, null, 'softwareasset5');1 row inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);0 rows inserted/updated/deletedij> maximumdisplaywidth 20000;ij> -- queries that cannot eliminate the distinct-- no unique indexselect distinct c2 from one;C2 -----------1 ij> -- Following runtime statistics output should have Distinct Scan in itvalues SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();1 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Statement Name: nullStatement Text: -- queries that cannot eliminate the distinct-- no unique indexselect distinct c2 from oneParse 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: Distinct Scan ResultSet for ONE using index xxxxFILTERED-UUIDxxxx at serializable isolation level using share table locking: Number of opens = 1Hash table size = 1Distinct column is column number 1Rows seen = 1Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0scan information: Bit set of columns fetched={1} Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=8 Number of rows visited=8 Scan type=heap start position: None stop position: None scan qualifiers:None next qualifiers:Noneij> --Derby251 Distinct should not get eliminated for following query--because there is no equality condition on unique column of table--in the outside queryselect distinct q1."NO1", q1."NAME", q1."AUDITOR_NO", q1."REPORTTO_NO", q1."DISCRIM_DEPT", q1."SOFTWAREASSET" from IDEPT q1, IDEPT q2 where ( q2."DISCRIM_DEPT" = 'HardwareDept') and ( q1."DISCRIM_DEPT" = 'SoftwareDept') and ( q1."NO1" <> ALL ( select q3."NO1" from IDEPT q3 where ( ( q3."DISCRIM_DEPT" = 'Dept') or ( q3."DISCRIM_DEPT" = 'HardwareDept') or ( q3."DISCRIM_DEPT" = 'SoftwareDept') ) and ( q3."REPORTTO_NO" = q2."NO1") ) ) ;NO1 |NAME |AUDITOR_NO |REPORTTO_NO|DISCRIM_DEPT |SOFTWAREASSET ---------------------------------------------------------------------------------------------------------------------------------------4 |Department4 |25 |1 |SoftwareDept |softwareasset4 5 |Department5 |30 |4 |SoftwareDept |softwareasset5 ij> ----Another test case of Derby251 where the exists table column is embedded in an expression.select distinct q1."NO1" from IDEPT q1, IDEPT q2where ( q2."DISCRIM_DEPT" = 'HardwareDept')and ( q1."DISCRIM_DEPT" = 'SoftwareDept') and ( q1."NO1" <> ALL(select q3."NO1" from IDEPT q3 where ( ABS(q3."REPORTTO_NO") = q2."NO1")));NO1 -----------4 5 ij> -- result ordering is not guaranteed, but order by clause will change how-- distinct is executed. So test by retrieving data into a temp table and-- return results ordered after making sure the query was executed as expected.create table temp_result (c2 int, c3 int);0 rows inserted/updated/deletedij> insert into temp_result select distinct c2, c3 from two;3 rows inserted/updated/deletedij> -- Following runtime statistics output should have Distinct Scan in itvalues SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();1 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Statement Name: nullStatement Text: insert into temp_result select distinct c2, c3 from twoParse 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: Insert ResultSet using row locking:deferred: falseinsert mode: normalRows inserted = 3Indexes updated = 0Execute Time = 0 Distinct Scan ResultSet for TWO using index xxxxFILTERED-UUIDxxxx at serializable isolation level using share table locking: Number of opens = 1 Hash table size = 3 Distinct columns are column numbers (1,2) Rows seen = 3 Rows filtered = 0 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={1, 2} Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=9 Number of rows visited=9 Scan type=heap start position: None stop position: None scan qualifiers:None next qualifiers:Noneij> select c2, c3 from temp_result order by c2, c3;C2 |C3 -----------------------1 |1 1 |2 1 |3 ij> drop table temp_result;0 rows inserted/updated/deletedij> -- Try same query, but with an order by at the end. This will use the sort for-- the "order by" to do the distinct and not do a "DISTINCT SCAN".select distinct c2, c3 from two order by c2, c3;C2 |C3 -----------------------1 |1 1 |2 1 |3 ij> -- Following runtime statistics output should not have Distinct Scan in itvalues SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();1 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Statement Name: nullStatement Text: -- Try same query, but with an order by at the end. This will use the sort for-- the "order by" to do the distinct and not do a "DISTINCT SCAN".select distinct c2, c3 from two order by c2, c3⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -