8 Replies Latest reply: Mar 11, 2008 11:04 AM by Sven W. RSS

    Max (date) in sub-query

    497150
      I have an issue with a big view and several queries running on it where the max (date) is required in the sub-select (example below). When I run traces and explain, the perf problem is when the where cluase is evaluated, the view is pulled second time - so I basically use this view twice. The fact that the date in the underlined table is indexed did not help.
      I tried to use "ROW_NUMBER() OVER (ORDER BY date ASC) AS row_number, " rather to select the max(date) in teh sub query but I get only 1 records returned by the query instead the expected about 25,000.
      Need help on how to correct the max(date) to perform better and also to understand how to correct the ROW_NUMBER() not to limit me to 1 row.
      Thanks a lot for the help, Tom

      create view test_date as select * from user_objects;
      select * --(I have specific columns in the real query)
      from test_date t1
      where LAST_DDL_TIME in
      (select max(LAST_DDL_TIME)
      from test_date t2
      where t1.OBJECT_ID=t2.OBJECT_ID --(more clauses to limit the result but not reflecting the problem)
      and t1.OBJECT_NAME =t2.OBJECT_NAME
      and OBJECT_TYPE not in('TABLE')
      group by t2.OBJECT_ID , t2.OBJECT_NAME )
        • 1. Re: Max (date) in sub-query
          572471
          I tried to use "ROW_NUMBER() OVER (ORDER BY date
          ASC) AS row_number, " rather to select the max(date)
          in teh sub query but I get only 1 records returned by
          the query instead the expected about 25,000.
          probably you wanted to sort on date DESC, so that max date came first, and then use where rn=1 in the outer query.
          but don't forget, if you have several rows with max date - row_number() will assign row_number=1 only to the first one (probably randomly, if you don't have any other sorting).
          In your case you need to use DENSE_RANK() over () analytic function.
          • 2. Re: Max (date) in sub-query
            497150
            Thanks a lot. This is obviosly my problem - I need to return the max date for each of the OBJECT_ID in the example query.
            Any good examples on how to use the DENSE_RANK() over () ?
            I'll try that - thanks a lot again,
            Tom
            • 3. Re: Max (date) in sub-query
              497150
              I tired to use DENSE_RANK() but I still do not get the number of rows I need. I need to get back only the last (most recent) record of the each objects_name/type in the table.
              Thanks a lot.
              • 4. Re: Max (date) in sub-query
                572471
                Any good examples on how to use the DENSE_RANK() over () ?
                look in doc or search the forum, there were a lot of examples.
                I need to get back only
                the last (most recent) record of the each
                objects_name/type in the table.
                pay attention to PARTITION BY part in analytic functions.
                • 5. Re: Max (date) in sub-query
                  497150
                  I got the query to run - with dense ranck and row-Number I get the same results. My partition by cluse was wrong. But the perfromance is not much better.
                  Any other idea on how to optimize a query that uses pattern like below and needs to return all rows from a table that have the max enter date.
                  Thanks a lot, tom
                  Example:
                  col1 col2 col3
                  1 sysdate 'I need this record'
                  2 sysdate 'I need this record'
                  3 sysdate 'I need this record'
                  4 sysdate 'I do NOT need this record'
                  5 sysdate 'I do NOT need this record'
                  5 sysdate+3 'I need this record'
                  5 sysdate+2 'I do NOT need this record'
                  4 sysdate+3 'I need this record'
                  4 sysdate+2 'I do NOT need this record'
                  4 sysdate+1 'I do NOT need this record'

                  My query corrently is using the following construcion:
                  select col1, col2 , col3
                  from test_table t
                  where 1,2,3
                  and col3 = (select max(col3) from test_table t2 where t2.col1 = t.col1) group by t2.col1
                  • 6. Re: Max (date) in sub-query
                    497150
                    I still need help on this query. I also tried to use inner join on table and inline view which perform better but aI still pull the whole view twice.
                    Any otehr ideas?
                    Thanks a lot, tom
                    • 7. Re: Max (date) in sub-query
                      John Spencer
                      something along the lines of:
                      SELECT col1, col2 , col3
                      FROM (SELECT col1, col2 , col3,
                                   DENSE_RANK() OVER(PARTITION BY col1, col2
                                                     ORDER BY col3 DESC) rn
                            FROM test_table)
                      where 1,2,3 and
                            rn = 1
                      should do what you want.

                      John
                      • 8. Re: Max (date) in sub-query
                        Sven W.
                        What is your oracle database version?

                        From 10g onwards you should be able to use the KEEP clause of the aggregate functions.

                        Something like (not sure about correct syntax, since I'm on a 9i database currently).

                        select id, max(date) keep (dense rank first other columns)
                        from mytable
                        group by ID


                        Just noticed that Maxim gave a very nice example in this other thread: Grouping - beginner's question

                        Message was edited by:
                        Sven W.