第 3章.txt

oracle操作基础基础基础基础基础基础基础基础基础基础基础基础

例3.1 
connect system/system@oradb as sysdba
CREATE TABLESPACE my_index 
      DATAFILE '%ORACLE_HOME%\database\my_index.dbf' SIZE 1M reuse;
connect scott/tiger@oradb
CREATE INDEX emp_ename_index 
      ON emp(ename DESC)
      TABLESPACE my_index;

例3.2
CREATE UNIQUE INDEX dept_unique_index 
ON dept (dname) TABLESPACE my_index;

例3.3 
DROP INDEX dept_unique_index;
INSERT INTO dept VALUES(80,'RESEARCH','HarBin');
commit;
CREATE UNIQUE INDEX unique_dname ON
   dept(dname DESC);

例3.4
CREATE BITMAP INDEX stu_sex_index 
   ON student(sex)
   TABLESPACE my_index;

例3.5  
CREATE INDEX year_index 
  ON student (substrb(sno,1,4) )
  TABLESPACE my_index;
SELECT * FROM USER_SYS_PRIVS WHERE USERNAME='SCOTT';
connect system/system@oradb as sysdba
GRANT QUERY REWRITE TO SCOTT;
CONN scott/tiger@oradb
SELECT * FROM USER_SYS_PRIVS WHERE USERNAME='SCOTT';
CREATE INDEX year_index 
  ON student(substrb(sno,1,4))
  TABLESPACE my_index;

例3.6 
DROP TABLE student CASCADE CONSTRAINTS;
CREATE TABLE student(
  sno   VARCHAR2(10) Primary Key,
  name  VARCHAR2(20) Not NULL UNIQUE
                     USING INDEX TABLESPACE my_index,
  sex    VARCHAR2(2)  Check (sex IN('男','女')),
  age    NUMBER(2)    Check (age BETWEEN 16 And 20),
  class   VARCHAR2(7)  Not NULL);

例3.7
DROP INDEX dept_hash_index;
CREATE INDEX dept_hash_index
    ON dept_hash(deptno)   
    TABLESPACE my_index LOCAL (
    PARTITION P1 ,
    PARTITION P2 ,
    PARTITION P3 ,
    PARTITION P4 );

例3.8  
DROP INDEX dept_hash_index;
CREATE INDEX dept_hash_index
    ON dept_hash(deptno)   
    TABLESPACE my_index LOCAL ;

例3.9 
DROP INDEX dept_hash_index;
CREATE INDEX dept_hash_index
    ON dept_hash(deptno)   
    TABLESPACE my_index LOCAL (
    PARTITION ppa ,
    PARTITION ppb ,
    PARTITION ppx ,
    PARTITION ppy );

例3.10 
DROP INDEX dept_hash_index;
CREATE INDEX dept_hash_index
    ON dept_hash(deptno)   
    TABLESPACE my_index LOCAL (
    PARTITION P1 ,
    PARTITION P2 ,
    PARTITION P3 ,
    PARTITION P4 ,
    PARTITION P5 ,
    PARTITION P6 );

例3.11 
DROP INDEX salgrade_fg;
CREATE INDEX salgrade_fg 
    ON salgrade  (losal)  GLOBAL 
    PARTITION BY RANGE (losal)
    ( PARTITION P1 VALUES LESS THAN (1000)  TABLESPACE my_index ,
      PARTITION P2 VALUES LESS THAN (2000)  TABLESPACE my_index ,
      PARTITION P3 VALUES LESS THAN (3000)  TABLESPACE my_index ,
      PARTITION P4 VALUES LESS THAN (MAXVALUE)  TABLESPACE my_index );

例3.12 
ALTER INDEX year_index COALESCE;
ALTER INDEX year_index REBUILD ONLINE;

例3.13 
DROP VIEW v_dept;
CREATE OR REPLACE VIEW v_dept 
AS SELECT * FROM dept WITH READ ONLY;
INSERT INTO v_dept(deptno,dname,loc) VALUES(90,'SALES_10','ChengDu');

例3.14 
CREATE OR REPLACE VIEW v_emp_d20
  AS SELECT ename, sal*12 annual_sal 
     FROM emp WHERE deptno=20;
SELECT * FROM v_emp_d20;

例3.15 
SELECT a.ename, a.sal, a.deptno, b.maxsal 
FROM emp a, (SELECT deptno, MAX(sal) maxsal
             FROM emp 
             GROUP BY deptno ) b
WHERE a.deptno = b.deptno AND a.sal < b.maxsal ;
SELECT deptno, ename, sal
    FROM emp 
    ORDER BY deptno,sal desc;