Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Getting rid of table scan

Re: Getting rid of table scan

From: Jonathan Gauthier <jonathan.gauthier_at_cgi.ca>
Date: Fri, 01 Oct 1999 18:15:06 GMT
Message-ID: <KS6J3.408$iY.587@198.235.216.4>


Thanks to you all for the help. The table/indexes were analyzed. The problem was fixed by creating a new index with both (pmt_agent_id and event_date).

Special thanks to Richard Sawkins

<michael_bialik_at_my-deja.com> wrote in message news:7t2su3$9qo$1_at_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:15:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US