CODEWITHSUNDEEP
oracle-databaseperformancedatabase-performance
vecio88
vecio88

Reputation: 95

Oracle tuning for query with query annidate

i am trying to better a query. I have a dataset of ticket opened. Every ticket has different rows, every row rappresent an update of the ticket. There is a field (dt_update) that differs it every row.

I have this indexs in the st_remedy_full_light.
IDX_ASSIGNMENT (ASSIGNMENT)
IDX_REMEDY_INC_ID (REMEDY_INC_ID)
IDX_REMDULL_LIGHT_DTUPD (DT_UPDATE)

Now, the query is performed in 8 second. Is high for me.

WITH last_ticket AS
  ( SELECT *
   FROM st_remedy_full_light a
   WHERE a.dt_update IN
     ( SELECT MAX(dt_update)
       FROM st_remedy_full_light
       WHERE remedy_inc_id = a.remedy_inc_id
     )
  )
SELECT remedy_inc_id, ASSIGNMENT FROM last_ticket

This is the plan Explain Plan How i could to better this query?

P.S. This is just a part of a big query

Additional information: - The table st_remedy_full_light contain 529.507 rows

Upvotes: 0

Views: 65

Answers (2)

Rob van Wijk
Rob van Wijk

Reputation: 17705

The best alternative query, which is also much easier to execute, is this:

select remedy_inc_id
     , max(assignment) keep (dense_rank last order by dt_update)
  from st_remedy_full_light
 group by remedy_inc_id

This will use only one full table scan and a (hash/sort) group by, no self joins.

Don't bother about indexed access, as you'll probably find a full table scan is most appropriate here. Unless the table is really wide and a composite index on all columns used (remedy_inc_id,dt_update,assignment) would be significantly quicker to read than the table.

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132650

You could try:

WITH last_ticket AS
  ( SELECT remedy_inc_id, ASSIGNMENT,
           rank() over (partition by remedy_inc_id order by dt_update desc) rn
   FROM st_remedy_full_light a
  )
SELECT remedy_inc_id, ASSIGNMENT FROM last_ticket 
where rn = 1;

Upvotes: 2

Related Questions