I have a optimizer question I was hoping for someone well versed could
provide some input to:
As part of my work on DERBY-2998, the implementation of the ROW_NUMBER()
window function, I'm having trouble with the optimizer and its (lack of)
pushing predicates down the query tree.
The scenario I have is a nested SELECT with a WHERE clause on the outer
SELECT referring an aliased column in the inner SELECT. The query in
question is this one:
SELECT * FROM (SELECT ROW_NUMBER() AS R, T.* FROM T) AS TR WHERE R < 3;
I've attached the output from derby.language.logQueryPlan along with
derby.debug.true=DumpOptimizedTree for reference.
Reading the log of the optimized tree and query plan, I deduce the
optimizer does not push the where predicate(s) into the inner select
like it should (ought to), and I can't seem to wrap my head around why
this isn't so, and where in the code this was supposed to have happened.
I've probably just gone blind at this, and I may be interpreting the
graph from DumpOptimizedTree wrong for all I know.
Ideally I'd like the optimizer to push the where predicate all the way
into the OLAPResultSet node, and stop the execution there once the
restricion given in the where predicate is met. In this particular case
that decision is based on the fact that row_number() is ever increasing.
But that's all part of the restriction evaluation, and at this stage I
have no restriction/where predicate in OLAPResultSet - even after
optimization.
To add to the info I see the same kind of queryplan in the similar query
where the aliased expression is exchanged with 'a*b':
SELECT * FROM (SELECT a*b AS R, T.* FROM T) AS TR WHERE R < 3;
So I'm starting to wonder if this is a general Derby problem, where
restrictions on aliased expression columns are not properly optimized?
Your comments and input is greatly appreciated!
Cheers,
Thomas
--
Thomas Nielsen
----------------------------------------------------------------
2007-12-07 10:32:28.556 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby - 10.4.0.0 alpha - (601732M): instance c013800d-0116-b429-b344-ffffe275b270
on database directory /var/tmp/ij/mydb
Database Class Loader started - derby.database.classpath=''
[EMAIL PROTECTED]
name: null
updateMode: READ_ONLY (1)
statementType: SELECT
resultSet:
[EMAIL PROTECTED]
correlation Name: null
null
tableNumber -1
level 0
resultSetNumber: 0
referencedTableMap: {0}
statementResultSet: false
resultColumns:
[EMAIL PROTECTED]
exposedName: R
name: R
tableName: TR
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: R
name: R
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
expression:
[EMAIL PROTECTED]
columnName: R
tableNumber: 0
columnNumber: 1
replacesAggregate: false
tableName: TR
nestingLevel: 0
sourceLevel: 0
dataTypeServices: BIGINT NOT NULL
source:
[EMAIL PROTECTED]
exposedName: R
name: R
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: R
name: R
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
expression:
[EMAIL PROTECTED]
exposedName: null
name: null
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 0
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: TR
isNameGenerated: false
sourceTableName: T
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: A
tableNumber: 0
columnNumber: 2
replacesAggregate: false
tableName: TR
nestingLevel: 0
sourceLevel: 0
dataTypeServices: INTEGER
source:
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: T
isNameGenerated: false
sourceTableName: T
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: A
tableNumber: 1
columnNumber: 1
replacesAggregate: false
tableName: T
nestingLevel: 1
sourceLevel: 1
dataTypeServices: INTEGER
source:
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: A
columnPosition: 1
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: A
columnPosition: 1
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: A
TdataTypeServices: INTEGER
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: TR
isNameGenerated: false
sourceTableName: T
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 3
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 3
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: B
tableNumber: 0
columnNumber: 3
replacesAggregate: false
tableName: TR
nestingLevel: 0
sourceLevel: 0
dataTypeServices: INTEGER
source:
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: T
isNameGenerated: false
sourceTableName: T
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 3
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 3
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: B
tableNumber: 1
columnNumber: 2
replacesAggregate: false
tableName: T
nestingLevel: 1
sourceLevel: 1
dataTypeServices: INTEGER
source:
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: B
columnPosition: 2
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: B
columnPosition: 2
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: B
TdataTypeServices: INTEGER
childResult:
[EMAIL PROTECTED]
correlation Name: null
null
tableNumber -1
level 0
resultSetNumber: 0
referencedTableMap: {0}
statementResultSet: false
resultColumns:
[EMAIL PROTECTED]
exposedName: R
name: R
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
expression:
[EMAIL PROTECTED]
columnName: R
tableNumber: 0
columnNumber: 1
replacesAggregate: false
tableName: TR
nestingLevel: 0
sourceLevel: 0
dataTypeServices: BIGINT NOT NULL
source:
[EMAIL PROTECTED]
exposedName: R
name: R
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: R
name: R
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
expression:
[EMAIL PROTECTED]
exposedName: null
name: null
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 0
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: A
tableNumber: 0
columnNumber: 2
replacesAggregate: false
tableName: TR
nestingLevel: 0
sourceLevel: 0
dataTypeServices: INTEGER
source:
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: T
isNameGenerated: false
sourceTableName: T
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: A
tableNumber: 1
columnNumber: 1
replacesAggregate: false
tableName: T
nestingLevel: 1
sourceLevel: 1
dataTypeServices: INTEGER
source:
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: A
columnPosition: 1
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: A
columnPosition: 1
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: A
TdataTypeServices: INTEGER
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 3
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: B
tableNumber: 0
columnNumber: 3
replacesAggregate: false
tableName: TR
nestingLevel: 0
sourceLevel: 0
dataTypeServices: INTEGER
source:
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: T
isNameGenerated: false
sourceTableName: T
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 3
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 3
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: B
tableNumber: 1
columnNumber: 2
replacesAggregate: false
tableName: T
nestingLevel: 1
sourceLevel: 1
dataTypeServices: INTEGER
source:
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: B
columnPosition: 2
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: B
columnPosition: 2
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: B
TdataTypeServices: INTEGER
childResult:
[EMAIL PROTECTED]
correlation Name: null
null
tableNumber 0
level 0
resultSetNumber: 0
referencedTableMap: {0}
statementResultSet: false
resultColumns:
[EMAIL PROTECTED]
exposedName: R
name: R
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: R
name: R
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
expression:
[EMAIL PROTECTED]
exposedName: null
name: null
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 0
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: T
isNameGenerated: false
sourceTableName: T
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: A
tableNumber: 1
columnNumber: 1
replacesAggregate: false
tableName: T
nestingLevel: 1
sourceLevel: 1
dataTypeServices: INTEGER
source:
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: A
columnPosition: 1
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: A
columnPosition: 1
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: A
TdataTypeServices: INTEGER
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: T
isNameGenerated: false
sourceTableName: T
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 3
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 3
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: B
tableNumber: 1
columnNumber: 2
replacesAggregate: false
tableName: T
nestingLevel: 1
sourceLevel: 1
dataTypeServices: INTEGER
source:
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: B
columnPosition: 2
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: B
columnPosition: 2
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: B
TdataTypeServices: INTEGER
childResult:
[EMAIL PROTECTED]
correlation Name: null
null
tableNumber -1
level 0
resultSetNumber: 0
referencedTableMap: {1}
statementResultSet: false
resultColumns:
[EMAIL PROTECTED]
exposedName: R
name: R
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
expression:
[EMAIL PROTECTED]
exposedName: null
name: null
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 0
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: A
tableNumber: 1
columnNumber: 1
replacesAggregate: false
tableName: T
nestingLevel: 1
sourceLevel: 1
dataTypeServices: INTEGER
source:
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: A
columnPosition: 1
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: A
columnPosition: 1
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: A
TdataTypeServices: INTEGER
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 3
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: B
tableNumber: 1
columnNumber: 2
replacesAggregate: false
tableName: T
nestingLevel: 1
sourceLevel: 1
dataTypeServices: INTEGER
source:
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: B
columnPosition: 2
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: B
columnPosition: 2
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: B
TdataTypeServices: INTEGER
childResult:
[EMAIL PROTECTED]
correlation Name: null
null
tableNumber -1
level 0
resultSetNumber: 0
referencedTableMap: null
statementResultSet: false
resultColumns:
[EMAIL PROTECTED]
exposedName: R
name: R
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
expression:
[EMAIL PROTECTED]
exposedName: null
name: null
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 0
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: A
tableNumber: 1
columnNumber: 1
replacesAggregate: false
tableName: T
nestingLevel: 1
sourceLevel: 1
dataTypeServices: INTEGER
source:
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: A
columnPosition: 1
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: A
name: A
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: A
columnPosition: 1
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: A
TdataTypeServices: INTEGER
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 3
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: B
tableNumber: 1
columnNumber: 2
replacesAggregate: false
tableName: T
nestingLevel: 1
sourceLevel: 1
dataTypeServices: INTEGER
source:
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: B
columnPosition: 2
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
dataTypeServices: null
sourceColumn:
[EMAIL PROTECTED]
exposedName: B
name: B
tableName: null
isNameGenerated: false
sourceTableName: null
type: INTEGER
columnDescriptor: columnName: B
columnPosition: 2
columnType: INTEGER
columnDefault: null
uuid: 72e9c096-0114-cb72-028f-ffffe275b270
defaultUUID: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: true
isRedundant: false
virtualColumnId: 2
resultSetNumber: -1
dataTypeServices: INTEGER
expression:
[EMAIL PROTECTED]
columnName: B
TdataTypeServices: INTEGER
restrictionList:
[EMAIL PROTECTED]
projectSubquerys:
[EMAIL PROTECTED]
restrictSubquerys:
[EMAIL PROTECTED]
restrictionList:
[EMAIL PROTECTED]
null.R < <expr>
referencedSet: {0}
pushable: true
;
[EMAIL PROTECTED]
null.R < <expr>
referencedSet: {0}
pushable: true
andNode:
[EMAIL PROTECTED]
operator: and
methodName: and
dataTypeServices: BOOLEAN NOT NULL
leftOperand:
[EMAIL PROTECTED]
operator: <
methodName: lessThan
dataTypeServices: BOOLEAN NOT NULL
leftOperand:
[EMAIL PROTECTED]
columnName: R
tableNumber: 0
columnNumber: 1
replacesAggregate: false
tableName: null
nestingLevel: 0
sourceLevel: 0
dataTypeServices: null
source:
[EMAIL PROTECTED]
exposedName: R
name: R
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 1
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
expression:
[EMAIL PROTECTED]
exposedName: null
name: null
tableName: null
isNameGenerated: false
sourceTableName: null
type: BIGINT NOT NULL
columnDescriptor: null
isGenerated: false
isGeneratedForUnmatchedColumnInInsert: false
isGroupingColumn: false
isReferenced: false
isRedundant: false
virtualColumnId: 0
resultSetNumber: -1
dataTypeServices: BIGINT NOT NULL
rightOperand:
[EMAIL PROTECTED]
value: 3
dataTypeServices: INTEGER NOT NULL
rightOperand:
[EMAIL PROTECTED]
value: true
dataTypeServices: BOOLEAN NOT NULL
restrictionList:
[EMAIL PROTECTED]
projectSubquerys:
[EMAIL PROTECTED]
restrictSubquerys:
[EMAIL PROTECTED]
orderByList: 2007-12-07 10:33:18.125 GMT Thread[main,5,main] (XID = 492), (SESSIONID = 0), select * from (select row_number() as r, t.* from t) as tr where r < 3 ******* Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 5
Rows filtered = 3
restriction = true
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
optimizer estimated row count: 10.00
optimizer estimated cost: 69.48
Source result set:
Project-Restrict ResultSet (3):
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
optimizer estimated row count: 10.00
optimizer estimated cost: 69.48
Source result set:
Number of opens = 1
Rows seen = 5
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 10.00
optimizer estimated cost: 69.48
Table Scan ResultSet for T at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 5
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 pages visited=2
Number of rows qualified=5
Number of rows visited=10
Scan type=heap
start position:
null stop position:
null qualifiers:
None
optimizer estimated row count: 10.00
optimizer estimated cost: 69.48
