Convert postgres sql queries into oracle

Question

I am trying to speed up a random selection query in Oracle and found this blog. I am not able to convert their following Postgres queries in oracle:

    select * from users
      where 
        random() < 200 / (select count(1) from logs)::float
      order by random()
      limit 100;

and

   select * from users
     where id in (
       select round(random() * 21e6)::integer as id
       from generate_series(1, 110)
       group by id -- Discard duplicates
       )
     limit 100;

How would this queries look like in oracle?

Both support FETCH FIRST instead of LIMIT. And I suppose CAST instead of ::. — jarlh
– jarlh, Commented Apr 11, 2019 at 12:34
@a_horse_with_no_name: I know that there are many ways (and many posts) on how to use native sampling oracle techniques. I am interested in comparing them against the two queries above in terms of performance, therefore I need first to write them in oracle. — giotto
– giotto, Commented Apr 11, 2019 at 13:00

Barbaros Özhan · Accepted Answer · 2019-04-11 12:52:05Z

1

You can use such queries :

 select * from 
 (
 select u.*, row_number() over (order by dbms_random.value) as rn 
   from users u
  where 
    dbms_random.value < 200 / (select count(1) from logs)
  )
  where rn <= 100;

and

select * from
(
select u.*, row_number() over (order by 1) as rn
  from users u
 where id in (
              select round(dbms_random.value * 21e6) as id                    
                from dual
             connect by level <= 110  
             )
 )
 where rn <= 100;

If your Oracle DB's version is 12c, you can replace where rn <= 100 parts with fetch first 100 rows only and remove rn ( which are made up of row_number() function ) columns in the subqueries.

edited Apr 11, 2019 at 12:52

answered Apr 11, 2019 at 12:43

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

giotto Over a year ago

I don't get from the original blog post, and from your answer, what is logs in the first query, I thought some specific Postgres thing..

Barbaros Özhan Over a year ago

@giotto I think they are not platform spesific structures, such as dictionary views, but user-created ordinary tables.

giotto Over a year ago

Any clue on why I get SQL Error [904] [42000]: ORA-00904: "ID": invalid identifier error if I run the second query?

Barbaros Özhan Over a year ago

@giotto seems you have no such column ID in users table. You might rename in the subquery to a name which is a column of that table.

Collectives™ on Stack Overflow

Convert postgres sql queries into oracle

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related