2)transform example02 split
1@@@@split table
guide:
  Continue the example01, the table apr_orders. Spliting a table, you must use
  key column. Accroding to the key column, implement split.

@@@
@@@<1>create test table sh.apr_orders
@@@
[oracle@station78 ~]$ cat apr_orders02.sql
DROP TABLE sh.apr_orders;
CREATE TABLE sh.apr_orders
(product_id  VARCHAR2(8),
 time_key    DATE,
 customer_id  VARCHAR2(10),
 ship_date   DATE,
 purchase_price   NUMBER(6,2),
 shipping_charge  NUMBER(5,2),
 today_special_offer VARCHAR2(1)
)
TABLESPACE USERS;
INSERT INTO sh.apr_orders VALUES
('SP-1001', '01-APR-04','AB123456','01-APR-04', 28.01, 5.45, 'Y');
INSERT INTO sh.apr_orders VALUES
('SP-1001', '01-APR-04','AB123457','01-APR-04', 28.01, 5.45, 'Y');
INSERT INTO sh.apr_orders VALUES
('SP1061', '01-APR-04','AB123456','01-APR-04', 28.01, 8.42, 'Y');
INSERT INTO sh.apr_orders VALUES
('SP1062', '01-APR-04','AB123457','01-APR-04', 28.01, 3.58, 'Y');
INSERT INTO sh.apr_orders VALUES
('SP1061', '01-APR-04','AB123456','01-APR-04', 28.01, 8.42, 'N');
INSERT INTO sh.apr_orders VALUES
('SP1061', '01-APR-04','AB123456','01-APR-04', 28.01, 8.42, 'N');
COMMIT;

@@@
SYS@ocp> @apr_orders02.sql
Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.


@@@
@@@<2>create split table structure from source table
@@@
SYS@ocp> CREATE TABLE sh.special_orders AS SELECT * FROM sh.apr_orders WHERE 1 != 1;
Table created.
SYS@ocp> CREATE TABLE sh.common_orders AS SELECT * FROM sh.apr_orders WHERE 1 != 1;
Table created.
SYS@ocp> select count(*) from sh.special_orders;
  COUNT(*)
----------
     0
SYS@ocp> select count(*) from sh.common_orders;
  COUNT(*)
----------
     0


@@@
@@@<3>split the table now, and inspect result.
@@@
Note:
    The INSERT statement specifies a condition, which is evaluated to
  determine which table each row should be inserted into. In this example,
  there is only one WHEN clause, but you can have multiple WHEN clause if
  there are multiple conditions to evaluate. If no WHEN clasue evaluates
  to true, the ELSE clause is executed.
     By specifying FIRST, Oracle stops evaluating the WHEN clause when
  first condition is met, Alternatively, if ALL is specified, all conditions
  will be checked for each row. ALL is usefull when the same row is stored in
  multiple tables.

@@@
SYS@ocp> INSERT FIRST WHEN today_special_offer = 'Y'    
  2  THEN INTO sh.special_orders
  3  ELSE INTO sh.common_orders
  4  SELECT * FROM sh.apr_orders;
6 rows created.

SYS@ocp> select product_id, today_special_offer from sh.special_orders;
PRODUCT_ID         TOD
------------------------ ---
SP-1001          Y
SP-1001          Y
SP1061             Y
SP1062             Y

SYS@ocp> select product_id, today_special_offer from sh.common_orders;
PRODUCT_ID         TOD
------------------------ ---
SP1061             N
SP1061             N


@@@
@@@<4> this is another method.
@@@
@@@if table already exist, you must use insert clause.
SYS@ocp> create table sh.s_orders
2> as select * from sh.apr_orders where today_special_offer = 'Y';
Table created.

SYS@ocp> create table sh.c_orders
2> as select * from sh.apr_orders where today_special_offer = 'N';
Table created.