Types of Btree (Descending and Key compressed) index:

The Types of BTree indexes is classified into

1)Descending index

2)Key compressed Index

3)Reverse Key Index

4)B-tree Cluster Index

5)Index-organized tables

*Let Us we discuss about Descending Index & Key Compressed Index

1)Descending Indexes:

*In B tree is a default index, the value is stored in that ascending order.

*This type of index stores data on a particular column or columns in descending order

*It contains each byte of value, numeric data from smallest to largets number and date from earliest to latest value.

Syntax:



create  index using the desc keyword



Note: In this type index, the right side leaf have the lowest value and left side leaf to have highest values

Type-1

*Descending index mostly will use for composite indexes

  
     
SQL>create index HR_REV_INDEX on hr.employees(salary,First_name,department_id)

 index created.



Now I am going  to write a query like this below

SQL> set autotrace on
SQL> select first_name,salary,department_ID from hr.employees where department_i
d=20 and salary>3000
  2     order by first_name desc,
  3      department_id asc,
  4      salary desc;

FIRST_NAME               SALARY DEPARTMENT_ID
-------------------- ---------- -------------
Pat                        6000            20
Michael                   13000            20


Execution Plan
----------------------------------------------------------
Plan hash value: 3264269043

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

--

| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time
 |

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

--

|   0 | SELECT STATEMENT  |              |     2 |    28 |     2  (50)| 00:00:01

 |

|   1 |  SORT ORDER BY    |              |     2 |    28 |     2  (50)| 00:00:01

 |

|*  2 |   INDEX RANGE SCAN| HR_REV_INDEX |     2 |    28 |     1   (0)| 00:00:01

 |

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

--


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SALARY">3000 AND "DEPARTMENT_ID"=20 AND "SALARY" IS NOT
              NULL)
       filter("DEPARTMENT_ID"=20)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        608  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed

  

Note: in above composite index we used to customize some of the order ascending and descending so the order by will take one more process

Type-2

*In that index creation time, i directly use d keyword asc and desc for my required column


SQL> Create index hr_reverse_ind on hr.employees(first_name desc,     department
_id asc,salary desc);

Index created.



SQL> select first_name,salary,department_ID from hr.employees where department_i
d=20 and salary>3000
  2    order by first_name desc,
  3          department_id asc,
  4          salary desc;

FIRST_NAME               SALARY DEPARTMENT_ID
-------------------- ---------- -------------
Pat                        6000            20
Michael                   13000            20


Execution Plan
----------------------------------------------------------
Plan hash value: 614818736

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

---

| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time
  |

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

---

|   0 | SELECT STATEMENT |                |     2 |    28 |     1   (0)| 00:00:0

1 |

|*  1 |  INDEX FULL SCAN | HR_REVERSE_IND |     2 |    28 |     1   (0)| 00:00:0

1 |

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

---


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPARTMENT_ID"=20 AND SYS_OP_DESCEND("SALARY")3000)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        608  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed





Note:Now my index range scan ignored the order by process

2)Key Compressed Index:

*This B-tree index used to eliminates duplicate occurrences of index key values in a B tree index.

*Key compression is useful when the database deals with duplicates in the index keys.

* The idea behind this technique is that every entry is broken into two pieces or components

1)Prefix - leading components of composite index

2)Suffix - Almost unique component of index entry

 

To view overAll index;




SELECT c.index_owner, c.index_name, c.table_owner, c.table_name, c.column_name, c.column_position
  FROM all_ind_columns c
 WHERE c.table_owner = 'HR'
  and c.table_name='ARROW'
 ORDER BY c.index_owner, c.index_name, c.column_position;

S-1 Create Normal Index:


SQL> create index hr_arrow_rev on hr.arrow(id,Family_name);

Index created.


SQL> analyze index hr_arrow_rev validate structure;

Index analyzed.

SQL> select name,height,LF_blks,Br_blks,BTREE_SPACE,OPT_CMPR_COUNT,OPT_CMPR_PCTS
AVE from index_stats;

NAME                               HEIGHT    LF_BLKS    BR_BLKS BTREE_SPACE  OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
------------------------------ ---------- ---------- ---------- ----------- -------------- ----------------
HR_ARROW_REV                            2          9          1       80032    2               44
             

LF_BLKS=Leaf Blocks
BTREE_SPACE=Btree occupied space



(Note: In Key compression Index we should focus This two parameter 
OPT_CMPR_COUNT=Estimated compression count which is best suitable for key compression, 
OPT_CMPR_PCTSAVE=Optimum Compression percent saved)

S-2 Create on temp table to populate index statistics:


SQL> create Table T_index as select name,height,LF_blks,Br_blks,BTREE_SPACE,OPT_
CMPR_COUNT,OPT_CMPR_PCTSAVE from index_stats;

Table created.


delete the old index:

SQL> drop index hr_arrow_rev;

Index dropped.

How does one enable compression on indexes?

*This can be achieved easily by specifying the COMPRESS option for the index.

*New indexes can be automatically created as compressed, or the existing indexes can be rebuilt compressed.

Syntax:


CREATE INDEX idxname ON tabname(col1, col2, col3) COMPRESS;

or 

CREATE INDEX idxname ON tabname(col1, col2, col3) COMPRESS [] 

* By default, the prefix consists of all indexed columns for non-unique indexes, and all indexed columns excluding the last one for unique indexes.

(Note: The after the COMPRESS keyword denotes how many columns to compress. The default (and the maximum) prefix length for a non-unique index is the number of key columns, and the maximum prefix length for a unique index is the number of key columns minus one.)



SQL> create index hr_arrow_comp on hr.arrow(id,Family_name) compress;

Index created.

SQL> analyze index hr_arrow_comp validate structure;

Index analyzed.

SQL> insert into T_index select name,height,LF_blks,Br_blks,BTREE_SPACE,OPT_CMPR
_COUNT,OPT_CMPR_PCTSAVE from index_stats;

1 row created.

SQL> select * from T_index;

NAME                               HEIGHT    LF_BLKS    BR_BLKS BTREE_SPACE  OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
------------------------------ ---------- ---------- ---------- ----------- -------------- ----------------
HR_ARROW_REV                            2          9          1       80032  2               44
             


HR_ARROW_COMP                           2          5          1       48012  2                0
             

Here compare this two index before compress and after compress no.of leaf blocks 
can be reduced and BTree space also reduced.

(Note: OPT_CMPR_PCTSAVE can be reduced from 44 into 0 its best compress save method)


  

Source: Google