Community
Application Development and Automation Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

bind variable statement in st05

Go to solution
Former Member

‎2008 Nov 11 10:17 AM

0 Likes
1,224

Hi,

I have given the following bind variable statement in st05 and clicked on explain sql.

SELECT

"BUKRS" , "VKORG" , "KUNAG" , "KUNNR" , "MATNR" , "ZZDATIN" , "ZZSTOCK"

FROM

"ZRS06"

WHERE

"MANDT" = :A0 AND "BUKRS" = :A1 AND "VKORG" = :A2 AND "KUNAG" = :A3 AND "KUNNR" = :A4 AND "WERKS" = :A5 AND "MATNR" = :A6 AND "ZZDATIN" <= :A7 OR "MANDT" = :A7 AND "BUKRS" = :A8 AND "VKORG" = :A9 AND "KUNAG" = :A10 AND "KUNNR" = :A11 AND "WERKS" = :A12 AND "MATNR" = :A13 AND "ZZDATIN" <= :A14 OR "MANDT" = :A15 AND "BUKRS" = :A16 AND "VKORG" = :A17 AND "KUNAG" = :A18 AND "KUNNR" = :A19 AND "WERKS" = :A20 AND "MATNR" = :A21 AND "ZZDATIN" <= :A22 OR "MANDT" = :A23 AND "BUKRS" = :A24

AND "VKORG" = :A25 AND "KUNAG" = :A26 AND "KUNNR" = :A27 AND "WERKS" = :A28 AND "MATNR" = :A29 AND "ZZDATIN" <= :A30 OR "MANDT" = :A31 AND "BUKRS" = :A32 AND "VKORG" = :A33 AND "KUNAG" = :A34 AND "KUNNR" = :A35 AND "WERKS" = :A36 AND "MATNR" = :A37 AND "ZZDATIN" <= :A38

then i got the follwing data.

SQL Statement

SELECT

"BUKRS" , "VKORG" , "KUNAG" , "KUNNR" , "MATNR" , "ZZDATIN" , "ZZSTOCK"

FROM

"ZRS06"

WHERE

"MANDT" = :A0 AND "BUKRS" = :A1 AND "VKORG" = :A2 AND "KUNAG" = :A3 AND "KUNNR" = :A4 AND

"WERKS" = :A5 AND "MATNR" = :A6 AND "ZZDATIN" <= :A7 OR "MANDT" = :A7 AND "BUKRS" = :A8 AND

"VKORG" = :A9 AND "KUNAG" = :A10 AND "KUNNR" = :A11 AND "WERKS" = :A12 AND "MATNR" = :A13 AND

"ZZDATIN" <= :A14 OR "MANDT" = :A15 AND "BUKRS" = :A16 AND "VKORG" = :A17 AND "KUNAG" = :A18 AND

"KUNNR" = :A19 AND "WERKS" = :A20 AND "MATNR" = :A21 AND "ZZDATIN" <= :A22 OR "MANDT" = :A23 AND

"BUKRS" = :A24 AND "VKORG" = :A25 AND "KUNAG" = :A26 AND "KUNNR" = :A27 AND "WERKS" = :A28 AND

"MATNR" = :A29 AND "ZZDATIN" <= :A30 OR "MANDT" = :A31 AND "BUKRS" = :A32 AND "VKORG" = :A33 AND

"KUNAG" = :A34 AND "KUNNR" = :A35 AND "WERKS" = :A36 AND "MATNR" = :A37 AND "ZZDATIN" <= :A38

Execution Plan

SELECT STATEMENT ( Estimated Costs = 5 , Estimated #Rows = 5 )

11 CONCATENATION

2 TABLE ACCESS BY INDEX ROWID ZRS06

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

1 INDEX RANGE SCAN ZRS06~Z00

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

Search Columns: 7

4 TABLE ACCESS BY INDEX ROWID ZRS06

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

3 INDEX RANGE SCAN ZRS06~Z00

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

Search Columns: 7

6 TABLE ACCESS BY INDEX ROWID ZRS06

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

5 INDEX RANGE SCAN ZRS06~Z00

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

Search Columns: 7

8 TABLE ACCESS BY INDEX ROWID ZRS06

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

7 INDEX RANGE SCAN ZRS06~Z00

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

Search Columns: 7

10 TABLE ACCESS BY INDEX ROWID ZRS06

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

9 INDEX RANGE SCAN ZRS06~Z00

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

Search Columns: 7

Can anyone tell me the order of these statements?and what is the meaning of table access by index rowid and index range scan?

1 ACCEPTED SOLUTION
Read only

Go to solution
Former Member

‎2008 Nov 11 11:03 AM

0 Likes
1,147

Hi,

1. execution plan

You have the execution step numbers in front of the steps and

you read the explain plan from inside to outside and from bottom to top:

> 2 TABLE ACCESS BY INDEX ROWID ZRS06 *** this is the 2nd step

> 1 INDEX RANGE SCAN ZRS06~Z00 *** this is the 1st step

You can say that 2 uses 1 to execute the step (here retrieving rowids from the index and lookup in the table)

Then it branches to

> 4 TABLE ACCESS BY INDEX ROWID ZRS06

> 3 INDEX RANGE SCAN ZRS06~Z00

..... upto

> 11 CONCATENATION

wich combines all the rows retrieved and provides them to the output buffer of the SELECT

2.Index range scan

Index range scan is a method for accessing a range values of a particular

column. AT LEAST the leading column of the index must be supplied to

access data via the index.

Can be used for range operations (e.g. > < <> >= <= between)

3. Documentation

Well, you touched the area of database optimizing (here: ORACLE)

Get yourself a free account on http://www.oracle.com/technology/index.html

and I would encourage you to step into the docs:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#721

i.e: Understanding Access Paths for the CBO

Bye

yk

7 REPLIES 7
Read only

Go to solution
Former Member

‎2008 Nov 11 11:03 AM

0 Likes
1,148

Hi,

1. execution plan

You have the execution step numbers in front of the steps and

you read the explain plan from inside to outside and from bottom to top:

> 2 TABLE ACCESS BY INDEX ROWID ZRS06 *** this is the 2nd step

> 1 INDEX RANGE SCAN ZRS06~Z00 *** this is the 1st step

You can say that 2 uses 1 to execute the step (here retrieving rowids from the index and lookup in the table)

Then it branches to

> 4 TABLE ACCESS BY INDEX ROWID ZRS06

> 3 INDEX RANGE SCAN ZRS06~Z00

..... upto

> 11 CONCATENATION

wich combines all the rows retrieved and provides them to the output buffer of the SELECT

2.Index range scan

Index range scan is a method for accessing a range values of a particular

column. AT LEAST the leading column of the index must be supplied to

access data via the index.

Can be used for range operations (e.g. > < <> >= <= between)

3. Documentation

Well, you touched the area of database optimizing (here: ORACLE)

Get yourself a free account on http://www.oracle.com/technology/index.html

and I would encourage you to step into the docs:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#721

i.e: Understanding Access Paths for the CBO

Bye

yk

Read only

Go to solution
Former Member
In response to Former Member

‎2008 Nov 12 4:25 AM

0 Likes
1,147

Hi Yukon,

Thanks for the reply.

In the above bind variable statement only zzdatin field is having <= operator.Does it mean that the table is accessed by the rowid's generated by accessing the range values of this field.

Read only

Go to solution
Former Member
In response to Former Member

‎2008 Nov 12 7:10 AM

0 Likes
1,147

> 

> Hi Yukon,
> 
> Thanks for the reply.
> 
> 
> In the above bind variable statement only zzdatin field is having <= operator.Does it mean that the table is accessed by the rowid's generated by accessing the range values of this field.

Hi,

Rowid is a ORACLE specific globally unique identifier (another term is extended or physical rowid) for a row in a database. It is created at the time the row is inserted into a table, and destroyed when it is removed from a table. It points to the row wich contains the data in the table.

It's the fastest way to retrieve a table row

Ex.: table 


ROWID                            ZZDATIN 
------------------ ---------------------------------- 
AAAAaoAATAAABrXAAA 20081111 
AAAAaoAATAAABrXAAE 20081110 
AAAAaoAATAAABrXAAG 20081109 
AAAAaoAATAAABrXAAN 20081108

An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:

  • OOOOOO: The data object number that identifies the database segment (AAAAao in the example)

( a segment could be i.e. a table or an index )

  • FFF: The tablespace-relative datafile number of the datafile that contains the row (file AAT in the example).

  • BBBBBB: The data block that contains the row (block AAABrX in the example).

  • RRR: The row in the block.

An index is storing this pointer along with the ordered values of the indexed columns.

If the field zzdatin gets selected Optimizer decides to use an index range scan (because of the <=)

The index blocks belonging to the value range are read and using the rowid's does a fast lookup into the table.

SAP provides the real values of zzdatin to the database and the database takes care of the rest (execution plan, index read, table lookup).

Did you check the original ORACLE documentation I mentioned?

You have also SAP Notes on access paths.

912620 - FAQ: Oracle Indexes

766349 - FAQ: Oracle SQL-Optimization

Bye

yk

Read only

Go to solution
Former Member

‎2008 Nov 11 12:49 PM

0 Likes
1,147 

"MANDT" = :A0 AND "BUKRS" = :A1 AND "VKORG" = :A2 AND "KUNAG" = :A3 AND
"KUNNR" = :A4 AND "WERKS" = :A5 AND "MATNR" = :A6 AND "ZZDATIN" <= :A7 OR

 "MANDT" = :A7 AND "BUKRS" = :A8 AND "VKORG" = :A9 AND "KUNAG" = :A10 AND
 "KUNNR" = :A11 AND "WERKS" = :A12 AND "MATNR" = :A13 AND "ZZDATIN" <= :A14 OR

 "MANDT" = :A15 AND "BUKRS" = :A16 AND "VKORG" = :A17 AND "KUNAG" = :A18 AND
"KUNNR" = :A19 AND "WERKS" = :A20 AND "MATNR" = :A21 AND "ZZDATIN" <= :A22 OR 

"MANDT" = :A23 AND "BUKRS" = :A24 AND "VKORG" = :A25 AND "KUNAG" = :A26 AND
"KUNNR" = :A27 AND "WERKS" = :A28 AND "MATNR" = :A29 AND "ZZDATIN" <= :A30 OR

 "MANDT" = :A31 AND "BUKRS" = :A32 AND "VKORG" = :A33 AND "KUNAG" = :A34 AND
 "KUNNR" = :A35 AND "WERKS" = :A36 AND "MATNR" = :A37 AND "ZZDATIN" <= :A38

I guess it is a FOR ALL ENTRIES, 5 blocks with 8 variables => 40 variables.

=> 5 Branches in the Explain, alway index scan and table access.

And there is a bug in the display, A7 comes twice.

Siegfried

Read only

Go to solution
Former Member
In response to Former Member

‎2008 Nov 11 2:16 PM

0 Likes
1,147

> 

> And there is a bug in the display, A7 comes twice.
> 
> Siegfried

More likely a bug in the original program.

Rob

Read only

Go to solution
Former Member
In response to Former Member

‎2008 Nov 12 4:30 AM

0 Likes
1,147

Hi,

Can anyone send me a detailed document on INDEX RANGE SCAN and INDEX UNIQUE SCAN.

Read only

Go to solution
Former Member

‎2008 Nov 12 8:11 AM

0 Likes
1,147

> 9 INDEX RANGE SCAN ZRS06~Z00

> ( Estim. Costs = 4 , Estim. #Rows = 1 )

> Search Columns: 7

check the index Z000 on your system, the optimzer uses 7 columns of this index,

=> many records fulfill condition : INDEX RANGE SCAN

=> if all fields of a unique index are specified => INDEX UNIQUE SCAN, like SELECT SINGLE