Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Understanding SQL DDL Statements

By: FYIcenter.com

Part:   1  2  3  

(Continued from previous part...)

How To Drop an Index?

If you don't need an existing index any more, you should delete it with the DROP INDEX statement. Here is an example SQL script:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  birth_date DATE NOT NULL,
  social_number VARCHAR(80) UNIQUE NOT NULL);
Table created.

CREATE INDEX student_birth_date ON student(birth_date);
Index created.

SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME              TABLE_NAME            UNIQUENES
----------------------- --------------------- ---------
SYS_C004129             STUDENT               UNIQUE
SYS_C004130             STUDENT               UNIQUE
STUDENT_BIRTH_DATE      STUDENT               NONUNIQUE 

DROP INDEX STUDENT_BIRTH_DATE;
Index dropped.

How To Create a New View?

You can create a new view based on one or more existing tables by using the CREATE VIEW statement as shown in the following script:

CREATE VIEW employee_department AS 
  SELECT e.employee_id, e.first_name, e.last_name, 
    e.email, e.manager_id, d.department_name 
  FROM employees e, departments d
  WHERE e.department_id = d.department_id;
View created.

SELECT first_name, last_name, department_name 
  FROM employee_department WHERE manager_id = 101;
FIRST_NAME           LAST_NAME           DEPARTMENT_NAME
-------------------- ------------------- ----------------
Nancy                Greenberg           Finance
Jennifer             Whalen              Administration
Susan                Mavris              Human Resources 
Hermann              Baer                Public Relations
Shelley              Higgins             Accounting

How To Drop an Existing View?

If you have an existing view, and you don't want it anymore, you can delete it by using the DROP VIEW statement as shown in the following script:

DROP VIEW employee_department;
View dropped.

Part:   1  2  3