Re: Getting rid of table scan

From: <michael_bialik_at_my-deja.com>
Date: Fri, 01 Oct 1999 18:01:49 GMT
Message-ID: <7t2su3$9qo$1@nnrp1.deja.com>

Hi.

 When did you last time analyzed your tables ( at least PMT_AUDIT )?  Are the stats correct?
 Make "SELECT * FROM USER_TABLES WHERE TABLE_NAME = 'PMT_AUDIT'" and  look for NUM_ROWS value. It must be around 300K.  Anyway - when you define new indexes - you must ANALYZE them as well.

 Hope it helps. Michael.

In article <iR2J3.323$iY.445_at_198.235.216.4>,   "Jonathan Gauthier" <jonathan.gauthier_at_cgi.ca> wrote:
> 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)
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 01 1999 - 13:01:49 CDT