CODEWITHSUNDEEP
sqloracle-databasepostgresql
giotto
giotto

Reputation: 622

Convert postgres sql queries into oracle

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?

Upvotes: 0

Views: 486

Answers (1)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65218

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.

Upvotes: 1

Related Questions