Slow Fetches with hints
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Slow Fetches with hints

  1. #1
    Join Date
    Oct 2005
    Posts
    1

    Slow Fetches with hints

    I am trying to tune the following sql statement.

    SELECT /*+ INDEX (F003 F003_FORENAME_FIRST_ALIAS_INDX) */
    f001_san,
    f003_san,
    alias_alias_name,
    forename_first_alias,
    forename_second_alias,
    surname_alias,
    maidenname_alias,
    alias_alias_dob,
    alias_dob1,
    alias_dob2,
    alias_dob_count,
    alias_alias_dob_is_estimate
    FROM f003
    WHERE ( forename_first_alias IN ( select * from THE ( select cast( in_list('JOHN,JHON') as mytableType ) from dual ) )
    AND ( surname_alias IN ( select * from THE ( select cast( in_list('SMITH,SMYTH') as mytableType ) from dual ) )
    OR maidenname_alias IN ( select * from THE ( select cast( in_list('SMITH,SMYTH') as mytableType ) from dual ) ) ) )

    I am using the hint to use the index to reduce the execution time of the query but I am still getting a very slow fetch time (see below).



    call count cpu elapsed disk query current rows

    Parse 1 0.01 0.01 0 21 0 0
    Execute 1 0.00 0.00 0 6 0 0
    Fetch 59 5.92 12.72 16830 306094 0 867

    total 61 5.93 12.73 16830 306121 0 867

    Does anyone know why this fetch time is so great and what can be done to reduce it?

    Regards

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Lets have a look at the explain plan.... and make sure you have analyzed first...
    Assistance is Futile...

  3. #3
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    "THE(subquery)" has been deprecated since 8i, and you can factor out that repeat evaluation of the IN_LIST function with something like (untested):

    Code:
    WITH names AS
         ( SELECT column_name AS name
           FROM   TABLE(in_list('JOHN,JHON') ))
    SELECT /*+ INDEX (F003 F003_FORENAME_FIRST_ALIAS_INDX) */
           f001_san
         , f003_san
         , alias_alias_name
         , forename_first_alias
         , forename_second_alias
         , surname_alias
         , maidenname_alias
         , alias_alias_dob
         , alias_dob1
         , alias_dob2
         , alias_dob_count
         , alias_alias_dob_is_estimate
    FROM   f003 f
    WHERE  f.forename_first_alias IN
           ( SELECT name FROM names )
    AND    (   f.surname_alias IN
               ( SELECT name FROM names )
            OR f.maidenname_alias IN
               ( SELECT name FROM names ) );
    however that isn't likely to be the problem. As mentioned above, the question you have to ask yourself is "why is the execution plan not the best despite the tables being analyzed?" You haven't mentioned what the execution plan is, why you think it's not so good, or whether the tables have been analyzed (or how).

  4. #4
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Did you check if the full table scan is better than the index scan or not. Normally index scan is better when less than 20% of records retrieved by the query. Your query seems to me it's retrieving lot of records.
    http://www.perf-engg.com
    A performance engineering forum

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