Oracle Query Optimization

Question

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

Answer 1

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

Answer 2

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

Answer 3

Hi AlteredConcept,

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

Answer 4

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.