function based indexes

A nonunique index can be used to enforce a primary key or unique constraint.

In Oracle8i indexes can be rebuilt without locking the table.

The DROP COLUMN option of the ALTER TABLE command is restartable.

The MOVE option of the ALTER TABLE command retains the constraints of the table.

Data rows in the global temporary table are always deleted when A user session is terminated

1. As user Scott, create a table with three columns. Create an index on all three columns in the order they appear in the table. Then add a primary key constraint using the first two columns with the second column of the table appearing first. Verify that only one index, the index being used to enforce the constraint, has been defined for the table.

Hint: if the columns in the table were labeled A, B, and C, the index would be on (A, B, C) while the constraint would be on columns (B, A).

Solution:

connect scott/tiger

CREATE TABLE acct

( acct_no NUMBER(10),

customer_id NUMBER(10),

acct_comment VARCHAR2(200),

CONSTRAINT pk_cid_aid PRIMARY KEY(customer_id, acct_no) DISABLE

)

CREATE INDEX I_ANO_CNO_ACOMM

ON acct(acct_no, customer_id, acct_comment)

ONLINE

ALTER TABLE acct

ENABLE CONSTRAINT pk_cid_aid

select index_name, table_name from user_indexes

where table_name = ‘ACCT’

2. As user Scott create a table containing three columns. Remove the third column using one of the new methods introduced in Oracle8i. Verify that the column is no longer part of the table.

Solution:

connect scott/tiger

CREATE TABLE acct_col

( acct_col_no NUMBER(10),

customer_id NUMBER(10),

acct_col_comment VARCHAR2(200)

)

ALTER TABLE acct_col

SET UNUSED COLUMN acct_col_comment

desc acct_col

SELECT * FROM user_unused_col_tabs

ALTER TABLE acct_col

DROP UNUSED COLUMNS

SELECT * FROM user_unused_col_tabs

3. As user SYS create a global temporary table containing three columns. The inserted rows should remain available until explicitly deleted or the session ends. Make the table available to anyone who wishes to use it. The users should not have to know the table owner in order to make use of it.

Solution:

connect / as sysdba

CREATE GLOBAL TEMPORARY TABLE emp_temp_X

(eno NUMBER,

ename VARCHAR2(20),

sal NUMBER)

ON COMMIT PRESERVE ROWS;

connect / AS SYSDBA

CREATE PUBLIC SYNONYM emp_temp for emp_temp_x

GRANT ALL ON emp_temp TO PUBLIC

col object_name format a20

SELECT owner, object_name, object_type FROM dba_objects

WHERE object_name LIKE ‘%EMP_TEMP%’

connect scott/tiger

desc emp_temp

select * from emp_temp

Function Based Indexes and Global Temporary Tables