Home » SQL & PL/SQL » SQL & PL/SQL » "with" clause Vs. multiple views (9iR2)
| "with" clause Vs. multiple views [message #604076] |
Mon, 23 December 2013 15:44  |
Amine
Messages: 375
Registered: March 2010
|
Senior Member |
|
|
Hi all,
I have a "strange" problem. I have this query :
set timing on
select *
from
(
with v_1 as
(
select
id
, c1
, c2
from table1 /* contains 1795062 rows */
where 1 = 1
),
v_2 as
(
select
id
, c3
, c4
from table2 /* contains 456392 rows */
where 1 = 1
) ,
v_3 as
(
select
v_1.id
, c1
, c2
-- ----
, c3
, c4
from v_1, v_2
where 1 = 1
and v_1.id = v_2.id(+)
)
select v_1.id, v_11, v_22
from v_3
where 1 = 1
)
/
Now when I create the 3 views and do the select
select v_1.id, v_11, v_22
from v_3
where 1 = 1
The query runs in a reasonable time.
Can you explain me how the "with clause" works ? I thought it was the same as creating views without creating them through a create statement, but it seems like v_1 is calculated then v_2 then v_3 then the last query is performed ...
Thanks in advance,
Amine
|
|
|
|
|
|
| Re: "with" clause Vs. multiple views [message #604234 is a reply to message #604076] |
Thu, 26 December 2013 08:35  |
Amine
Messages: 375
Registered: March 2010
|
Senior Member |
|
|
Hi all,
Thanks BlackSwan for your help.
Here is what you asked.
drop table t1;
create table t1
(
ID NUMBER(12) ,
P NUMBER(2) ,
U NUMBER(6) ,
DAT DATE
)
/
drop table t2;
create table t2
(
ID NUMBER(12) ,
U NUMBER(6) ,
F NUMBER(6) ,
DAT_START DATE ,
DAT_END DATE
)
/
alter table t1 add constraint pk_t1 primary key (id, dat)
/
alter table t2 add constraint pk_t2 primary key (id, dat_start)
/
Here is the query I want to run and that is very solw :
SQL> set autotrace traceonly explain
SQL> COLUMN PLAN_PLUS_EXP FORMAT A80
SQL> select *
2 from
3 (
4 with
5 v_rng_t1 as
6 (
7 select
8 id
9 , p
10 , u
11 , dat dat_start_t1
12 , lead(dat - 1, 1, sysdate) over (partition by id order by dat) dat_end_t1
13 , row_number() over (partition by id order by dat) rn
14 from t1
15 where 1 = 1
16 ),
17 v_rng_t2 as
18 (
19 select
20 id
21 , u
22 , dat_start
23 , dat_end
24 , f
25 , nvl((select rn from v_rng_t1 where id = t2.id and dat_start >= dat_start_t1 and dat_start <= dat_end_t1), -1) rn
26 from t2
27 where 1 = 1
28 ) ,
29 v_verif as
30 (
31 select
32 v_rng_t1.id
33 , p
34 , v_rng_t1.u u_t1
35 , v_rng_t2.u u_t2
36 , f
37 , v_rng_t1.rn rn_t1
38 , v_rng_t2.rn rn_t2
39 , dat_start_t1
40 , dat_end_t1
41 , dat_start
42 , dat_end
43 from v_rng_t1, v_rng_t2
44 where 1 = 1
45 and v_rng_t1.id = v_rng_t2.id(+)
46 and v_rng_t1.rn = v_rng_t2.rn(+)
47 )
48 select id
49 , u_t1
50 , dat_start_t1
51 , dat_start
52 from v_verif
53 where 1 = 1
54 and dat_end is null
55 and dat_start is not null
56 and
57 (
58 (p <> 10 and p <> 35)
59 or u_t1 <> u_t2
60 )
61 )
62 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 3 RECURSIVE EXECUTION OF 'SYS_LE_3_0'
3 1 TEMP TABLE TRANSFORMATION
4 3 FILTER
5 4 MERGE JOIN (OUTER)
6 5 SORT (JOIN)
7 6 VIEW
8 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6701_E60F6FC4'
9 5 SORT (JOIN)
10 9 VIEW
11 10 TABLE ACCESS (FULL) OF 'T2'
Now, I create the three (03) views used in the with clause explicitly :
SQL> create or replace view
2 v_rng_t1 as
3 (
4 select
5 id
6 , p
7 , u
8 , dat dat_start_t1
9 , lead(dat - 1, 1, sysdate) over (partition by id order by dat) dat_end_t1
10 , row_number() over (partition by id order by dat) rn
11 from t1
12 where 1 = 1
13 );
View created.
SQL> create or replace view
2 v_rng_t2 as
3 (
4 select
5 id
6 , u
7 , dat_start
8 , dat_end
9 , f
10 , nvl((select rn from v_rng_t1 where id = t2.id and dat_start >= dat_start_t1 and dat_start <= dat_end_t1), -1) rn
11 from t2
12 where 1 = 1
13 );
View created.
SQL> create or replace view
2 v_verif as
3 (
4 select
5 v_rng_t1.id
6 , p
7 , v_rng_t1.u u_t1
8 , v_rng_t2.u u_t2
9 , f
10 , v_rng_t1.rn rn_t1
11 , v_rng_t2.rn rn_t2
12 , dat_start_t1
13 , dat_end_t1
14 , dat_start
15 , dat_end
16 from v_rng_t1, v_rng_t2
17 where 1 = 1
18 and v_rng_t1.id = v_rng_t2.id(+)
19 and v_rng_t1.rn = v_rng_t2.rn(+)
20 );
View created.
And I run the query :
SQL> select id
2 , u_t1
3 , dat_start_t1
4 , dat_start
5 from v_verif
6 where 1 = 1
7 and dat_end is null
8 and dat_start is not null
9 and
10 (
11 (p <> 10 and p <> 35)
12 or u_t1 <> u_t2
13 )
14 ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FILTER
2 1 MERGE JOIN (OUTER)
3 2 SORT (JOIN)
4 3 VIEW OF 'V_RNG_T1'
5 4 WINDOW (SORT)
6 5 TABLE ACCESS (FULL) OF 'T1'
7 2 SORT (JOIN)
8 7 VIEW OF 'V_RNG_T2'
9 8 TABLE ACCESS (FULL) OF 'T2'
About the SQL trace, I couldn't get it cause the "with clause" query was really very slow (launched it at 11:30 AM and at 01:30 PM it was still running).
So, I get no trace file.
So to summrize : why the with clause query is so slow ? Why I have to create the views explicitly to get a reasonable time response ?
Thanks in advance for the help,
Amine
|
|
|
|
| Re: "with" clause Vs. multiple views [message #604237 is a reply to message #604234] |
Thu, 26 December 2013 08:48 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
They're not equivalent. They'll give the same results but they're aren't the same from the amount of work being requested.
Remember:
is the same as
not:
SELECT * FROM (<view query>)
So try the equivalent of this:
with v_1 as
(
select
id
, c1
, c2
from table1 /* contains 1795062 rows */
where 1 = 1
),
v_2 as
(
select
id
, c3
, c4
from table2 /* contains 456392 rows */
where 1 = 1
)
select
v_1.id
, v_11
, v_22
from v_1, v_2
where 1 = 1
and v_1.id = v_2.id(+)
ie. merge the 3rd with into the main select
|
|
|
|
|
|
| Re: "with" clause Vs. multiple views [message #604334 is a reply to message #604076] |
Sat, 28 December 2013 15:42 |
Amine
Messages: 375
Registered: March 2010
|
Senior Member |
|
|
do you mean this :
SQL> set autotrace traceonly explain
SQL> COLUMN PLAN_PLUS_EXP FORMAT A80
SQL> select *
2 from
3 (
4 with
5 v_rng_t1 as
6 (
7 select
8 id
9 , p
10 , u
11 , dat dat_start_t1
12 , lead(dat - 1, 1, sysdate) over (partition by id order by dat) dat_end_t1
13 , row_number() over (partition by id order by dat) rn
14 from t1
15 where 1 = 1
16 ),
17 v_rng_t2 as
18 (
19 select
20 id
21 , u
22 , dat_start
23 , dat_end
24 , f
25 , nvl((select rn from v_rng_t1 where id = t2.id and dat_start >= dat_start_t1 and dat_start <= dat_end_t1), -1) rn
26 from t2
27 where 1 = 1
28 )
29 select
30 v_rng_t1.id
31 , p
32 , v_rng_t1.u u_t1
33 , v_rng_t2.u u_t2
34 , f
35 , v_rng_t1.rn rn_t1
36 , v_rng_t2.rn rn_t2
37 , dat_start_t1
38 , dat_end_t1
39 , dat_start
40 , dat_end
41 from v_rng_t1, v_rng_t2
42 where 1 = 1
43 and v_rng_t1.id = v_rng_t2.id(+)
44 and v_rng_t1.rn = v_rng_t2.rn(+)
45 );
Execution Plan
----------------------------------------------------------
Plan hash value: 1128254579
--------------------------------------------------------------------------------
-----------------------------
| Id | Operation | Name | Rows | By
tes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
-----------------------------
| 0 | SELECT STATEMENT | | 1 |
127 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 1 |
44 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6612_62B5CB | 1 |
48 | 2 (0)| 00:00:01 |
| 3 | VIEW | | 1 |
127 | 2 (0)| 00:00:01 |
| 4 | TEMP TABLE TRANSFORMATION | | |
| | |
| 5 | LOAD AS SELECT | SYS_TEMP_0FD9D6612_62B5CB | |
| | |
| 6 | WINDOW BUFFER | | 1 |
48 | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| T1 | 1 |
48 | 0 (0)| 00:00:01 |
| 8 | INDEX FULL SCAN | PK_T1 | 1 |
| 0 (0)| 00:00:01 |
| 9 | NESTED LOOPS OUTER | | 1 |
127 | 2 (0)| 00:00:01 |
| 10 | VIEW | | 1 |
70 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6612_62B5CB | 1 |
48 | 2 (0)| 00:00:01 |
|* 12 | VIEW PUSHED PREDICATE | | 1 |
57 | 0 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID| T2 | 1 |
57 | 0 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | PK_T2 | 1 |
| 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:B1 AND "DAT_START_T1"<=:B2 AND "DAT_END_T1">=:B3)
12 - filter("V_RNG_T1"."RN"="V_RNG_T2"."RN"(+))
14 - access("ID"="V_RNG_T1"."ID")
But we still have a temp table transformation, that , I think, slows the query.
What astonished me about this problem, is that the "with" query was working very well, and gives a response in a reasonable time, and from one day to another, it freezes SQL*Plus. Does this problem has something with statistics ?
|
|
|
|
| Re: "with" clause Vs. multiple views [message #604342 is a reply to message #604334] |
Sun, 29 December 2013 00:54 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Amine wrote on Sun, 29 December 2013 03:12
| 2 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6612_62B5CB | 1 |
48 | 2 (0)| 00:00:01 |
| 3 | VIEW | | 1 |
127 | 2 (0)| 00:00:01 |
| 4 | TEMP TABLE TRANSFORMATION | | |
| | |
| 5 | LOAD AS SELECT | SYS_TEMP_0FD9D6612_62B5CB | |
| | |
But we still have a temp table transformation, that , I think, slows the query.
No, not at all. It is an expected behaviour with subquery factoring. When Oracle finds the WITH clause to be complex, then the subquery is resolved using temporary tables. This is how Oracle would MATERIALIZE the subquery result to make it more efficient. There are two hints, MATERIALIZE and INLINE.
Tim updated his article on my request regarding MATERIALIZE.
And this one too.
|
|
|
|
| Re: "with" clause Vs. multiple views [message #604375 is a reply to message #604238] |
Sun, 29 December 2013 11:43 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Solomon Yakobson wrote on Thu, 26 December 2013 15:02cookiemonster wrote on Thu, 26 December 2013 09:48merge the 3rd with into the main select
And where is that 3rd WITH?
SY.
v3 or v_verif.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 04:05:59 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
