| Download Docs | Site Map | Glossary | |
|
|
|||
| bea.com | products | dev2dev | support | askBEA |
|
|
|
|||||||
 |
| e-docs > Liquid Data for WebLogic > Building Queries and Data Views > Optimization Hints for Joins |
|
Building Queries and Data Views
|
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.
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.
Apply these rules to determine the correct hint to choose.
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.
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
|
|
|
|
||
 |
|
|
 |
 |
 |
|
||