填充临时表的有效方法 [英] Efficient approach to populate the temp table
问题描述
Default_accounts
========================
p_type varchar2
t_type varchar2
A varchar2
B varchar2
C varchar2
D varchar2
=========================
p_type和t_type是表的主键
p_type t_type A B C D
============================
apple sell Y N N Y
banana buy N N N Y
orange sell Y N N N
mango buy Y Y N Y
papaya buy Y N N Y
apple buy Y N N Y
banana sell Y Y Y Y
============================
Accounts_Exceptions
=============================
acc_excep_id number --sequence (Primary key)
p_type varchar2
t_type varchar2
excep_attribute varchar2 --contains column of default_accounts like A,B,C,D
priority number
excep_base_sql varchar2
excep_value varchar2
===============================
Unique constraint:p_type，t_type，excep_attribute和优先级
foreign_key:Default_accounts
Unique constraint :p_type,t_type,excep_attribute and priority
foreign_key :p_type and t_type from Default_accounts
acc_excep_id p_type t_type excep_attribute priority excep_base_sql excep_value
---------------------------------------------------------------------------------------------
1 apple buy A 1 --some-- XYZ
2 apple buy A 2 --some-- PQR
3 banana sell B 1 --some-- GHT
4 banana sell B 2 --some-- GFF
5 orange sell C 1 --some-- DSA
---------------------------------------------------------------------------------------------
excep_base_sql:它包含类似于select 1 from alloc where alloc_id =:alloc;的sql查询-其示例查询，但始终包含bind vairable:alloc
excep_base_sql: it contain a sql query like select 1 from alloc where alloc_id =:alloc; --its a sample query but it always contain bind vairable :alloc
-
现在我的要求是我得到一个包含
alloc_id ,p_type,t_type
我将记录放入具有以下结构的临时表中:
I put the records in a temporary table having structure as below:
alloc_id p_type t_type A B C D
---------------------------------------------
11 apple buy
22 apple sell
33 mango buy
12 mango buy
13 mango buy
24 banana buy
54 orange sell
3.但是，正如您所看到的，我需要批量填充A，B，C和D值，临时表可以包含millions of record左右.
3.But as you see ,i need to populate A,B,C and D value in bulk ,temp table can contain around millions of record.
4.要填充值，我需要首先检查account_exceptions表是否存在任何异常.
例如:我从临时表中获取11 apple buy并查看异常表，在异常表中找到具有p_type作为apple和t的记录
t_type作为buy顺序，按优先级升序排列.
4.For populating the value i need to check the account_exceptions table first for any exception .
For example: i fetched 11 apple buy from the temp table and see the exception table ,In exception table find records having p_type as apple and t
t_type as buy order by priority in ascending order .
5.因此，获取具有acc_excep_id的记录:1,2，我获取第一条记录并使用alloc_id 11运行excep_base_sql，如果返回行，则将更新
在临时表中将excep_attribute A与XYZ一起使用，然后跳过优先级为2的第二条记录，因为优先级为1的记录满足.
5.So, records with acc_excep_id :1,2 are fetched , i fetch first record and run the excep_base_sql with alloc_id 11 ,if row is returned then i ll update
the excep_attribute A with XYZ in temp table and then skip the second record having priority 2 ,because the record with priority one satisfy .
6.如果execptions表中没有apple buy条目，那么我需要用default_accounts表填充temp表，因此该值为Y.
6.If no entry of apple buy is present in execptions table ,then i need to populate the temp table with default_accounts table ,so this value is Y.
7.因此，如果没有异常令人满意，则从default_accounts中选择默认值.
7.So ,if no exception satisy then default value is picked from default_accounts.
8.简而言之，我需要在表temp中填充A B C D的值.
8.In short i need to populate the value of A B C D in table temp.
9.我需要一种有效地将数据填充到值A B C D的临时表中的方法.
9.I need a way to efficiently populate the data into temp table for value A B C D .
有人可以用这种方法帮助我
Can anyone help me with this approach
推荐答案
我将推荐两件事.
1)进行批量插入时，插入为select或创建表为select总是最有效的.
1) An insert as select or create table as select is always the most efficient when doing a bulk insert.
2)听起来就像您尝试将行转换为列一样.我会推荐Oracle的数据透视功能.此处描述了枢轴功能: Oracle Base Pivot
2) It "sounds" like you are trying to convert rows into columns. I would recommend Oracle's pivot feature. The pivot feature is described here: Oracle Base Pivot
这篇关于填充临时表的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
