Solved

Speeding up a query that finds the max or latest data

Posted on 2006-12-01
7
706 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 143

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

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 143

Expert Comment

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

glad I could help
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

733 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