Oracle-SQL讲座.xls

概要 1 8 Oracle SQLOracle SQL使使用用及及性性能能优优化化 作成者 赵金平 一 前言 在业务中能够使用好Oracle和SQL文应当注意以下几个问题 1 熟练地了解业务 尤其是实现目的 2 了解表的结构 尤其使主键 index等内容 3 了解需要处理数据的特点 4 反复实践和尝试 5 熟悉一些经常使用的工具 如SQL PLUS SQL EXPERT KFPROF等 6 一定的理论指导 二 Oracle及SQL的体系结构 SQL 结构查询语言 是一种描述性的语言 因此 怎样从 理 上实现功能 就成为解决问题重要方面 1 Oracle体系结构 2 常用的Oracle命令 三 SQL文的书写规则及运行经验 1 书写规则 1 良好的可读性 2 在多表使用的情况下 使用别名 3 使用有意义的对象名称 2 用正确 简单的方法运行SQL文 尤其注意常量的使用 常量用HOST变量替换 四 SQL实现的功能和技巧 1 INSERT语句 2 UPDATE语句 3 Group By的使用 4 Union的使用 5 Minus的使用 6 同一表自连接的使用 7 Decode的使用 8 外结合及NVL的使用 9 ROWNUM的使用 10 ROWID的使用 五 Oracle中SQL文的性能优化问题 1 Oracle性能调整优化的层次 1 环境调整 2 数据库服务器调整 3 网络调整 4 磁盘调整 5 实例调整 6 对象调整 7 SQL调整 2 SQL调整的过程 1 定位高频使用的SQL语句 2 调整SQL语句 3 添加索引 概要 2 8 4 更改优化器模式 5 添加提示 6 将调整持久化 3 SQL调整的手段 1 对高频使用的表和索引重点处理 2 对大表避免全表扫描和读取 3 检验优化索引的使用 4 缓存小表的全表扫描 5 检验优化的连接技术 6 检查子查询 4 SQL文的执行计划及使用的工具 1 SQL EXPERT 2 TKPROF 5 Oracle优化 OracleDB体系结构 3 8 OracleDB基本構造体 OracleDB 段 Segment 38KB 表空间物理文件 区间 init 6KB 区间 next 4KB 表 数据段索引段2KB 2KB 2KB2KB 2KB 数据区间索引区间物理块 数据块数据块 关于索引 对象表 索引字段 记录号 物理地址ROWID 000001 1 索引字段 记录号 物理地址ROWID 000010 2 索引文件0000011 6 000002 3 0000023 5 7 9 000006 4 0000064 000002 5 0000102 8 000001 6 000002 7 000010 8 000002 9 n 1 1 n 1 n 1 n 1 n 1 n 1 n 1 n TBLCreate文说明 4 8 CREATE TABLE WSIT9081 MSG SUB CD CHAR 05 NOT NULL MSG TEXT 1 VARCHAR2 134 MSG TEXT 2 VARCHAR2 134 PCTFREE 10 PCTUSED 40 STORAGE INITIAL 50K NEXT 8K MAXEXTENTS UNLIMITED PCTINCREASE 0 TABLESPACE TBS USER1 説明 INITIAL NEXT PCTINCREASE MINEXTENTS MAXEXTENTS TABLESPACE PCTFREE PCTUSED PARALLEL SQL実行経験 5 8 用简单的方法运行SQL文 変数 宣言 VARIABLE h TM1 char 8 VARIABLE h T0 char 8 VARIABLE h T1 char 8 VARIABLE h T2 char 8 VARIABLE h T3 char 8 VARIABLE h T4 char 8 VARIABLE h T5 char 8 VARIABLE h T6 char 8 VARIABLE h T7 char 8 VARIABLE h T8 char 8 VARIABLE h T9 char 8 VARIABLE h T10 char 8 基準日管理TBL T 1 T 10 取得 EXECUTE select T M1 T 0 T 1 T 2 T 3 T 4 T 5 T 6 T 7 T 8 T 9 T 10 into h TM1 h T0 h T1 h T2 h T3 h T4 h T5 h T6 h T7 h T8 h T9 h T10 from SN KJNBI KNR B where KBN 01 select h TM1 from dual select h T0 from dual select h T1 from dual select h T2 from dual select h T3 from dual select h T4 from dual select h T5 from dual select h T6 from dual SQL実行経験 6 8 select h T7 from dual select h T8 from dual select h T9 from dual select h T10 from dual truncate table WSIT5012 set timing on insert into SN SINY KINSEN YRK SUM B USER CD KOZA NO TUKA CD UKWBI BTN CD KJNBI SAK TIMESTNP UP TIMESTNP UP PROG ID TRNSCT ID UP TEAM ADDRESS UP SHAIN CD select VW2 USER CD VW2 KOZA NO JPY VW1 RDATE nvl VW2 BTN CD h T0 to number to char sysdate YYYYMMDDHH24MISS 00 to number to char sysdate YYYYMMDDHH24MISS 00 WSI3350 WSI3350 h T0 from SN KYK KNJO ZDAK A VW2 select h TM1 RDATE from dual union select h T0 from dual union select h T1 from dual union select h T2 from dual union select h T3 from dual union select h T4 from dual union select h T5 from dual union select h T6 from dual union SQL実行経験 7 8 select h T7 from dual union select h T8 from dual union select h T9 from dual union select h T10 from dual VW1 group by VW2 USER CD VW2 KOZA NO VW1 RDATE nvl VW2 BTN CD set timing off 常用的Oracle命令 8 8 表复制 create table WSHT2143 as select 部店 扱者 口座番号 顧客名 漢字 基準日 0 from WSHT2144 create table WSHT2143 as select from WSHT2144 create table WSHT2143 as select from WSHT2144 where 基準日 20030701 数据复制 insert into WSHT2143 select from WSHT2144 表更名 alter table WSHT2143 rename to WSHT2144 错误解析 oerr ora xxxx 错误号码 查询表的主键 main COLUMN con FORMAT A30 HEADING 制約 COLUMN tblname FORMAT A30 HEADING 名 COLUMN clname FORMAT A30 HEADING 主 名 select distinct CONSTRAINT NAME con TABLE NAME tblname COLUMN NAME clname from all cons columns where table name UPPER WSHT2052 and CONSTRAINT NAME like PRKY 查询表的index 索引 COLUMN idxname FORMAT A30 HEADING 索引名 COLUMN tblname FORMAT A30 HEADING 名 COLUMN clname FORMAT A30 HEADING 名 select distinct INDEX NAME idxname TABLE NAME tblname