Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 57143 invoked from network); 17 Jan 2006 01:11:49 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 17 Jan 2006 01:11:49 -0000 Received: (qmail 36589 invoked by uid 500); 17 Jan 2006 01:11:49 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 36507 invoked by uid 500); 17 Jan 2006 01:11:47 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 35731 invoked by uid 99); 17 Jan 2006 01:11:43 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 16 Jan 2006 17:11:43 -0800 X-ASF-Spam-Status: No, hits=-9.4 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from [209.237.227.194] (HELO minotaur.apache.org) (209.237.227.194) by apache.org (qpsmtpd/0.29) with SMTP; Mon, 16 Jan 2006 17:11:40 -0800 Received: (qmail 56811 invoked by uid 65534); 17 Jan 2006 01:11:19 -0000 Message-ID: <20060117011119.56809.qmail@minotaur.apache.org> Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r369619 [4/4] - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/reference/ engine/org/apache/derby/iapi/sql/compile/ engine/org/apache/derby/iapi/sql/execute/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl... Date: Tue, 17 Jan 2006 01:11:11 -0000 To: derby-commits@db.apache.org From: bandaram@apache.org X-Mailer: svnmailer-1.0.5 X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/optimizerOverrides.out URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/optimizerOverrides.out?rev=369619&r1=369618&r2=369619&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/optimizerOverrides.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/optimizerOverrides.out Mon Jan 16 17:10:52 2006 @@ -1,5 +1,9 @@ ij> -- test the optimizer overrides autocommit off; +ij> -- change display width in anticipation of runtimestatistics +maximumdisplaywidth 5000; +ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); +0 rows inserted/updated/deleted ij> -- create the tables create table t1 (c1 int, c2 int, c3 int, constraint cons1 primary key(c1, c2)); 0 rows inserted/updated/deleted @@ -115,6 +119,60 @@ 2 |2 |2 3 |3 |3 4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + null +Statement Text: + -- index which includes columns in for update of list +select * from t1 --derby-properties index = t1_c1 +for update +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Index Row to Base Row ResultSet for T1: +Number of opens = 1 +Rows seen = 4 +Columns accessed from heap = {0, 1, 2} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + User supplied optimizer overrides on T1 are { index=T1_C1 } + Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using exclusive row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + Rows filtered = 0 + Fetch Size = 1 + 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=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None ij> select * from t1 exposedname --derby-properties index = t1_c1 for update; C1 |C2 |C3 @@ -123,6 +181,59 @@ 2 |2 |2 3 |3 |3 4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + null +Statement Text: + select * from t1 exposedname --derby-properties index = t1_c1 +for update +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Index Row to Base Row ResultSet for T1: +Number of opens = 1 +Rows seen = 4 +Columns accessed from heap = {0, 1, 2} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + User supplied optimizer overrides on T1 are { index=T1_C1 } + Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using exclusive row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + Rows filtered = 0 + Fetch Size = 1 + 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=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None ij> select * from t1 --derby-properties index = t1_c1 for update of c2, c1; C1 |C2 |C3 @@ -131,6 +242,59 @@ 2 |2 |2 3 |3 |3 4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + null +Statement Text: + select * from t1 --derby-properties index = t1_c1 +for update of c2, c1 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Index Row to Base Row ResultSet for T1: +Number of opens = 1 +Rows seen = 4 +Columns accessed from heap = {0, 1, 2} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + User supplied optimizer overrides on T1 are { index=T1_C1 } + Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using exclusive row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + Rows filtered = 0 + Fetch Size = 1 + 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=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None ij> select * from t1 exposedname --derby-properties index = t1_c1 for update of c2, c1; C1 |C2 |C3 @@ -139,6 +303,59 @@ 2 |2 |2 3 |3 |3 4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + null +Statement Text: + select * from t1 exposedname --derby-properties index = t1_c1 +for update of c2, c1 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Index Row to Base Row ResultSet for T1: +Number of opens = 1 +Rows seen = 4 +Columns accessed from heap = {0, 1, 2} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + User supplied optimizer overrides on T1 are { index=T1_C1 } + Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using exclusive row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + Rows filtered = 0 + Fetch Size = 1 + 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=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None ij> -- constraint which includes columns in for update of list select * from t1 --derby-properties constraint = cons1 for update; @@ -148,6 +365,60 @@ 2 |2 |2 3 |3 |3 4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + null +Statement Text: + -- constraint which includes columns in for update of list +select * from t1 --derby-properties constraint = cons1 +for update +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Index Row to Base Row ResultSet for T1: +Number of opens = 1 +Rows seen = 4 +Columns accessed from heap = {0, 1, 2} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + User supplied optimizer overrides on T1 are { index=xxxxGENERATED-IDxxxx } + Index Scan ResultSet for T1 using constraint CONS1 at read committed isolation level using exclusive row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + Rows filtered = 0 + Fetch Size = 1 + 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=3 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None ij> select * from t1 exposedname --derby-properties constraint = cons1 for update; C1 |C2 |C3 @@ -156,6 +427,59 @@ 2 |2 |2 3 |3 |3 4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + null +Statement Text: + select * from t1 exposedname --derby-properties constraint = cons1 +for update +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Index Row to Base Row ResultSet for T1: +Number of opens = 1 +Rows seen = 4 +Columns accessed from heap = {0, 1, 2} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + User supplied optimizer overrides on T1 are { index=xxxxGENERATED-IDxxxx } + Index Scan ResultSet for T1 using constraint CONS1 at read committed isolation level using exclusive row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + Rows filtered = 0 + Fetch Size = 1 + 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=3 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None ij> select * from t1 --derby-properties constraint = cons1 for update of c2, c1; C1 |C2 |C3 @@ -164,6 +488,59 @@ 2 |2 |2 3 |3 |3 4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + null +Statement Text: + select * from t1 --derby-properties constraint = cons1 +for update of c2, c1 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Index Row to Base Row ResultSet for T1: +Number of opens = 1 +Rows seen = 4 +Columns accessed from heap = {0, 1, 2} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + User supplied optimizer overrides on T1 are { index=xxxxGENERATED-IDxxxx } + Index Scan ResultSet for T1 using constraint CONS1 at read committed isolation level using exclusive row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + Rows filtered = 0 + Fetch Size = 1 + 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=3 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None ij> select * from t1 exposedname --derby-properties constraint = cons1 for update of c2, c1; C1 |C2 |C3 @@ -172,9 +549,87 @@ 2 |2 |2 3 |3 |3 4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + null +Statement Text: + select * from t1 exposedname --derby-properties constraint = cons1 +for update of c2, c1 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Index Row to Base Row ResultSet for T1: +Number of opens = 1 +Rows seen = 4 +Columns accessed from heap = {0, 1, 2} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + User supplied optimizer overrides on T1 are { index=xxxxGENERATED-IDxxxx } + Index Scan ResultSet for T1 using constraint CONS1 at read committed isolation level using exclusive row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + Rows filtered = 0 + Fetch Size = 1 + 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=3 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None ij> -- select from view with bad derby-properties list select * from neg_v1; ERROR 42Y44: Invalid key 'asdf' specified in the Properties list of a FROM list. The case-sensitive keys that are currently supported are 'index, constraint, joinStrategy'. +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + null +Statement Text: + values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Row ResultSet: +Number of opens = 1 +Rows returned = 1 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 ij> -- bad derby-properties tests on outer joins select * from t1 --derby-properties i = a left outer join t2 on 1=1; @@ -198,6 +653,31 @@ ij> -- dependent on index prepare p1 as 'select * from t1 --derby-properties index = t1_c1 '; +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + null +Statement Text: + values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Row ResultSet: +Number of opens = 1 +Rows returned = 1 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 ij> execute p1; C1 |C2 |C3 ----------------------------------- @@ -205,15 +685,111 @@ 2 |2 |2 3 |3 |3 4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + null +Statement Text: + select * from t1 --derby-properties index = t1_c1 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Index Row to Base Row ResultSet for T1: +Number of opens = 1 +Rows seen = 4 +Columns accessed from heap = {0, 1, 2} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + User supplied optimizer overrides on T1 are { index=T1_C1 } + Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using instantaneous share row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + 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={1} + Number of columns fetched=1 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None ij> drop index t1_c1; 0 rows inserted/updated/deleted ij> execute p1; ERROR 42Y46: Invalid Properties list in FROM list. There is no index 'T1_C1' on table 'T1'. +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + null +Statement Text: + drop index t1_c1 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +null ij> remove p1; ij> rollback; ij> -- dependent on constraint prepare p2 as 'select * from t1 --derby-properties constraint = cons1 '; +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + null +Statement Text: + values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Row ResultSet: +Number of opens = 1 +Rows returned = 1 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 ij> execute p2; C1 |C2 |C3 ----------------------------------- @@ -221,16 +797,83 @@ 2 |2 |2 3 |3 |3 4 |4 |4 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + null +Statement Text: + select * from t1 --derby-properties constraint = cons1 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Index Row to Base Row ResultSet for T1: +Number of opens = 1 +Rows seen = 4 +Columns accessed from heap = {0, 1, 2} + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + User supplied optimizer overrides on T1 are { index=xxxxGENERATED-IDxxxx } + Index Scan ResultSet for T1 using constraint CONS1 at read committed isolation level using instantaneous share row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + 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={2} + Number of columns fetched=1 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None ij> alter table t1 drop constraint cons1; 0 rows inserted/updated/deleted ij> execute p2; ERROR 42Y48: Invalid Properties list in FROM list. Either there is no named constraint 'CONS1' on table 'T1' or the constraint does not have a backing index. +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + null +Statement Text: + alter table t1 drop constraint cons1 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +null ij> remove p2; ij> rollback; -ij> -- change display width in anticipation of runtimestatistics -maximumdisplaywidth 5000; -ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); -0 rows inserted/updated/deleted ij> -- the token derby-properties is case insensitive. Few tests for that select * from t1 --DeRbY-pRoPeRtIeS index = t1_c1 ; @@ -267,6 +910,7 @@ open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 + User supplied optimizer overrides on T1 are { index=T1_C1 } Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 4 @@ -381,6 +1025,7 @@ open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 + User supplied optimizer overrides on T1 are { index=t1_c2c1 } Index Scan ResultSet for T1 using index t1_c2c1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 4 @@ -434,6 +1079,7 @@ Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: +User supplied optimizer overrides on T1 are { index=NULL } Table Scan ResultSet for T1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 4 @@ -490,6 +1136,7 @@ open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 + User supplied optimizer overrides on T1 are { index=T1_C1 } Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 4 @@ -552,6 +1199,7 @@ open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 + User supplied optimizer overrides on T1 are { index=T1_C1 } Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 4 @@ -643,6 +1291,7 @@ next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: + User supplied optimizer overrides on T1 are { index=T1_C1 } Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 4 @@ -669,6 +1318,7 @@ qualifiers: None Right result set: + User supplied optimizer overrides on T2 are { index=T2_C2 } Index Scan ResultSet for T2 using index T2_C2 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 4 Rows seen = 16 @@ -694,6 +1344,116 @@ None qualifiers: None +ij> select 1 from --derby-PROPERTIES joinOrder=fixed +t1, t2 where t1.c1 = t2.c1; +1 +----------- +1 +1 +1 +1 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- +Statement Name: + null +Statement Text: + select 1 from --derby-PROPERTIES joinOrder=fixed +t1, t2 where t1.c1 = t2.c1 +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Project-Restrict ResultSet (4): +Number of opens = 1 +Rows seen = 4 +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: + User supplied optimizer overrides for join are { joinOrder=FIXED } + Hash Join ResultSet: + Number of opens = 1 + Rows seen from the left = 4 + Rows seen from the right = 4 + Rows filtered = 0 + Rows returned = 4 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Index Scan ResultSet for T1 using constraint CONS1 at read committed isolation level using instantaneous share row locking chosen by the optimizer + Number of opens = 1 + Rows seen = 4 + 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} + Number of columns fetched=1 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None + Right result set: + Hash Scan ResultSet for T2 using constraint CONS2 at read committed isolation level using instantaneous share row locking: + Number of opens = 4 + Hash table size = 4 + Hash key is column number 0 + Rows seen = 4 + 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={0} + Number of columns fetched=1 + Number of deleted rows visited=0 + Number of pages visited=1 + Number of rows qualified=4 + Number of rows visited=4 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + scan qualifiers: +None + next qualifiers: +Column[0][0] Id: 0 +Operator: = +Ordered nulls: false +Unknown return value: false +Negate comparison result: false ij> -- comparisons that can't get pushed down select * from t1 --derby-properties index = t1_c1 where c1 = c1; @@ -770,6 +1530,7 @@ open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 + User supplied optimizer overrides on T1 are { index=T1_C1 } Index Scan ResultSet for T1 using index T1_C1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 4 @@ -817,6 +1578,7 @@ open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 + User supplied optimizer overrides on T2 are { index=T2_C2 } Index Scan ResultSet for T2 using index T2_C2 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 4 Rows seen = 16 @@ -895,6 +1657,7 @@ next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: + User supplied optimizer overrides on T1 are { joinStrategy=NESTEDLOOP } Index Scan ResultSet for T1 using index T1_C1C2C3 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 4 Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql?rev=369619&r1=369618&r2=369619&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql Mon Jan 16 17:10:52 2006 @@ -1,6 +1,10 @@ -- test the optimizer overrides autocommit off; +-- change display width in anticipation of runtimestatistics +maximumdisplaywidth 5000; +call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); + -- create the tables create table t1 (c1 int, c2 int, c3 int, constraint cons1 primary key(c1, c2)); create table t2 (c1 int not null, c2 int not null, c3 int, constraint cons2 unique(c1, c2)); @@ -89,25 +93,40 @@ -- index which includes columns in for update of list select * from t1 --derby-properties index = t1_c1 for update; +values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); + select * from t1 exposedname --derby-properties index = t1_c1 for update; +values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); + select * from t1 --derby-properties index = t1_c1 for update of c2, c1; +values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); + select * from t1 exposedname --derby-properties index = t1_c1 for update of c2, c1; +values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); -- constraint which includes columns in for update of list select * from t1 --derby-properties constraint = cons1 for update; +values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); + select * from t1 exposedname --derby-properties constraint = cons1 for update; +values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); + select * from t1 --derby-properties constraint = cons1 for update of c2, c1; +values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); + select * from t1 exposedname --derby-properties constraint = cons1 for update of c2, c1; +values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); -- select from view with bad derby-properties list select * from neg_v1; +values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); -- bad derby-properties tests on outer joins select * from t1 --derby-properties i = a @@ -131,26 +150,27 @@ -- dependent on index prepare p1 as 'select * from t1 --derby-properties index = t1_c1 '; +values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); execute p1; +values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); drop index t1_c1; execute p1; +values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); remove p1; rollback; -- dependent on constraint prepare p2 as 'select * from t1 --derby-properties constraint = cons1 '; +values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); execute p2; +values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); alter table t1 drop constraint cons1; execute p2; +values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); remove p2; rollback; --- change display width in anticipation of runtimestatistics -maximumdisplaywidth 5000; - -call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); - -- the token derby-properties is case insensitive. Few tests for that select * from t1 --DeRbY-pRoPeRtIeS index = t1_c1 ; @@ -184,6 +204,10 @@ select 1 from t1 a --derby-properties index = t1_c1 , t2 b --derby-properties index = t2_c2 ;values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); + +select 1 from --derby-PROPERTIES joinOrder=fixed +t1, t2 where t1.c1 = t2.c1; +values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); -- comparisons that can't get pushed down select * from t1 --derby-properties index = t1_c1