AlteredConcept
Reputation: 2632
Oracle Query Optimization
I have the following tables
Master Table
id
----
1x
2x
....
Assignment Table
id | type_id | assign_id
-----------------------------
1x | 2 | 554
1x | 3 | 664
2x | 2 | 919
2x | 4 | 514
Type table
type_id | create_date
----------------------
1 | 01/01/2009
2 | 01/01/2009
3 | 03/01/2009
4 | 04/01/2009
What i need for the query to output is something like this
id | max create_date type_id | assign_id
----------------------------------------------
1x | 3 | 664
2x | 4 | 514
Right now i'm doing something like this to acquire the results, but i'm sure there is a much better way to do this.
Q1
---
CREATE TABLE tmp_table as
SELECT m.id, max(t.create_date)
FROM master m, assignment a, type t
WHERE m.id=a.id
and a.type_id=t.type_id
GROUP BY m.id
Q2
--
SELECT tmp.id, a.type_id, a.assign_id
from tmp_table tmp, assignment a, type t
WHERE tmp.create_date=t.create_date
and t.type_id=a.type_id
Thanks for any help
Upvotes: 0
Views: 979
Answers (4)
Rob van Wijk
Reputation: 17705
Using analytics, and then applying the DISTINCT operator is not the way to go, when you need to aggregate.
Here is a simpler and more performant version using only aggregates:
SQL> select a.id
2 , max(t.type_id) keep (dense_rank last order by t.create_date) max_create_date_type_id
3 , max(a.assign_id) keep (dense_rank last order by t.create_date) assign_id
4 from assignment a
5 , type t
6 where a.type_id = t.type_id
7 group by a.id
8 /
ID MAX_CREATE_DATE_TYPE_ID ASSIGN_ID
-- ----------------------- ----------
1x 3 664
2x 4 514
2 rows selected.
And here is a test to prove it's more performant:
SQL> exec dbms_stats.gather_table_stats(user,'assignment')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'type')
PL/SQL procedure successfully completed.
SQL> select /*+ gather_plan_statistics */
2 distinct
3 a.id,
4 first_value(t.type_id)
5 over (partition by a.id order by t.create_date desc)
6 as max_create_date_type_id,
7 first_value(a.assign_id)
8 over (partition by a.id order by t.create_date desc)
9 as assign_id
10 from assignment a, type t
11 where a.type_id = t.type_id
12 /
ID MAX_CREATE_DATE_TYPE_ID ASSIGN_ID
-- ----------------------- ----------
2x 4 514
1x 3 664
2 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
2 /
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
SQL_ID fu520w4kf2bbp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ distinct a.id,
first_value(t.type_id) over (partition by a.id order by
t.create_date desc) as max_create_date_type_id,
first_value(a.assign_id) over (partition by a.id order by
t.create_date desc) as assign_id from assignment a, type t where
a.type_id = t.type_id
Plan hash value: 4160194652
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 6 | | | |
| 1 | HASH UNIQUE | | 1 | 4 | 2 |00:00:00.01 | 6 | 898K| 898K| 493K (0)|
| 2 | WINDOW SORT | | 1 | 4 | 4 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
| 3 | WINDOW SORT | | 1 | 4 | 4 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
|* 4 | HASH JOIN | | 1 | 4 | 4 |00:00:00.01 | 6 | 898K| 898K| 554K (0)|
| 5 | TABLE ACCESS FULL| ASSIGNMENT | 1 | 4 | 4 |00:00:00.01 | 3 | | | |
| 6 | TABLE ACCESS FULL| TYPE | 1 | 4 | 4 |00:00:00.01 | 3 | | | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."TYPE_ID"="T"."TYPE_ID")
28 rows selected.
SQL> select /*+ gather_plan_statistics */
2 a.id
3 , max(t.type_id) keep (dense_rank last order by t.create_date) max_create_date_type_id
4 , max(a.assign_id) keep (dense_rank last order by t.create_date) assign_id
5 from assignment a
6 , type t
7 where a.type_id = t.type_id
8 group by a.id
9 /
ID MAX_CREATE_DATE_TYPE_ID ASSIGN_ID
-- ----------------------- ----------
1x 3 664
2x 4 514
2 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
2 /
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
SQL_ID 156kpxgxmfjd3, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ a.id , max(t.type_id)
keep (dense_rank last order by t.create_date) max_create_date_type_id
, max(a.assign_id) keep (dense_rank last order by t.create_date)
assign_id from assignment a , type t where a.type_id =
t.type_id group by a.id
Plan hash value: 3494156172
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 6 | | | |
| 1 | SORT GROUP BY | | 1 | 2 | 2 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
|* 2 | HASH JOIN | | 1 | 4 | 4 |00:00:00.01 | 6 | 898K| 898K| 594K (0)|
| 3 | TABLE ACCESS FULL| ASSIGNMENT | 1 | 4 | 4 |00:00:00.01 | 3 | | | |
| 4 | TABLE ACCESS FULL| TYPE | 1 | 4 | 4 |00:00:00.01 | 3 | | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."TYPE_ID"="T"."TYPE_ID")
25 rows selected.
As you can see, both are full scanning the tables and perform a hash join. The difference is after this step. The aggregate variant takes 4 rows and aggregates them to 2 rows with a SORT GROUP By. The analytic one is first sorting the 4-row-set twice and then applies a HASH UNIQUE to reduce the set to 2 rows.
Regards, Rob.
Upvotes: 3
Jeffrey Kemp
Reputation: 60312
No temp table necessary.
select distinct
a.id,
first_value(t.type_id)
over (partition by a.id order by t.create_date desc)
as max_create_date_type_id,
first_value(a.assign_id)
over (partition by a.id order by t.create_date desc)
as assign_id
from assignment a, type t
where a.type_id = t.type_id
Upvotes: 4
Vincent Malgrat
Reputation: 67802
you can use analytics to get the result in one query:
SQL> WITH assignment_t AS (
2 SELECT '1x' ID, 2 type_id, 554 assign_id FROM dual UNION ALL
3 SELECT '1x', 3, 664 FROM dual UNION ALL
4 SELECT '2x', 2, 919 FROM dual UNION ALL
5 SELECT '2x', 4, 514 FROM dual
6 ), type_t AS (
7 SELECT 1 type_id, DATE '2009-01-01' create_date FROM dual UNION ALL
8 SELECT 2, DATE '2009-01-01' FROM dual UNION ALL
9 SELECT 3, DATE '2009-01-03' FROM dual UNION ALL
10 SELECT 4, DATE '2009-01-04' FROM dual
11 )
12 SELECT DISTINCT a.ID "id",
13 first_value(a.type_id)
14 OVER( PARTITION BY a.id
15 ORDER BY t.create_date DESC) "max create_date type_id",
16 first_value(a.assign_id)
17 OVER( PARTITION BY a.id
18 ORDER BY t.create_date DESC) "assign_id"
19 FROM assignment_t a
20 JOIN type_t t ON (a.type_id = t.type_id)
21 ;
id max create_date type_id assign_id
-- ----------------------- ----------
2x 4 514
1x 3 664
Upvotes: 2
b.roth
Reputation: 9552
If you are using Oracle9i release 2 or later, you can use the WITH clause. Then, instead of having to create a temp table Q1 and then running Q2, you could have one statement only.
Not only the syntax will be shorter, but this can also improve the query speed.
http://www.dba-oracle.com/t_with_clause.htm
Your query would become something like:
WITH tmp_table as (
SELECT m.id, max(t.create_date)
FROM master m, assignment a, type t
WHERE m.id=a.id
and a.type_id=t.type_id
GROUP BY m.id )
SELECT tmp.id, a.type_id, a.assign_id
from tmp_table tmp, assignment a, type t
WHERE tmp.create_date=t.create_date
and t.type_id=a.type_id
Upvotes: 2