• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 721
  • Last Modified:

Speeding up a query that finds the max or latest data

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
E43509
Asked:
E43509
  • 4
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
E43509Author Commented:
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
 
E43509Author Commented:
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
E43509Author Commented:
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
 
E43509Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sometimes, less is more
sometimes, more is less

glad I could help
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now