select certain records in oracle with latest date

Question

So I currently have one way of doing what I want it goes like:

SELECT * FROM 
( SELECT  
e.*,
ROW_NUMBER() OVER (partition by USER_ID order by COPIED_TIMESTAMP DESC) r
FROM  
TABLE  e)
WHERE r = 1;

This works fine, but if the table becomes big I feel there may be performance issues. I was looking at something like this:

SELECT MAX(COPIED_TIMESTAMP), USER_ID
              FROM TABLE
              GROUP BY USER_ID

This also works, but is there a way to show all the information not just the timestamp and id. I'm quite new to SQL/Oracle queries and am a bit lost on this one.

possible duplicate of [Window functions SORT costly, can we overcome it?](http://stackoverflow.com/questions/24620405/window-functions-sort-costly-can-we-overcome-it) — Maheswaran Ravisankar, Apr 14 '15 at 10:32
You could consider adding index to your key column`(USER_ID,COPIED_TIMESTAMP)` . — Maheswaran Ravisankar, Apr 14 '15 at 10:36

score 1 · Answer 1 · answered Apr 14 '15 at 10:20

but if the table becomes big I feel there may be performance issues.

I think the analytic query is just fine since it will scan the table only once. You could further optimize it's performance by creating indexes if required.

Your second query will return only the timestamp and user_id column, however, when you need the other columns, you would anyway do more than one table scan. So, it won't be a good idea.

If you are looking for other options like NOT EXISTS, it will still need more than one table scan.

Let's see a small test case:

Analytic query

SQL> EXPLAIN PLAN FOR SELECT * FROM
  2  (SELECT e.*,
  3    ROW_NUMBER() OVER (partition BY deptno order by hiredate DESC) r
  4  FROM emp e
  5  ) WHERE r = 1;

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 3291446077

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |    14 |  1400 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                    |      |    14 |  1400 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|      |    14 |   518 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMP  |    14 |   518 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "DEPTNO" ORDER BY
              INTERNAL_FUNCTION("HIREDATE") DESC )<=1)

17 rows selected.

SQL>

NOT EXISTS query

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM emp t1
  4  WHERE NOT EXISTS (SELECT 1 FROM emp t2
  5                    WHERE t1.deptno = t2.deptno
  6                      and t2.hiredate > t1.hiredate);

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 3353202012

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    48 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     1 |    48 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   154 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO")
       filter("T2"."HIREDATE">"T1"."HIREDATE")

16 rows selected.

SQL>

So, you can see that the analytic query does only one table scan.

score 0 · Answer 2 · edited Apr 14 '15 at 14:53

0

Return rows where not exists same id with later timestamp:

SELECT USER_ID, COPIED_TIMESTAMP, other_column1, other_column2
FROM TABLE t1
WHERE NOT EXISTS (select 1 from table t2
                  where t1.user_id = t2.user_id
                    and t2.COPIED_TIMESTAMP > t1.COPIED_TIMESTAMP)

edited Apr 14 '15 at 14:53

psaraj12

4,772
2
21
30

answered Apr 14 '15 at 10:00

jarlh

42,561
8
45
63

You are scanning the table twice. How is this going to be better in terms of performance? – Lalit Kumar B Apr 14 '15 at 10:03
No guaranties here, but I'd give it a try! – jarlh Apr 14 '15 at 10:04

score 0 · Answer 3 · answered Apr 14 '15 at 11:41

Analytic functions are your friend:

SELECT MAX( COPIED_TIMESTAMP ) KEEP ( DENSE_RANK LAST ORDER BY COPIED_TIMESTAMP ) AS COPIED_TIMESTAMP,
       MAX( other_column     ) KEEP ( DENSE_RANK LAST ORDER BY COPIED_TIMESTAMP ) AS other_column,
       MAX( other_column2    ) KEEP ( DENSE_RANK LAST ORDER BY COPIED_TIMESTAMP ) AS other_column2,
       USER_ID
FROM   TABLE_NAME
GROUP BY USER_ID

select certain records in oracle with latest date

3 Answers3