Results 1 to 11 of 11

Thread: Indexes

  1. #1
    Join Date
    Sep 2014
    Posts
    8

    Answered: Indexes

    Hi There;
    I am reading a paragraph about the indexes, and I can not understand a thing.
    Here is the paragraph from the oracle docs:

    CREATE INDEX employees_ix
    ON employees (last_name, job_id, salary);

    Queries that access all three columns, only the last_name column, or only the last_name and job_id columns use this index. In this example, queries that do not access the last_name column do not use the index.
    What if queries that do not access the job_id, salary?

  2. Best Answer
    Posted by healdem

    "erm no
    the index will be used if:-
    last_name is in the where or join
    ... or last_name and job_ID
    ... or last_name and job_ID and salary
    but that index won't be used is last_name isn't in the join or where

    ..or at least thats my understanding of index usage. although each sql engine uses its own methodology on query parsing I doubt there will be some fiendishly clever trick that Oracle can use here "


  3. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Index is considered only for columns in the WHERE clause.

    SELECT DEPARTMENT FROM EMPLOYEES WHERE LAST_NAME= 'JONES';

    for query above the index EXPLOYEES_IX will be used to return the result set.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #3
    Join Date
    May 2014
    Location
    World Wide On The Web
    Posts
    16
    Quote Originally Posted by tsozgen View Post
    What if queries that do not access the job_id, salary?
    You mean if the query predicate doesn't have job_id and salary? Then there could be just two possibilities :

    1. You have last_name in the predicate, and it will use the index.

    Or,

    2. You don't have last_name in the predicate, which means you don't have any of the three columns of your composite index, hence there is no question about index usage.

    Regards,
    Lalit

  5. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    also, notice the order of the index (last_name, job_id, salary). If your where clause users last_name it can use the index. If it only referenced job_id or salary oracle would have do do a full table scan to find the records.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #5
    Join Date
    May 2014
    Location
    World Wide On The Web
    Posts
    16
    Quote Originally Posted by beilstwh View Post
    If your where clause users last_name it can use the index. If it only referenced job_id or salary oracle would have do do a full table scan to find the records.
    Also, index will be used when where clause has last_name and job_id.

  7. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    erm no
    the index will be used if:-
    last_name is in the where or join
    ... or last_name and job_ID
    ... or last_name and job_ID and salary
    but that index won't be used is last_name isn't in the join or where

    ..or at least thats my understanding of index usage. although each sql engine uses its own methodology on query parsing I doubt there will be some fiendishly clever trick that Oracle can use here
    I'd rather be riding on the Tiger 800 or the Norton

  8. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I'm not positive in Oracle, but in DB2, I am sure that you can have what is called a NON-Matching index access. Continuing with current example:

    Code:
    select last_name
        from employees
    where job_id = ?
    --you could even add in
    and salary >= ???
    In DB2 the access path here would be non-matching index only. would mean you read every row in the index, checking each job id, but you do not touch the data page since there is no need for any additional data.
    Dave

  9. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In general, an index is a listing of the values (in this case Last_Name, Job_ID, and Salary) sorted in that order. If you are old enough to remember phone books, think of how that is ordered by last name, first name, then middle initial. That is in effect what an index looks like (although without the address and phone number).

    Now with that image in mind, why would it be difficult for you to extract all people with a first name of "John" from a phone book?
    Last edited by MCrowley; 09-09-14 at 17:15.

  10. #9
    Join Date
    Sep 2014
    Posts
    8
    I would like to thank the everybody whom answers the question. I mostly understand the issue. I try to say, whether the query :

    select * from employee where last_name = 'gold' and salary = 35000

    uses the index or not.

  11. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by dav1mo View Post
    I'm not positive in Oracle, but in DB2, I am sure that you can have what is called a NON-Matching index access. Continuing with current example:

    Code:
    select last_name
        from employees
    where job_id = ?
    --you could even add in
    and salary >= ???
    In DB2 the access path here would be non-matching index only. would mean you read every row in the index, checking each job id, but you do not touch the data page since there is no need for any additional data.
    oracle has the same thing if all the data you want is in the index, it will scan the index.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  12. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by tsozgen View Post
    I would like to thank the everybody whom answers the question. I mostly understand the issue. I try to say, whether the query :

    select * from employee where last_name = 'gold' and salary = 35000

    uses the index or not.
    If the data is not severely skewed with a lot of values in the last_name column that equal 'gold', and the table is big enough to make indexes worth considering (on the order of thousands of rows), then yes, it will likely use the index. There is actually a lot behind indexes, so you will want to test your queries to see if they benefit, or not.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •