Oracle SQL in 10 Minutes - Asim Abbasi
CH1: SQL Basics | CH2: SQL Operators | CH3: SQL Built-in Functions | CH4: SQL Joins | CH5: UPDATE, INSERT & DELETE Statements | CH6: CREATE, ALTER & DROP Statements | CH7: Constraints | CH8: Linking Tables vs Joining Tables | CH9: SQL Statements for Other Database Objects | CH10: SQL Statements for Database Security
Chapter 9: SQL Statements for Other Database Objects
Each time you create a index on any column of a table Oracle creates a implicit table that can help it in locating the records much faster. That table size depends on the size of column on which you have created the index but both these quantities are in direct relationship with each other i.e. more the number of values in column, on which you have created the index, more would be size of implicit table that Oracle will create. So creating indexes on all the columns of all the table inside the database will to do much rather it will tremendously increase the size of the database and its growing rate. Moreover, you would not be using them all in your search criteria.
Example:
CREATE INDEX emp_test_idx
ON emp_tests (empno);
Where emp_test_idx is the name that we have given to this index and its
created on empno column of emp_test table.
Example:
CREATE INDEX m1_idx
ON m1 (id1, id2);
Where m1_idx is the name of the index, which is created on two columns
id1, and id2 of m1 table. Such type of index is called composite index
as they gets created on two or more than two columns.
Example:
ALTER INDEX m1_idx RENAME TO m2_idx;
This statement will rename the index to m2_idx. There are lot other
stuff that we can do with ALTER INDEX command but we will it later in
this book when we talk about different forms of indexes that are
available.
Example:
DROP INDEX m2_idx;
This statement will drop the index on that particular column all
together.
Chapter 9: SQL Statements for Other Database Objects
CH1: SQL Basics | CH2:
SQL Operators |
CH3: SQL Built-in Functions | CH4:
SQL Joins | CH5:
UPDATE, INSERT & DELETE Statements |
CH6: CREATE, ALTER & DROP Statements | CH7:
Constraints | CH8:
Linking Tables vs Joining Tables | CH9:
SQL
Statements for Other Database Objects | CH10:
SQL Statements for
Database Security Share with others: