Practice: Types of Indexes

 

This practice works with the commands to create different types of indexes.

 

 

Assumptions

 

·        An Oracle 9i database is up and running.

·        An HR schema.

 

Instructions

 

1.      Create a compressed index on the first_name column of the hr.employees table.  Also, compress the index as part of a rebuild command

 

SQL> create index emp_first_name_idx

  2  on employees (first_name)

  3  compress;

 

Index created.

 

SQL> alter index emp_first_name_idx

  2  rebuild compress;

 

Index altered.

 

 

2.      Create a bitmap index on the manager_id column of the departments table.

 

SQL> connect hr/hr

Connected.

SQL> create bitmap index departments_idx

  2  on departments (manager_id)

  3  storage (initial 200k next 200k

  4  pctincrease 0 maxextents 100)

  5  tablespace indx;

 

Index created.

 

SQL>

 

 

3.      Create a reverse key index as part of the “create unique index…” statement.  Also, create a reverse key as part of the rebuild command.

 

SQL> create unique index id_t1 on t1(id)

  2  reverse pctfree 30

  3  storage (initial 200k next 200k

  4  pctincrease 0 maxextents 100)

  5  tablespace indx;

 

Index created.

 

SQL> create unique index name_t1 on t1(name);

 

Index created.

 

SQL> alter index name_t1 rebuild reverse;

 

Index altered.

 

SQL>

 

This completes this practice.

Practice: Index Organized Tables

 

This practice works with the commands to create Index Organized Tables.

 

 

Assumptions

 

·        An Oracle 9i database is up and running.

·        An HR schema.

 

Instructions

 

1.      Create an Index Organized Table named country with id, country_name, currency_name, currency_symbol, map, and flag columns and a primary key constraint in the HR schema.  Define the overflow tablespace as users.

 

SQL> connect hr/hr

Connected.

SQL> get country

  1  create table country

  2  (id char(2)

  3  constraint id_nn not null,

  4  country_name varchar2(40),

  5  currency_name varchar2(25),

  6  currency_symbol varchar2(3),

  7  map blob,

  8  flag blob,

  9  constraint id_pk

 10  primary key (id))

 11  organization index

 12  pctthreshold 20

 13* overflow tablespace users

SQL> /

 

Table created.

 

SQL>

 

 

2.      Use the dba_tables view to confirm the table_name, tablespace_name, iot_name, and iot_type for the new table where owner is HR.

 

SQL> l

  1  select table_name, tablespace_name,

  2  iot_name, iot_type

  3  from dba_tables

  4* where owner = 'HR'

SQL> /

 

TABLE_NAME           TABLESPACE_NAME      IOT_NAME        IOT_TYPE

-------------------- -------------------- --------------- ------------

COUNTRIES                                                 IOT

COUNTRY                                                   IOT

DEPARTMENTS          EXAMPLE

EMPLOYEES            DATA5

JOBS                 EXAMPLE

JOB_HISTORY          EXAMPLE

LOCATIONS            EXAMPLE

REGIONS              EXAMPLE

SYS_IOT_OVER_37423   USERS                COUNTRY         IOT_OVERFLOW

T1                   EXAMPLE

TEMP_EMPS            EXAMPLE

 

11 rows selected.

 

SQL>

 

 

3.      Use the dba_indexes view to confirm the index_name, index_type, tablespace_name, and table_name of the new indexes.

 

SQL> l

  1  select Index_name, index_type,

  2  tablespace_name, table_name

  3  from dba_indexes

  4  where owner = 'HR'

  5* and index_name = 'ID_PK'

SQL> /

 

INDEX_NAME                     INDEX_TYPE TABLESPACE_NAME TABLE_NAME

------------------------------ ---------- --------------- ---------------

ID_PK                          IOT - TOP  EXAMPLE         COUNTRY

 

SQL>

 

 

4.      Create an IOT table called mycountry specifying a mapping table in tablespace users for a bitmap index.

 

SQL> connect hr/hr

Connected.

SQL> create table mycountry

  2  (id char(2)

  3  constraint mycountry_id_nn not null,

  4  country_name varchar2(40),

  5  currency_name varchar2(25),

  6  currency_symbol varchar2(3),

  7  constraint mycountry_c_id_pk

  8  primary key (id))

  9  organization index

 10  mapping table tablespace users;

 

Table created.

 

SQL>

 

This completes this practice

Practice: Materialized Views and Query Rewrites

 

This practice works with the commands to create Materialized Views and Query Rewrites.

 

 

Assumptions

 

·        An Oracle 9i database is up and running.

·        An HR schema.

·        An SH schema.

 

Instructions

 

1.      Create a Materialized view called depart_sal_sum which selects the department_name, and the sum of salaries from the department and employees tables and group by department_name.  Use the HR schema.

 

SQL> connect hr/hr

Connected.

SQL> get matview

  1  create materialized view

  2  depart_sal_sum as

  3  select d.department_name, sum(e.salary)

  4  from departments d, employees e

  5  where d.department_id = e.department_id

  6* group by d.department_name

SQL> /

 

Materialized view created.

 

SQL>

 

 

2.      Use the dbms_mview package to refresh the materialized view just created.

 

SQL> exec dbms_mview.refresh('depart_sal_sum');

 

PL/SQL procedure successfully completed.

 

SQL>

 

 

3.      Create a materialized view which performs a query rewrite any time the base query is executed.  In this view select the the product_name, sum of quantity sold, and sum of amount sold from the sales and product tables in the sh schema.

 

SQL> connect sh/sh

 

SQL> create materialized view sales_summary

  2  tablespace users

  3  parallel (degree 4)

  4  build immediate refresh complete

  5  enable query rewrite

  6  as

  7  select p.prod_name, sum(s.quantity_sold),

  8  sum(s.amount_sold)

  9  from sales s, products p

 10  where s.prod_id = p.prod_id

 11* group by p.prod_name;

 

Materialized view created.

 

SQL>

 

 

This completes this practice.

1