You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Eric Radzinski <er...@yahoo.com> on 2006/01/21 00:22:53 UTC

documenting DERBY -573, optimizer overrides

Here's my attempt at documenting the optimizer overrides that are introduced by DERBY -573.  I'm assuming that this information would be added to the Tuning Guide, probably as a topic within "Performance and optimization" (let me know if anything needs to be added to the Ref Guide or any other book(s)).
   
    Overriding the default optimizer behavior
  You can override the default behavior of the Derby query optimizer by including a --DERBY PROPERTIES clause and an associated property as a comment within an SQL statement. 
   
  Because optimizer overrides are expressed as comments, they must be included at the end of a line. You can specify optimizer override properties for an entire FROM clause, for tables in the FROM clause, or for both. 
   
  The syntax for FROM clause properties is:
   
   FROM [ -- DERBY-PROPERTIES propertyName = value ]
       TableExpression [,TableExpression]*
   
  The syntax for table optimizer override properties, which must be included at the end of a TableExpression, is:
   
  {TableName | ViewName }
       [ [ AS ] CorrelationName
         [ (SimpleColumnName [ , SimpleColumnName]* ) ] ]
         [ -- DERBY-PROPERTIES clause]
   
  Note that the space between -- and DERBY-PROPERTIES is optional. 
   
  Important: Make sure that you adhere to the correct syntax when using the -- DERBY PROPERTIES clause. Failure to do so will cause the parser to interpret it as a comment and ignore it.
   
  The following four properties are available for use in a --DERBY PROPERTIES clause:
   
  constraint 
  The Derby optimizer chooses an index, including the indexes that enforce constraints, as the access path for query execution if the index is useful. If there is more than one useful index, in most cases Derby chooses the index that is most useful. Use the constraint property to override the index that the optimizer selects and force the use of a particular index or force a table scan. To force the use of the index that enforces a primary key or unique constraint, use the constraint property and specify the unqualified name of the constraint. The constraint property can be specified only on base tables; it cannot be specified on views or derived tables.
   
  index
  The index property is similar to the constraint property. To force use of a particular index, specify the unqualified index name. To force a table scan, specify null for the index name. The index property can be specified only on base tables; it cannot be specified on views or derived tables.
   
  joinOrder 
  Use the joinOrder property to override the optimizer’s choice of join order for two tables. When the value FIXED is specified, the optimizer will choose the order of tables as they appear in the FROM clause as the join order. Valid values for the joinOrder property include FIXED and UNFIXED.
   
  The joinOrder property can be specified on all table expressions.
   
  joinStrategy
  Use the joinStrategy property to override the optimizer’s choice of join strategy. The two types of join strategy are called nested loop and hash. In a nested loop join strategy, for each qualifying row in the outer table, Derby uses the appropriate access path (index or table scan) to find the matching rows in the inner table. In a hash join strategy, Derby constructs a hash table that represents the inner table. For each qualifying row in the outer table, Derby does a quick lookup on the hash table to find the matching rows in the inner table. Derby needs to scan the inner table or index only once to create the hash table. The –DERBY-PROPERTIES parameter must immediately follow the inner table. The joinOrder property can be specified on all table expressions. Valid values include HASH and NESTED LOOP.
   
  The joinStrategy property can be specified on all table expressions, but it must be used in conjunction with the joinOrder property. Do not let the optimizer choose the join order.
   
  Examples:
  I could use some help with some examples here.


		
---------------------------------
 
 What are the most popular cars?  Find out at Yahoo! Autos

Re: documenting DERBY -573, optimizer overrides

Posted by Mamta Satoor <ms...@gmail.com>.
Hi Eric,

I have entered JIRA entry Derby-855 for documentation changes for optimizer
overrides. Since you are working on it, can you please make yourself the
owner of that JIRA entry? You might need to get youself an id for JIRA if
you don't have one already, which you can sign up
for at http://issues.apache.org/jira/ .

thanks,
Mamta


On 1/22/06, Mamta Satoor <ms...@gmail.com> wrote:
>
> Hi Eric,
>
> Thanks for working on the documentation part of this feature.
>
> Couple comments
> 1)Wherever you have referenced DERBY-PROPERTIES as DERBY PROPERTIES,
> please put a - between the 2 words. Following are the specific lines that
> need to be changed.
> "You can override the default behavior of the Derby query optimizer by
> including a --DERBY PROPERTIES "
> "Make sure that you adhere to the correct syntax when using the -- DERBY
> PROPERTIES "
> "The following four properties are available for use in a --DERBY
> PROPERTIES "
>
> 2)There is a typo "forc e" instead of "force"
>
> 3)There is another typo "app ropriate " instead of "appropriate "
>
> 4)As for the examples, you can find them in
> db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/optimizerOverrides.out
>
> Please let me know if you need any specific information/examples other
> than what is in that master file.
>
> 5)Also, like Satheesh mentioned, we need to let the users know that they
> can see their optimizer overrides in RUNSTAT output. If they don't see their
> overrides there, it is possible that the user has misspelled
> "DERBY_PROPERTIES" or didn't put - between DERBY and PROPERTIES,
>
> 6)In the end, when you talk about various properties, mention whether they
> belong after FROM or after table. Jeff brought up this point in his
> comments.
>
> Hope this helps,
> Mamta
>
> On 1/20/06, Eric Radzinski <ericrad7@yahoo.com > wrote:
>
> > Here's my attempt at documenting the optimizer overrides that are
> > introduced by DERBY -573.  I'm assuming that this information would be added
> > to the Tuning Guide, probably as a topic within "Performance and
> > optimization" (let me know if anything needs to be added to the Ref Guide or
> > any other book(s)).
> >
> > * Overriding the default optimizer behavior
> > * You can override the default behavior of the Derby query optimizer by
> > including a --DERBY PROPERTIES clause and an associated property as a
> > comment within an SQL statement.
> >
> > Because optimizer overrides are expressed as comments, they must be
> > included at the end of a line. You can specify optimizer override properties
> > for an entire FROM clause, for tables in the FROM clause, or for both.
> >
> > The syntax for FROM clause properties is:
> >
> >  *FROM [ -- DERBY-PROPERTIES propertyName = value ]*
> >      *TableExpression* [,*TableExpression*]*
> >
> > The syntax for table optimizer override properties, which must be
> > included at the end of a TableExpression, is:
> >
> > *{TableName | ViewName }*
> >      [ [ AS ] CorrelationName
> >        [ (SimpleColumnName [ , SimpleColumnName]* ) ] ]
> >        [ -- DERBY-PROPERTIES clause]
> >
> > Note that the space between -- and DERBY-PROPERTIES is optional.
> > *
> > Important:
> > *Make sure that you adhere to the correct syntax when using the -- DERBY
> > PROPERTIES clause. Failure to do so will cause the parser to interpret i t
> > as a comment and ignore it.
> >
> > The following four properties are available for use in a --DERBY
> > PROPERTIES clause:
> > **
> > *constraint*
> > The Derby optimizer chooses an index, including the indexes that enforce
> > constraints, as the access path for query execution if the index is useful.
> > If there is more than one useful index, in most cases Derby chooses the
> > index that is most useful. Use the constraint property to override the index
> > that the optimizer selects and force the use of a particular index or force
> > a table scan. To force the use of the index that enforces a primary key or
> > unique constraint, use the constraint property and specify the unqualified
> > name of the constraint. The constraint property can be specified only on
> > base tables; it cannot be specified on views or derived tables.
> > *
> > index
> > *The index property is similar to the constraint property. To forc e use
> > of a particular index, specify the unqualified index name. To force a table
> > scan, specify null for the index name. The index property can be specified
> > only on base tables; it cannot be specified on views or derived tables.
> > *
> > joinOrder
> > * Use the joinOrder property to override the optimizer's choice of join
> > order for two tables. When the value FIXED is specified, the optimizer will
> > choose the order of tables as they appear in the FROM clause as the join
> > order. Valid values for the joinOrder property include FIXED and UNFIXED.
> >
> > The joinOrder property can be specified on all table expressions.
> > *
> > joinStrategy
> > *Use the joinStrategy property to override the optimizer's choice of
> > join strategy. The two types of join strategy are called *nested loop*and
> > *hash*. In a nested loop join strategy, for each qualifying row in the
> > outer table, Derby uses the app ropriate access path (index or table scan)
> > to find the matching rows in the inner table. In a hash join strategy, Derby
> > constructs a hash table that represents the inner table. For each qualifying
> > row in the outer table, Derby does a quick lookup on the hash table to find
> > the matching rows in the inner table. Derby needs to scan the inner table or
> > index only once to create the hash table. The –DERBY-PROPERTIES parameter
> > must immediately follow the inner table. The joinOrder property can be
> > specified on all table expressions. Valid values include HASH and NESTED
> > LOOP.
> > The joinStrategy property can be specified on all table expressions, but
> > it must be used in conjunction with the joinOrder property. Do not let the
> > optimizer choose the join order.
> >
> > * Examples:
> > I could use some help with some examples here.
> > *
> >
> > ------------------------------
> >
> > What are the most popular cars? Find out at Yahoo! Autos<http://us.rd.yahoo.com/evt=38382/_ylc=X3oDMTEzNWFva2Y2BF9TAzk3MTA3MDc2BHNlYwNtYWlsdGFncwRzbGsDMmF1dG9z/*http://autos.yahoo.com/newcars/popular/thisweek.html+%0A>
> >
> >
>

Re: documenting DERBY -573, optimizer overrides

Posted by Eric Radzinski <er...@yahoo.com>.
  Hi Mamta,
   
  I've fixed the typos (they were related to bad line breaks in the WordPad version that I originally drafted the topic in).
   
  I have a handful of questions about your other comments, though:
   
    4) As for the examples, you can find them in  
  db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/optimizerOverrides.out 
  Please let me know if you need any specific information/examples other than what is in that master file. 
   
  I do need some help extracting relevant examples from the source that you provided.  Which specific ones would be best to use?
   
  5)Also, like Satheesh mentioned, we need to let the users know that they can see their optimizer overrides in RUNSTAT output. If they don't see their overrides there, it is possible that the user has misspelled "DERBY_PROPERTIES" or didn't put - between DERBY and PROPERTIES, 
   
  I'm assuming that this update would go in the tuning guide as a separate topic in the "Working with RunTimeStatistics" section.  Can you confirm?
   
  6)In the end, when you talk about various properties, mention whether they belong after FROM or after table. Jeff brought up this point in his comments.
   
  Please confirm my understanding of where the four properties can be used:  joinOrder can be used with a FROM clause; index, constraint, and joinStrategy can be used only within a TableExpression.  Is that correct?
   
  Thanks,

   
  

Mamta Satoor <ms...@gmail.com> wrote:
    Hi Eric,
   
  Thanks for working on the documentation part of this feature.
   
  Couple comments
  1)Wherever you have referenced DERBY-PROPERTIES as DERBY PROPERTIES, please put a - between the 2 words. Following are the specific lines that need to be changed.
  "You can override the default behavior of the Derby query optimizer by including a --DERBY PROPERTIES "
  "Make sure that you adhere to the correct syntax when using the -- DERBY PROPERTIES "
  "The following four properties are available for use in a --DERBY PROPERTIES "
   
  2)There is a typo "forc e" instead of "force"
   
  3)There is another typo "app ropriate " instead of "appropriate "
   
  4)As for the examples, you can find them in  
  db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/optimizerOverrides.out 
  Please let me know if you need any specific information/examples other than what is in that master file.
   
  5)Also, like Satheesh mentioned, we need to let the users know that they can see their optimizer overrides in RUNSTAT output. If they don't see their overrides there, it is possible that the user has misspelled "DERBY_PROPERTIES" or didn't put - between DERBY and PROPERTIES, 
   
  6)In the end, when you talk about various properties, mention whether they belong after FROM or after table. Jeff brought up this point in his comments.
   
  Hope this helps,
  Mamta
   
  On 1/20/06, Eric Radzinski <ericrad7@yahoo.com > wrote: 
      Here's my attempt at documenting the optimizer overrides that are introduced by DERBY -573.  I'm assuming that this information would be added to the Tuning Guide, probably as a topic within "Performance and optimization" (let me know if anything needs to be added to the Ref Guide or any other book(s)). 
   
    Overriding the default optimizer behavior
  You can override the default behavior of the Derby query optimizer by including a --DERBY PROPERTIES clause and an associated property as a comment within an SQL statement. 
   
  Because optimizer overrides are expressed as comments, they must be included at the end of a line. You can specify optimizer override properties for an entire FROM clause, for tables in the FROM clause, or for both. 
   
  The syntax for FROM clause properties is:
   
   FROM [ -- DERBY-PROPERTIES propertyName = value ]
       TableExpression [,TableExpression]*
   
  The syntax for table optimizer override properties, which must be included at the end of a TableExpression, is:
   
  {TableName | ViewName }
       [ [ AS ] CorrelationName
         [ (SimpleColumnName [ , SimpleColumnName]* ) ] ]
         [ -- DERBY-PROPERTIES clause]
   
  Note that the space between -- and DERBY-PROPERTIES is optional. 
   
  Important: 
Make sure that you adhere to the correct syntax when using the -- DERBY PROPERTIES clause. Failure to do so will cause the parser to interpret i t as a comment and ignore it.
   
  The following four properties are available for use in a --DERBY PROPERTIES clause:
   
  constraint 
  The Derby optimizer chooses an index, including the indexes that enforce constraints, as the access path for query execution if the index is useful. If there is more than one useful index, in most cases Derby chooses the index that is most useful. Use the constraint property to override the index that the optimizer selects and force the use of a particular index or force a table scan. To force the use of the index that enforces a primary key or unique constraint, use the constraint property and specify the unqualified name of the constraint. The constraint property can be specified only on base tables; it cannot be specified on views or derived tables. 
   
  index
  
The index property is similar to the constraint property. To forc e use of a particular index, specify the unqualified index name. To force a table scan, specify null for the index name. The index property can be specified only on base tables; it cannot be specified on views or derived tables.    
  joinOrder
  Use the joinOrder property to override the optimizer's choice of join order for two tables. When the value FIXED is specified, the optimizer will choose the order of tables as they appear in the FROM clause as the join order. Valid values for the joinOrder property include FIXED and UNFIXED. 
   
  The joinOrder property can be specified on all table expressions.
   
  joinStrategy
  
Use the joinStrategy property to override the optimizer's choice of join strategy. The two types of join strategy are called nested loop and hash. In a nested loop join strategy, for each qualifying row in the outer table, Derby uses the app ropriate access path (index or table scan) to find the matching rows in the inner table. In a hash join strategy, Derby constructs a hash table that represents the inner table. For each qualifying row in the outer table, Derby does a quick lookup on the hash table to find the matching rows in the inner table. Derby needs to scan the inner table or index only once to create the hash table. The –DERBY-PROPERTIES parameter must immediately follow the inner table. The joinOrder property can be specified on all table expressions. Valid values include HASH and NESTED LOOP.    
  The joinStrategy property can be specified on all table expressions, but it must be used in conjunction with the joinOrder property. Do not let the optimizer choose the join order.
   
  Examples:
  I could use some help with some examples here.
    
---------------------------------
  
What are the most popular cars? Find out at Yahoo! Autos   
  
  
  



  



			
---------------------------------
Yahoo! Photos
 Got holiday prints? See all the ways to get quality prints in your hands ASAP.

Re: documenting DERBY -573, optimizer overrides

Posted by Mamta Satoor <ms...@gmail.com>.
Hi Eric,

Thanks for working on the documentation part of this feature.

Couple comments
1)Wherever you have referenced DERBY-PROPERTIES as DERBY PROPERTIES, please
put a - between the 2 words. Following are the specific lines that need to
be changed.
"You can override the default behavior of the Derby query optimizer by
including a --DERBY PROPERTIES "
"Make sure that you adhere to the correct syntax when using the -- DERBY
PROPERTIES "
"The following four properties are available for use in a --DERBY
PROPERTIES "

2)There is a typo "forc e" instead of "force"

3)There is another typo "app ropriate " instead of "appropriate "

4)As for the examples, you can find them in
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/optimizerOverrides.out

Please let me know if you need any specific information/examples other than
what is in that master file.

5)Also, like Satheesh mentioned, we need to let the users know that they can
see their optimizer overrides in RUNSTAT output. If they don't see their
overrides there, it is possible that the user has misspelled
"DERBY_PROPERTIES" or didn't put - between DERBY and PROPERTIES,

6)In the end, when you talk about various properties, mention whether they
belong after FROM or after table. Jeff brought up this point in his
comments.

Hope this helps,
Mamta

On 1/20/06, Eric Radzinski <ericrad7@yahoo.com > wrote:

> Here's my attempt at documenting the optimizer overrides that are
> introduced by DERBY -573.  I'm assuming that this information would be added
> to the Tuning Guide, probably as a topic within "Performance and
> optimization" (let me know if anything needs to be added to the Ref Guide or
> any other book(s)).
>
> * Overriding the default optimizer behavior
> * You can override the default behavior of the Derby query optimizer by
> including a --DERBY PROPERTIES clause and an associated property as a
> comment within an SQL statement.
>
> Because optimizer overrides are expressed as comments, they must be
> included at the end of a line. You can specify optimizer override properties
> for an entire FROM clause, for tables in the FROM clause, or for both.
>
> The syntax for FROM clause properties is:
>
>  *FROM [ -- DERBY-PROPERTIES propertyName = value ]*
>      *TableExpression* [,*TableExpression*]*
>
> The syntax for table optimizer override properties, which must be included
> at the end of a TableExpression, is:
>
> *{TableName | ViewName }*
>      [ [ AS ] CorrelationName
>        [ (SimpleColumnName [ , SimpleColumnName]* ) ] ]
>        [ -- DERBY-PROPERTIES clause]
>
> Note that the space between -- and DERBY-PROPERTIES is optional.
> *
> Important:
> *Make sure that you adhere to the correct syntax when using the -- DERBY
> PROPERTIES clause. Failure to do so will cause the parser to interpret i t
> as a comment and ignore it.
>
> The following four properties are available for use in a --DERBY
> PROPERTIES clause:
> **
> *constraint*
> The Derby optimizer chooses an index, including the indexes that enforce
> constraints, as the access path for query execution if the index is useful.
> If there is more than one useful index, in most cases Derby chooses the
> index that is most useful. Use the constraint property to override the index
> that the optimizer selects and force the use of a particular index or force
> a table scan. To force the use of the index that enforces a primary key or
> unique constraint, use the constraint property and specify the unqualified
> name of the constraint. The constraint property can be specified only on
> base tables; it cannot be specified on views or derived tables.
> *
> index
> *The index property is similar to the constraint property. To forc e use
> of a particular index, specify the unqualified index name. To force a table
> scan, specify null for the index name. The index property can be specified
> only on base tables; it cannot be specified on views or derived tables. *
>
> joinOrder
> * Use the joinOrder property to override the optimizer's choice of join
> order for two tables. When the value FIXED is specified, the optimizer will
> choose the order of tables as they appear in the FROM clause as the join
> order. Valid values for the joinOrder property include FIXED and UNFIXED.
>
> The joinOrder property can be specified on all table expressions.
> *
> joinStrategy
> *Use the joinStrategy property to override the optimizer's choice of join
> strategy. The two types of join strategy are called *nested loop* and *
> hash*. In a nested loop join strategy, for each qualifying row in the
> outer table, Derby uses the app ropriate access path (index or table scan)
> to find the matching rows in the inner table. In a hash join strategy, Derby
> constructs a hash table that represents the inner table. For each qualifying
> row in the outer table, Derby does a quick lookup on the hash table to find
> the matching rows in the inner table. Derby needs to scan the inner table or
> index only once to create the hash table. The –DERBY-PROPERTIES parameter
> must immediately follow the inner table. The joinOrder property can be
> specified on all table expressions. Valid values include HASH and NESTED
> LOOP.
> The joinStrategy property can be specified on all table expressions, but
> it must be used in conjunction with the joinOrder property. Do not let the
> optimizer choose the join order.
>
> * Examples:
> I could use some help with some examples here.
> *
>
> ------------------------------
>
> What are the most popular cars? Find out at Yahoo! Autos<http://us.rd.yahoo.com/evt=38382/_ylc=X3oDMTEzNWFva2Y2BF9TAzk3MTA3MDc2BHNlYwNtYWlsdGFncwRzbGsDMmF1dG9z/*http://autos.yahoo.com/newcars/popular/thisweek.html+%0A>
>
>