Re: Getting rid of table scan

From: Mark G <mgumbs_at_hotmail.com>
Date: Fri, 1 Oct 1999 15:54:25 +0100
Message-ID: <37f4c889.0@145.227.194.253>

You are doing a count from that table so it has to do a full table scan by default to obtain the count anyway.

SQL> create table foo
  2 (id number primary key,
  3 name varchar2(10));

Table created.

SQL> insert into foo values (1,'abc');

1 row created.

SQL> insert into foo values (2,'def');

1 row created.

SQL> insert into foo values (3,'ghi');

1 row created.

SQL> explain plan set statement_id = 'Query 1' for   2 select count(id) from foo;

Explained.

SQL> @d:\oraclework\sql\exp_plan

Execution Plan

---

SELECT STATEMENT Cost =
  SORT AGGREGATE
    TABLE ACCESS FULL FOO Input truncated to 1 characters

HTH
....(hope i'm right!!)

M

Jonathan Gauthier <jonathan.gauthier_at_cgi.ca> wrote in message news:iR2J3.323$iY.445_at_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 - 09:54:25 CDT