Urgent Help On Index please....
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Urgent Help On Index please....

  1. #1
    Join Date
    Jan 2002
    Posts
    59
    hi,

    i have created an index on a table as
    create index on table ( C1||C2||C3)

    and i fired a query
    select /*+indexhint too */.....where (C1||C2||C3)=(X||y||Z)

    but the explan plan shows full table scan...

    am i missing something???

    thanx in adv
    sanjay

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    because your index is b-tree and you are putting a function in your predicate, you have to use functin based indexes

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Are your tables analyzed?
    Are you retrieving < 10% of the data?
    How selective is your key?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Jan 2002
    Posts
    59
    Created an index on a table as
    create index IDX on table ( C1||C2||C3||C4)
    when i query as follows , in all cases Oracle Performs
    FULL TABLE SCAN.
    1 select C10 from table where (C1||C2||C3||C4)='xxxxx' OR
    2 select /*+ index hint */ C10 from table where (C1||C2||C3||C4)='xxxxx' OR
    3 select C10 from table where C1||C2||C3||C4='xxxxx'
    4 select C10 from table where ='xxxxx'
    PLNOTE: STATS COLLECTED FOR THE INDEX USING DBMS_STATS pkg. And
    GRANTED QUERY REWRITE TO THE USER,
    ALTER SESSION QUERY_REWRITE_ENABLED =TRUE ,
    is already done before the above query is fired.
    Could you please help me why its going for FULL TABLE SCAN.

    ** If am using query as stated in step 3 , does Function based index concept comes here?


    Thanx
    Sanjay


  5. #5
    Join Date
    Jan 2002
    Posts
    59
    Yes , the index is analyzed and table too.
    and with the where clause i am trying to retrieve only one record.
    Just to update you the Table is of 350M rows.

    Thanx
    Sanjay

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    The index wont be used if you are concatenating or using any function in where clause.
    Use FBI if you on 8i

    Sanjay

Posting Permissions

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



Click Here to Expand Forum to Full Width