into the suppliers table. The supplier_id field would be assigned the next number from the supplier_seq sequence. The supplier_name field would be set to Kraft Foods.
Index
What is an Index?
An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.
Create an Index
The syntax for creating a index is:
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];
UNIQUE indicates that the combination of values in the indexed columns must be unique.
COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed.
For example:
CREATE INDEX supplier_idx
ON supplier (supplier_name);
In this example, we've created an index on the supplier table called supplier_idx. It consists of only one field - the supplier_name field.
We could also create an index with more than one field as in the example below:
CREATE INDEX supplier_idx
ON supplier (supplier_name, city);
We could also choose to collect statistics upon creation of the index as follows:
CREATE INDEX supplier_idx
ON supplier (supplier_name, city)
COMPUTE STATISTICS;
Drop an Index
The syntax for dropping an index is:
DROP INDEX index_name;
For example:
DROP INDEX supplier_idx;
In this example, we're dropping an index called supplier_idx.
Practical 2: Create, alter and update views
A view is, in essence, a virtual table. It does not physically exist. Rather, it is created by a query joining one or more tables.
Creating a VIEW
The syntax for creating a VIEW is:
CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;
For example:
CREATE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'IBM';
This would create a virtual table based on the result set of the select statement. You can now query the view as follows:
SELECT *
FROM sup_orders;
Updating a VIEW
You can update a VIEW without dropping it by using the following syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;
For example:
CREATE or REPLACE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'Microsoft';
Dropping a VIEW
The syntax for dropping a VIEW is:
DROP VIEW view_name;
For example:
DROP VIEW sup_orders;
Конец ознакомительного фрагмента.
Текст предоставлен ООО «ЛитРес».
Прочитайте эту книгу целиком, купив полную легальную версию на ЛитРес.
Безопасно оплатить книгу можно банковской картой Visa, MasterCard, Maestro, со счета мобильного телефона, с платежного терминала, в салоне МТС или Связной, через PayPal, WebMoney, Яндекс.Деньги, QIWI Кошелек, бонусными картами или другим удобным Вам способом.
