Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69

    Unanswered: Composite index not used with multiple values

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    Linux


    Maybe this is my confusion of how composite indexes work on Oracle, but I have a case of where my query will utilize the index when one value is select, but as soon as multiple values exist in the IN clause it does not use the index.

    The index is on ASSIGNMENT, OPEN_TIME


    Query 1 (utilizes the index)
    Code:
    SELECT fields from TABLE
    WHERE ASSIGNMENT IN ('GROUP1') AND OPEN_TIME > sysdate -2
    Query 2 (does not utilize index)
    Code:
    SELECT fields from TABLE
    WHERE ASSIGNMENT IN ('GROUP1', 'GROUP2') AND OPEN_TIME > sysdate -2
    Total records of GROUP1 and GROUP2 are a very small percentage of the total table. I've even tried using a hint and the CBO will not use the composite index. The last time I ran into this, i basically scripted the cgi to run query1 X amount of times and UNION the results, but in this case its not an option and I really want to understand why Oracle will not use the index.


    Thanks,

    Charlie

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    WHY do you obfuscate table name is an obviously phony example?
    >I really want to understand why Oracle will not use the index.
    https://asktom.oracle.com/pls/asktom...63445044804318

    Are statistics current on both table & index?
    post DDL (CREATE INDEX) statement for the index on ASSIGNMENT, OPEN_TIME

    post results of follow SQL
    SELECT COUNT(*) FROM TABLE1;
    SELECT COUNT(*) FROM TABLE1 WHERE ASSIGNMENT = 'GROUP1';
    SELECT COUNT(*) FROM TABLE1 WHERE ASSIGNMENT = 'GROUP2';
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by PKPChuck View Post
    I really want to understand why Oracle will not use the index.
    May be the order of columns in the index is not appropriate for the query.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The optimizer decides whats the fastest way to access the records. If it thinks a FTS is fastest it will use it instead of the index. if what you say about the total records of GROUP1 and GROUP2 is correct have you gathered statistics on the table? unless it really knows the distribution of data it does the best it can.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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