Miscellaneous

  Home arrow Miscellaneous arrow Oracle Database XE: Indexes and Sequen...
MISCELLANEOUS

Oracle Database XE: Indexes and Sequences
By: Apress Publishing
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 1
    2010-11-10

    Table of Contents:
  • Oracle Database XE: Indexes and Sequences
  • Creating and Using Sequences

  •  
     

    SEARCH CODEWALKERS

    Oracle Database XE: Indexes and Sequences


    (Page 1 of 2 )

    In this conclusion to a seven-part series on using and administering Oracle Database XE, you'll learn how to create and maintain indexes and sequences. This article excerpted from chapter 30 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

    Creating and Maintaining Indexes

    Indexes are optional data structures built on one or more columns of a table. The primary reason for creating an index on a column or columns in a table is to improve access to rows in the table. Instead of scanning all blocks in a table to find the desired row, you can access the index and read a small number of blocks from the index to find the address of the block in the table with the desired rows.

    In the following sections, we show you how to create and maintain two types of indexes: B-tree and bitmap indexes.

    Using B-tree Indexes

    B-tree indexes are the default index type; they can be unique or nonunique. Oracle uses a unique B-tree index to enforce a PRIMARY KEY constraint. You use B-tree indexes for columns with medium to high cardinality—in other words, columns with more than just a few distinct values, such as last name, or city.

    In this example, you realize that some of your queries on theCUSTOMER_ORDERtable are taking a long time because your customer service representatives are searching for orders on theORDER_DTcolumn. Since this column is not indexed, any search based on this column must read the entire table contents until the desired row is retrieved. Therefore, you decide to create an index on theORDER_DTcolumn:

    create index customer_order_ix01
         on customer_order(order_date) tablespace php_apps;

    --------------------------------------------
    Index created.
    --------------------------------------------

    The name of the index must be unique within the schema that owns the index. After you create the index, your customer service representatives don’t notice much improvement in response time, so you decide to drop the index:

    drop index customer_order_ix01;

    --------------------------------------------
    Index dropped.
    --------------------------------------------

    Using Bitmap Indexes

    Bitmap indexes, in contrast to B-tree indexes, are useful in environments where your table data is relatively static and the indexed column has a relatively low cardinality, such as gender or state code.

    You often analyze your CUSTOMER table by gender, so you create a bitmap index on the GENDER_CD column:

    create bitmap index customer_bix01 on customer(gender_cd);

    -------------------------------------------
    create bitmap index customer_bix01 on customer(gender_cd)
    *
    ERROR at line 1:
    ORA-00439: feature not enabled: Bit-mapped indexes
    --------------------------------------------

    Bitmap indexes are one of the few features not available in Oracle Database XE; until you upgrade to another version of Oracle, you create a B-tree index instead:

    create index customner_ix01 on customer(gender_cd);

    --------------------------------------------
    Index created.
    --------------------------------------------

    More Miscellaneous Articles
    More By Apress Publishing

    blog comments powered by Disqus

    MISCELLANEOUS ARTICLES

    - Attention: Forum and Site Maintenance
    - Oracle Database XE: Indexes and Sequences
    - Modifying Tables in Oracle Database XE
    - Oracle Database XE: Tables and Constraints
    - More on Oracle Databases and Datatypes
    - Oracle Database XE Datatypes: Datetime and L...
    - Oracle Database XE Datatypes: Character and ...
    - From Databases to Datatypes
    - Firefox 3.6.6 Released with Improved Plug-in...
    - Attention Bloggers: WordPress 3.0 Now Releas...
    - Reflection in PHP 5
    - Inheritance and Other Advanced OOP Features
    - Advanced OOP Features
    - Linux from Scratch V.6.6 Review
    - Linux Gaining in Strength

    Developer Shed Affiliates

     



    © 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap