1

I have two tables:

A(name VARCHAR2(20), type VARCHAR2(20))

and

B (type VARCHAR2(20), ... (20 fields more))

I need to select a single row from B for each distinct name,type from A taking into account that table b contains millions of records and my current select runs for 20min+

SELECT *
FROM   (SELECT a.*,
               Row_number()
                 over (
                   PARTITION BY a.TYPE
                   ORDER BY a.TYPE) rn
        FROM   tablea a,
               tableb b
        WHERE  a.name = 'SEARCHED_NAME'
               AND b.TYPE = a.TYPE)
WHERE  rn = 1 
2
  • 2
    What indexes do you have on the table? What does the execution plan look like?
    – user1822
    Commented Apr 14, 2014 at 13:16
  • As @a_horse_with_no_name says, the first thing you should do when a query is "slow", is determine the execution plan. Commented Apr 15, 2014 at 9:04

1 Answer 1

1

How about this:

Select * 
from 
  tableB b
 inner join
  (select a.*, row_number() over (partition by a.type order by a.type) rn from tableA a) a1
    on b.type=a1.type and a1.name='SEARCHED_NAME' and a1.rn=1

PS. Not tested, as you didn't provide a script to create and populate the tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.