db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Satheesh Bandaram (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-781) Materialize union subqueries in select list where possible to avoid creating invariant resultsets many times.
Date Fri, 23 Dec 2005 05:10:41 GMT
     [ http://issues.apache.org/jira/browse/DERBY-781?page=all ]

Satheesh Bandaram updated DERBY-781:
------------------------------------

    Description: 
Derby's handling of union subqueries in from list can be improved by materializing invariant
resultsets once, rather than creating them many times.

For example:

create view V1 as select i, j from T1 union select i,j from T2;
create view V2 as select a,b from T3 union select a,b from T4;
insert into T1 values (1,1), (2,2), (3,3), (4,4), (5,5);

For a query like select * from V1, V2 where i in (1,2,3,4,5), it is possible the resultset
for V2 is created 5 times. (assuming V2 is choosen as the the inner table) This can be very
costly if the underlying selects can take long time and also may perform union many times.

Enhance materialization logic in setOperatorNode.java. It currently returns FALSE always.

public boolean performMaterialization(JBitSet outerTables)
		throws StandardException
{
	// RESOLVE - just say no to materialization right now - should be a cost based decision
	return false;

	/* Actual materialization, if appropriate, will be placed by our parent PRN.
	 * This is because PRN might have a join condition to apply.  (Materialization
	 * can only occur before that.
	 */
	//return true;
} 

  was:
Derby's handling of union subqueries in from list can be improved by materializing invariant
resultsets once, rather than creating them many times.

For example:

create view V1 as select i, j from T1 union all select i,j from T2;
create view V2 as select a,b from T3 union all select a,b from T4;
insert into T1 values (1,1), (2,2), (3,3), (4,4), (5,5);

For a query like select * from V1, V2 where i in (1,2,3,4,5), it is possible the resultset
for V2 is created 5 times. (assuming V2 is choosen as the the inner table) This can be very
costly if the underlying selects can take long time and also may perform union many times.

Enhance materialization logic in setOperatorNode.java. It currently returns FALSE always.

public boolean performMaterialization(JBitSet outerTables)
		throws StandardException
{
	// RESOLVE - just say no to materialization right now - should be a cost based decision
	return false;

	/* Actual materialization, if appropriate, will be placed by our parent PRN.
	 * This is because PRN might have a join condition to apply.  (Materialization
	 * can only occur before that.
	 */
	//return true;
} 


> Materialize union subqueries in select list where possible to avoid creating invariant
resultsets many times.
> -------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-781
>          URL: http://issues.apache.org/jira/browse/DERBY-781
>      Project: Derby
>         Type: Improvement
>   Components: SQL
>     Versions: 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram

>
> Derby's handling of union subqueries in from list can be improved by materializing invariant
resultsets once, rather than creating them many times.
> For example:
> create view V1 as select i, j from T1 union select i,j from T2;
> create view V2 as select a,b from T3 union select a,b from T4;
> insert into T1 values (1,1), (2,2), (3,3), (4,4), (5,5);
> For a query like select * from V1, V2 where i in (1,2,3,4,5), it is possible the resultset
for V2 is created 5 times. (assuming V2 is choosen as the the inner table) This can be very
costly if the underlying selects can take long time and also may perform union many times.
> Enhance materialization logic in setOperatorNode.java. It currently returns FALSE always.
> public boolean performMaterialization(JBitSet outerTables)
> 		throws StandardException
> {
> 	// RESOLVE - just say no to materialization right now - should be a cost based decision
> 	return false;
> 	/* Actual materialization, if appropriate, will be placed by our parent PRN.
> 	 * This is because PRN might have a join condition to apply.  (Materialization
> 	 * can only occur before that.
> 	 */
> 	//return true;
> } 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message