topic Re: SQl excecute plan in Application Development and Automation Discussions

SQl excecute plan

Former Member — Tue, 27 Dec 2005 08:10:12 GMT

SQL Statement

SELECT

"VBELN" , "PARVW" , "KUNNR"

FROM

"VBPA"

WHERE

"MANDT" = '210' AND "VBELN" IN ( '400000','400019','400019','400066','400066','500000' ) AND (

"PARVW" = 'AG' OR "PARVW" = 'RE' )

Execution Plan

SELECT STATEMENT ( Estimated Costs = 1 , Estimated #Rows = 8 )

3 INLIST ITERATOR

2 TABLE ACCESS BY INDEX ROWID VBPA

( Estim. Costs = 1 , Estim. #Rows = 8 )

1 INDEX RANGE SCAN VBPA~Z01

( Estim. Costs = 2 , Estim. #Rows = 8 )

Search Columns: 3

<b>Could you give me a explanation of this excecute plan aboved ?</b>

Re: SQl excecute plan

Former Member — Tue, 27 Dec 2005 08:31:07 GMT

Hi guixin,

1. SELECT STATEMENT ( Estimated Costs = 1 , Estimated #Rows = 8 )

It says that the command given to the

database is a select statement.

(other commands like insert, delete are also possible)

2.INLIST ITERATOR

The database has to do some additional

process based upon the SQL.

The sql contains an IN statement.

VBELN" IN ( '400000','400019','400019','400066','400066','500000'

3. TABLE ACCESS BY INDEX ROWID VBPA

( Estim. Costs = 1 , Estim. #Rows = 8 )

The database has accessed the table

and used an index available on the table

instead of FULL SCAN on the table.

4. 1 INDEX RANGE SCAN VBPA~Z01

( Estim. Costs = 2 , Estim. #Rows = 8 )

Search Columns: 3

Based upon the index, the database

scanned the table based upon rowid

and as required, searched for 3 columns.

regards,

amit m.

Re: SQl excecute plan

Former Member — Tue, 27 Dec 2005 11:21:26 GMT

HI:

Could you explain "Estim. Costs = 2 , Estim. #Rows = 8"

Re: SQl excecute plan

Former Member — Tue, 27 Dec 2005 11:39:49 GMT

Hi again,

Estimated Cost: 2

The estimated cost is a number assigned to the query and is

primarily used as a comparison to other query paths. When this

query is run over and over again this number may vary slightly.

This number is useful when a change is made to the query (i.e.

adding an index) and it drops significantly.

Estimated # of Rows Returned: 8

The estimated number of rows returned is only an estimate. There are queries which returns thousands of rows and this number

is only 2 or 3.

regards,

amit m.

Re: SQl excecute plan

Former Member — Tue, 27 Dec 2005 12:48:28 GMT

Hi,

pls reward points if u have find any answers helpful.

regards,

amit m.