OWB 11gR2 - Subquery

One of the new mapping operators introduced in 11gR2 is the much talked about (over the years) subquery filter operator. As well as the heterogeneous capabilities added, a number of enhancements to existing operators (such as joiner and lookup operator) and a few new ones were added. The subquery filter supports the SQL grammar for exists, in, no exists and not in.

This simple example illustrated here illustrates a scenario where we want to get all the addresses of customers where they have a high credit limit and are married for example. The filter on customers gets the customers who are married and have a credit limit > 10000;


subquery_example1

The subquery filter operator is defined to use the Exists query type and a condition since we have a correlated subquery;


subquery_example2

The generated SQL with the correlated subquery looks like this;

SELECT
  "ADDRESSES"."CUSTOMER_ID" "CUSTOMER_ID",
  "ADDRESSES"."PHONE_NUMBER" "PHONE_NUMBER",
  "ADDRESSES"."CUST_STREET_ADDRESS" "CUST_STREET_ADDRESS",
  "ADDRESSES"."CUST_POSTAL_CODE" "CUST_POSTAL_CODE",
  "ADDRESSES"."CITY_ID" "CITY_ID"
FROM
  "ADDRESSES"  "ADDRESSES"
  WHERE
  (EXISTS (SELECT
  1 
  FROM
  "CUSTOMERS"  "CUSTOMERS"
  WHERE
  ( "CUSTOMERS"."MARITAL_STATUS" = 'married' )  AND
  ( "CUSTOMERS"."CREDIT_LIMIT" > 10000)  AND
  ( "CUSTOMERS"."ID" = "ADDRESSES"."CUSTOMER_ID" ) ))

When the mapping is executed the result is a table with contact information;


subquery_example4 

The OWB mapping operators are all pluggable together, so we can take this example further to utilize a lookup operator for example, so the CITY_ID is also taken from addresses and we lookup the CITY_NAME and STATE_PROVINCE from the CITIES table, and the resultant table has this information.
So the mapping now looks like;
subquery_example5
The data after execution in the resultant table has;


subquery_example6

So the subquery filter lets you model more scenarios than previous releases, there are still some cases that are not supported such as using less than (<) for example rather than exists/in etc. So the following style where clause 'where quantity < (select max(quantity) from sales where book_key = s.book_key)' cannot be modeled.

So what to do in this case? There is another new mapping related feature which is interesting is the in-line view operator. The view operator has an inline property such that if this is checked the SQL defined within OWB for the view will be placed 'in-line' in the generated code rather than the view name.

Here we have seen the subquery filter mapping operator introduced in 11gR2. The subquery filter supports the SQL grammar for exists, in, no exists and not in.

Comments:

Note....if you want an IN or NOT IN style query such as; SELECT name FROM emp WHERE dept_id IN (SELECT dept_id FROM dept WHERE budget > 20,000). Then for the in/out group representing the EMP table in the subquery filter operator you have to click on the DEPT_ID column and set the property 'IN Matching Attribute' and pick the source select's column to match, so you'd pick DEPT_ID from the DEPT column - this will generate the DEPT_ID IN (SELECT DEPT_ID part of the clause. Cheers David

Posted by David Allan on April 01, 2010 at 05:30 AM PDT #

Hi David, How do you implement in (select ....) in 10G? Please advise, Thanks, Regards, Michael

Posted by Michael Chiu on June 06, 2010 at 08:18 AM PDT #

Hi Michael In the older releases you have to redesign your query using joins (or use a view to hide). Cheers David

Posted by David Allan on June 07, 2010 at 07:56 AM PDT #

Hi David,

Can you use an inline view that uses relational tables to construct an xmltype to have control over the selected data for the view?

Posted by Michael Reitsma on June 30, 2011 at 02:14 AM PDT #

Hi Michael

Yes you can do that.

Cheers
David

Posted by David Allan on June 30, 2011 at 09:32 AM PDT #

Hallo, David, I am using subquery filters and I get an awful efficiency where I run the mapping (1h 15 min with subquery filter, 10 min without) is this normal?
Thanx

Posted by guest on May 07, 2012 at 04:29 AM PDT #

Depends on the specific case, it sounds like you scenario using the join is much better suited with it. Did you compare execution plans of each to see why?

Cheers
David

Posted by David on May 07, 2012 at 03:50 PM PDT #

how to get employee details without using where clause in sql

Posted by mastan on June 01, 2012 at 12:12 AM PDT #

Need some more details about your question in order to help.
Cheers
David

Posted by David on June 01, 2012 at 07:54 AM PDT #

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