OWB 11gR2 – Lookup operator

The lookup operator in OWB 11gR2 has been supercharged letting you utilize it in many more scenarios and generate more efficient SQL and in general be more flexible than before. The enhancements were requested from large use case scenarios and are summarized below (and expand on the doc here);

  • Lookup operator supports multiple lookup objects - more efficient use of screen real estate.

  • Lookup operator supports complex lookup conditions including a range of condition types such as =, <, >, or BETWEEN...AND (any boolean expression basically).

  • Preferences for which row to return in case of multiple rows in lookup result (ANY, FIRST,LAST,nTH).

Below we can see that there are multiple output groups in the lookup operator. We can lookup all the surrogate keys for example of one sales tables in a single lookup.

image

For each output group you can specify the table which you will be looking up information in, below we see that OUTGRP3 is looking up information in CHANNELS_TAB, note we could rename OUTGRP3 to have a meaningful name.

image

The lookup condition as before by default is a simple column equals column style condition for each output group and input pair. Note now in the panel there is a 'Freestyle Editing' option.

image

 

If you select the 'Freestyle Editing' you will change into a more traditional OWB expression editor with the available inputs in the tree and now the ability to do different types of lookups including complex expressions.

image

Last but definitely not least, we have the ability to control for each output group how the lookup row is to returned, do we want a single row and let it be;

  • any row (uses rownum = 1)
  • first row - uses MIN(column) KEEP (DENSE_RANK FIRST ORDER BY column_4_order ASC)
  • last row - uses MAX(column) KEEP (DENSE_RANK FIRST ORDER BY column_4_order ASC)
  • nTH row

image

We can also decide to make the mapping error if there are multiple matching lookup entries. How does it error? Well the generated code would cause the map to error with the SQL error;

ORA-01427: single-row subquery returns more than one row

Or we can decide to return all the matching rows.

For the case where no row matches you can still specify the default values to return.

image

These defaults are then used in the generated code. Many more options than before as you can see.

You'll also see from the generated code that a scalar subquery is generated for the single-row style lookups.

Advice on Using The Wizard? How do I use the lookup?

Well the quickest way of building one up when you add in a lookup operator is to exit the lookup wizard at Step 4 when defining the Input Attributes. Its much easier just to use the mapper to draw the inputs rather than type them in. So after I exit from the lookup wizard I map the input attributes then edit the lookup operator and define the actual lookups etc.

Some interesting changes there that were included in the lookup operator for the OWB 11gR2 release.

Comments:

Performance wise which is better lookup or joiner & why ?

Posted by guest on December 22, 2012 at 09:13 PM PST #

The lookup is also implemented as a join in some cases. In some it is a scalar subquery which can be faster. The lookup operator in OWB has additional capabilities such as defaults when no record matched and stuff like that so it provides more functionality - its like a super join.
Cheers
David

Posted by David on December 24, 2012 at 10:25 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today