CREATING INDEXES



CREATING INDEXES

An index is an ordered list of all the values that are contained in one or more columns. Creating indexes makes queries that test the values in those columns vastly more efficient. However, indexes consume storage space. Therefore, the associated costs and benefits must be carefully weighed to determine whether a column or columns should be indexed. Oracle can use indexes to improve performance when searching for rows with specified index column values or accessing tables in index column order. Inserting rows into an indexed table is oftwn slower. This means tt is generally faster to create the table, insert the rows, and then create the index. A table can contain unlimited number of indexes. When indexes are creating ORACLE first sorts the rows in the table before creating an index. The option NOSORT can be used to specify that such sorting need not be done. Using this option may significantly reduce the time to create the INDEX. However, the user must guarantee the rows are already in sorted order. If this were not so, ORACLE would return an error.

CREATE INDEX i_emp ON employee (emp_city)

NOSORT ;

Although you could use a UNIQUE option while creating indexes, it is better done during defining the table.

DROPPING A TABLE

To remove a table and all its rows from a database , use DROP TABLE command. Be aware that this command removes both the table structure as well as the rows contained in it. After you issue the DROP TABLE command, there is no way to “undo” the command. The ROLLBACK command applies to only the DML commands and NOT DDL commands. You can only drop the table in your schema or you must have privileges to DROP it.

When you drop a table, Oracle also automatically performs the following operations:

1. Oracle removes all rows from the table (as if the rows were deleted).

2. Oracle drops all the indexes associated with the table.

3. If the table is a base table for views or if it is referenced in stored procedures, functions, or packages, Oracle only invalidates these objects without dropping them. These objects automatically become available again if you either re-create the table or drop and re-create the objects to eliminate any references to the dropped table.

4. If the table is re-created, it must contain all the columns selected by the queries originally used to define the views and all the columns referenced in the stored procedures, functions, or packages. But the privileges need not be re-granted.

Example:

DROP TABLE employee;

DELETING ROWS FROM A TABLE

To remove rows from a table but retain its structure , you can use the DELETE command.

DELETE FROM employee;

Deletes all rows from the table “employee.” Delete command can contain a WHERE clause OR a query as shown in the following examples:

DELETE FROM employee

WHERE city = 'MONTGOMERY'

AND contr_amt < 100;

DELETE FROM (select * from emp)

WHERE city = 'MONTGOMERY'

AND contr_amt < 100;

To delete rows from a table, the table must be in the user’s own schema or the user must have DELETE privilege on the table.

INSERTING ROWS INTO A TABLE

In order to insert rows into a table, the table must be in the user’s own schema or the user must have INSERT privilege on the table.

An INSERT statement with a VALUES clause adds to the table a single row containing the values specified in the VALUES clause. A column list can be specified after “INSERT” to insert values into only specified columns. In any case, the value list must provide values of the right data type.

ORACLE also allows you to use an INSERT statement with a subquery instead of a VALUES clause. When used with a subquery all rows returned by the subquery are inserted into the table. The subquery can refer to any table or view including the table into which the rows are inserted.

Example 1 ( No column list)

INSERT INTO employee

VALUES (40, 'ACCOUNTING', 'BLUE ASH’);

Example 2 (Inserting into specified columns)

INSERT INTO employee (emp_id, emp_name, dept, loc, sal )

VALUES (40, ‘David Manning’, 'ACCOUNTING', 'BLUE ASH’, 45000);

Example 3 (Inserting using a subquery)

INSERT INTO employee

SELECT eid, ename, dept, loc, sal

FROM temp_emp

WHERE sal > 42000;

OPERATORS

Operators are used to process individual data items to return a desire result set.

|Operator |Operation |

|+, - |identity, negation |

|*, / |multiplication, division |

|+, -, || |addition, subtraction, concatenation |

|=, !=, , =, IS NULL, LIKE, BETWEEN, IN |comparison |

|NOT |exponentiation, logical negation |

|AND |conjunction |

|OR |disjunction |

|Operator |Purpose |Example |

|= |Equality test. |SELECT * |

| | |FROM emp |

| | |WHERE sal = 1500; |

|!= |Inequality test. Some forms of the inequality operator|SELECT * |

|^= |may be unavailable on some platforms. |FROM emp |

| | |WHERE sal != 1500; |

|¬= | | |

|> |"Greater than" and "less than" tests. |SELECT * FROM emp |

| | |WHERE sal > 1500; |

|< | |SELECT * FROM emp |

| | |WHERE sal < 1500; |

|>= |"Greater than or equal to" and "less than or equal to"|SELECT * FROM emp |

| |tests. |WHERE sal >= 1500; |

| ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download