查询多个Oracle数据库性能问题

发布于 2024-12-04 09:11:56 字数 309 浏览 1 评论 0原文

我在两个数据库的这些表中都有超过一百万条记录。我正在尝试找出跨数据库表中的数据。

SELECT COUNT(*) FROM DB1.MYTABLE WHERE SEQ_NO NOT IN(SELECT SEQ_NO FROM DB2.MYTABLE) AND FILENAME NOT LIKE '%{%'  
and PT_TYPE NOT IN(15,24,268,284,285,286,12,17,9,290,214,73) AND STTS=1

查询需要很长时间。有什么办法可以让它快点吗？

提前感谢您的帮助

原文

I have over million records in these tables in both the databases.
I am trying to figure out data in both the tables acros databases.

SELECT COUNT(*) FROM DB1.MYTABLE WHERE SEQ_NO NOT IN(SELECT SEQ_NO FROM DB2.MYTABLE) AND FILENAME NOT LIKE '%{%'  
and PT_TYPE NOT IN(15,24,268,284,285,286,12,17,9,290,214,73) AND STTS=1

The query is taking ages. Is there any way I can make it fast?

Appreciate your help in advance

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

流心雨 2024-12-11 09:11:56

您实际上是指不同的数据库吗？或者你的意思是不同的模式？您谈论不同的数据库，但语法似乎使用两个不同模式中的表，而不是两个不同的数据库。我没有看到任何对数据库链接的引用，如果有两个不同的数据库，则需要该链接，但也许 DB2.MYTABLE 应该是 MYTABLE@DB2 的同义词。

如果您可以发布生成的查询计划，将会很有帮助。指示存在哪些索引以及每个谓词的选择性程度也很有用。修改查询

SELECT count(*)
  FROM schema1.mytable a
 WHERE NOT EXISTS (
    SELECT 1
      FROM schema2.mytable b
     WHERE a.seq_no = b.seq_no )
   AND a.filename NOT LIKE '%{%'
   AND a.pt_type NOT IN (15,24,268,284,285,286,12,17,9,290,214,73)
   AND a.stts = 1

我的猜测是，如果由于 SCHEMA2.MYTABLE 中存在 SEQ_NO 而消除了 SCHEMA1.MYTABLE 中的大多数行，则可能会更有效/代码>。

Do you actually mean different databases? Or do you mean different schemas? You talk about different databases but the syntax appears to be using tables in two different schemas, not two different databases. I don't see any references to a database link which would be needed if there were two different databases but perhaps DB2.MYTABLE is supposed to be a synonym for MYTABLE@DB2.

It would be helpful if you could post the query plan that is generated. It would also be useful to indicate what indexes exist and how selective each of these predicates is. My guess is that modifying the query to be

SELECT count(*)
  FROM schema1.mytable a
 WHERE NOT EXISTS (
    SELECT 1
      FROM schema2.mytable b
     WHERE a.seq_no = b.seq_no )
   AND a.filename NOT LIKE '%{%'
   AND a.pt_type NOT IN (15,24,268,284,285,286,12,17,9,290,214,73)
   AND a.stts = 1

might be more efficient if most of the rows in SCHEMA1.MYTABLE are eliminated because the SEQ_NO exists in SCHEMA2.MYTABLE.

回复收藏 0 原文

~没有更多了~