Re: Getting rid of table scan

What is the TYPE of agent_id ?
have you analyzed the tables ?
How many different agent_ids are there in the table ? Have you tried a histogram on the agent_id column ?

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Jonathan Gauthier wrote in message ...
>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 - 12:17:32 CDT