Solved

Speeding up a query that finds the max or latest data

Posted on 2006-12-01
7
695 Views
Last Modified: 2008-01-09
I have a table called course histories that contains transcripts of a worker's training.
The business rule to ascertain what is the latest record is to find the record with the latest completion date, if more than one, find the record with the latest 'updated date' , if still a tie, use a unique sequence value found in the record (no_ch_seq).
I basically convert all of the date values to characters and order them by year, month day.
The query works but is slow (taking 40 seconds if no no_emp is supplied)
My query looks like this:
SELECT
    c.no_emp  -- worker ID
    ,c.cd_crs  -- course code
    ,c.no_ch_seq  -unique value
    ,c.dt_ch_compdt  -- completion date
    ,c.dt_lst_updt  -- last updated date
FROM course_histories c
WHERE TO_CHAR(c.dt_ch_compdt, 'YYYYMMDDHH24MISS')
   || TO_CHAR(c.dt_lst_updt, 'YYYYMMDDHH24MISS')
   || TO_CHAR(c.no_ch_seq)  
        = (SELECT MAX(TO_CHAR(h.dt_ch_compdt, 'YYYYMMDDHH24MISS')
                || TO_CHAR(h.dt_lst_updt, 'YYYYMMDDHH24MISS')
                || TO_CHAR(h.no_ch_seq))
           FROM course_histories h
           WHERE c.no_emp = h.no_emp
                 AND c.cd_crs = h.cd_crs)

There are indices on no_emp, cd_crs.
I also created a function based index
 
   CREATE INDEX ch_most_recent_idx ON course_histories (TO_CHAR(dt_ch_compdt, 'YYYYMMDDHH24MISS')
                     || TO_CHAR(dt_lst_updt, 'YYYYMMDDHH24MISS')
                     || TO_CHAR(no_ch_seq));
                   
 ANALYZE TABLE course_histories compute STATISTICS;


ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

and tried to suply hints in the query
SELECT /*+ INDEX(course_histories, ch_most_recent_idx) */

Can this query be rewritten to be faster or other thoughts???


0
Comment
Question by:E43509
  • 4
  • 3
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18057162
if you look at the explain plan, does it actually USE the index?
I doubt, because you need the no_emp and cd_crs also int the index...

please, try this index:
   CREATE INDEX ch_most_recent_idx ON course_histories ( no_emp, cd_crs, TO_CHAR(dt_ch_compdt, 'YYYYMMDDHH24MISS')
                     || TO_CHAR(dt_lst_updt, 'YYYYMMDDHH24MISS')
                     || TO_CHAR(no_ch_seq));
0
 

Author Comment

by:E43509
ID: 18057190
Thanks, tried it, reanalyzed table, supplied hint, no performance improvement.  Not sure if this helps but here is the explain plan.
  SQL Statement from editor:
   
   
  SELECT /*+ INDEX(course_histories, ch_most_recent_idx) */
 
      c.no_emp
      ,c.cd_crs
      ,c.no_ch_seq
      ,c.cd_ch_status
      ,c.dt_ch_expire
      ,c.tx_exm_reason
      ,c.nm_lst_updt
      ,c.dt_lst_updt
 
  FROM course_histories c
 
  WHERE TO_CHAR(c.dt_ch_compdt, 'YYYYMMDDHH24MISS')
     || TO_CHAR(c.dt_lst_updt, 'YYYYMMDDHH24MISS')
     || TO_CHAR(c.no_ch_seq)
          = (SELECT MAX(TO_CHAR(h.dt_ch_compdt, 'YYYYMMDDHH24MISS')
                  || TO_CHAR(h.dt_lst_updt, 'YYYYMMDDHH24MISS')
                  || TO_CHAR(h.no_ch_seq))
             FROM course_histories h
             WHERE c.no_emp = h.no_emp
                   AND c.cd_crs = h.cd_crs)
 
 
  ------------------------------------------------------------
   
  Statement Id=228   Type=
  Cost=1.95943226987942E-307  TimeStamp=01-12-06::17::40:38
 
       (1)  SELECT STATEMENT  CHOOSE
     Est. Rows: 1  Cost: 2,677
       (6)  HASH JOIN
     Est. Rows: 1  Cost: 2,677
           (4)  VIEW SYS.VW_SQ_1
                Est. Rows: 697,311  Cost: 1,960
               (3)  SORT GROUP BY
                    Est. Rows: 697,311  Cost: 1,960
                   (2)  TABLE ACCESS FULL SYSADM.COURSE_HISTORIES  [Analyzed]
                   (2)   Blocks: 14,572 Est. Rows: 697,311 of 697,311  Cost: 351
                        Tablespace: TMS_DATA_01
           (5)  TABLE ACCESS FULL SYSADM.COURSE_HISTORIES  [Analyzed]
           (5)   Blocks: 14,572 Est. Rows: 697,311 of 697,311  Cost: 351
                Tablespace: TMS_DATA_01
0
 

Author Comment

by:E43509
ID: 18057293
I also tried these hints but the oracle engine continues to ignore them ...
SELECT /*+ no_parrallel(course_histories) no_parrallel_index(course_histories)
no_use_hash INDEX(course_histories, ch_most_recent_idx) */

thougths?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18057352
as you see in the explain plan, it does 2 times a full table access....

any better using:  
SELECT /*+ first_rows */


or, what about this:

put 1 indexes:
no_emp, cd_crs, dt_ch_compdt, dt_lst_updt, no_ch_seq


SELECT
      c.no_emp
      ,c.cd_crs
      ,c.no_ch_seq
      ,c.cd_ch_status
      ,c.dt_ch_expire
      ,c.tx_exm_reason
      ,c.nm_lst_updt
      ,c.dt_lst_updt
  FROM course_histories c
  WHERE c.no_ch_seq = (
       select max ( h3.no_ch_seq ) from course_histories h3
       where h3.no_emp = c.no_emp
           and h3.cd_crs = c.cd_crs
           and h3.dt_lst_updt = (
             select max ( h2.dt_lst_upt ) from course_histories h2
                where h2.no_emp = c.no_emp
                    and h2.cd_crs = c.cd_crs
                    and h2.dt_ch_compdt = (
                   select max ( h1.dt_ch_compdt ) from course_histories h1
                      where h1.no_emp = c.no_emp
                          and h1.cd_crs = c.cd_crs
                    )
               )
           )
0
 

Author Comment

by:E43509
ID: 18057376
SELECT /*+ first_rows */ brought it back in about 1/2 the time
New explain plan showing one full table access scan gone.
 (Let me try your next suggestion and I'll comment back in another message ... Thanks for your quick response)
  SQL Statement from editor:
   
   
  SELECT /*+ first_rows */
 
      c.no_emp
      ,c.cd_crs
      ,c.no_ch_seq
      ,c.cd_ch_status
      ,c.dt_ch_expire
      ,c.tx_exm_reason
      ,c.nm_lst_updt
      ,c.dt_lst_updt
 
  FROM course_histories c
 
  WHERE TO_CHAR(c.dt_ch_compdt, 'YYYYMMDDHH24MISS')
     || TO_CHAR(c.dt_lst_updt, 'YYYYMMDDHH24MISS')
     || TO_CHAR(c.no_ch_seq)
          = (SELECT MAX(TO_CHAR(h.dt_ch_compdt, 'YYYYMMDDHH24MISS')
                  || TO_CHAR(h.dt_lst_updt, 'YYYYMMDDHH24MISS')
                  || TO_CHAR(h.no_ch_seq))
             FROM course_histories h
             WHERE c.no_emp = h.no_emp
                   AND c.cd_crs = h.cd_crs)
 
 
  ------------------------------------------------------------
   
  Statement Id=228   Type=
  Cost=1.9594322703349E-307  TimeStamp=01-12-06::18::16:32
 
       (1)  SELECT STATEMENT  HINT: FIRST_ROWS
     Est. Rows: 1  Cost: 350,616
       (7)  NESTED LOOPS
     Est. Rows: 1  Cost: 350,616
           (4)  VIEW SYS.VW_SQ_1
                Est. Rows: 697,311  Cost: 1,960
               (3)  SORT GROUP BY
                    Est. Rows: 697,311  Cost: 1,960
                   (2)  TABLE ACCESS FULL SYSADM.COURSE_HISTORIES  [Analyzed]
                   (2)   Blocks: 14,572 Est. Rows: 697,311 of 697,311  Cost: 351
                        Tablespace: TMS_DATA_01
           (6)  TABLE ACCESS BY INDEX ROWID SYSADM.COURSE_HISTORIES  [Analyzed]
           (6)   Blocks: 14,572 Est. Rows: 1 of 697,311  Cost: 3
                Tablespace: TMS_DATA_01
               (5)  NON-UNIQUE INDEX RANGE SCAN SYSADM.CH_MOST_RECENT_IDX  [Analyzed]
                    Est. Rows: 1  Cost: 2
0
 

Author Comment

by:E43509
ID: 18057491
Winner Winner Chicken Dinner!!!
When I changed the query to the one you created above my response time is now less than a second!!!
I even dropped my function index as the best I can tell (still novice on explain plan) it wasn't using it.

Now I'm onto another query building off of this one.  
Thanks for your help and I like the way you did the max solution.  I'm gonna have to remember that method

  SQL Statement from editor:
   
   
  SELECT
        c.no_emp
        ,c.cd_crs
        ,c.no_ch_seq
        ,c.cd_ch_status
        ,c.dt_ch_expire
        ,c.tx_exm_reason
        ,c.nm_lst_updt
        ,c.dt_lst_updt
    FROM course_histories c
    WHERE c.no_ch_seq = (
         select max ( h3.no_ch_seq ) from course_histories h3
         where h3.no_emp = c.no_emp
             and h3.cd_crs = c.cd_crs
             and h3.dt_lst_updt = (
               select max ( h2.dt_lst_updt ) from course_histories h2
                  where h2.no_emp = c.no_emp
                      and h2.cd_crs = c.cd_crs
                      and h2.dt_ch_compdt = (
                     select max ( h1.dt_ch_compdt ) from course_histories h1
                        where h1.no_emp = c.no_emp
                            and h1.cd_crs = c.cd_crs
                      )
                 )
             )
 
  ------------------------------------------------------------
   
  Statement Id=4348   Type=
  Cost=2.64018716311899E-308  TimeStamp=01-12-06::18::34:59
 
       (1)  SELECT STATEMENT  CHOOSE
     Est. Rows: 5,323  Cost: 64,227
    FILTER
           (2)  TABLE ACCESS FULL SYSADM.COURSE_HISTORIES  [Analyzed]
           (2)   Blocks: 14,572 Est. Rows: 5,323 of 697,311  Cost: 351
                Tablespace: TMS_DATA_01
           (13)  SORT AGGREGATE
                Est. Rows: 1
               (12)  FILTER
                   (4)  TABLE ACCESS BY INDEX ROWID SYSADM.COURSE_HISTORIES  [Analyzed]
                   (4)   Blocks: 14,572 Est. Rows: 1 of 697,311  Cost: 4
                        Tablespace: TMS_DATA_01
                       (3)  NON-UNIQUE INDEX RANGE SCAN SYSADM.CH_EMPCRS  [Analyzed]
                            Est. Rows: 1  Cost: 3
                   (11)  SORT AGGREGATE
                        Est. Rows: 1
                       (10)  FILTER
                           (6)  TABLE ACCESS BY INDEX ROWID SYSADM.COURSE_HISTORIES  [Analyzed]
                           (6)   Blocks: 14,572 Est. Rows: 1 of 697,311  Cost: 4
                                Tablespace: TMS_DATA_01
                               (5)  NON-UNIQUE INDEX RANGE SCAN SYSADM.CH_EMPCRS  [Analyzed]
                                    Est. Rows: 1  Cost: 3
                           (9)  SORT AGGREGATE
                                Est. Rows: 1
                               (8)  TABLE ACCESS BY INDEX ROWID SYSADM.COURSE_HISTORIES  [Analyzed]
                               (8)   Blocks: 14,572 Est. Rows: 1 of 697,311  Cost: 4
                                    Tablespace: TMS_DATA_01
                                   (7)  NON-UNIQUE INDEX RANGE SCAN SYSADM.CH_EMPCRS  [Analyzed]
                                        Est. Rows: 1  Cost: 3
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18058972
sometimes, less is more
sometimes, more is less

glad I could help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now