select first N distinct rows without inner select in oracle

I have something like the following structure: Table1 -> Table2 relationship is 1:m

I need to perform queries similar to the next one:

select Table1.id from Table1 left outer join Table2 on (Table1.id1 = Table2.id2) where Table2.name like '%a%' and rownum < 11
开发者_Python百科

i.e. I want first 10 ids from Table 1 which fulfils conditions in Table2. The problem is that I've to use distinct, but the distinct clause applies after 'rownum < 11', so the result could be e.g. 5 records even if their number is more than 10.

The apparent solution is to use the following:

select id from ( select Table1.id from Table1 left outer join Table2 on (Table1.id1 = Table2.id2) where Table2.name like '%a%' ) where rownum < 11

But I'm afraid of performance of such a query. If Table1 contains about 300k records, and Table2 contains about 700k records, wouldn't such a query be really slow?

Is there another query, but without inner select? Unluckily, I want to avoid using inner selects.

Unluckily, I want to avoid using inner selects

With having the WHERE clause on TABLE2, you are filtering the select to an INNER JOIN (ie. since Table2.name IS null <> Table2.name like '%a%' you will only get results where the join is INNER to one another. Also, the %a% without a function based index will result in a full table scan on each iteration.

but @lweller is completely correct, to do the query correctly you will need to use a subquery. keep in mind, without an ORDER BY you have no guarantee of the order of your top X records (it may always 'appear' that the values conform to the primary key or whatnot, but there is no guarantee.

WITH TABLE1 AS(SELECT 1 ID FROM DUAL 
               UNION ALL
               SELECT 2 ID FROM DUAL 
               UNION ALL
               SELECT 3 ID FROM DUAL 
               UNION ALL
               SELECT 4 ID FROM DUAL 
               UNION ALL
               SELECT 5 ID FROM DUAL) ,
     TABLE2 AS(SELECT 1 ID, 'AAA' NAME FROM DUAL
               UNION ALL
               SELECT 2 ID, 'ABB' NAME FROM DUAL
               UNION ALL
               SELECT 3 ID, 'ACC' NAME FROM DUAL
               UNION ALL
               SELECT 4 ID, 'ADD' NAME FROM DUAL
               UNION ALL
               SELECT 1 ID, 'BBB' NAME FROM DUAL
               ) ,
     sortable as( --here is the subquery
         SELECT
            Table1.ID ,
            ROW_NUMBER( ) OVER (ORDER BY Table2.NAME NULLS LAST) ROWOverName , --this wil handle the sort
            table2.name
           from
            Table1
            LEFT OUTER JOIN  --this left join it moot, pull the WHERE table2.name into the join to have it LEFT join as expected
            Table2
             on
            (
                Table1.id = Table2.id
            )
          WHERE
          Table2.NAME LIKE '%A%')
    SELECT * 
      FROM sortable
     WHERE ROWOverName <= 2; 

-- you can drop the ROW_NUMBER( ) analytic function and replace the final query as such (as you initially indicated)

SELECT * 
  FROM sortable
 WHERE 
       ROWNUM <= 2
 ORDER BY sortable.NAME  --make sure to put in an order by!
 ;

You don't need DISTINCT here at all, and there is nothing bad in subqueries as such.

SELECT  id
FROM    Table1
WHERE   id IN
        (
        SELECT  id
        FROM    Table2
        WHERE   name LIKE '%a%'
        )
        AND rownum < 11

Note that the order is not guaranteed. To guarantee order, you have to use a nested query:

SELECT  id
FROM    (
        SELECT  id
        FROM    Table1
        WHERE   id IN
                (
                SELECT  id
                FROM    Table2
                WHERE   name LIKE '%a%'
                )
        ORDER BY
                id -- or whatever else
        )
WHERE   rownum < 11

There is no way to do it without nested queries (or the CTE).

For me there is no reason to be afraid of performance. I think the sub select ist the best way to solve your problem. And if you want don't trust me, take a look at explain plan of your query and you will see that it behave not so bad as you might think.