Slow Fetches with hints

[ALF130277]

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

[waitecj]

Lets have a look at the explain plan.... and make sure you have analyzed first...

[WilliamR]

"THE(subquery)" has been deprecated since 8i, and you can factor out that repeat evaluation of the IN_LIST function with something like (untested):

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).

[malay_biswal]

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.