<tfoot id='7hLl2'></tfoot>
  • <i id='7hLl2'><tr id='7hLl2'><dt id='7hLl2'><q id='7hLl2'><span id='7hLl2'><b id='7hLl2'><form id='7hLl2'><ins id='7hLl2'></ins><ul id='7hLl2'></ul><sub id='7hLl2'></sub></form><legend id='7hLl2'></legend><bdo id='7hLl2'><pre id='7hLl2'><center id='7hLl2'></center></pre></bdo></b><th id='7hLl2'></th></span></q></dt></tr></i><div id='7hLl2'><tfoot id='7hLl2'></tfoot><dl id='7hLl2'><fieldset id='7hLl2'></fieldset></dl></div>

    <small id='7hLl2'></small><noframes id='7hLl2'>

      <legend id='7hLl2'><style id='7hLl2'><dir id='7hLl2'><q id='7hLl2'></q></dir></style></legend>

          <bdo id='7hLl2'></bdo><ul id='7hLl2'></ul>
      1. 在 Oracle 中插入一百万行的最快方法

        时间:2023-11-28

          • <small id='QUyvt'></small><noframes id='QUyvt'>

          • <legend id='QUyvt'><style id='QUyvt'><dir id='QUyvt'><q id='QUyvt'></q></dir></style></legend>

              <bdo id='QUyvt'></bdo><ul id='QUyvt'></ul>
              <tfoot id='QUyvt'></tfoot>
              • <i id='QUyvt'><tr id='QUyvt'><dt id='QUyvt'><q id='QUyvt'><span id='QUyvt'><b id='QUyvt'><form id='QUyvt'><ins id='QUyvt'></ins><ul id='QUyvt'></ul><sub id='QUyvt'></sub></form><legend id='QUyvt'></legend><bdo id='QUyvt'><pre id='QUyvt'><center id='QUyvt'></center></pre></bdo></b><th id='QUyvt'></th></span></q></dt></tr></i><div id='QUyvt'><tfoot id='QUyvt'></tfoot><dl id='QUyvt'><fieldset id='QUyvt'></fieldset></dl></div>
                  <tbody id='QUyvt'></tbody>
                1. 本文介绍了在 Oracle 中插入一百万行的最快方法的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  对于以下过程,我如何以最佳方式在 Oracle 中插入超过一百万行?如果我将 FOR 循环增加到一百万行,它就会挂起.

                  How can I insert more than a million rows in Oracle in optimal way for the following procdeure? It hangs if I increase FOR loop to a million rows.

                  create or replace procedure inst_prc1 as
                     xssn number;
                     xcount number;
                     l_start Number;
                     l_end Number;
                     cursor c1 is select max(ssn)S1 from dtr_debtors1;
                  
                  Begin
                    l_start := DBMS_UTILITY.GET_TIME;
                    FOR I IN 1..10000 LOOP
                      For C1_REC IN C1 Loop
                        insert into dtr_debtors1(SSN) values (C1_REC.S1+1);
                      End loop;
                    END LOOP;
                    commit;
                    l_end := DBMS_UTILITY.GET_TIME;
                    DBMS_OUTPUT.PUT_LINE('The Procedure  Start Time is '||l_start);
                    DBMS_OUTPUT.PUT_LINE('The Procedure End Time is '||l_end); 
                  End inst_prc1;
                  

                  推荐答案

                  您的方法将导致内存问题.最快的方法是[在大卫的评论后编辑查询以处理空场景]:

                  Your approach will lead to memory issues. Fastest way will be this [Query edited after David's comment to take care of null scenario] :

                  insert into dtr_debtors1(SSN)
                  select a.S1+level
                     from dual,(select nvl(max(ssn),0) S1 from dtr_debtors1) a
                  connect by level <= 10000 
                  

                  选择插入是最快的方法,因为所有内容都保留在 RAM 中.如果此查询滑入全局临时区域,则它可能会变慢,但这需要 DB 调整.我认为没有比这更快的了.

                  A select insert is the fastest approach as everything stays in RAM. This query can become slow if it slips into Global temp area but then that needs DB tuning . I don't think there can be anything faster than this.

                  关于查询内存使用的更多细节:

                  Few more details on memory use by Query:

                  每个查询都有自己的 PGA [程序全局区域],它基本上是每个查询可用的 RAM.如果这个区域不足以返回查询结果,那么 SQL 引擎开始使用 Golabl 临时表空间,就像硬盘一样,查询开始变慢.如果查询需要的数据太大,甚至临时区域都不够用,那么你就会出现表空间错误.

                  Each query will have its own PGA [Program global area] which is basically RAM available to each query. If this this area is not sufficient to return query results then SQL engine starts using Golabl temp tablespace which is like hard disk and query starts becoming slow. If data needed by query is so huge that even temp area is not sufficient then you will tablespace error.

                  所以总是设计查询,使其留在 PGA 中,否则它是一个危险信号.

                  So always design query so that it stays in PGA else its a Red flag.

                  这篇关于在 Oracle 中插入一百万行的最快方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:Oracle - 字符串组合排列 下一篇:查询帮助 - where 子句中的字符串有 &amp;特点

                  相关文章

                  <tfoot id='Matwo'></tfoot>
                2. <small id='Matwo'></small><noframes id='Matwo'>

                  1. <i id='Matwo'><tr id='Matwo'><dt id='Matwo'><q id='Matwo'><span id='Matwo'><b id='Matwo'><form id='Matwo'><ins id='Matwo'></ins><ul id='Matwo'></ul><sub id='Matwo'></sub></form><legend id='Matwo'></legend><bdo id='Matwo'><pre id='Matwo'><center id='Matwo'></center></pre></bdo></b><th id='Matwo'></th></span></q></dt></tr></i><div id='Matwo'><tfoot id='Matwo'></tfoot><dl id='Matwo'><fieldset id='Matwo'></fieldset></dl></div>

                    <legend id='Matwo'><style id='Matwo'><dir id='Matwo'><q id='Matwo'></q></dir></style></legend>
                      <bdo id='Matwo'></bdo><ul id='Matwo'></ul>