
P119
6.1
CONN system/systempwd@orcl AS SYSDBA
CREATE TABLESPACE my_index 
  DATAFILE '%ORACLE_HOME%\database\my_index.dbf' SIZE 1M REUSE;
CONN scott/tiger@orcl
CREATE INDEX emp_ename_index
  ON emp(ename DESC)
  TABLESPACE my_index;

COL TABLE_NAME FORMAT A15
COL INDEX_NAME FORMAT A18
COL COLUMN_NAME FORMAT A15
SELECT ix.table_name, ic.index_name, ic.column_name,
       ic.column_position col_pos, ix.uniqueness
  FROM user_indexes ix , user_ind_columns ic
  WHERE ic.index_name = ix.index_name
  ORDER BY ix.table_name;

P120
6.2
CONN scott/tiger@orcl
CREATE UNIQUE INDEX dept_unique_index
  ON dept (dname) TABLESPACE my_index;

P120
6.3
CONN scott/tiger@orcl
DROP INDEX dept_unique_index;
INSERT INTO dept VALUES(80,'RESEARCH','HarBin');
COMMIT;
CREATE UNIQUE INDEX unique_dname ON
  dept(dname DESC);

P121
6.4
CONN scott/tiger@orcl
CREATE BITMAP INDEX stu_sex_index
  ON student(sex)
  TABLESPACE my_index;

P122
6.5
CONN scott/tiger@orcl
DROP INDEX stu_year_index ;
CREATE INDEX stu_year_index
  ON student (substrb(sno,1,4) )
  TABLESPACE my_index;
SELECT * FROM user_sys_privs WHERE username='SCOTT';
CONNECT system/systempwd@orcl AS SYSDBA
GRANT QUERY REWRITE TO scott;

P122
6.6
CONN scott/tiger@orcl
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);
DROP TABLE student CASCADE CONSTRAINTS;
CREATE TABLE student(
  sno  VARCHAR2(10) PRIMARY KEY,
  name VARCHAR2(20) NOT NULL,
  sex  VARCHAR2(2) CHECK (sex IN('','Ů')),
  age  NUMBER(2) CHECK (age BETWEEN 16 AND 20),
  class VARCHAR2(7)  NOT NULL);
ALTER TABLE student MODIFY(name VARCHAR2(20)
     UNIQUE USING INDEX TABLESPACE my_index);

P124
6.7
CONN scott/tiger@orcl
DROP INDEX hash_emp_index;
CREATE INDEX hash_emp_index
  ON hash_emp(deptno)   
  TABLESPACE my_index LOCAL (
  PARTITION P1 ,
  PARTITION P2 ,
  PARTITION P3 ,
  PARTITION P4 );

P124
6.8
CONN scott/tiger@orcl
DROP INDEX hash_emp_index;
CREATE INDEX hash_emp_index
  ON hash_emp(deptno)   
  TABLESPACE my_index LOCAL ;

P124
6.9
DROP INDEX hash_emp_index;
CREATE INDEX hash_emp_index
  ON hash_emp(deptno)   
  TABLESPACE my_index LOCAL (
  PARTITION ppa ,
  PARTITION ppb ,
  PARTITION ppx ,
  PARTITION ppy );

P124
6.10
DROP INDEX hash_emp_index;
CREATE INDEX hash_emp_index
  ON hash_emp(deptno)   
  TABLESPACE my_index LOCAL (
  PARTITION P1 ,
  PARTITION P2 ,
  PARTITION P3 ,
  PARTITION P4 ,
  PARTITION P5 ,
  PARTITION P6 );

P125
6.11
CONN scott/tiger@orcl
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 );

P126
6.12
CONN scott/tiger@orcl
ALTER INDEX stu_year_index COALESCE;
ALTER INDEX stu_year_index REBUILD ONLINE;

P126
6.13 
CONN /@ORCL AS SYSDBA
COL index_name FORMAT A15
SELECT index_name,pct_free,
       pct_increase,initial_extent,next_extent
  FROM dba_indexes
  WHERE owner='SCOTT' AND index_name='PK_EMP';
ALTER INDEX scott.pk_emp REBUILD
  PCTFREE 30;
SELECT index_name,pct_free,
       pct_increase,initial_extent,next_extent
  FROM dba_indexes
  WHERE owner='SCOTT' AND index_name='PK_EMP';

P126
6.14
CONN /@ORCL AS SYSDBA 
COL segment_name FORMAT A15
COL tablespace_name FORMAT A15
SELECT segment_name,segment_type,tablespace_name,extents
  FROM dba_segments
  WHERE owner='SCOTT'
        AND segment_type='INDEX' AND segment_name='PK_EMP';
ALTER INDEX scott.PK_EMP ALLOCATE EXTENT;
SELECT segment_name,segment_type,tablespace_name,extents
  FROM dba_segments
  WHERE owner='SCOTT'
        AND segment_type='INDEX' AND segment_name='PK_EMP';
ALTER INDEX scott.PK_EMP DEALLOCATE UNUSED ;

P127
6.15
CONN scott/tiger@orcl
ALTER INDEX PK_EMP MONITORING USAGE ;
COL INDEX_NAME FORMAT A10
COL TABLE_NAME FORMAT A10
SELECT * FROM v$object_usage;
ALTER INDEX pk_emp NOMONITORING USAGE ;

P128
6.16
CONN system/systempwd@orcl
GRANT CREATE VIEW TO scott;
CONN scott/tiger@orcl
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');
CONN scott/tiger@orcl
COL view_name FOR A10
COL text FOR A60
 SELECT view_name,text FROM user_views;

P129
6.17 
CREATE OR REPLACE VIEW v_emp_d20
  AS SELECT ename, (sal+nvl(comm,0))*12 annual_income
     FROM emp WHERE deptno=20;
SELECT * FROM v_emp_d20;
 
P130
6.18
CONN scott/tiger@orcl
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
  ORDER BY a.deptno ;

