模式对象(schema)

模式：用户及拥有的对象
模式对象：有拥有者的对象，如表、视图就是典型的模式对象
非模式对象：与用户无关的对象（没有属主），在某些情况下是sys拥有的对象
      
模式对象命名：
    除数据库链接可以为128个字符外，模式对象名最多为30个字符
    保留字不能作对象名
    名称必须以字母开头
    能够包含的字符有：字母、数字、下划线、$、#
    小写会自动转换为大写

    如果对象名，加 "" 括起来，除了长度不能超限以外，其他都可例外了

下列对象共享相同的命名空间
    表
    视图
    序列
    私有同义词
    过程
    函数
    包
    物化视图

下列对象有自己的命名空间
    索引
    约束
    触发器
    私有数据库链接
    角色
    公共同义词
    表空间

表

表的规范化设计
    第一范式
    列不可再分

    第二范式
    主键依赖

    列必须完全依赖于主键

    如表中有以下字段：
    学号   姓名  科目  成绩     教室
   
    如果学号作主键的话，教室就不依赖于学号了，而依赖于科目，解决办法就是折分
    折为两个表：

    学号   姓名  科目  成绩

    科目   教室
   
    第三范式
    非主键字段间不要存在依赖，如
   
    学号  姓名   语文   数学  英语   总分

    这样，语文 、数学、英语和总分就存在依赖，要把总分字段去除


列的选择问题：
    列的选择没有强制性要求的，但从性能角度出发，通常会作以下的考虑
    1、主键列放前，非主键列放后
    2、非空列放前，可以为空的列放后
    3、较短的列放前，较长列放后
   

表创建
     create table 表名
    (列名  数据类型 默认值 约束条件 [,...])
    tablespace 表空间名

    例：
    SQL> create table test
         (
        id number(5),
        name varchar2(20)
         )
         tablespace users;


    SQL> create table test2
         (
        id number(5) not null,
        name varchar2(20),
        dept number(5) default 10
         );


    数据类型：
        char(size)     定长字符型
        varchar2(size) 变长字符型 
        number(p,s)    数值型
        binary float   单精度浮点型
        binary double  双精度浮点型
        data        日期型
 
    
       
子查询建表
    create table table_name as select ...

    将查询结果创建为表保存
    如：
    SQL> create table aa as select * from scott.emp;

    SQL> create table aa as select * from scott.emp where 1=2;


与表相关的数据字典：
    dba_objects
    dba_tables
    dba_extents
    dba_segments

    如：查一个表占多大空间
    SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 from dba_segments
      2  where SEGMENT_NAME='AA';

    如：查看一个表有多少行
    SQL> exec dbms_stats.GATHER_TABLE_STATS('SYS','AA');
 
    SQL> select OWNER,TABLE_NAME,NUM_ROWS from dba_tables where table_name='AA';

表结构修改
    添加列
    ALTER TABLE ... ADD ()        

    SQL> alter table a add (loc varchar2(50));
   
    修改列
    ALTER TABLE ... MODIFY ()

    SQL> alter table a modify (name varchar2(50));


    删除列
    ALTER TABLE ... DROP COLUMN  

    或

    ALTER TABLE ... DROP (col1,col2.col3)


    删除列时要注意，删除过程中会锁表，不能再对它进DDL和DML，只能查询

    将列设置为无用状态
    ALTER TABLE ... SET UNUSED COLUMN

    ALTER TABLE ... SET UNUSED (col1,col2)

    将无用列清除掉
    ALTERT TABLE ... DROP UNUSED COLUMNS

 
对象改名：
    列改名
    ALTER TABLE ... RENAME COLUMN ... TO ...

    SQL> alter table a rename column ename to name;

    表改名
    ALTER TABLE ... RENAME TO ...
   
    SQL> ALTER TABLE a RENAME TO b;


删除表
    DROP TABLE ...

   
视图
    视图其实就是一个保存了的查询语句给他起了一个名--视图名


创建视图：
    CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
    AS SELECT ....
    [WITH CHECK OPTION]
    [WITH READ ONLY]


    OR REPLACE     修改视图
    FORCE        基表不存在时，也创建视图
    WITH CHECK OPTION  通过视图修改数据时，修改的数据必须符合视图的创建条件
    WITH READ ONLY  只读


    SQL> create or replace view v1 as select * from scott.emp where deptno=10;


与视图相关的数据字典：
    dba_objects
    dba_views

    SQL> select view_name,text from dba_views where view_name='V1';

视图使用DML的限制：
    视图中含有以下元素之一不能delete
    组函数
    group by
    distinct
    rownum

    视图中含有以下元素之一不能 update
    组函数
    group by
    distinct
    rownum
    列的定义是表达式

    视图中含有以下元素之一不能 insert
    组函数
    group by
    distinct
    rownum
    列的定义是表达式
    表中的非空字段没有出现在视图中
   

删除视图：
    drop view ...


序列
    提供自动增长的唯一的数值，常用于订单号之类的

创建序列:
     CREATE SEQUENCE sequence_name
    [INCREMENT BY n]                  增长步长（默认为1）
    [START WITH n]            起始值（默认为1）
    [MAXVALUE n | NOMAXVALUE]    最大值（默认为NOMAXVALUE）
                    NOMAXVALUE 升序时为 10的27次方，降序为 -1   
    [MINVALUE n | NOMINVALUE]       最大值（默认为NOMINVALUE）
                    NOMINVALUE 升序时为1 降序为 -10的26次方
    [CYCLE | NO CYCLE]              是否循环使用
    [CACHE n | NOCACHE]             是否缓存


    SQL> CREATE SEQUENCE s1
      2  INCREMENT BY 1
      3  START WITH 10
      4  MAXVALUE 20
      5  CYCLE
      6  NOCACHE;

使用序列：
    使用序列主要是使用序列的两个伪列：NEXTVAL  CURRVAL


修改序列：
   
     CREATE SEQUENCE sequence_name
    [INCREMENT BY n]                  增长步长（默认为1）   
    [MAXVALUE n | NOMAXVALUE]    最大值（默认为NOMAXVALUE）
                    NOMAXVALUE 升序时为 10的27次方，降序为 -1   
    [MINVALUE n | NOMINVALUE]       最大值（默认为NOMINVALUE）
                    NOMINVALUE 升序时为1 降序为 -10的26次方
    [CYCLE | NO CYCLE]              是否循环使用
    [CACHE n | NOCACHE]             是否缓存   


 
    SQL> alter sequence s1
      2  nocycle;

使用序列时遇到的问题：
    有时会遇到序列不连续的现象
    主要原因有：
    1、回滚
    2、系统异常
    3、多个表同时使用同一序列
    4、使用了cache时，清空了共享池

   
删除序列：
    DROP SEQUENCE squence_name

    SQL> drop sequence s3;


同义词：
    同义词是一个对象别名


创建同义词：
    CREATE [PUBLIC] SYNONYM synonym_name FOR object

    例：
    SQL> CREATE PUBLIC SYNONYM a for scott.emp;

查看同义词：
    dba_synonyms

删除同义词：
    DROP [PUBLIC] SYNONYM synonym_name


索引
    索引是建立在表上一列或多列上的辅助对象
    建索引的目的主要是加快数据的查询速度

索引类型：
    B树索引         适合重复值比较低
    位图索引        适合复复值比较高的情况
    基于函数的索引    
    反向键索引（反转索引）
        能解决两个问题：索引倾斜、热块

创建索引
    索引很多情况会自动创建的
    例：有主键约束、唯一约束会自动创建索引

    手动创建B树索引：
    CREATE INDEX index_name ON table_name(colunm_name)


    手动创建位图索引:
    CREATE BITMAP INDEX index_name ON table_name(column_name)
 
    创建基于函数的索引：
    CREATE INDEX index_name ON table_name(function(colunm))

    SQL> CREATE INDEX t1_ename_bf ON T1(UPPER(ename));

    创建反向键索引    
    SQL> CREATE INDEX t1_empno_rever ON t1(mgr) REVERSE;


    创建唯一性索引
    CREATE UNIQUE INDEX index_name ON table_name (column)


查看是否用到索引
    1、查看执行计划
    SQL> set autotrace on;
    SQL> select * from t2 where empno=7369;

    2、索引监控
    SQL> alter index t2_empno_i1 monitoring usage;
   
    SQL> select * from v$object_usage;
   
   SQL> alter index t2_empno_i1 nomonitoring usage;  --关闭监控


使用提示让oracle尽可能的使用索引
    SQL> select  /*+index (t2 t2_ename_idx) */ *
             from scott.t2 t2 where ename!='SCOTT';

    SQL> select /*+ full(t2) */ * from scott.t2 t2 where empno>1000 and empno<7500;


索引重建
     如果表的物理状态发生变化时，索引会变得无效，这里需要重建索引
    ALTER INDEX ... REBUILD

    SQL> alter index T2_ENAME_IDX rebuild;


索引删除:
    DROP INDEX index_name


索引使用上的一些事项：
    1、辟免在索引字段使用is null，因为在B树索引中不会存放空值

    2、对索引列使用函数，这样会忽略掉索引，除非创建基于函数的索引
    
     3、为了优化器能更准确的评估执行计划，要适时对表进行分析，以便获得更准确的统计信息

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21175589/viewspace-755636/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21175589/viewspace-755636/