Results 1 to 7 of 7

Thread: not using Index

  1. #1
    Join Date
    Oct 2003
    Location
    Canada, Quebec
    Posts
    11

    Unanswered: not using Index

    ON 7.3 HP10.20


    How can I force a query to use a Index?

    OPTCOMPIND=1
    OPTGOAL=-1
    Stats update on a weekly basis

    When I run my query for YEAR > 2002 and use the index I want and
    runs for a few seconds. It as a order by on date.

    When I run my query for YEAR > 2000 it's not using the index and takes hours to run. I get this in the explain plan for > 2000 "Temporary Files Required For: Order By"

  2. #2
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Hi,

    You may use Optimizer Directives which forces the optimizer to take a alternate path. Example:

    1.
    SELECT --+ INDEX ( orders year_index )
    *
    FROM orders
    WHERE year > 2000
    ORDER BY year

    2.
    SELECT --+ AVIOD_FULL(b), INDEX ( b year_index )
    *
    FROM customer a, orders b
    WHERE a.custno=b.custno and year > 2000
    ORDER BY year

    Please post snapshots of below for further support:
    info indexes for <tabname> ;
    sqexplain.out

    Regards,
    Shriyan

  3. #3
    Join Date
    Oct 2003
    Location
    Canada, Quebec
    Posts
    11
    Thanks Shriyan.

    I did like you mention, the explain plan shows
    that it did not use a a temporary file.

    But it also indicated "Directive not followed"
    But it did use the index instead and the query
    took about 2 second to show up instead of hours.

    So now the question is..."Drum roll"...Why?
    Why is the optimizer not picking this up?
    What should I change to be able to run my query
    without having to put --+...

  4. #4
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Alex:

    As we know, in normal circumstances the optimizer considers existing indices in the least cost method of path selection. In your case, by not selecting one, it may be behaving abnormally. But this can be confirmed only after scanning thoroghly the SQL that been fired without directive been passed. In very rare cases, the optimizer inteprets certain "good" paths as inefficient and costly and resorts to otherwise a costly or to a poor path.

    It would be very helpful if you could post the sqexplain output along with info indexes for tables involved in the query.

    Regards,
    Shriyan

  5. #5
    Join Date
    Oct 2003
    Location
    Canada, Quebec
    Posts
    11
    Here it is.....

    Header which is the main table is 1.5 million rows and result query is
    half a million.

    QUERY(BAD):
    ------
    select *
    from header, address, outer email_gen
    WHERE region_code = '1'
    AND email_gen.sma_em = 'P'
    AND header.head_number = email_gen.sma_number
    AND address.supplier_number = header.head_vendor
    AND address.address_number = header.head_vendor_addr
    AND 1=1
    and head_date > '2000/01/01'
    ORDER BY head_date

    Estimated Cost: 355796
    Estimated # of Rows Returned: 1
    Temporary Files Required For: Order By

    1) sumdba.sdf_address: SEQUENTIAL SCAN

    Filters: 1 = 1

    2) sumdba.poheader: INDEX PATH

    Filters: (sumx.header.region_code = '1' AND sumx.header.head_date > 2000/01/01 )

    (1) Index Keys: head_vendor head_vendor_addr (Serial, fragments: ALL)
    Lower Index Filter: (sumx.header.head_vendor_addr = sumx.sdf_address.address_number AND sumx.po
    header.head_vendor = sumx.address.supplier_number )
    NESTED LOOP JOIN

    3) sumy.email_gen: INDEX PATH

    (1) Index Keys: sma_number sma_em
    Lower Index Filter: (sumy.email_gen.sma_em = 'P' AND sumy.email_gen.sma_number = sumx
    .header.head_number )
    NESTED LOOP JOIN
    =============================================

    QUERY(GOOD):
    ------
    select --+ INDEX (header header_i02)
    *
    from header, address, outer email_gen
    WHERE region_code = '1'
    AND email_gen.po_sma_em = 'P'
    AND header.head_number = email_gen.sma_number
    AND address.supplier_number = header.head_vendor
    AND address.address_number = header.head_vendor_addr
    AND 1=1
    and head_date > '2000/01/01'
    ORDER BY head_date


    DIRECTIVES FOLLOWED:
    INDEX ( header header_i02 )
    DIRECTIVES NOT FOLLOWED:

    Estimated Cost: 578199
    Estimated # of Rows Returned: 1

    1) sumx.header: INDEX PATH

    Filters: (1 = 1 AND sumx.header.region_code = '1' )

    (1) Index Keys: head_date (Serial, fragments: ALL)
    Lower Index Filter: sumx.header.head_date > 2000/01/01

    2) sumx.address: INDEX PATH

    (1) Index Keys: supplier_number address_number
    Lower Index Filter: (sumx.address.address_number = sumx.poheader.head_vendor_addr AND sumx.address.supplier_number = sumx.header.head_vendor )
    NESTED LOOP JOIN

    3) sumy.email_gen: INDEX PATH

    (1) Index Keys: sma_number sma_em
    Lower Index Filter: (sumy.email_gen.sma_em = 'P' AND sumy.email_gen.sma_number = sumx
    .header.head_number )
    NESTED LOOP JOIN
    ============================================
    --Index for Header--
    Index name Owner Type Cluster Columns
    header_i02 informix dupls No head_date

  6. #6
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Alex:

    Query seems to be alright, except the join clause:

    AND 1=1

    I'm bit wary of condition, as it is always true, and I don't see any reason for specifying it. Explain suggests table header being scanned squentially, may be due to 1=1 specification. Please remove this and fire the SQL without the index directive been passed.

    Regards,
    Shriyan

  7. #7
    Join Date
    Oct 2003
    Location
    Canada, Quebec
    Posts
    11
    Yep, if I took out the 1=1 and it run fine.
    Why would someone put 1=1 in a query?

Posting Permissions

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