Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    1

    Unanswered: Index Suppressed

    I have a query which does a join on 3 tables. In a normal and simple form of the query the index is utilised .But when a column using a function is added , the index gets supressed . Why ?? The quey is
    SELECT count(*) FROM (
    SELECT account_id,
    max(start_date) start_date --(Column adding which the indexgets supressed)
    FROM Table1 t1,Table2 t2, Table3 t3
    WHERE t2.status_date=t1.status_date
    AND t2.account_id=t1.account_id
    AND t3.account_id=t2.account_id
    AND t3.status_date=t2.status_date
    AND t2.status_date=TO_DATE('31/05/2004','DD/MM/YYYY')
    GROUP BY account_id)
    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What did the query look like before? If it was this:

    SELECT count(*) FROM (
    SELECT account_id
    FROM Table1 t1,Table2 t2, Table3 t3
    WHERE t2.status_date=t1.status_date
    AND t2.account_id=t1.account_id
    AND t3.account_id=t2.account_id
    AND t3.status_date=t2.status_date
    AND t2.status_date=TO_DATE('31/05/2004','DD/MM/YYYY')
    GROUP BY account_id)

    and if that index is on columns account_id and status_date, then the query could be answered from the index alone; add max(start_date) and if start_date is not in the index then you are going to need to access the table - in which case the optimizer may decide to do a full table scan.

    If that isn't what happened, then more info is required to help you:
    1) What was the query before?
    2) Which table are you selecting account_id and start_date from?
    3) What is the index that was being used?
    ... followed by supplementary questions about the cardinality of the tables etc.

Posting Permissions

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