Sunday, April 26, 2009

Oracle : Using WITH Clause for improving performance

Many times using inline views destroy the execution plans. This is mainly because the execution plan of inline view interfere with the overall execution plan of the query.

To counter this, inline view can be taken to the top inside WITH clause. But this alone will not help. We need to incorporate hint /*+ materialize */ , as only then database treats the view as a physical table. Database creates a temporary table out of the view which is then used in the query. Execution plan is found far improved than before.


Query with following structure showed nested loop join between tables tab1 and tab2. This mainly was influenced by the inline view. Specifying USE_HASH hint also does not work.

SELECT col1,
col2,
Sum(col4)
FROM tab1,
tab2,
(SELECT DISTINCT Last_day(To_date(dates,'DD-MM-YYYY')) end_dt,
Add_months(Last_day(To_date(dates,'DD-MM-YYYY')) + 1,
-1) strt_dt,
To_char(To_date(dates,'DD-MON-YYYY'),'MMYYYY') mon_dt
FROM (SELECT To_char(Add_months(SYSDATE,-10) + (LEVEL - 1),'DD-MON-YYYY') AS dates
FROM dual
CONNECT BY To_number(To_char((Add_months(SYSDATE,-10) + (LEVEL - 1)),'YYYYMMDD')) <= To_number(To_char(SYSDATE,'YYYYMMDD')))) tim
WHERE tab1.assign_dt <= tim.end_dt
AND tab1.assign_dt >= tim.strt_dt
AND tab1.col3 = tab2.col3
GROUP BY col1,
col2

Changing the query to the following using WITH caluse leads to perfect execution plan using HASH join .


WITH TIM as (
(SELECT /*+ materialize */ DISTINCT Last_day(To_date(dates,'DD-MM-YYYY')) end_dt,
Add_months(Last_day(To_date(dates,'DD-MM-YYYY')) + 1,
-1) strt_dt,
To_char(To_date(dates,'DD-MON-YYYY'),'MMYYYY') mon_dt
FROM (SELECT To_char(Add_months(SYSDATE,-10) + (LEVEL - 1),'DD-MON-YYYY') AS dates
FROM dual
CONNECT BY To_number(To_char((Add_months(SYSDATE,-10) + (LEVEL - 1)),'YYYYMMDD')) <= To_number(To_char(SYSDATE,'YYYYMMDD'))))
)
SELECT col1,
col2,
Sum(col4)
FROM tab1,
tab2,
tim
WHERE tab1.assign_dt <= tim.end_dt
AND tab1.assign_dt >= tim.strt_dt
AND tab1.col3 = tab2.col3
GROUP BY col1,
col2


6 comments:

Anonymous said...

Lets say I want to refer tab1 or tab2 in TIM (the with clause) then how to do that.

Nitin Aggarwal said...

You can not refer to tab1 or tab2 in the inline view of the original query (without WITH clause) also. you need to use these tables again in the view which will in no way be referring to tab1 & tab2 of the main query. So is the case with the subquery in the WITH clause.

Anonymous said...

Does Informatica support WITH clause

Nitin Aggarwal said...

Informatica will support any sql that is compatible with the database platform it is sourcing data from or loading data into. We just need to override the sql in informatica workflow session.

Anonymous said...

Nitin,
you may want to rethink about your last statement. Informatica doesn't support WITH clause for SQL Server db platform. We are having lot of headaches trying to make lookup override work with WITH clause in SQL Server database.

Nitin Aggarwal said...

That's why the title says "Oracle:..." and as I noted in one of my comments:
Informatica will support any sql that is "compatible with the database platform it is sourcing data from" or loading data into. We just need to override the sql in informatica workflow session.