Home » SQL & PL/SQL » SQL & PL/SQL » please help me in query...
| please help me in query... [message #627917] |
Tue, 18 November 2014 02:49  |
 |
hahaie
Messages: 194
Registered: May 2014
|
Senior Member |
|
|
hello,
i have this code:
SELECT 'ali'name,SYSDATE birth_day FROM DUAL
UNION
SELECT 'arash'name,SYSDATE+1 birth_day FROM DUAL
i want name of person that date of birth is greater than others.
thanks
|
|
|
|
| Re: please help me in query... [message #627920 is a reply to message #627917] |
Tue, 18 November 2014 03:08  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
WITH DATA AS (SELECT 'ali'name,SYSDATE birth_day FROM DUAL
UNION
SELECT 'arash'name,SYSDATE+1 birth_day FROM DUAL)
SELECT NAME, birth_day
FROM (SELECT NAME, birth_day, MAX(birth_day) OVER(PARTITION BY NULL) max_birth_day
FROM DATA)
WHERE birth_day = max_birth_day;
|
|
|
|
|
|
|
|
|
|
|
|
| Re: please help me in query... [message #627951 is a reply to message #627917] |
Tue, 18 November 2014 04:13 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I took a simpler approach: joining the dat to an aggregation. Explain plan gives all three solutions
the same cost of 6 for materializing te view, but then my solution adds 4 as it hits the view several times.
The MC solution adds 1 as it does a WINDOW SORT PUSHED RANK, the CM solution adds zero.
Which I guess makes CM the winner 
(except the OP is really the winner, for getting the work done for free)
Tests done on 12.1.0.2 Windows:
orclz>
orclz> set autot on exp
orclz> WITH DATA AS (SELECT 'ali'name,SYSDATE birth_day FROM DUAL
2 UNION
3 SELECT 'arash'name,SYSDATE+1 birth_day FROM DUAL)
4 select name,birth_day from data where birth_day=(select max(birth_day) from data);
NAME BIRTH_DAY
----- -------------------
arash 2014-11-19:10:05:57
Execution Plan
----------------------------------------------------------
Plan hash value: 3929412940
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 26 | 10 (20)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6624_252B17 | | | | |
| 3 | SORT UNIQUE | | 2 | | 6 (34)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 7 | VIEW | | 2 | 26 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6624_252B17 | 2 | 26 | 2 (0)| 00:00:01 |
| 9 | SORT AGGREGATE | | 1 | 9 | | |
| 10 | VIEW | | 2 | 18 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6624_252B17 | 2 | 26 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("BIRTH_DAY"= (SELECT MAX("BIRTH_DAY") FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1")
*/ "C0" "NAME","C1" "BIRTH_DAY" FROM "SYS"."SYS_TEMP_0FD9D6624_252B17" "T1") "DATA"))
orclz>
orclz> with data as (
2 SELECT 'ali'name,SYSDATE birth_day FROM DUAL
3 UNION
4 SELECT 'arash'name,SYSDATE+1 birth_day FROM DUAL
5 )
6 select name, birth_day
7 from (select name, birth_day,
8 rank() over (order by birth_day desc) rk
9 from data)
10 where rk = 1;
NAME BIRTH_DAY
----- -------------------
arash 2014-11-19:10:06:37
Execution Plan
----------------------------------------------------------
Plan hash value: 539676268
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 52 | 7 (43)| 00:00:01 |
|* 1 | VIEW | | 2 | 52 | 7 (43)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 2 | 26 | 7 (43)| 00:00:01 |
| 3 | VIEW | | 2 | 26 | 6 (34)| 00:00:01 |
| 4 | SORT UNIQUE | | 2 | | 6 (34)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RK"=1)
2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("BIRTH_DAY") DESC
)<=1)
orclz>
orclz> WITH DATA AS (SELECT 'ali'name,SYSDATE birth_day FROM DUAL
2 UNION
3 SELECT 'arash'name,SYSDATE+1 birth_day FROM DUAL)
4 SELECT NAME, birth_day
5 FROM (SELECT NAME, birth_day, MAX(birth_day) OVER(PARTITION BY NULL) max_birth_day
6 FROM DATA)
7 WHERE birth_day = max_birth_day;
NAME BIRTH_DAY
----- -------------------
arash 2014-11-19:10:06:58
Execution Plan
----------------------------------------------------------
Plan hash value: 3553615855
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 44 | 6 (34)| 00:00:01 |
|* 1 | VIEW | | 2 | 44 | 6 (34)| 00:00:01 |
| 2 | WINDOW BUFFER | | 2 | 26 | 6 (34)| 00:00:01 |
| 3 | VIEW | | 2 | 26 | 6 (34)| 00:00:01 |
| 4 | SORT UNIQUE | | 2 | | 6 (34)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("BIRTH_DAY"="MAX_BIRTH_DAY")
orclz>
orclz> |
|
|
|
| Re: please help me in query... [message #627964 is a reply to message #627951] |
Tue, 18 November 2014 05:25 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I've always been suspicious of plans against a two row table - You could write something ridiculously complex with cartesian joins thrown in and the cost would probably stay in single digits.
Create a table with a lot more rows and the explain plans suggest my version is better again:
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> set lines 250
SQL> CREATE TABLE testtab AS SELECT 'ali'||ROWNUM name,SYSDATE+ ROWNUM birth_day FROM DUAL CONNECT BY LEVEL < 10000;
Table created.
SQL> set autot on exp
SQL> select name,birth_day from testtab where birth_day=(select max(birth_day) from testtab);
NAME BIRTH_DAY
------------------------------------------- ---------------
ali9999 20420404 114006
Execution Plan
----------------------------------------------------------
Plan hash value: 3232461785
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 16 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | TESTTAB | 1 | 32 | 8 (0)| 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | 9 | | |
| 3 | TABLE ACCESS FULL| TESTTAB | 9999 | 89991 | 8 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("BIRTH_DAY"= (SELECT MAX("BIRTH_DAY") FROM "TESTTAB"
"TESTTAB"))
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> select name, birth_day
from (select name, birth_day,
rank() over (order by birth_day desc) rk
from testtab)
where rk = 1; 2 3 4 5
NAME BIRTH_DAY
------------------------------------------- ---------------
ali9999 20420404 114006
Execution Plan
----------------------------------------------------------
Plan hash value: 860320518
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 439K| | 98 (2)| 00:00:02 |
|* 1 | VIEW | | 9999 | 439K| | 98 (2)| 00:00:02 |
|* 2 | WINDOW SORT PUSHED RANK| | 9999 | 312K| 408K| 98 (2)| 00:00:02 |
| 3 | TABLE ACCESS FULL | TESTTAB | 9999 | 312K| | 8 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RK"=1)
2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("BIRTH_DAY") DESC )<=1)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> SELECT NAME, birth_day
FROM (SELECT NAME, birth_day, MAX(birth_day) OVER(PARTITION BY NULL) max_birth_day
FROM testtab)
WHERE birth_day = max_birth_day; 2 3 4
NAME BIRTH_DAY
------------------------------------------- ---------------
ali9999 20420404 114006
Execution Plan
----------------------------------------------------------
Plan hash value: 2361134552
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 400K| 8 (0)| 00:00:01 |
|* 1 | VIEW | | 9999 | 400K| 8 (0)| 00:00:01 |
| 2 | WINDOW BUFFER | | 9999 | 312K| 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TESTTAB | 9999 | 312K| 8 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("BIRTH_DAY"="MAX_BIRTH_DAY")
Note
-----
- dynamic sampling used for this statement (level=2)
However, if you actually run them:
SQL> set autot off;
SQL> set serveroutput on
SQL> DECLARE
l_start TIMESTAMP;
2 3 l_name testtab.name%TYPE;
4 l_birth_day testtab.birth_day%TYPE;
5 BEGIN
6
7
8 l_start := SYSTIMESTAMP;
9
10 FOR i IN 1..10000 LOOP
11
12 select name,birth_day INTO l_name, l_birth_day
13 from testtab where birth_day=(select max(birth_day) from testtab);
14
15 END LOOP;
16 17 dbms_output.put_line(' time aggregate '||to_char(SYSTIMESTAMP - l_start));
18 19 l_start := SYSTIMESTAMP;
20
21 FOR i IN 1..10000 LOOP
22
23 select name, birth_day INTO l_name, l_birth_day
24 from (select name, birth_day,
25 rank() over (order by birth_day desc) rk
26 from testtab)
27 where rk = 1;
28
29 END LOOP;
30
31 dbms_output.put_line(' time rank'||to_char(SYSTIMESTAMP - l_start));
32 33 l_start := SYSTIMESTAMP;
34 35 FOR i IN 1..10000 LOOP
36 37 SELECT NAME, birth_day INTO l_name, l_birth_day
38 FROM (SELECT NAME, birth_day, MAX(birth_day) OVER(PARTITION BY NULL) max_birth_day
39 FROM testtab)
40 WHERE birth_day = max_birth_day;
41
42 END LOOP;
43 44 dbms_output.put_line(' time anlytic max'||to_char(SYSTIMESTAMP - l_start));
45
46 END;
47 /
time aggregate +000000000 00:00:17.180089000
time rank+000000000 00:00:41.575387000
time anlytic max+000000000 00:00:55.829415000
PL/SQL procedure successfully completed.
SQL>
Turns out the aggregate is significantly faster. In fact that makes a complete mockery of the explain plans, since according to them the sqls in order slowest to fastest is:
rank
max aggregate
max analytic
In reality it is:
max analytic
rank
max aggregate
However, if you make it a two row table you get:
time aggregate +000000000 00:00:01.181640000
time rank+000000000 00:00:01.148062000
time analytic max+000000000 00:00:01.149203000
So looking at that I'd favour the aggregate, it's only slower when the difference isn't particularly meaningful
|
|
|
|
| Re: please help me in query... [message #627970 is a reply to message #627964] |
Tue, 18 November 2014 06:07 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:I've always been suspicious of plans against a two row table -
And against tables fill with ordered data... : )
Same version (11.2.0.3.0 but Windows 32-bit):
SQL> CREATE TABLE testtab AS
2 SELECT 'ali'||ROWNUM name,SYSDATE+ dbms_random.value(-1000,1000) birth_day
3 FROM DUAL CONNECT BY LEVEL < 10000;
Table created.
SQL> exec dbms_stats.gather_table_stats (user, 'TESTTAB');
PL/SQL procedure successfully completed.
SQL> set autot on exp
SQL> select name,birth_day from testtab where birth_day=(select max(birth_day) from testtab);
NAME BIRTH_DAY
------------------------------------------- -------------------
ali3638 14/08/2017 07:37:13
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3232461785
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 18 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | TESTTAB | 1 | 16 | 9 (0)| 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | 8 | | |
| 3 | TABLE ACCESS FULL| TESTTAB | 9999 | 79992 | 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("BIRTH_DAY"= (SELECT MAX("BIRTH_DAY") FROM "TESTTAB"
"TESTTAB"))
SQL> select name, birth_day
2 from (select name, birth_day,
3 rank() over (order by birth_day desc) rk
4 from testtab)
5 where rk = 1;
NAME BIRTH_DAY
------------------------------------------- -------------------
ali3638 14/08/2017 07:37:13
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 860320518
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 439K| | 65 (2)| 00:00:01 |
|* 1 | VIEW | | 9999 | 439K| | 65 (2)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 9999 | 156K| 248K| 65 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TESTTAB | 9999 | 156K| | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RK"=1)
2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("BIRTH_DAY") DESC )<=1)
SQL> SELECT NAME, birth_day
2 FROM (SELECT NAME, birth_day, MAX(birth_day) OVER(PARTITION BY NULL) max_birth_day
3 FROM testtab)
4 WHERE birth_day = max_birth_day;
NAME BIRTH_DAY
------------------------------------------- -------------------
ali3638 14/08/2017 07:37:13
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2361134552
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999 | 400K| 9 (0)| 00:00:01 |
|* 1 | VIEW | | 9999 | 400K| 9 (0)| 00:00:01 |
| 2 | WINDOW BUFFER | | 9999 | 156K| 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TESTTAB | 9999 | 156K| 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("BIRTH_DAY"="MAX_BIRTH_DAY")
SQL>
SQL> DECLARE
2 l_start TIMESTAMP;
3 l_name testtab.name%TYPE;
4 l_birth_day testtab.birth_day%TYPE;
5 BEGIN
6 l_start := SYSTIMESTAMP;
7 FOR i IN 1..10000 LOOP
8 select name,birth_day INTO l_name, l_birth_day
9 from testtab where birth_day=(select max(birth_day) from testtab);
10 END LOOP;
11 dbms_output.put_line(' time aggregate '||to_char(SYSTIMESTAMP - l_start));
12 l_start := SYSTIMESTAMP;
13 FOR i IN 1..10000 LOOP
14 select name, birth_day INTO l_name, l_birth_day
15 from (select name, birth_day,
16 rank() over (order by birth_day desc) rk
17 from testtab)
18 where rk = 1;
19 END LOOP;
20 dbms_output.put_line(' time rank '||to_char(SYSTIMESTAMP - l_start));
21 l_start := SYSTIMESTAMP;
22 FOR i IN 1..10000 LOOP
23 SELECT NAME, birth_day INTO l_name, l_birth_day
24 FROM (SELECT NAME, birth_day, MAX(birth_day) OVER(PARTITION BY NULL) max_birth_day
25 FROM testtab)
26 WHERE birth_day = max_birth_day;
27 END LOOP;
28 dbms_output.put_line(' time anlytic max '||to_char(SYSTIMESTAMP - l_start));
29 END;
30 /
time aggregate +000000000 00:00:10.455000000
time rank +000000000 00:00:17.667000000
time anlytic max +000000000 00:00:55.754000000
PL/SQL procedure successfully completed.
"Time rank" is closer to "time aggregate" than to "time analytic max" like for you.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 05:31:31 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
