Getting rid of table scan

From: Jonathan Gauthier <jonathan.gauthier_at_cgi.ca>
Date: Fri, 01 Oct 1999 13:40:30 GMT
Message-ID: <iR2J3.323$iY.445@198.235.216.4>

I'm trying to get rid of a table scan but even adding indexes to the tables involved does not solve my problem. I can't change the SQL statement because it is generated by an application and I don't have access to the source code:

Here is the information(statement, indexes, explain plan): If anyone got an idea how to force the SQL to use the indexes on table pmt_audit without changing the SQL statement help would be greatly appreciated

select count(distinct t22.pmt_instruction_id) from omtxdba.pmt_audit t22, omtxdba.pmt_instruction t23 where t22.pmt_agent_id = 'de09d220165511d185f5b747339cf6a9'

and t23.pmt_audit_id = t22.pmt_audit_id
and t22.pmt_instruction_id = t23.pmt_instruction_id
and t22.request_type = 3110 and t22.error_category = 902
and t23.pmt_error = 902 and t22.event_date >= 937769520
and t22.event_date <= 938723520

/

rem INDEX_NAME                     COLUMN_NAME

COLUMN_POSITION

rem ------------------------------ ------------------------------ ----------


-----


rem PMT_AUDIT_PK                   PMT_AUDIT_ID
1
rem PMT_AUDIT_PMT_AGENT_ID_I       PMT_AGENT_ID
1
rem PMT_AUDIT_PMT_INSTRUCTIO_I     PMT_INSTRUCTION_ID
1
rem PMT_AUDIT_REQUEST_TYPE         REQUEST_TYPE

1

rem INDEX_NAME                     COLUMN_NAME

COLUMN_POSITION

rem ------------------------------ ------------------------------ ----------


-----


rem PMT_INSTRUCTION_PK             PMT_INSTRUCTION_ID

1
rem PMT_INSTRUCTI_PMT_INSTRUCTIO_I PMT_INSTRUCTION_SEQID 1
rem PMT_INSTRUCTI_SELLER_ACCOUNT_I SELLER_ACCOUNT_ID 1

rem Rows Execution Plan

rem -------  ---------------------------------------------------
rem       0  SELECT STATEMENT   GOAL: CHOOSE
rem       1   SORT (GROUP BY)
rem       1    NESTED LOOPS
rem  380326     TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'PMT_AUDIT'
rem       2     TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF
rem                 'PMT_INSTRUCTION'
rem       2      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
rem                  'PMT_INSTRUCTION_PK' (UNIQUE)

Received on Fri Oct 01 1999 - 08:40:30 CDT