DBMS Lab Manual. Jitendra Inc. Patel

Чтение книги онлайн.

СКАЧАТЬ 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 Кошелек, бонусными картами или другим удобным Вам способом.

/9j/4AAQSkZJRgABAQEBLAEsAAD/2wBDAAEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEB AQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQH/2wBDAQEBAQEBAQEBAQEBAQEBAQEBAQEB AQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQH/wAARCAfQBZQDASIA AhEBAxEB

СКАЧАТЬ