Schema Management

Schema Management :

Visible and invisble Indexes :

Indexes can be visible or invisible , an invisble index is maintained by DML operations and cannot be
used by the optimizer. making an index invisible is an alternative to making it unusable or dropping it.

Oracle 11g New Feature: Invisible index advantages for performance tuning

Oracle 11g introduced a new feature called invisible indexes. We can make an index to invisible mode when it is not needed. Once we make an index to invisible the index will not come to optimizer reach. The syntax for making an index to invisible is as below.



ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;

Invisible index is maintained by Oracle database but, ignored by the optimizer unless you specify explicitly.
How to check the visibility parameter of an index?
It can be identified from VISIBILITY column of the dba_indexes table.

SQL>select index_name, visibility from dba_indexes where index_name='EXAMPLE_INVI_IDX';

INDEX_NAME VISIBILIT
------------------------------ ---------
EXAMPLE_INVI_IDX VISIBLE


Creating an invisible index:

sys>>create INDEX emp_ename ON emp(ename)
tablespace users
storage (initial 20k
next 20k
pctincrease 75)
invisible;

Making an index invisible

sys>>alter index emp_ename INVISIBLE;

 Making invisble index to visible

sys>>alter index emp_ename visible;

  To check an index or Invisble or visble

sys>>select INDEX_NAME,VISIBILITY from users_INDEXES;


Read Only tables;

In oracle 11g, can place a table in read only mode by merely executing alter table read only statements

 Creating a table

sys>create table emp (a number);

sys>>insert into emp values (1);

>>commit;

  Using alter table_read only statement

sys>>alter table emp read only;

note:
cannot insert ,update,delete contents in the read only table.


sys>>select table_name,read_only from user_tables;

 Table back to read write

sys>>alter table emp read write;


DDL Wait Options:

sys>>alter session set ddl_lock_timeout=10;

sys>>alter table sales add(taxe_code varchar1(10));


Virtual columns:

If sales_amt           and sale_amt is less     then sale_category
is more than           that or equal to             is

 0                            1000                             LOW

 10001                    100000                         MEDIUM

 100001                  1000000                       HIGH

 1000001                unlimited                     ULTRA




 sys>>create table sales
        (
       sales_id     number,
       cust_id      number,
       sales_amt   number,
           sale_category  varchar2(6)
       generate always as
       (
         case
            when sales_amt <= 10000 then 'LOW'
        when sales_amt > 10000  and sales_amt <= 100000 then 'MEDIUM'
        when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'
        else 'ULTRA'
        end
        ) virtual
        );



OLTP TABLE Comparison:

BASIC COMPARISON:

sys>>create table emp (tno number) compress basic;
sys>>create table dept (sno number) compress;


sys>>select user_table,compression,compress_for from user_tables;


OLTP compression:

sys>>create table sr1 (no number) compress for OLTP;

sys>>select user_table,compression,compress_for from user_tables;


Disable the compress the table;


sys>>alter able <tablename> nocompress;

sys>>alter table emp nocompress;