这篇文章阐述了如何管理oracle ERP的interface表
这篇文章阐述了如何管理oracle ERP的interface表
这篇文章阐述了如何管理oracle ERP的interface表
http://blog.oraclecontractors.com/?p=212
There are a number of tables used by Oracle Applications that should have no rows in them when all is running well, and if any, only a few rows that are in error. Open Interface tables are an example of such tables; they are populated with data to be interfaced in to the system, and an interface specific concurrent program is then used to validate the data and import it in to the main system tables. Once the row has been imported it is removed from the interface table, leaving only invalid rows behind.
Assuming that the system is correctly configured and these interfaces are working correctly, the table is left empty.
Often, when these tables are accessed it is to process all the rows in the table, which involves a full table scan. However over time, depending on data volumes, these tables can have a large number of blocks allocated to them that no longer contain data.
A table segment is divided in to used blocks (i.e. blocks that at some time contained data) and free blocks (i.e. blocks that have never contained data) and the point of separation between the 2 groups is called the high water mark. Whenever Oracle performs a full table scan, it scans all the used blocks up to the high watermark.
I have found that when I am working with interface tables, they often seem to take a long time to return data; this is down to there being a huge number of used blocks that no longer contain data. Certainly if open interface tables are used during the go live for data migration, then the high watermark will be very high.
Examples of such tables are:
MTL_TRANSACTIONS_INTERFACE
OE_HEADERS_IFACE_ALL
RA_INTERFACE_LINES_ALL
SQL> set timing on
SQL> select count(*)
2 from MTL_TRANSACTIONS_INTERFACE
3 /
COUNT(*)
———
20
real: 2750
Nearly 3 seconds to count 20 rows!
As these tables are interface tables used b
相关文档：
需要大量oracle测试数据时，可以使用以下方法。
DECLARE
i INT;
BEGIN
i := 0;
WHILE(i < 100000)
LOOP
i := i + 1;
INSERT INTO TEST_TABLE(ID, XM) VALUES(i, '姓名' || i);
END LOOP;
COMMIT;
END; ......
/*============创建Customer表==========*/
create table Customer
(
Customer_id number(6) not null,
Customer_name varchar2(50) not null,
Password varcha ......
DECODE函数是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能。DECODE有什么用途 呢？ 先构造一个例子，假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，通常的做法是，先选出记录 中的工资字段值? select ......
一、语法
大致写法：select * from some_table [where 条件1] connect by [条件2] start with [条件3];
其中 connect by 与 start with 语句摆放的先后顺序不影响查询的结果，[where 条件1]可以不需要。
[where 条件1]、[条件2]、[条件3]各自作用的范围都不相同：
[where 条件1]是在根据“connect by [条件2] ......
1.
概念不同：
连接是指物理的客
户端到oracle服务端的连接。一般是通过一个网络的连接。
在已建立的连接
上，建立客户端与oracle
的会话，以后客
户端与oracle
的交互都在一个会话环境中
进行。
2.
关系是多对多：[同意网友的意见，应该是1对
多。一个会话要么 ......
