简单动机:出于对运行速度的无法忍受,我将嵌套sql语句中关于group by的内容放入临时表,作为中间变量。
简单做法:第一次使用临时表,查看有事务级别和会话级别2种,我选会话级别的即可。
简单理解:一次DB连接就是一次会话,如果有N个用户连接DB,大家都可以来用这个临时表,即为“全局”;但是需要隔离各自的内容,互不干扰,所以是会话级别的;所谓“临时”,即会话结束后,系统帮助清空,很方便。表内内容应该是存在内存里面的。
CREATE OR REPLACE PROCEDURE P_WY_NE_ZTE_LAC_CI AS
BEGIN
/*ZTE 后台以 BSC,BTS,CELL 唯一标识小区,而LAC,CI是要配置数据关联过来的*/
--ON COMMIT DELETE ROWS 说明临时表是事务指定,每次提交后ORACLE将截断表(删除全部行)
--ON COMMIT PRESERVE ROWS 说明临时表是会话指定,当中断会话时ORACLE将截断表。
DELETE IBTS_BTS_NOW WHERE RECDATE = TRUNC(SYSDATE);
COMMIT;
INSERT INTO
IBTS_BTS_BSCCONFSETID_TEMP SELECT BSC, MIN(CONFSETID) CONFSETID
FROM IBTS_BTS
WHERE RECDATE = TRUNC(SYSDATE)
GROUP BY BSC;
COMMIT;
INSERT /*APPEND*/
INTO IBTS_BTS_NOW
SELECT *
FROM IBTS_BTS
WHERE RECDATE = TRUNC(SYSDATE)
AND (BSC, CONFSETID) IN (SELECT * FROM IBTS_BTS_BSCCONFSETID_TEMP);
COMMIT;
DELETE IBTS_BTS_NOW WHERE RECDATE <= TRUNC(SYSDATE) - 30;
COMMIT;
END;
-- Create table
create global temporary table
IBTS_BTS_BSCCONFSETID_TEMP(
bsc NUMBER(10) not null,
confsetid NUMBER
)
on commit preserve rows;
分享到:
相关推荐
Oracle存储过程中使用临时表 会话级临时表 事务级临时表
主要介绍了C++编程中__if_exists与__if_not_exists语句的用法,是C++中用于判断指定的标识符是否存在的基础的条件判断语句,需要的朋友可以参考下
CREATE GLOBAL TEMPORARY <TABLE> ( <column> ) ON COMMIT PRESERVE ROWS; 2。事务特有的临时表 CREATE GLOBAL TEMPORARY <TABLE> ( <column> ) ON COMMIT DELETE ROWS; CREATE GLOBAL TEMPORARY TABLE ...
sql> temporary tablespace temp quota 10m on data password expire sql> [account lock|unlock] [profile profilename|default]; <1>.查看当前用户的缺省表空间 SQL>select username,default_tablespace ...
- Fixed a couple table mapping issues during table load - Fixed a couple alignment issues for IA64 - Initialize input array to zero in AcpiInitializeTables - Additional parameter validation for ...
Table 6.1 displays a field guide to the subclasses of wx.DC and their usage. Device contexts, which are used to draw to a wxPython widget, should always be locally created, temporary objects, and ...
实验三 移植U-Boot-1.3.1 实验 【实验目的】 了解 U-Boot-1.3.1 的代码结构,掌握其移植方法。 【实验环境】 1、Ubuntu 7.0.4发行版 2、u-boot-1.3.1 3、FS2410平台 4、交叉编译器 arm-softfloat-linux-gnu-...
where c.owner = upper('&table_owner') and c.table_name = upper('&table_name') and c.owner = cc.owner and c.constraint_name = cc.constraint_name order by cc.position; 8、存储函数和过程 查看函数...
I deferred from testing the get test over 100 million record as it would require a huge array in memory to store the Guid keys for finding later, that is why there is a NT (not tested) in the table. ...
Another type of table lock is a schema stability lock (Sch-S) and is compatible with all table locks except the schema modification lock (Sch-M). The schema modification lock (Sch-M) is incompatible ...
简单对象模型(A Simple Object Model) 表格驱动对象模型(A Table-driven Object Model) C++对象模型(Th e C++ Object Model) 对象模型如何影响程序(How the Object Model Effects Programs) 1.2 关键词所...
简单对象模型(A Simple Object Model) 表格驱动对象模型(A Table-driven Object Model) C++对象模型(Th e C++ Object Model) 对象模型如何影响程序(How the Object Model Effects Programs) 1.2 关键词所...
简单对象模型(A Simple Object Model) 表格驱动对象模型(A Table-driven Object Model) C++对象模型(The C++ Object Model) 对象模型如何影响程序(How the Object Model Effects Programs) 1.2 关键词所带来...
-snapshot write to temporary files instead of disk image files -m megs set virtual RAM size to megs MB [default=128] -mem-path FILE provide backing storage for guest RAM -k language use keyboard ...
-snapshot write to temporary files instead of disk image files -m megs set virtual RAM size to megs MB [default=128] -mem-path FILE provide backing storage for guest RAM -k language use keyboard ...
TABLE OF CONTENTS ----------------- 1. How to Get Help 2. Installation 3. Important Notes 4. Corrections to the Manuals 5. Additional Notes 6. Notes for Turbo Prolog Users 7. Files on the Disks...
;;;;;;;;... 1.... 2.... 3.... 4.... 5.... 6.... The syntax of the file is extremely simple.... Section headers (e.g.... at runtime.... There is no name validation.... (e.g.... previously set variable or directive (e.g....
TABLE OF CONTENTS ----------------- 1. How to Get Help 2. Installation 3. Important Notes 4. Additional Notes 5. Notes for Turbo Prolog Users 6. Files on the Disks 1. HOW TO GET HELP --------...
The user address space is where application code, global variables, per-thread stacks, and DLL code would reside. The system address space is where the kernel, executive, HAL, boot drivers, page ...
目录.................................................................................................................................2 内容提要...........................................................