blocks|key|4048224|text|如果您使用的是Oracle+12c或更高版本，则可以通过使用OFFSET和FETCH直接使用分页，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4048225|SELECT+*+FROM+DB_A+
++WHERE+data+>=n+days
ORDER+BY+n
OFFSET+m*x+rows
fetch+next+m+rows+only;|code-block|syntax|javascript|4048226|这里，x是从0开始的迭代次数。|4048227|假设你一次想要5行。|4048228|对于第一次迭代：|4048229|OFFSET+0+rows
fetch+next+5+rows+only;|4048230|对于第二次迭代：|4048231|OFFSET+5+rows
fetch+next+5+rows+only;|4048232|一直持续到最后……|4048233|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

If you are using the Oracle 12c or higher, you can directly use pagination by using OFFSET and FETCH as follows:

<pre><code>SELECT * FROM DB_A 
 WHERE data &gt;=n days
ORDER BY n
OFFSET m*x rows
fetch next m rows only;
</code></pre>

here, x is a number of iterations, starting with 0.

Let's say you want 5 rows at a time.

for the first iteration:

<pre><code>OFFSET 0 rows
fetch next 5 rows only;
</code></pre>

for the second iteration:

<pre><code>OFFSET 5 rows
fetch next 5 rows only;
</code></pre>

Continue till the end .....

blocks|key|4048257|text|如果数据量太大，无法作为一个数据量处理，则在分界日期上循环显式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4048258|+SELECT+*+FROM+DB_A+
+WHERE+date_column+>=+DATE_FROM++and+date_column+<+DATE_TO|code-block|syntax|javascript|4048259|请注意，性能(也就是内存消耗)不仅与结果行数相关，还与处理数据的方式相关。|4048260|尽量避免在大容量数据上出现游标循环。首选的处理方式不是逐行的，而是面向集合的，即|4048261|INSERT+....++SELECT+....+FROM+DB_A+WHERE+...|4048262|如果源表DB_A按相关的date列(上面查询中的date_column)进行分区，您可能会从中获利。查询将只选择那些具有受限制日期的分区。|offset|length|style|CODE|4048263|您可以(在使用SQL方法的同时)从parallel+DML中获益，从而提高性能。|4048264|示例|BOLD|4048265|假设表DB_A的date_column列中包含30天的数据，并且您希望以5天为一批处理这些数据。|4048266|您将以6个批次结束，如下面的查询所示。|4048267|查询首先从表中选择不同的截断日期，然后使用ROW_NUMBER计算batch_idx，并使用trunc除以5。最后，计算每个batch_idx的边界日期。|4048268|with+batch1+as
(select+distinct+trunc(date_column)++start_date+from+db_a),
batch2+as+(
select+start_date,
++trunc((row_number()+over+(order+by+start_date)-1)+/+5)+as+batch_idx
from+batch1)
select+batch_idx,+min(START_DATE)+START_DATE,+max(START_DATE)%2B1+END_DATE+from+batch2
group+by+batch_idx
order+by+1
;

+BATCH_IDX+START_DATE++++++++++END_DATE+++++++++++
----------+-------------------+-------------------
+++++++++0+01.05.2020+00:00:00+06.05.2020+00:00:00
+++++++++1+06.05.2020+00:00:00+11.05.2020+00:00:00
+++++++++2+11.05.2020+00:00:00+16.05.2020+00:00:00
+++++++++3+16.05.2020+00:00:00+21.05.2020+00:00:00
+++++++++4+21.05.2020+00:00:00+26.05.2020+00:00:00
+++++++++5+26.05.2020+00:00:00+31.05.2020+00:00:00|4048269|您可以在游标循环中使用这个查询来处理数据，如下所示。|4048270|请注意，START_DATE是包含的，但END_DATE是排他的。|4048271|BEGIN
+FOR+cur+in+(
+with+batch1+as
++(select+distinct+trunc(date_column)++start_date+from+db_a),
+batch2+as+(
++select+start_date,
+++trunc((row_number()+over+(order+by+start_date)-1)+/+5)+as+batch_idx
++from+batch1)
++select+batch_idx,+min(START_DATE)+START_DATE,+max(START_DATE)%2B1+END_DATE+from+batch2
++group+by+batch_idx
++order+by+1)+
LOOP
++++dbms_output.put_line('Processing+dates+from+'%7C%7Cto_char(cur.START_DATE,'dd.mm.yyyy')+%7C%7C+'+to+'+%7C%7C+to_char(cur.END_DATE,'dd.mm.yyyy'));
++++insert+into+DB_TARGET(date_column,....)+
++++select+date_column,....+from+DB_A+
++++where+date_column+>=+cur.START_DATE+and+date_column+<+cur.END_DATE;
++++commit;
END+LOOP;
END;
/|4048272|该循环将使用预期的日期范围执行六次：|4048273|Processing+dates+from+01.05.2020+to+06.05.2020
Processing+dates+from+06.05.2020+to+11.05.2020
Processing+dates+from+11.05.2020+to+16.05.2020
Processing+dates+from+16.05.2020+to+21.05.2020
Processing+dates+from+21.05.2020+to+26.05.2020
Processing+dates+from+26.05.2020+to+31.05.2020+|4048274|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/types-parallelism.html#GUID-1D5C8D6C-0A0E-4CDB-8B32-16EC3C856ACC^0|0|0|0|0|0|4|4|O|B|0|H|C|0|0|0|2|0|3|4|8|B|0|0|L|A|X|9|1A|5|1Q|9|0|0|0|4|A|K|8|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1N|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1O|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1P|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|1R|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|1S|8|@$O|1T|P|1U|Q|R]|$O|1V|P|1W|Q|R]]|9|@]|A|$]]|$1|S|3|T|5|6|7|1X|8|@]|9|@$O|1Y|P|1Z|1|20]]|A|$]]|$1|U|3|V|5|6|7|21|8|@$O|22|P|23|Q|W]]|9|@]|A|$]]|$1|X|3|Y|5|6|7|24|8|@$O|25|P|26|Q|R]|$O|27|P|28|Q|R]]|9|@]|A|$]]|$1|Z|3|10|5|6|7|29|8|@]|9|@]|A|$]]|$1|11|3|12|5|6|7|2A|8|@$O|2B|P|2C|Q|R]|$O|2D|P|2E|Q|R]|$O|2F|P|2G|Q|R]|$O|2H|P|2I|Q|R]]|9|@]|A|$]]|$1|13|3|14|5|D|7|2J|8|@]|9|@]|A|$E|F]]|$1|15|3|16|5|6|7|2K|8|@]|9|@]|A|$]]|$1|17|3|18|5|6|7|2L|8|@$O|2M|P|2N|Q|R]|$O|2O|P|2P|Q|R]]|9|@]|A|$]]|$1|19|3|1A|5|D|7|2Q|8|@]|9|@]|A|$E|F]]|$1|1B|3|1C|5|6|7|2R|8|@]|9|@]|A|$]]|$1|1D|3|1E|5|D|7|2S|8|@]|9|@]|A|$E|F]]|$1|1F|3|-4|5|6|7|2T|8|@]|9|@]|A|$]]]|1G|$1H|$5|1I|1J|1K|A|$1L|1M]]]]

If the data volume is too large to be processed as one, loop explicite on the demarcations dates:

<pre><code> SELECT * FROM DB_A 
 WHERE date_column &gt;= DATE_FROM and date_column &lt; DATE_TO
</code></pre>

Note that for the performance (i.e. also for the memory consumption) is not only the number of the result rows is relevant, but the way you process the data.

Try to avoid a cursor loop on large volume data. The prefered way of processing is not the row by row, but set oriented, i.e.

<pre><code>INSERT .... SELECT .... FROM DB_A WHERE ...
</code></pre>

You may profit if the source table <code>DB_A</code> is partitioned on the relevant date column (<code>date_column</code> in the query above). The query will select only those partitions with the constrained dates.

You may profit (while using the SQL approach) from <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/types-parallelism.html#GUID-1D5C8D6C-0A0E-4CDB-8B32-16EC3C856ACC" rel="nofollow noreferrer">parallel DML</a> to boost the performance.

Example

Suppose the table <code>DB_A</code> contains data of 30 days in the column <code>date_column</code>and you want to process the data in 5 day batches. 

You will end with 6 batches as show in the query below.

The query select the distinct truncated days from the table in the first place, than calculates the <code>batch_idx</code> using <code>ROW_NUMBER</code> and division by 5 with <code>trunc</code>. Finally the boundary dates per <code>batch_idx</code> are calculated.

<pre><code>with batch1 as
(select distinct trunc(date_column) start_date from db_a),
batch2 as (
select start_date,
 trunc((row_number() over (order by start_date)-1) / 5) as batch_idx
from batch1)
select batch_idx, min(START_DATE) START_DATE, max(START_DATE)+1 END_DATE from batch2
group by batch_idx
order by 1
;

 BATCH_IDX START_DATE END_DATE 
---------- ------------------- -------------------
 0 01.05.2020 00:00:00 06.05.2020 00:00:00
 1 06.05.2020 00:00:00 11.05.2020 00:00:00
 2 11.05.2020 00:00:00 16.05.2020 00:00:00
 3 16.05.2020 00:00:00 21.05.2020 00:00:00
 4 21.05.2020 00:00:00 26.05.2020 00:00:00
 5 26.05.2020 00:00:00 31.05.2020 00:00:00
</code></pre>

This very same query you can use in a cursor LOOP to process tha data as illustrated below.

Note that <code>START_DATE</code> is inclusive, but the <code>END_DATE</code> is exclusive.

<pre><code>BEGIN
 FOR cur in (
 with batch1 as
 (select distinct trunc(date_column) start_date from db_a),
 batch2 as (
 select start_date,
 trunc((row_number() over (order by start_date)-1) / 5) as batch_idx
 from batch1)
 select batch_idx, min(START_DATE) START_DATE, max(START_DATE)+1 END_DATE from batch2
 group by batch_idx
 order by 1) 
LOOP
 dbms_output.put_line('Processing dates from '||to_char(cur.START_DATE,'dd.mm.yyyy') || ' to ' || to_char(cur.END_DATE,'dd.mm.yyyy'));
 insert into DB_TARGET(date_column,....) 
 select date_column,.... from DB_A 
 where date_column &gt;= cur.START_DATE and date_column &lt; cur.END_DATE;
 commit;
END LOOP;
END;
/
</code></pre>

The loop will be execute six time with the expected date ranges:

<pre><code>Processing dates from 01.05.2020 to 06.05.2020
Processing dates from 06.05.2020 to 11.05.2020
Processing dates from 11.05.2020 to 16.05.2020
Processing dates from 16.05.2020 to 21.05.2020
Processing dates from 21.05.2020 to 26.05.2020
Processing dates from 26.05.2020 to 31.05.2020 
</code></pre>

I have a generic question about structuring a query in SQL. 

I have a database for which I need to pull data for the last n days which turns out to be huge to fit in memory. 

I have a query like 

<pre><code> SELECT * FROM DB_A 
 WHERE data &gt;=n days
</code></pre>

The best way to fit the query into memory is to use a loop, such that I can pull the data say m days at time ( where m &lt;&lt; n) 

So how can I do that?

I am not getting any idea.

Looping in a SQL QUERY from a huge database to do incremental data pull

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个关于在SQL中构造查询的一般性问题。我有一个数据库，我需要为它提取过去n天的数据，这被证明是非常庞大的，无法放入内存中。我有一个类似这样的查询 SELECT * FROM DB_A WHERE data >=n days将查询放入内存的最好方法是使用循环，这样我就可以一次提取数据，比如m天(其中m << n...

问循环来自大型数据库的SQL查询以执行增量数据拉取
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问循环来自大型数据库的SQL查询以执行增量数据拉取EN