bea.com | products | dev2dev | support | askBEA

Download Docs | Site Map | Glossary
Search

Building Queries and Data Views

Optimization Hints for Joins

A query hint is a way to supply more information to the Liquid Data server about how to process the join.

The Optimize tab on the Data View Builder provides a drop-down menu for where you can select a hint for each join in the query that helps the Liquid Data Server choose the most appropriate join algorithm. (See the Optimize tab in Figure 4-1.)

Query hints appear in the query as character strings enclosed within braces {--! hint !--}. They specify which join algorithm should be selected when the query runs. The Join Hints frame contains a drop-down list of data source pairs, and a table that shows all the joins for each pair. Only source pairs that have join conditions across them appear in the drop-down list. For each join condition in the table, you can provide a hint about how to join the data most efficiently.

After you run the query, you can always return to the Optimize view to change the source orders and the hints for each join operation.

Choosing the Best Hint

The Liquid Data Server has three hints to choose from when it processes a join request. By default no hints are specified. To add a hint, select the join to which you want the hint to apply and choose a hint from the drop-down Query Hints list. The available hints are shown below.

Table 4-1 Optimization Hints

Hint | Description | Syntax
--- | --- | ---
No Hint (default) | Index |
Left | Parameter Pass to the Left (ppleft) | {--! ppleft !--}
Right | Parameter Pass to the Right (ppright) | {--! ppright !--}
Merge | Merge | {--! merge !--}

Apply these rules to determine the correct hint to choose.

Table 4-2 When to Use a Hint

Use this Hint | When
--- | ---
No Hint (default) | The size of the source identified on the right side of the hint is small enough to fit into memory. Where the left and right sources are generally equal in size. There is at least one non-relational source used in the join.
Merge | Both the sources in the join are relational databases. Both the sources are large and cannot fit into memory.
Parameter Passing (Left or Right) | One of the sources has fewer objects than the other. When you choose the direction for the Parameter Passing hint, always choose the database to the left or right with the larger number of items as the receiver. For example, if there are more items on the right side of the equality, choose Right. The direction indicated in the hint identifies the side in the equation that receives the parameter.

Notes:

- Using optimization hints can help you improve performance on equijoin conditions, which contain only one equality. The optimization features do not support complex join conditions, such as (A eq B) eq (C eq D). This type of conditional expression would be treated as A eq (B eq C eq D).

- Choosing the wrong direction could degrade performance instead of improving it.

Using Parameter Passing Hints (ppleft or ppright)

Choose a Parameter Passing hint when one of the sources has fewer objects than the other. In order to use the parameter passing hints (ppleft and ppright) effectively, you need to know which data sources contain the larger data sets.

When you choose the direction for the Parameter Passing hint, always choose the data source to the left or right with the larger number of items as the receiver. For example, if there are more items on the right side of the equality, then pass the parameter to the Right. The direction indicated in the hint identifies the side in the equation that receives the parameter. In other words, the hints are named for the receiver.

Consider the following example, which is described fully in Example 1: Simple Joins in Query Cookbook.

Listing 4-1 XQuery with ppright Hints

>     {--	Generated by Data View Builder 1.0 	--}

>     <customers>
>     	{
>     	for $PB-WL.CUSTOMER_1 in document("PB-WL")/db/CUSTOMER
>     	where ($#wireless_id of type xs:string  eq  $PB-WL.CUSTOMER_1/CUSTOMER_ID)
>     	return
>     	<customer id={$PB-WL.CUSTOMER_1/CUSTOMER_ID}>
>     		<first_name>{ xf:data($PB-WL.CUSTOMER_1/FIRST_NAME) }</first_name>
>     		<last_name>{ xf:data($PB-WL.CUSTOMER_1/LAST_NAME) }</last_name>
>     		<orders>
>     			{
>     			for $PB-BB.CUSTOMER_ORDER_3 in document("PB-BB")/db/CUSTOMER_ORDER
>     			where
>     ($PB-WL.CUSTOMER_1/CUSTOMER_ID  eq  {--! ppright !--} $PB-BB.CUSTOMER_2/CUSTOMER_ID)
>     			return
>     			<order id={$PB-BB.CUSTOMER_ORDER_3/ORDER_ID} date={$PB-BB.CUSTOMER_ORDER_3/ORDER_DATE}></order>
>     			}
>     		</orders>
>     	</customer>
>     	}
>     </customers>

Let's focus on the second join in the example; the join between PB-WL customer IDs and PB-BB customer IDs:

>     			where
>     ($PB-WL.CUSTOMER_1/CUSTOMER_ID  eq  {--! ppright !--} $PB-BB.CUSTOMER_2/CUSTOMER_ID)

In the example above, the where clause indicates that the PB-WL data source CUSTOMER table will output only one customer ID. We can assume the PB-BB data source has a larger amount of customer IDs. We can optimize the join by providing the hint shown above (ppright), which tells the server to retrieve the PB-WL customer information first and then pass the CUSTOMER ID as a parameter to the right to look for matches in the PB-BB data source. The engine will thus require much less memory and respond faster than if no hint was provided. Then it might have iterated through multiple records, and for each one asked the database to select the one with a highly optimized query.

Using a Merge Hint

Choose a merge hint when both the sources in the join are relational databases and both the sources are large and cannot fit into memory.

The following example shows the XQuery for a merge hint.

Listing 4-2 XQuery with Merge Hint

>     <root>
>     	{
>     	for $Wireless.CUSTOMER_1 in document("Wireless")/db/CUSTOMER
>     	for $Broadband.CUSTOMER_2 in document("Broadband")/db/CUSTOMER
>     	where ($Wireless.CUSTOMER_1/CUSTOMER_ID  eq  {--!merge!--} $Broadband.CUSTOMER_2/CUSTOMER_ID)
>     	return
>     	<row>
>     		<CUSTOMER_ID>{ xf:data($Broadband.CUSTOMER_2/CUSTOMER_ID) }</CUSTOMER_ID>
>     	</row>
>     	}
>     </root>

A merge join requires a minimal amount of memory to operate; however, it requires that the input be sorted on join attributes. A query using a merge join might have slower response time than a query without a hint, but the memory footprint is typically much smaller with the merge join.

Note: A merge join in a character column might yield unexpected results because the collating sequence for each database may be vary. See Table 4-3 for an example of how incompatible ordering sequences for strings from two different vendors can affect query results.

Table 4-3 Collation Sequences for Some Data Types Vary by Database Vendor

Oracle | MS SQL
--- | ---
ORDER_ID_8009_4 | ORDER_ID_8009_4
ORDER_ID_8010_0 | ORDER_ID_801_0
ORDER_ID_8011_0 | ORDER_ID_8010_0
ORDER_ID_8012_0 | ORDER_ID_8011_0
ORDER_ID_801_0 | ORDER_ID_8011_1
ORDER_ID_801_1 | ORDER_ID_8012_0

To ensure predictable results you should use an index join when merging character (varchar, string, and so forth) columns from different databases.