Oracle循环连接语句 [英] Oracle Loop Through Join Statement

问题描述

我需要在PL-SQL中执行一条语句，该语句将选择一个ID，对这些ID的子集执行联接...在下面的示例中，我有大约700000个客户，并且一个 far 比此示例中的简单while循环中显示的查询更复杂的查询...我播种的性能相当差，并且很好奇是否将当前的PL-SQL切成块"会带来性能提升? /p>

当前:

declare
  TYPE customerIdTabType IS TABLE OF customer.CustomerId%TYPE INDEX BY BINARY_INTEGER;
  vars customerIdTabType;

  -- maybe this should be in a table?
  cursor c is
  select
      c.CustomerId
  from customer c
  join productcustomers pc on pc.customerid = c.customerid
  join product p on p.productid = pc.productid
  where
      c.CustomerId > 1000;

begin  
  open c;
  loop
  fetch c bulk collect into vars limit 1000;

  -- here is where instead of looping through each item in vars
  -- i actually want to 'join' to the 1000 that i have.  
  forall i in 1..vars.count
  insert into xxx (CustomerId) 
  values (vars(i));
  commit;

  exit when vars.count = 0;
  end loop;
  close c;

end;

1. 在临时"存储容器中选择一个CustomerId列表-不确定选项是什么?
2. 通过将它们连接到另一个查询来批量处理诸如... 1000的那些CustomerId
3. 将所有结果插入物理表

所以，在T-SQL中可能是..

-- create a temp table
create table #MyTempTable (
    id int identity(1,1)
    ,customerid varchar(10)
)

-- populate that table
insert into #MyTempTable
select Customerid 
from schema.Customers

-- create some vars for looping
declare i int, c int;
select i = 0;
select c = count(*) from #MyTempTable;

-- loop through the original set in 'chunks' of 1000​
while i < c
begin
    insert into SomeOtherTable
        (CustomerId, CustomerAttribute)
    select
        o.CustomerId
        ,o.CustomerAttribute
    from OtherTable o
    join #MyTempTable t
    on o.CustomerId = t.CustomerId
    where
        t.Id between i and i+1000    -- from 0 to 1000

    set @i = i+1000    ​-- next loop will be from 1000 to 2000
end

谢谢

解决方案

然后，这

select startid = min(id) from customer;
select maxid = max(id) from customer;

i = startid
while i <= maxid

begin
   with myCTE as (select ... from customer where id >= i and id < i + 1000)
   insert into xxx (....)
   select ....
   from myCustomerChunk
        join productcustomers pc on ....
        join product p on ....

   i = i+1000
end

这样可以避免在一个语句中进行所有700000次插入操作，这可能会使查询更加困难……而游标使事情变得更糟. PS:这是伪代码和实际代码的大杂烩，因此您必须确定真实的语法.

I need to execute a statement in PL-SQL that taking a selection of Ids, executes a join against a sub-set of those ids... in the example below i have about 700000 customers, and a far more complex query than shown here in the simple while loop in this example...i'm seeding quite poor performance and am curious if chopping up my current PL-SQL into 'chunks' would yield a perf increase?

Currently:

declare
  TYPE customerIdTabType IS TABLE OF customer.CustomerId%TYPE INDEX BY BINARY_INTEGER;
  vars customerIdTabType;

  -- maybe this should be in a table?
  cursor c is
  select
      c.CustomerId
  from customer c
  join productcustomers pc on pc.customerid = c.customerid
  join product p on p.productid = pc.productid
  where
      c.CustomerId > 1000;

begin  
  open c;
  loop
  fetch c bulk collect into vars limit 1000;

  -- here is where instead of looping through each item in vars
  -- i actually want to 'join' to the 1000 that i have.  
  forall i in 1..vars.count
  insert into xxx (CustomerId) 
  values (vars(i));
  commit;

  exit when vars.count = 0;
  end loop;
  close c;

end;

1. Select a list of CustomerIds into a "temporary" storage container - not sure what the options are?
2. Process those CustomerIds in batches of say... 1000 by joining them to another query
3. Insert all results into a physical table

so, in T-SQL might be..

-- create a temp table
create table #MyTempTable (
    id int identity(1,1)
    ,customerid varchar(10)
)

-- populate that table
insert into #MyTempTable
select Customerid 
from schema.Customers

-- create some vars for looping
declare i int, c int;
select i = 0;
select c = count(*) from #MyTempTable;

-- loop through the original set in 'chunks' of 1000​
while i < c
begin
    insert into SomeOtherTable
        (CustomerId, CustomerAttribute)
    select
        o.CustomerId
        ,o.CustomerAttribute
    from OtherTable o
    join #MyTempTable t
    on o.CustomerId = t.CustomerId
    where
        t.Id between i and i+1000    -- from 0 to 1000

    set @i = i+1000    ​-- next loop will be from 1000 to 2000
end

Thanks

解决方案

Then, what about this

select startid = min(id) from customer;
select maxid = max(id) from customer;

i = startid
while i <= maxid

begin
   with myCTE as (select ... from customer where id >= i and id < i + 1000)
   insert into xxx (....)
   select ....
   from myCustomerChunk
        join productcustomers pc on ....
        join product p on ....

   i = i+1000
end

That avoids doing all 700000 inserts in one statement which is what was probably grinding down your query... and the cursor made things just worse. PS: this is a hodge-podge of pseudo and actual code, so you will have to work out the real syntax.

这篇关于Oracle循环连接语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！