使用选择列表查询 Oracle
我有一个具有只读访问权限的 Oracle 数据库(无权创建临时表)。 我有一个包含 28000 个 ID 的选择列表(在 Excel 中),对应于具有数百万条记录的表中的 28000 行。 如何编写查询来返回 28000 行?
我尝试在 Access 中创建一个表并通过 ODBC 执行联接,但 Access 冻结/花费了令人难以置信的长时间。 我是否必须在 IN 语句中创建包含 28,000 个项目的查询?
PL/SQL 中有什么东西可以让它变得更容易吗?
感谢您的时间和帮助。
-JC
I have an oracle database that I have read-only access (with no permission to create temporary tables). I have a pick list (in Excel) of 28000 IDs corresponding to 28000 rows in a table which has millions of records. How do I write a query to return the 28000 rows?
I tried creating a table in access and performing a join through ODBC but Access freezes/takes an incredible long time. Would I have to create a query with 28,000 items in an IN statement?
Is there anything in PL/SQL that would make it easier?
Thank you for your time and help.
-JC
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
是什么让您的 28,000 行如此特别?
记录中是否还有另一个字段可以用来限制 WHERE 子句中的查询(或者至少缩小数百万行的范围)? 也许您感兴趣的 ID 属于某个范围?
What makes your 28,000 rows special?
Is there another field in the records you can use to restrict you query in a WHERE clause (or at least narrow down the millions of rows a bit)? Perhaps the ID's you're interested in fall within a certain range?
在 Oracle 10g 中,IN (.., .. ,,) 类型查询的最大变量数为 1000。
The max number of variables for an IN (.., .. ,,) type query is 1000 in Oracle 10g.
尝试在 Access 中创建的表上创建索引。
Try creating an index on the table you created in Access.
这是一种痛苦的情况。一种解决方法是创建一个包含所有 id 的视图,然后加入其中。
下面的例子是Oracle。
这基本上将所有 28000 个 id 嵌入到 with 子句中,允许您进行连接,而无需实际创建表。
丑陋,但应该有用。
That's a painful condition to be in. One workaround is to create a view that contains all of the ids, then join to it.
The example below is Oracle.
This basically embeds all 28000 ids, in the with clause, allowing you to do the join, without actually creating a table.
Ugly, but it should work.
这里描述了最好的方法:如何将超过 1000 个值放入 Oracle IN 子句
The best way to do it is described here: How to put more than 1000 values into an Oracle IN clause