[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Speeding up a query that finds the max or latest data

Posted on 2006-12-01
7
Medium Priority
?
709 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

650 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