How To Drop an Index

Q

How To Drop an Index? - Oracle DBA FAQ - Understanding SQL DDL Statements

✍: FYIcenter.com

A

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.

2007-04-22, 4901👍, 0💬

What Are DML Statements
What Are DML Statements? - Oracle DBA FAQ - Understanding SQL DML Statements DML (Data Manipulation Language) statements are statements to change data values in database tables. The are 3 primary DML statements: INSERT - Inserting new rows into database tables. UPDATE - Updating existing rows in dat... 2007-04-22, 5120👍, 0💬

How To Add a New Column to an Existing Table
How To Add a New Column to an Existing Table? - Oracle DBA FAQ - Understanding SQL DDL Statements If you have an existing table with existing data rows, and want to add a new column to that table, you can use the ALTER TABLE ... ADD statement to do this. Here is an example script: SQL> connect HR/fy... 2007-04-22, 5082👍, 0💬

How To Delete a Column in an Existing Table
How To Delete a Column in an Existing Table? - Oracle DBA FAQ - Understanding SQL DDL Statements If you have an existing column in a table and you need that column any more, you can delete it with ALTER TABLE ... DROP COLUMN statement. Here is an example SQL script: SQL> CREATE TABLE emp_dept_90 2 A... 2007-04-22, 4954👍, 0💬

How To Create a Testing Table
How To Create a Testing Table? - Oracle DBA FAQ - Understanding SQL DML Statements If you want to practice DML statements, you should create a testing table as shown in the script below: CREATE TABLE fyi_links (id NUMBER(4) PRIMARY KEY, url VARCHAR2(80) NOT NULL, notes VARCHAR2(1024), counts NUMBER,... 2007-04-22, 4920👍, 0💬

How To Drop an Index
How To Drop an Index? - Oracle DBA FAQ - Understanding SQL DDL Statements 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 VARCHA... 2007-04-22, 4902👍, 0💬

How To Create a New View
How To Create a New View? - Oracle DBA FAQ - Understanding SQL DDL Statements 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.emai... 2007-04-22, 4859👍, 0💬

How To Create a Table Index
How To Create a Table Index? - Oracle DBA FAQ - Understanding SQL DDL Statements If you have a table with a lots of rows, and you know that one of the columns will be used often a search criteria, you can add an index for that column to in improve the search performance. To add an index, you can use... 2007-04-22, 4813👍, 0💬

How To Drop an Existing Table
How To Drop an Existing Table? - Oracle DBA FAQ - Understanding SQL DDL Statements If you want to delete an existing table and its data rows, you can use the DROP TABLE statement as shown in this script: SQL> connect HR/fyicenter Connected. SQL> CREATE TABLE emp_dept_10 2 AS SELECT * FROM employees ... 2007-04-22, 4768👍, 0💬

How To Drop an Existing View
How To Drop an Existing View? - Oracle DBA FAQ - Understanding SQL DDL Statements 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. 2007-04-22, 4674👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.