EnterpriseDB: Using Index Tablespace Oracle Tips by Burleson Consulting

USING INDEX TABLESPACE will allow you to create your primary key index in a tablespace of your choosing.

INHERITS|LIKE

EnterpriseDB is based on PostgreSQL which is an Object Relational Database Management System (ORDBMS).  INHERITS and LIKE are very obvious examples of its Object feature set.

When you use the INHERITS keyword, your new table inherits all of its attributes from the parent table(s).  There is a permanent link between the tables and anytime you make a change to the parent, that change is visible in the child table.  When you create a table using INHERITS, every time you select data from the parent, the child table is also scanned.  I will show examples using INHERITS below in the section on Partitioned tables.

The LIKE keyword is very much like the INHERITS keyword but there is no permanent link.  The new table will be created with all of the same columns and constraints (and optionally the default values) but the link ends there.  When you scan the older table, your query will not scan the new table.

We have seen many examples of creating a table above but I will show a few more here.  I will use the EMP and DEPT tables that we saw earlier in my examples.

CREATE TABLE
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ]
TABLE <table name> (
{ <column name> <data type>
[ DEFAULT <default> ]
[[ CONSTRAINT <constraint name> ]
  { NOT NULL |
     NULL |
                 UNIQUE [ USING INDEX TABLESPACE <tablespace> ] |
 PRIMARY KEY
            [USING INDEX TABLESPACE <tablespace> ] |
     CHECK (<expression>) |
REFERENCES <parent table> [ ( <parent column> ) ]
[ MATCH FULL |
  MATCH PARTIAL |
  MATCH SIMPLE ]
[ ON DELETE
<NO ACTION|
  RESTRICT|
  CASCADE|
   SET NULL|
   SET DEFAULT> ]
[ ON UPDATE  
<NO ACTION|
  RESTRICT|
  CASCADE|
   SET NULL|
   SET DEFAULT> ] }
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] 
[ ... ] ]
| [ CONSTRAINT <constraint name> ]
   { UNIQUE ( <column name> [, ... ] )
   [ USING INDEX TABLESPACE <tablespace> ] |
   PRIMARY KEY ( <column name> [, ... ] )
[ USING INDEX TABLESPACE <tablespace> ] |
  CHECK ( <expression> ) |
  FOREIGN KEY ( <column name> [, ... ] )
  REFERENCES <parent table> [ ( <parent column> ) ]
[ MATCH FULL |
  MATCH PARTIAL |
  MATCH SIMPLE ]
[ ON DELETE
<NO ACTION|
  RESTRICT|
  CASCADE|
   SET NULL|
   SET DEFAULT> ]
[ ON UPDATE  
<NO ACTION|
  RESTRICT|
  CASCADE|
   SET NULL|
   SET DEFAULT> ] }
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] 
| LIKE <origin table>
[ { INCLUDING | EXCLUDING } DEFAULTS ] }  [, ... ]
)
[ INHERITS ( <origin table> [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <tablespace> ]

Create a table "mydept" in the public schema with DEPTNO as the primary key.  Create the table in the "data_tbs" tablespace and put the primary key index in the "index_tbs" tablespace.

CREATE TABLE public.mydept (
DEPTNO        NUMBER(2)    NOT NULL PRIMARY KEY,
DNAME         VARCHAR2(14) NOT NULL,
LOC           VARCHAR2(13)
)
TABLESPACE data_tbs
USING INDEX TABLESPACE index_tbs; 

edb=# CREATE TABLE public.mydept (
edb(# DEPTNO        NUMBER(2)    NOT NULL PRIMARY KEY,
edb(# DNAME         VARCHAR2(14) NOT NULL,
edb(# LOC           VARCHAR2(13)
edb(# )
edb-# TABLESPACE data_tbs
edb-# USING INDEX TABLESPACE index_tbs;
CREATE TABLE
edb=# desc mydept

           Table "public.mydept"

 Column |         Type          | Modifiers
--------+-----------------------+-----------
 deptno | numeric(2,0)          | not null
 dname  | character varying(14) | not null
 loc    | character varying(13) |

Indexes:

    "mydept_pkey" PRIMARY KEY, btree (deptno)

edb=#

Create table myemp in the default schema with EMPNO as the primary key.  Allow the primary key to default to the default tablespace.  Do not allow nulls in ename and force a default of "BOB".  Only allow 4 jobs, "CLERK", "MGR", 'GEEK" or "DWEEB".  Create a foreign key constraint between the DEPTNO in MYEMP to the DEPTNO in MYDEPT.

CREATE TABLE myemp (
EMPNO         NUMBER(4)    NOT NULL PRIMARY KEY,
ENAME         VARCHAR2(10) NOT NULL DEFAULT 'BOB',
JOB           VARCHAR2(9)  CHECK (job in ('CLERK', 'MGR', 'GEEK', 'DWEEB')),
MGR           NUMBER(4),
HIREDATE      TIMESTAMP(0),
SAL           NUMBER(7,2),
COMM          NUMBER(7,2),
DEPTNO        NUMBER(2)    CONSTRAINT deptno_fk REFERENCES mydept (deptno)
);

edb=# CREATE TABLE myemp (
edb(# EMPNO         NUMBER(4)    NOT NULL PRIMARY KEY,
edb(# ENAME         VARCHAR2(10) NOT NULL DEFAULT 'BOB',
edb(# JOB           VARCHAR2(9)  CHECK (job in ('CLERK', 'MGR', 'GEEK', 'DWEEB')),
edb(# MGR           NUMBER(4),
edb(# HIREDATE      TIMESTAMP(0),
edb(# SAL           NUMBER(7,2),
edb(# COMM          NUMBER(7,2),
edb(# DEPTNO        NUMBER(2)    CONSTRAINT deptno_fk REFERENCES mydept (deptno)
edb(# );
CREATE TABLE
edb=# desc myemp

                                 Table "public.myemp"

  Column  |              Type              |                 Modifiers
----------+--------------------------------+-------------------------------------------
 empno    | numeric(4,0)                   | not null
 ename    | character varying(10)          | not null default
        'BOB'::character varying
 job      | character varying(9)           |
 mgr      | numeric(4,0)                   |
 hiredate | timestamp(0) without time zone |
 sal      | numeric(7,2)                   |
 comm     | numeric(7,2)                   |
 deptno   | numeric(2,0)                   |

Indexes:
    "myemp_pkey" PRIMARY KEY, btree (empno)

Check constraints:
    "myemp_job_check"

CHECK
(job::text = 'CLERK'::text OR
job::text = 'MGR'::text OR
job::text = 'GEEK'::text OR
job::text = 'DWEEB'::text)
Foreign-key constraints:
    "deptno_fk" FOREIGN KEY (deptno) REFERENCES mydept(deptno)

edb=#

Create a duplicate of MYEMP and call it emp_clone.  There are no links or dependencies between emp and cmp_clone.

CREATE TABLE emp_clone ( LIKE myemp );

edb=# CREATE TABLE emp_clone ( LIKE myemp );
CREATE TABLE
edb=# desc emp_clone

               Table "public.emp_clone"

  Column  |              Type              | Modifiers
----------+--------------------------------+-----------
 empno    | numeric(4,0)                   | not null
 ename    | character varying(10)          | not null
 job      | character varying(9)           |
 mgr      | numeric(4,0)                   |
 hiredate | timestamp(0) without time zone |
 sal      | numeric(7,2)                   |
 comm     | numeric(7,2)                   |
 deptno   | numeric(2,0)                   | 

edb=#

CREATE TABLE AS SELECT (CTAS)
CREATE [
 [ GLOBAL | LOCAL ]
{ TEMPORARY | TEMP } ]
TABLE <table name>
 [ (<column list> ) ]
    AS <query>

There is an alternate syntax to create a table from an existing table.  If you use a CTAS, you will also populate the table with data from the original table.

CTAS is also called the poor Remote DBA's Materialized View.

If we wanted to create the emp_clone table pre-populated with data, instead of using the LIKE syntax, we would execute this command:

CREATE TABLE emp_clone AS SELECT * FROM emp;

edb=# CREATE TABLE emp_clone AS SELECT * FROM emp;
SELECT
edb=# desc emp_clone;

               Table "public.emp_clone"

  Column  |              Type              | Modifiers
----------+--------------------------------+-----------
 empno    | numeric(4,0)                   |
 ename    | character varying(10)          |
 job      | character varying(9)           |
 mgr      | numeric(4,0)                   |
 hiredate | timestamp(0) without time zone |
 sal      | numeric(7,2)                   |
 comm     | numeric(7,2)                   |
 deptno   | numeric(2,0)                   | 

edb=#

ALTER TABLE
ALTER TABLE <table name>
            ADD <table level constraint>

            DROP CONSTRAINT <constraint name>
[ RESTRICT | CASCADE ]
            DISABLE TRIGGER [ <trigger name> | ALL | USER ]
            ENABLE TRIGGER [ <trigger name> | ALL | USER ]
            CLUSTER ON <index name>
            SET WITHOUT CLUSTER
            SET WITHOUT OIDS
            OWNER TO <new database owner name>
            SET TABLESPACE <tablespace>
            RENAME TO <new table name>   
            RENAME COLUMN <column name> TO <new column name>
            ADD COLUMN <column type>
            DROP COLUMN <column name> [ RESTRICT | CASCADE ]
            ALTER COLUMN <column name> TYPE <data type>
            ALTER COLUMN <column name> SET DEFAULT <default>
            ALTER COLUMN <column name> DROP DEFAULT
            ALTER COLUMN <column name> { SET | DROP } NOT NULL
            ALTER COLUMN <column name> SET STATISTICS integer
            ALTER COLUMN <column name> SET STORAGE
{ PLAIN | EXTERNAL | EXTENDED | MAIN }
[, ... ]

Drop the deptno foreign key (which is called deptno_fk).

ALTER TABLE emp

  DROP constraint deptno_fk;   

Create a new deptno foreign key in the emp table and call it deptno_fk.  This foreign key references the deptno in the dept table.

ALTER TABLE emp
  ADD constraint deptno_fk
  FOREIGN KEY (deptno)
  REFERENCES dept(deptno);

Create a unique constraint on ename.  Duplicates of ename will not be allowed.

ALTER TABLE emp
  ADD constraint ename_uk
  UNIQUE (ename);

Alter the emp_clone table and add a review_date column of DATE data type.

ALTER TABLE emp_clone ADD
  REVIEW_DATE DATE; 

edb=# ALTER TABLE emp_clone ADD
edb-#   REVIEW_DATE DATE;
ALTER TABLE
edb=# desc emp_clone

                 Table "public.emp_clone"

   Column    |              Type              | Modifiers
-------------+--------------------------------+-----------
 empno       | numeric(4,0)                   |
 ename       | character varying(10)          |
 job         | character varying(9)           |
 mgr         | numeric(4,0)                   |
 hiredate    | timestamp(0) without time zone |
 sal         | numeric(7,2)                   |
 comm        | numeric(7,2)                   |
 deptno      | numeric(2,0)                   |
 review_date | timestamp(0) without time zone |

Alter the emp_clone table and drop the mgr column.

ALTER TABLE emp_clone
  DROP COLUMN mgr; 

edb=# ALTER TABLE emp_clone
edb-#   DROP COLUMN mgr;
ALTER TABLE
edb=# desc emp_clone

                 Table "public.emp_clone"

   Column    |              Type              | Modifiers
-------------+--------------------------------+-----------
 empno       | numeric(4,0)                   |
 ename       | character varying(10)          |
 job         | character varying(9)           |
 hiredate    | timestamp(0) without time zone |
 sal         | numeric(7,2)                   |
 comm        | numeric(7,2)                   |
 deptno      | numeric(2,0)                   |
 review_date | timestamp(0) without time zone |

GRANT TABLE

Give tom the ability to see records and create new records in emp.

GRANT select, insert ON emp TO tom;

Give tom the ability to see records and remove records in emp.

GRANT select, delete ON dept TO tom;

Give bill the ability to see, change, remove and create new records in dept.

GRANT ALL ON dept TO bill;

Using ALL also grants several other rights such as creating triggers and rules on the table.

REVOKE TABLE

Take away tom's ability to see data in emp.

REVOKE select ON emp FROM tom;

Take away bill's ability to create triggers on dept.  Bill got that ability by using the keyword ALL in the grant above.

REVOKE trigger ON dept FROM bill;

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.