Oracle学习笔记（九）-APISpace

Oracle学习笔记（九）

全局临时表自动处理数据：

drop table ljb_tmp_session;create global temporary table ljb_tmp_session on commit preserve rows as select * from dba_objects where 1=2;select table_name,temporary,duration from user_tables where table_name='LJB_TMP_SESSION';drop table ljb_tmp_transaction;create global temporary table ljb_tmp_transaction on commit delete rows as select * from dba_objects where 1=2;select table_name, temporary, DURATION from user_tables where table_name='LJB_TMP_TRANSACTION';insert all into ljb_tmp_transaction into ljb_tmp_sessionselect * from dba_objects;select session_cnt,transaction_cnt from (select count(*) session_cnt from ljb_tmp_session), (select count(*) transaction_cnt from ljb_tmp_transaction);commit; select session_cnt,transaction_cnt from (select count(*) session_cnt from ljb_tmp_session),(select count(*) transaction_cnt from ljb_tmp_transaction);select session_cnt,transaction_cnt from (select count(*) session_cnt from ljb_tmp_session),(select count(*) transaction_cnt from ljb_tmp_transaction);

不同session数据独立：

drop table ljb_tmp_session;create global temporary table ljb_tmp_session on commit preserve rows as select * from dba_objects where 1=2;select table_name,temporary,duration from user_tables where table_name='LJB_TMP_SESSION';drop table ljb_tmp_transaction;create global temporary table ljb_tmp_transaction on commit delete rows as select * from dba_objects where 1=2;select table_name, temporary, DURATION from user_tables where table_name='LJB_TMP_TRANSACTION';--不同session的例子试验基于sesssion 的临时表即可了，不用试验另外一个了。---连上session 1 insert into ljb_tmp_session select * from dba_objects　where rownum<=10; --可以体会提交，基于session 的提交并清理数据 commit; select count(*) from ljb_tmp_session; COUNT(*)---------- 10 ---不退出session 1，继续登录session 2insert into ljb_tmp_session select * from dba_objects　where rownum<=20;commit;select count(*) from ljb_tmp_session; COUNT(*)---------- 20

产生日志较少：

全局临时表是否在同样delete ,insert 和update 的情况下，产生的redo比普通表要少的多。sqlplus "/ as sysdba"grant all on v_$mystat to ljb;grant all on v_$statname to ljb;connect ljb/ljbdrop table t purge;create table t as select * from dba_objects ;--以下创建视图，方便后续直接用select * from v_redo_size进行查询create or replace view v_redo_size as select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size'; select * from v_redo_size;--中间输入你的删除插入更新的动作select * from v_redo_size; 两次相减就是产生的redo量

索引组织表：

--必须有主键！---分别建索引组织表和普通表进行试验set autotrace offdrop table heap_addresses purge;drop table iot_addresses purge;create table heap_addresses (empno number(10), addr_type varchar2(10), street varchar2(10), city varchar2(10), state varchar2(2), zip number, primary key (empno) )/create table iot_addresses (empno number(10), addr_type varchar2(10), street varchar2(10), city varchar2(10), state varchar2(2), zip number, primary key (empno) ) organization index/insert into heap_addresses select object_id,'WORK','123street','washington','DC',20123 from all_objects;insert into iot_addresses select object_id,'WORK','123street','washington','DC',20123 from all_objects;commit;---分别比较索引组织表和普通表的查询性能set linesize 1000set autotrace traceonlyselect * from heap_addresses where empno=22;SQL> select * from heap_addresses where empno=22;执行计划----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 50 | 1 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| HEAP_ADDRESSES | 1 | 50 | 1 (0)| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | SYS_C0013751 | 1 | | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 659 bytes sent via SQL*Net to client 405 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed select * from iot_addresses where empno=22;执行计划----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 50 | 1 (0)| 00:00:01 ||* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_104441 | 1 | 50 | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 751 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

簇表：

--构造环境--注意这个删除的先后顺序，否则会报ORA-00951: 簇非空set autotrace offdrop table cust_orders;drop cluster shc;CREATE CLUSTER shc( cust_id NUMBER, order_dt timestamp SORT)HASHKEYS 10000HASH IS cust_idSIZE 8192/CREATE TABLE cust_orders( cust_id number, order_dt timestamp SORT, order_number number, username varchar2(30), ship_addr number, bill_addr number, invoice_num number)CLUSTER shc ( cust_id, order_dt )/---开始执行分析set autotrace traceonly variable x number--以下是利用有序散列聚族表的方法，发现排序被避免select cust_id, order_dt, order_number from cust_orders where cust_id = :x order by order_dt; 执行计划----------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|----------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 39 | 0 (0)||* 1 | TABLE ACCESS HASH| CUST_ORDERS | 1 | 39 | |----------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 416 bytes sent via SQL*Net to client 404 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed --以下是普通方法，排序不可避免select job, hiredate, empno from scott.emp where job = 'CLERK' order by hiredate;执行计划---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 60 | 4 (25)| 00:00:01 || 1 | SORT ORDER BY | | 3 | 60 | 4 (25)| 00:00:01 ||* 2 | TABLE ACCESS FULL| EMP | 3 | 60 | 3 (0)| 00:00:01 |---------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 640 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 4 rows processed

外键索引性能：

drop table t_p cascade constraints purge;drop table t_c cascade constraints purge;CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));ALTER TABLE T_P ADD CONSTRAINT T_P_ID_PK PRIMARY KEY (ID);CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID);INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME FROM ALL_OBJECTS;COMMIT;---外键未建索引前的表连接性能分析set autotrace traceonlyset linesize 1000SELECT A.ID, A.NAME, B.NAME FROM T_P A, T_C B WHERE A.ID = B.FID AND A.ID = 880;执行计划------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 71 | 4260 | 137 (1)| 00:00:02 || 1 | NESTED LOOPS | | 71 | 4260 | 137 (1)| 00:00:02 || 2 | TABLE ACCESS BY INDEX ROWID| T_P | 1 | 30 | 0 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 0 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL | T_C | 71 | 2130 | 137 (1)| 00:00:02 |------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 452 consistent gets 0 physical reads 0 redo size 3605 bytes sent via SQL*Net to client 459 bytes received via SQL*Net from client 6 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 72 rows processed --外键建索引后的表连接性能分析CREATE INDEX IND_T_C_FID ON T_C (FID);SELECT A.ID, A.NAME, B.NAME FROM T_P A, T_C B WHERE A.ID = B.FID AND A.ID = 880;执行计划--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 72 | 4320 | 69 (0)| 00:00:01 || 1 | NESTED LOOPS | | 72 | 4320 | 69 (0)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID| T_P | 1 | 30 | 0 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 0 (0)| 00:00:01 || 4 | TABLE ACCESS BY INDEX ROWID| T_C | 72 | 2160 | 69 (0)| 00:00:01 ||* 5 | INDEX RANGE SCAN | IND_T_C_FID | 72 | | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 83 consistent gets 0 physical reads 0 redo size 3605 bytes sent via SQL*Net to client 459 bytes received via SQL*Net from client 6 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 72 rows processed

外键与锁：

--外键索引性能研究之准备drop table t_p cascade constraints purge;drop table t_c cascade constraints purge;CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));ALTER TABLE T_P ADD CONSTRAINT T_P_ID_PK PRIMARY KEY (ID);CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID);INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME FROM ALL_OBJECTS;COMMIT;create index idx_IND_T_C_FID on T_C(FID);外键有索引，没有死锁情况产生--首先开启会话1select sid from v$mystat where rownum=1;DELETE T_C WHERE FID = 2;--接下来开启会话2，也就是开启一个新的连接select sid from v$mystat where rownum=1;DELETE T_P WHERE ID = 2000;外键索引先删除drop index idx_IND_T_C_FID;外键索引删除后，立即有锁相关问题--首先开启会话1select sid from v$mystat where rownum=1;DELETE T_C WHERE ID = 2;--接下来开启会话2，也就是开启一个新的连接select sid from v$mystat where rownum=1; --然后执行如下进行观察DELETE T_P WHERE ID = 2000;--居然发现卡住半天不动了！

普通索引改主键：

drop table t_p cascade constraints purge;CREATE TABLE t_p(order_id NUMBER(3), item_id NUMBER(2), comments varchar2(400));CREATE INDEX ord_itm_idx ON t_p(order_id,item_id);alter table t_p add constraint ord_itm_id_pk primary key (order_id,item_id) using index ord_itm_idx ;---不需要如此操作(差别只是索引名不一样而已）drop index ord_itm_idx;alter table t_p add constraint ord_itm_id_pk primary key (order_id,item_id);

主键DISABLE再ENABLE后，外键需要重新生效

drop table t_p cascade constraints purge; create table t_p (dept_id number,emp_name varchar2(20),emp_id number);alter table t_p add constraints pk_dept_id PRIMARY KEY (dept_id);ALTER TABLE t_p ADD CONSTRAINT FK_T_F FOREIGN KEY (emp_id) REFERENCES T_P (dept_id) ;select CONSTRAINT_TYPE,TABLE_NAME,STATUS from user_constraints t where t.constraint_name in ('PK_DEPT_ID','FK_T_F');ALTER TABLE t_p DISABLE CONSTRAINT pk_dept_id CASCADE;select CONSTRAINT_TYPE,TABLE_NAME,STATUS from user_constraints t where t.constraint_name in ('PK_DEPT_ID','FK_T_F');ALTER TABLE t_p ENABLE CONSTRAINT pk_dept_id ;select CONSTRAINT_TYPE,TABLE_NAME,STATUS from user_constraints t where t.constraint_name in ('PK_DEPT_ID','FK_T_F');alter table t_p enable constraint FK_T_F;select CONSTRAINT_TYPE,TABLE_NAME,STATUS from user_constraints t where t.constraint_name in ('PK_DEPT_ID','FK_T_F');

主键约束有延迟约束到提交才验证的功能

drop table test cascade constraints;create table test as select rownum object_id,object_name from dba_objects where rownum<=20;ALTER TABLE test ADD CONSTRAINT pk_object_id PRIMARY KEY(object_id) DEFERRABLE INITIALLY DEFERRED;INSERT INTO test VALUES (21,'ABC'); INSERT INTO test VALUES (21,'EFG');COMMIT;SET CONSTRAINT pk_object_id IMMEDIATE;INSERT INTO test VALUES (1,'LATA');INSERT INTO test VALUES (2,'KING');COMMIT;

主外键设计其他约束条件：

表压缩：

---压缩表可减少数据量，从而减少IODROP TABLE t purge;CREATE TABLE t NOCOMPRESS ASSELECT rownum AS n, rpad(' ',500,mod(rownum,15)) AS padFROM dualCONNECT BY level <= 200000;execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t');--未压缩的表当前情况SELECT table_name, blocks,compression FROM user_tables WHERE table_name = 'T';TABLE_NAME BLOCKS COMPRESS------------------------------ ---------- --------T 14449 DISABLEDset autotrace traceonlyselect count(*) from t;执行计划-------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 3922 (1)| 00:00:48 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| T | 200K| 3922 (1)| 00:00:48 |-------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 14297 consistent gets 0 physical reads 0 redo size 423 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed --开始压缩表set autotrace offALTER TABLE t MOVE COMPRESS;execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t');SELECT table_name, blocks,compression FROM user_tables WHERE table_name = 'T';TABLE_NAME BLOCKS COMPRESS------------------------------ ---------- --------T 2639 ENABLEDset autotrace traceonly select count(*) from t;执行计划-------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 718 (1)| 00:00:09 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| T | 200K| 718 (1)| 00:00:09 |-------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 2595 consistent gets 0 physical reads 0 redo size 423 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 注意：压缩后的表更新的开销会更大，查询耗费的CPU也更多。所以压缩表一般适合在更新比较少，且CPU消耗不大，IO消耗很大系统中试用。

索引压缩：

---压缩索引(联合索引的压缩层度会高一些）DROP TABLE t1 purge;CREATE TABLE t1 AS select * from dba_objects;alter table T1 modify owner not null;alter table T1 modify object_name not null;alter table T1 modify object_type not null;insert into t1 select * from t1;insert into t1 select * from t1;commit;create index idx1_object_union on t1(owner,object_type,object_name);execute dbms_stats.gather_index_stats(ownname=>user, indname=>'idx1_object_union');--未压缩索引的当前情况SELECT t.index_name,t.compression,t.leaf_blocks,t.blevel FROM user_indexes t WHERE index_name = 'IDX1_OBJECT_UNION';INDEX_NAME COMPRESS LEAF_BLOCKS BLEVEL------------------------------ -------- ----------- ----------IDX1_OBJECT_UNION DISABLED 2043 2--开始压缩索引drop table t2 purge;create table t2 as select * from t1;alter table T2 modify owner not null;alter table T2 modify object_name not null;alter table T2 modify object_type not null;create index idx2_object_union on t2(owner,object_type,object_name);ALTER index idx2_object_union rebuild COMPRESS;execute dbms_stats.gather_index_stats(ownname=>user, indname=>'idx2_object_union');SELECT t.index_name,t.compression,t.leaf_blocks,t.blevel FROM user_indexes t WHERE index_name = 'IDX2_OBJECT_UNION';INDEX_NAME COMPRESS LEAF_BLOCKS BLEVEL------------------------------ -------- ----------- ----------IDX2_OBJECT_UNION ENABLED 907 2set linesize 1000set autotrace traceonlyselect count(*) from t1 ;执行计划-----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 572 (1)| 00:00:07 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FAST FULL SCAN| IDX1_OBJECT_UNION | 251K| 572 (1)| 00:00:07 |-----------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 2067 consistent gets 0 physical reads 0 redo size 425 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed select count(*) from t2 ;执行计划-----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 258 (1)| 00:00:04 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FAST FULL SCAN| IDX2_OBJECT_UNION | 282K| 258 (1)| 00:00:04 |-----------------------------------------------------------------------------------统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 922 consistent gets 0 physical reads 0 redo size 425 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

列值如何存放：

--什么类型就放什么值，否则会发生类型转换，导致系能问题！--（是存放字符的字段就设varchar2类型,是存放数值的字段就设置number类型,是存放日期的字段就设置date类型）--举例说明：drop table t_col_type purge;create table t_col_type(id varchar2(20),col2 varchar2(20),col3 varchar2(20));insert into t_col_type select rownum,'abc','efg' from dual connect by level<=10000;commit;create index idx_id on t_col_type(id);set linesize 1000set autotrace traceonlyselect * from t_col_type where id=6;执行计划--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 36 | 9 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T_COL_TYPE | 1 | 36 | 9 (0)| 00:00:01 |-------------------------------------------------------------------------------- 1 - filter(TO_NUMBER("ID")=6)统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 32 consistent gets 0 physical reads 0 redo size 540 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed--实际上只有如下写法才可以用到索引，这个很不应该，是什么类型的取值就设置什么样的字段。 select * from t_col_type where id='6';执行计划------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T_COL_TYPE | 1 | 36 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_ID | 1 | | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------ 2 - access("ID"='6')统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 544 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

过时的字段：

CHAR和LONG基本要被VARCHAR2和CLOB等替换，属于已经过时的字段类型。其中CHAR不利扩展，且有可能浪费空间。而LONG的更新查询操作极为麻烦！

c语言sscanf函数的用法是什么

255 2022-09-04

Oracle学习笔记（九）

c语言sscanf函数的用法是什么

c语言一维数组怎么快速排列

php怎么获取input输入的值

推荐文章

api接口有哪几种分类及功能

什么是API接口?API接口简单介绍

短信API接口概述，短信API接口的优势

7款快递物流的物流查询API工具，物流快递查询API接口怎么对接？

企业四要素: 了解企业经营成功的关键

什么是语音验证码?,语音验证码平台有哪些

全国工商查询系统怎么查企业名录

哪些平台提供实名认证的接口？

PHP如何调用API接口?

如何使用百度天气预报API接口?

最近发表

热评文章

数据接口api（数据接口API开发平台）

数据开放接口api（数据服务api开发）

Python爬虫教程：爬取酷狗音乐（python爬取

hbuilder怎么更改字体大小和颜色

直播平台api接口 - 构建卓越的直播平台

实时股票数据api接口（股票实时行情api接口）