* Nice 9i features relavent to data warehousing
------------------------------------------------

* List partitioning
   Example:
   
   create table part_range_temp (c1 number(3), c2 varchar2(2), c3 char(1))
   partition by range (c1)
   subpartition by list (c3)
     (partition p1 values less than (100)
        (subpartition p1_d1 values('A'),
         subpartition p1_d2 values('B')
      ),
      partition p2 values less than (200)
        (subpartition p2_d1 values('A'),
         subpartition p2_d2 values('B')
      ),
      partition p3 values less than (300)
        (subpartition p3_d1 values('A'),
         subpartition p3_d2 values('B')
        )
      );

* Automatic global index update during partition maintainence
   Syntax:
	  ALTER TABLE test1 ADD PARTITION p3 UPDATE GLOBAL INDEXES;

* The "WITH" clause
   The WITH clause allows a query block to be assigned a name and used multiple
   times in a query by referring to this name. This is very useful to reduce the
   cost of a query block which will need to be evaluated more than once in a query
   by materializing the query block.

   Example:  
   WITH dept_costs AS (
          SELECT department_name, SUM(salary) dept_total
          FROM employees e, departments d
          WHERE e.department_id = d.department_id
          GROUP BY department_name),
        avg_cost AS (
          SELECT SUM(dept_total)/COUNT(*) avg
          FROM dept_costs)
   SELECT * FROM dept_costs
   WHERE dept_total >
   (SELECT avg FROM avg_cost)
   ORDER BY department_name;

* SQL*LOADER direct path with SQL functions
   
  LOAD DATA
  INFILE 'sample.dat'
  BADFILE 'sample.bad'
  DISCARDFILE 'sample.dsc'
  APPEND
  INTO TABLE part_range_temp
  (c1 position(1:3) integer external(3) ":c1+10",
   c2 position(4:5) char "upper(:c2)",
   c3 position(6:8) char "substr(:c3,1,1)")
 
  Direct path load can now be used with SQL functions.

* User-defined aggregate functions
   These functions are written in programming languages such as PL/SQL, Java, and C, and 
   can be used as analytic functions or aggregates in materialized views.

* RAC(Real Application Cluster)
   High availability, scalibility solution in 9i.

* Online operation for almost everything.
   When you can not afford to lose access to your data during routine database operations.
   
* Multi-block size database.   
   To support transportable tablespace.

* Resumable space allocation   
   1. Grant "resumable" to user
   2. alter session enable resumable;
      alter session enable resumable timeout 1200;
   3. Select * from dba_resumable;
   4. alter session disbale resumable;

* Index monitoring   
   Identify unused indexes.
   1. alter index table1_pk monitoring usage;
   2. column index_name format a12
      column monitoring format a10
      column used format a4
      column start_monitoring format a19
      column end_monitoring format a19

      select index_name,monitoring,used,start_monitoring,end_monitoring
      from v$object_usage; 

* Resize SGA online
         
* Block-level recovery   
   This can be done with OEM.
       
* Data compression 

* Bit map join index
	  
* Change Data Capture(CDC)

* External table

* Fine Grained Auditing

* ORACLE Streams

* Merge operation

* Pipelined Table Function(PTF)