WL#8241: Hints for Join Buffering and Batched Key Access

Using the new syntax and infrastructure for hints (see WL#8016 and WL#8017), add
hints to decide whether join buffering or batched key access can be used for a
specified list of tables.

F-1: The hints specified in this work log shall conform with the syntax for
     table level hints as specified in WL#8017.  (Repeated in HLS).
F-2: If a table listed in a BNL hint is an inner table of a join operation and 
     ref/eq-ref access is not used for this table, block nested loop join 
     buffering shall be used.
F-3: Block-nested loops shall not be used for tables listed in a NO_BNL hint.
F-3: If a table listed in a BKA hint is an inner table of a join operation and 
     ref/eq-ref access is used for this table, batched key access will be used.
F-4: Batched key access shall not be used for tables listed in a NO_BKA hint.

NF-1: No warnings will be given if a BNL or BKA hint is ignored because it is 
      not compatible with the selected query plan.

The hints for this work log is all table level hints as specified in WL#8017
Table level hints have the following syntax:

  HINT([@QB_NAME] [table[, table] ...])
  or 
  HINT([table@QB_NAME [, table@QB_NAME] ...])

The optional QB_NAME is used to specify the query block of the specified
table(s). See WL#8017 for details.  For simplicity, QB_NAME is not included in
specifications below.

Table name is optional. Hint is applied to all tables of the query block 
if no table name is specified.

Note that table alias is used to refer to a table.  (If table alias is not
specified, the table name is used. Schema name is not allowed in table
specifications.)

The following hints will be implemented in this work log:

Hints for join buffering (Block nested loop):

1. Specify that join buffering (BNL) should be used for the specified tables.

   Syntax:
        /*+ BNL([table[, table] ...]) */
      
   BNL will be used for the specified table(s) when appropriate.  That is, the 
   table must be an inner table of a join operation and ref access is not 
   used.  For other tables, the hint is ignored without any warning.

2. Specify that join buffering (BNL) should NOT be used for the specified  
   tables.
   
   Syntax:
        /*+ NO_BNL([table[, table] ...]) */

Hints for Batched Key Access (BKA)

3. Specify that batched key access (BKA) should be used for the specified 
   tables.
   
   Syntax:
        /*+ BKA([table[, table] ...]) */

   BKA will be used for the specified table(s) when appropriate.
   That is, the table must be an inner table of a join operation
   and ref access is used.  For other tables, the hint is 
   ignored without any warning. (Note that there already exists code that 
   checks whether BKA is applicable.  The hint will provide a list of tables 
   that may be considered by the existing implementation.)

4. Specify that batched key access (BKA) should NOT be used for the
   specified tables.
   
   Syntax:
        /*+ NO_BKA([table[, table] ...]) */

All the above hints will override any setting of the optimizer_switch session
variable for the corresponding switches.


NOTE:
BNL and BKA always apply to a table which receives records from the
previous tables and buffers them. So the tables mentioned in the hints of this
WL is the receiving table, not the sender table. EXAMPLE:

    SELECT /*+ BNL(t1) */ FROM t1, t2;

If the optimizer chooses t2 as the first table to process, block-nested loop
join will be applied to t1 by buffering the rows from *t2* before starting to
read from t1.  On the other hand, if the optimizer chooses t1 as first table to
process, the hint will have no effect since t1 is a sender only table.