不同的查询需要很长时间才能运行 [英] Distinct query taking long time to run

问题描述

嗨 - 我在我的一个proc中有这个插入查询，将数据加载到目标表中，其中distinct子句产生巨大影响并花费太多时间（因为每个表都有超过2和3百万条记录的记录）。他们有什么方法可以优化这个选择查询吗？



我尝试过：

Hi - I have this insert query in one of my proc to load the data into a target table where the distinct clause is making huge impact and taking too much time(since each table is having records more than 2 and 3 million records). Is their any way to optimize this select query with distinct?

What I have tried:

Insert into target table
SELECT  DISTINCT

        tab1.col1 col1,

        tab1.col2  col2,

        tab2.col3  col3,

        tab2.col4 col4,

        tab3.col5 col5,

        tab3.col6 col6,

        tab3.col7 col7,

        tab3.col8 col8,

        tab3.col9 col9,

        to_char(tab3.col7, 'YYYY-MM') col10,

        to_char(tab3.col7, 'YYYY') col11,

        to_char(tab3.col7, 'MM') col12,

        CURRENT_DATE  col13

      FROM tab3@dblink t2

        JOIN tab4@dblink ON tab3.col5 = tab4.col13

        JOIN tab5@dblink ON tab4.col14 = tab5.col15

        JOIN tab2@dblink ON tab5.col16 = tab2.col17

        JOIN tab1 ON tab1.col18 = tab2.col3;

推荐答案

什么遇到了明显的问题，如果你有很多行而你需要根据多个列找到非重复的行，那么这需要时间和资源。如果选择性很高，那么正确的索引会对你有所帮助，但是如果查询检索到表中超过10％的行，则索引不是一个可行的选项。



基于语法，我认为这是Oracle？如果这是正确的，很少有事情要考虑：



- 目前你使用单独的数据库链接。这可能会强制所有行在进行连接之前通过链接传输。如果链接指向同一源数据库，请考虑在源中使用连接和distinct创建视图，并仅选择视图。这可以消除数据库之间不必要的数据传输的需要



- 确保有足够数量的PGA可用，换句话说你没有限制大小



- 但同时确保服务器不是分页。如果发生分页，则会大大减慢查询速度。

What comes to the distinct problem, if you have lots of rows and you need to find non-duplicates based on multiple columns, it's going to take time and resources. If the selectivity is high then proper indexing would benefit you but if the query retrieves say more than 10% of the rows in the table then indexing isn't a viable option.

Based on the syntax, I take it this is Oracle? If that is correct, few things to consider:

- At the moment you utilize separate database links. This probably forces all rows to be transferred via the link before doing the join. If the links point to the same source database, consider creating a view in the source with the joins and the distinct and select only the view. This could remove the need of unnecessary data transfer between databases

- Ensure that sufficient amount of PGA is available, in other words that you're not limiting the size

- But at the same time ensure that the server isn't paging. If paging occurs, it will drastically slow down the query.

这篇关于不同的查询需要很长时间才能运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！