7 ms·

In Oracle land you have no choice but to use a long list of OR because IN has a low clause limit (1000). So if you need e.g. 1.1K rows, even using a fast indexe

In Oracle land you have no choice but to use a long list of OR because IN has a low clause limit (1000). So if you need e.g. 1.1K rows, even using a fast indexed PK, your choices are OR or pain. The workarounds are horrible, like splitting your IN clauses and UNION ALL two copies of the same query (runs twice), or using a global temp table (which is awkward as heck to setup/destroy each execution, particularly with IN clauses that could range from 1 through 1K+). Postgres has no practical limit, there's examples of people using 100K+ IN clauses (not recommending). People keep telling me that Oracle is "enterprise grade" and that Postgres is not. To be honest all I see is Postgres is flexible and modern, and Oracle is kludgy and outmoded. I'm forced to use Oracle but I'd never voluntarily start any new projects on it, I'm all in on Postgres from here on out.

LessDmesg 7y ago
Just use a join on a temporary table. IN with a thousand clauses is an abuse of SQL.
silveroriole 7y ago
Used Oracle for a long time but have never had the need to do this. Can I ask what your use case is? I can’t imagine one which isn’t solved by a subquery or a built-in Oracle temp table which automatically restricts the contents of the temp table to one transaction, with no need to setup/teardown per execution (as long as you’re using sensible transactions).
- Someone1234 7y ago
  Generating reports. I think saying you "I can’t imagine [a usage] which isn’t solved by [workarounds]" kind of misses the core issue. It is a common use-case (to select a list of rows via PKs), and you're having to work around internal structures of the database, rather than it exposing a simple primitive and the database doing work behind the scenes to make it efficient. If building a temp table for that many IN clauses is more efficient absolutely do it. But don't expose this ugly to the outside world, particularly as the problem space and solution are well defined, common, and old. The fact that Oracle continues to ignore basic quality of life improvements and its defenders point to convoluted workarounds that push additional work onto its users is half the problem.
- jzoch 7y ago
  We have a problem at work where one of our tables is 5 billion rows and we get a csv (or maybe a few csv's ;) ) with a total of 21 million primary keys. We now need to find which of these 21 million keys are already in our table and do some task on those that are not. Originally, we batched 1k records at a time and used an IN. It was taking hours. Each query finished in 1.5 seconds. Then we moved to a global temp table. It was around 30% faster iirc. Still not good enough (open to suggestions!!!) but way better. Wish oracle would just do this for us internally.
  - bhaak 7y ago
    How about this: 21 million primary keys are easily kept in memory. Sort them. Then do "SELECT id FROM five_billion_rows ORDER BY id" and iterate over that with the CSV ids in lockstep. Cursor management is pretty good in Oracle, this is likely much better than your own paging with 1k of IN values. I would assume that it would be IO bound regarding the index of the id column. This also might be an interesting question for "Ask HN".
  - Tarq0n 7y ago
    Is an anti join not feasible in this scenario?
- psYchotic 7y ago
  Just as a heads up for anyone considering using Oracle's built-in support for temporary tables, there are a few restrictions [1], no support for distributed transactions being the main one I ran into at a previous client. Taking into consideration the fact that you'll want to avoid hard parses [2][3] whenever possible, this leaves you the option of mimicking a temporary table manually (which will probably include having to manually fix statistics on your fake temporary table), or maybe use some string manipulation in your query to split a delimited list (which is itself limited to 4000 characters [4]). That being said, I've been told that distributed transactions should themselves be avoided, so maybe look into that before getting mad at Oracle for what seems to be an arbitrary limitation on temporary tables. [1]: https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF54448 https://docs.oracle.com/database/121/SQLRF/statements_7002.h... [2]: https://docs.oracle.com/database/121/TGSQL/tgsql_sqlproc.htm#GUID-BFF0B26C-0A5D-4F79-B01E-8E1C4064A6AD https://docs.oracle.com/database/121/TGSQL/tgsql_sqlproc.htm... [3]: https://blogs.oracle.com/sql/improve-sql-query-performance-by-using-bind-variables https://blogs.oracle.com/sql/improve-sql-query-performance-b... [4]: https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#i45694 https://docs.oracle.com/database/121/SQLRF/sql_elements001.h...
irrational 7y ago
After being on Oracle for 15 years, we recently finished moving to Postgres. It has been amazing. Postgres does so many things better than Oracle and this IN issue is just one of them. I wish we had moved off of Oracle years ago. I don't think there is any reason to choose Oracle over Postgres these days. I will certainly never use it again.
- bluejekyll 7y ago
  Of course the issue for many companies is that they hit a size and usage of Oracle that moving off becomes a herculean effort. There's no direct replacement for the way people use RAC for example, which means changing the way you approach certain HA requirements. Add to that Oracle PLSQL is not fully portable to Postgres PGSQL. They're close enough that you think it's a simple task, but it's still significant. Since many people have many things built on top of a single DB, it also generally means a single massive switch that will take a long time and is difficult to test piecemeal. I'm only saying this so that people go into a switch with their eyes wide open (though, I'm sure you would anyway).
  - irrational 7y ago
    Oh, for sure. It took us 2 years to make the switch. But despite the time and effort it was totally worth it. I would hope that anyone starting a new project would not even consider choosing Oracle. In my opinion and experience there is no point anymore, especially considering the high cost of an Oracle license.
    - bluejekyll 7y ago
      Actually, I am curious about that question, is Oracle being picked by any new businesses? My guess is that on HN we get a skewed view, but my guess is that even given that, Oracle is not being chosen for anything new at this point?
spullara 7y ago
Both Oracle and Postgres have a way of creating an inline table from a list of constants. I wonder if doing that in the query and joining to it would be more efficient.
cyborgx7 7y ago
> Oracle is kludgy and outmoded Isn't that what people mean when they say 'enterprise grade'?
- dec0dedab0de 7y ago
  No, enterprise grade means they have a sales team that can take people out to lunch, and find round about ways to give kickbacks to decision makers. Being kludgy and outmoded is a common side effect, but not a requirement.
- mongol 7y ago
  I think enterprise grade means, among other things, having the means to scale support to meet demands. A large enterprise runs databases in 100s or even 1000s of different applications, developed by maybe 100 different teams. It helps to know that the vendor is able to meet support needs, not just for the use by one team where you know the circumstances, but generally, even when you don't know them.
eximius 7y ago
Is there no equivalent to https://dba.stackexchange.com/questions/86724/how-to-create-a-temporary-table-using-values-in-postgresql https://dba.stackexchange.com/questions/86724/how-to-create-... outside postgres to join against?
- cafard 7y ago
  How about select x, y from (select -1 * (rownum - 1) as x from all_objects where rownum <= 10000), (select rownum as y from all_objects where rownum <= 100) Wrap it in another SELECT * FROM () if you wish.
orf 7y ago
> and Oracle is kludgy and outmoded While adding EXPLAIN support to Django I ran into a very kludgy issue with Oracle. To EXPLAIN a query you need to: 1. Add an EXPLAIN PLAN FOR prefix, and give it a unique ID (and make sure it is unique!) 2. This plan is then written to a special table that you may or may not have access to, and may or may not be renamed. 3. Execute `DBMS_XPLAN.DISPLAY()` with the UUID you gave above (this function is not always available) 4. Find out that you cannot use any parameters with your SQL query while explaining, then give up. Madness.
oweiler 7y ago
Don't more recent versions of Oracle support CTEs?
paulddraper 7y ago
I rarely use massive IN clauses though. I use arrays. SELECT * FROM orders WHERE id = ANY(?::bigint[]) It originated as just being an easy way to parameterize my queries; IDK how the performance compares. From their blog post, I know Datadog does the same. [1] [1] https://www.datadoghq.com/blog/100x-faster-postgres-performance-by-changing-1-line/ https://www.datadoghq.com/blog/100x-faster-postgres-performa...

LessDmesg 7y ago

Just use a join on a temporary table. IN with a thousand clauses is an abuse of SQL.

silveroriole 7y ago

Used Oracle for a long time but have never had the need to do this. Can I ask what your use case is? I can’t imagine one which isn’t solved by a subquery or a built-in Oracle temp table which automatically restricts the contents of the temp table to one transaction, with no need to setup/teardown per execution (as long as you’re using sensible transactions).

Someone1234 7y ago
Generating reports. I think saying you "I can’t imagine [a usage] which isn’t solved by [workarounds]" kind of misses the core issue. It is a common use-case (to select a list of rows via PKs), and you're having to work around internal structures of the database, rather than it exposing a simple primitive and the database doing work behind the scenes to make it efficient. If building a temp table for that many IN clauses is more efficient absolutely do it. But don't expose this ugly to the outside world, particularly as the problem space and solution are well defined, common, and old. The fact that Oracle continues to ignore basic quality of life improvements and its defenders point to convoluted workarounds that push additional work onto its users is half the problem.
jzoch 7y ago
We have a problem at work where one of our tables is 5 billion rows and we get a csv (or maybe a few csv's ;) ) with a total of 21 million primary keys. We now need to find which of these 21 million keys are already in our table and do some task on those that are not. Originally, we batched 1k records at a time and used an IN. It was taking hours. Each query finished in 1.5 seconds. Then we moved to a global temp table. It was around 30% faster iirc. Still not good enough (open to suggestions!!!) but way better. Wish oracle would just do this for us internally.
- bhaak 7y ago
  How about this: 21 million primary keys are easily kept in memory. Sort them. Then do "SELECT id FROM five_billion_rows ORDER BY id" and iterate over that with the CSV ids in lockstep. Cursor management is pretty good in Oracle, this is likely much better than your own paging with 1k of IN values. I would assume that it would be IO bound regarding the index of the id column. This also might be an interesting question for "Ask HN".
- Tarq0n 7y ago
  Is an anti join not feasible in this scenario?
psYchotic 7y ago
Just as a heads up for anyone considering using Oracle's built-in support for temporary tables, there are a few restrictions [1], no support for distributed transactions being the main one I ran into at a previous client. Taking into consideration the fact that you'll want to avoid hard parses [2][3] whenever possible, this leaves you the option of mimicking a temporary table manually (which will probably include having to manually fix statistics on your fake temporary table), or maybe use some string manipulation in your query to split a delimited list (which is itself limited to 4000 characters [4]). That being said, I've been told that distributed transactions should themselves be avoided, so maybe look into that before getting mad at Oracle for what seems to be an arbitrary limitation on temporary tables. [1]: https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF54448 https://docs.oracle.com/database/121/SQLRF/statements_7002.h... [2]: https://docs.oracle.com/database/121/TGSQL/tgsql_sqlproc.htm#GUID-BFF0B26C-0A5D-4F79-B01E-8E1C4064A6AD https://docs.oracle.com/database/121/TGSQL/tgsql_sqlproc.htm... [3]: https://blogs.oracle.com/sql/improve-sql-query-performance-by-using-bind-variables https://blogs.oracle.com/sql/improve-sql-query-performance-b... [4]: https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#i45694 https://docs.oracle.com/database/121/SQLRF/sql_elements001.h...

irrational 7y ago

After being on Oracle for 15 years, we recently finished moving to Postgres. It has been amazing. Postgres does so many things better than Oracle and this IN issue is just one of them. I wish we had moved off of Oracle years ago. I don't think there is any reason to choose Oracle over Postgres these days. I will certainly never use it again.

bluejekyll 7y ago
Of course the issue for many companies is that they hit a size and usage of Oracle that moving off becomes a herculean effort. There's no direct replacement for the way people use RAC for example, which means changing the way you approach certain HA requirements. Add to that Oracle PLSQL is not fully portable to Postgres PGSQL. They're close enough that you think it's a simple task, but it's still significant. Since many people have many things built on top of a single DB, it also generally means a single massive switch that will take a long time and is difficult to test piecemeal. I'm only saying this so that people go into a switch with their eyes wide open (though, I'm sure you would anyway).
- irrational 7y ago
  Oh, for sure. It took us 2 years to make the switch. But despite the time and effort it was totally worth it. I would hope that anyone starting a new project would not even consider choosing Oracle. In my opinion and experience there is no point anymore, especially considering the high cost of an Oracle license.
  - bluejekyll 7y ago
    Actually, I am curious about that question, is Oracle being picked by any new businesses? My guess is that on HN we get a skewed view, but my guess is that even given that, Oracle is not being chosen for anything new at this point?

spullara 7y ago

Both Oracle and Postgres have a way of creating an inline table from a list of constants. I wonder if doing that in the query and joining to it would be more efficient.

cyborgx7 7y ago

> Oracle is kludgy and outmoded Isn't that what people mean when they say 'enterprise grade'?

dec0dedab0de 7y ago
No, enterprise grade means they have a sales team that can take people out to lunch, and find round about ways to give kickbacks to decision makers. Being kludgy and outmoded is a common side effect, but not a requirement.
mongol 7y ago
I think enterprise grade means, among other things, having the means to scale support to meet demands. A large enterprise runs databases in 100s or even 1000s of different applications, developed by maybe 100 different teams. It helps to know that the vendor is able to meet support needs, not just for the use by one team where you know the circumstances, but generally, even when you don't know them.

eximius 7y ago

Is there no equivalent to https://dba.stackexchange.com/questions/86724/how-to-create-a-temporary-table-using-values-in-postgresql https://dba.stackexchange.com/questions/86724/how-to-create-... outside postgres to join against?

cafard 7y ago
How about select x, y from (select -1 * (rownum - 1) as x from all_objects where rownum <= 10000), (select rownum as y from all_objects where rownum <= 100) Wrap it in another SELECT * FROM () if you wish.

orf 7y ago

> and Oracle is kludgy and outmoded While adding EXPLAIN support to Django I ran into a very kludgy issue with Oracle. To EXPLAIN a query you need to: 1. Add an EXPLAIN PLAN FOR prefix, and give it a unique ID (and make sure it is unique!) 2. This plan is then written to a special table that you may or may not have access to, and may or may not be renamed. 3. Execute `DBMS_XPLAN.DISPLAY()` with the UUID you gave above (this function is not always available) 4. Find out that you cannot use any parameters with your SQL query while explaining, then give up. Madness.

oweiler 7y ago

Don't more recent versions of Oracle support CTEs?

paulddraper 7y ago

I rarely use massive IN clauses though. I use arrays. SELECT * FROM orders WHERE id = ANY(?::bigint[]) It originated as just being an easy way to parameterize my queries; IDK how the performance compares. From their blog post, I know Datadog does the same. [1] [1] https://www.datadoghq.com/blog/100x-faster-postgres-performance-by-changing-1-line/ https://www.datadoghq.com/blog/100x-faster-postgres-performa...