CODEWITHSUNDEEP
performanceoracle-databasegroup-by
user1489849
user1489849

Reputation: 1

Oracle - performance issue. Query using groupby

This is the query i'm using:

SELECT p.name, p.id
FROM v_emp e 
INNER JOIN  v_prof p ON e.code = p.code
WHERE e.emp_id IN  (SELECT  a.id 
                   FROM t_approval a 
                   WHERE  a.code1 <> 'R'
                   AND a.code2 = 'P'
                   AND a.date1 IS NULL 
                   AND a.date2 IS NULL
                   AND a.code3 = 'ADMIN'
                   GROUP BY a.id
                                   )
GROUP BY p.name, p.id
ORDER BY p.name

The query takes more than 4 minutes to execute. the table t_approval has more than 15 million records and i need distinct id's from this table matching the search criteria.

This table already has index on id,code1, code2, code3.

Please let me know how to make this run faster.

Upvotes: 0

Views: 542

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

  • What is the query plan?
  • Why do the two GROUP BY clauses exist? Neither of them appear to be necessary but since we don't know your data or your data model, it's hard to know if they're trying to cover up a problem where some join condition is missing.
  • How many of the 15 million rows in t_approval does the subquery return? How selective are each of the predicates?
  • How many rows are in the other two tables? How many rows does your entire query return?
  • What indexes are available? I'm assuming the index(es) you are talking about are on the t_approval table. It's not clear, though, whether you are talking about a single composite index or 4 separate single-column indexes or something else. It's not clear what indexes exist on the other two tables.

Without seeing the query plan, without knowing what your data model looks like, and without knowing how selective your predicates are, it is very hard to guess what the problem is let alone to speculate about a solution. Potentially, since you are only selecting columns from one table and since I'm guessing the subquery returns a number of rows, it may be more efficient to do

SELECT p.name, p.id
  FROM v_prof p  
 WHERE EXISTS( SELECT 1
                 FROM v_emp e
                      JOIN t_approval a ON (e.emp_id = a.id)
                WHERE e.code = p.code
                  AND a.code1 <> 'R'
                  AND a.code2 = 'P'
                  AND a.date1 IS NULL 
                  AND a.date2 IS NULL
                  AND a.code3 = 'ADMIN' )
 ORDER BY p.name

If that doesn't work, you'll need to help us out by posting more information.

Upvotes: 5

Related Questions