1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

USe of Index in SQL*PLUS

Discussion in 'SQL PL/SQL' started by fahad08, Oct 19, 2013.

  1. fahad08

    fahad08 Active Member

    Messages:
    4
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Chittagong, Bangladesh
    Please, anybody tell me about the details of INDEX. Why to create it and what is the benefit of it , in details. I am now doing my SQL*PLUS query and reached to INDEX topics.

    Thanks in Advance.... :)
     
  2. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    I was not able to understand what do you mean by SQL*Plus query, maybe you wanted to talk about SQL query?

    Anyway, Indexes are explained in detail at Oracle Database Concepts online book. Here is the link to the Indexes and Index-Organized Tables chapter

    Also there is a very interesting book that goes in depth about indexes and performance related issues: Expert Indexing in Oracle Database 11g Maximum Performance for your Database By Darl Kuhn , Sam Alapati , Bill Padfield . This is a very informative and complete book (in my opinion).

    Another book (which is a must for everyone IMHO, no matter what one may want to do in oracle, everyone should read this): Expert Oracle Database Architecture Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions by Thomas Kyte
     
    fahad08 likes this.
  3. bkoniki

    bkoniki Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Also a book by Guy Harrison "Oracle Performance Survival Guide A Systematic Approach to Database Optimization" is very good.
     
  4. bkoniki

    bkoniki Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hi

    Introduction
    Oracle database is a tool to help do things better in real life. Same goes with indexes. Let us take slight detour into real like to understand index concept.
    Supposed you are maintaining a book about friend’s details. Every page contains one friend details.Over the time you may be camping in the bush with only a bunch of total friends, playing cricket with another bunch of friends.You raised your hand to be the team leader for camping group. You may be maintain a separate note somewhere with heading "Camping friends" and maintaining details like friend's name and Master diary page number which contains full details of that friend. This act of maintaining a secondary information as an aid in quickly finding location of master data is called index. Without this index, you are forced to browse through Master dairy page by page until you find friend's entry.
    Let us talk now about indexes by using oracle sample schema.

    Prerequisites:
    1) Oracle Samples schemas like 'HR' is available in your database.
    ( There is a checkbox available to select Samples schemas while creating a new database with the help of Database Configuration Assistant)
    2) plan_table is available

    Details about HR schema.
    We will concentrate only 2 tables, namely employees, departments
    1. Details about employees table:
    SQL> desc employees
    Name Type Nullable Default Comments
    -------------- ------------ -------- ------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    EMPLOYEE_ID NUMBER(6) Primary key of employees table.
    FIRST_NAME VARCHAR2(20) Y First name of the employee. A not null column.
    LAST_NAME VARCHAR2(25) Last name of the employee. A not null column.
    EMAIL VARCHAR2(25) Email id of the employee
    PHONE_NUMBER VARCHAR2(20) Y Phone number of the employee; includes country code and area code
    HIRE_DATE DATE Date when the employee started on this job. A not null column.
    JOB_ID VARCHAR2(10) Current job of the employee; foreign key to job_id column of the
    jobs table. A not null column.
    SALARY NUMBER(8,2) Y Monthly salary of the employee. Must be greater
    than zero (enforced by constraint emp_salary_min)
    COMMISSION_PCT NUMBER(2,2) Y Commission percentage of the employee; Only employees in sales
    department elgible for commission percentage
    MANAGER_ID NUMBER(6) Y Manager id of the employee; has same domain as manager_id in
    departments table. Foreign key to employee_id column of employees table.
    (useful for reflexive joins and CONNECT BY query)
    DEPARTMENT_ID NUMBER(4) Y Department id where employee works; foreign key to department_id
    column of the departments table
    2. Details about departments table:
    SQL> desc departments
    Name Type Nullable Default Comments
    --------------- ------------ -------- ------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    DEPARTMENT_ID NUMBER(4) Primary key column of departments table.
    DEPARTMENT_NAME VARCHAR2(30) A not null column that shows name of a department. Administration,
    Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
    Relations, Sales, Finance, and Accounting.
    MANAGER_ID NUMBER(6) Y Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.
    LOCATION_ID NUMBER(4) Y Location id where a department is located. Foreign key to location_id column of locations table.


    Indexes on tables:
    select t.COLUMN_NAME,t.COLUMN_POSITION,t.INDEX_NAME
    from sys.all_ind_columns t
    where t.TABLE_NAME=upper('employees');

    COLUMN_NAME COLUMN_POSITION INDEX_NAME
    -------------------------------------------------------------------------------- --------------- ------------------------------
    EMAIL 1 EMP_EMAIL_UK
    EMPLOYEE_ID 1 EMP_EMP_ID_PK
    DEPARTMENT_ID 1 EMP_DEPARTMENT_IX
    JOB_ID 1 EMP_JOB_IX
    MANAGER_ID 1 EMP_MANAGER_IX
    LAST_NAME 1 EMP_NAME_IX
    FIRST_NAME 2 EMP_NAME_IX

    7 rows selected

    2.select t.COLUMN_NAME,t.COLUMN_POSITION,t.INDEX_NAME
    from sys.all_ind_columns t
    where t.TABLE_NAME=upper('departments');

    COLUMN_NAME COLUMN_POSITION INDEX_NAME
    -------------------------------------------------------------------------------- --------------- ------------------------------
    DEPARTMENT_ID 1 DEPT_ID_PK
    LOCATION_ID 1 DEPT_LOCATION_IX

    By this stage we know details about tables and their index details. We use 'explain plan' command from SQL*Plus environment to see the plan details about oracle is going to
    execute the command. This command stores the plan details in plan_table. With a simple query we can see nice output about the execution plan. I use following query to fetch data from plan table:
    SELECT rtrim(lpad(' ', 2 * LEVEL) || rtrim(operation) || ' ' || '::' || rtrim(options) || ' ' || ':' || object_name) query_plan,
    cost,
    cardinality rows1
    FROM plan_table
    CONNECT BY PRIOR id = parent_id
    START WITH id = 0;

    Queries:
    Now let us play with some queries and see how oracle is planning to fetch data.
    Q1:
    explain plan for
    select e.employee_id,e.first_name,e.last_name,e.hire_date,e.department_id
    from hr.employees e
    where e.employee_id=100;

    Explained
    SELECT rtrim(lpad(' ', 2 * LEVEL) || rtrim(operation) || ' ' || '::' || rtrim(options) || ' ' || ':' || object_name) query_plan,
    cost,
    cardinality rows1
    FROM plan_table
    CONNECT BY PRIOR id = parent_id
    START WITH id = 0;

    QUERY_PLAN COST ROWS1
    -------------------------------------------------------------------------------- --------------------------------------- ---------------------------------------
    SELECT STATEMENT :: : 1 1
    TABLE ACCESS ::BY INDEX ROWID :EMPLOYEES 1 1
    INDEX ::UNIQUE SCAN :EMP_EMP_ID_PK 0 1
    rollback;
    Comments=>
    1) Oracle is using index: EMP_EMP_ID_PK
    Oracle will directly consult index:EMP_EMP_ID_PK. Oracle qill quickly find entry where employee_id=100 by help B*Tree index (default index type created by oracle).
    It find RowId from the index entry and now consult table:EMPLOYEES to fetch the record with given RowId.

    Q2:
    Commit complete
    explain plan for
    select e.employee_id,e.first_name,e.last_name,e.hire_date,e.department_id
    from hr.employees e
    where e.last_name='Chen';

    Explained
    SELECT rtrim(lpad(' ', 2 * LEVEL) || rtrim(operation) || ' ' || '::' || rtrim(options) || ' ' || ':' || object_name) query_plan,
    cost,
    cardinality rows1
    FROM plan_table
    CONNECT BY PRIOR id = parent_id
    START WITH id = 0;

    QUERY_PLAN COST ROWS1
    -------------------------------------------------------------------------------- --------------------------------------- ---------------------------------------
    SELECT STATEMENT :: : 2 1
    TABLE ACCESS ::BY INDEX ROWID :EMPLOYEES 2 1
    INDEX ::RANGE SCAN :EMP_NAME_IX 1 1
    rollback;

    Comments=>
    1) Oracle is using index: EMP_NAME_IX. This is possible because LAST_NAME is the first column in the concatenated index and oracle find no problem in finding the row.

    Q3:
    Commit complete
    explain plan for
    select e.employee_id,e.first_name,e.last_name,e.hire_date,e.department_id
    from hr.employees e
    where e.first_name='Alexander';

    Explained
    SELECT rtrim(lpad(' ', 2 * LEVEL) || rtrim(operation) || ' ' || '::' || rtrim(options) || ' ' || ':' || object_name) query_plan,
    cost,
    cardinality rows1
    FROM plan_table
    CONNECT BY PRIOR id = parent_id
    START WITH id = 0;

    QUERY_PLAN COST ROWS1
    -------------------------------------------------------------------------------- --------------------------------------- ---------------------------------------
    SELECT STATEMENT :: : 3 1
    TABLE ACCESS ::FULL :EMPLOYEES 3 1
    rollback;

    Comments=>
    This is a good example. Oracle has decided to use full table scan instead of using any indexes. There is an index:EMP_NAME_IX and first_name is the second index column.
    This is exactly the cause of the problem. Oracle doesn't know how to use an concatenated index , if the given column is not the first column. May be we need a separate index
    only on the first_name column.

    Conclusions:
    1.Concept of index is a special branch in it self.
    2.Indexes are essential for querying the database in an optimal way to save time and conserve resources.
    3.As an Oracle DBA or as a PL/SQL Developer once should have through knowledge about indexes.
    4. Oracle index plays major role in database tuning process.
    5.Maintaining indexes is an ongoing process.

    Cheers
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    "2.Indexes are essential for querying the database in an optimal way to save time and conserve resources."

    That is a false statement. Indexes are not essential and can sometimes use MORE resources. There are situations where a full table scan is more efficient than an index scan, especially when more than 30% of the table data is being returned.

    "This is exactly the cause of the problem. Oracle doesn't know how to use an concatenated index , if the given column is not the first column. May be we need a separate index "

    Incorrect as from release 10.2 Oracle can, and will, issue an index skip scan to read an index and skip the leading column.

    Your example and explanation are incorrect as new features and new functionality in Oracle releases allow the database to perform index skip scans, among other things. Also Exadata provides exceptional performance and puts the index myths you state to rest.

    Please keep up with the Oracle capabilities; it will save you from posting incorrect information.
     
    fahad08 likes this.