topic Re: bind variable statement in st05 in Application Development and Automation Discussions

topic Re: bind variable statement in st05 in Application Development and Automation Discussions https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738783#M1111808 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>Can anyone send me a detailed document on INDEX RANGE SCAN and INDEX UNIQUE SCAN.</P></BODY></HTML> Wed, 12 Nov 2008 04:30:33 GMT Former Member 2008-11-12T04:30:33Z bind variable statement in st05 https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738778#M1111803 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>I have given the following bind variable statement in st05 and clicked on explain sql.</P><P></P><P>SELECT</P><P></P><P>"BUKRS" , "VKORG" , "KUNAG" , "KUNNR" , "MATNR" , "ZZDATIN" , "ZZSTOCK"</P><P></P><P>FROM</P><P></P><P>"ZRS06"</P><P></P><P>WHERE</P><P></P><P>"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 </P><P>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</P><P></P><P>then i got the follwing data.</P><P></P><P>SQL Statement</P><P></P><P>SELECT</P><P> "BUKRS" , "VKORG" , "KUNAG" , "KUNNR" , "MATNR" , "ZZDATIN" , "ZZSTOCK"</P><P>FROM</P><P> "ZRS06"</P><P>WHERE</P><P> "MANDT" = :A0 AND "BUKRS" = :A1 AND "VKORG" = :A2 AND "KUNAG" = :A3 AND "KUNNR" = :A4 AND</P><P> "WERKS" = :A5 AND "MATNR" = :A6 AND "ZZDATIN" <= :A7 OR "MANDT" = :A7 AND "BUKRS" = :A8 AND</P><P> "VKORG" = :A9 AND "KUNAG" = :A10 AND "KUNNR" = :A11 AND "WERKS" = :A12 AND "MATNR" = :A13 AND</P><P> "ZZDATIN" <= :A14 OR "MANDT" = :A15 AND "BUKRS" = :A16 AND "VKORG" = :A17 AND "KUNAG" = :A18 AND</P><P> "KUNNR" = :A19 AND "WERKS" = :A20 AND "MATNR" = :A21 AND "ZZDATIN" <= :A22 OR "MANDT" = :A23 AND</P><P> "BUKRS" = :A24 AND "VKORG" = :A25 AND "KUNAG" = :A26 AND "KUNNR" = :A27 AND "WERKS" = :A28 AND</P><P> "MATNR" = :A29 AND "ZZDATIN" <= :A30 OR "MANDT" = :A31 AND "BUKRS" = :A32 AND "VKORG" = :A33 AND</P><P> "KUNAG" = :A34 AND "KUNNR" = :A35 AND "WERKS" = :A36 AND "MATNR" = :A37 AND "ZZDATIN" <= :A38</P><P></P><P></P><P>Execution Plan</P><P></P><P></P><P></P><P></P><P> SELECT STATEMENT ( Estimated Costs = 5 , Estimated #Rows = 5 )</P><P></P><P> 11 CONCATENATION</P><P></P><P> 2 TABLE ACCESS BY INDEX ROWID ZRS06</P><P> ( Estim. Costs = 1 , Estim. #Rows = 1 )</P><P></P><P> 1 INDEX RANGE SCAN ZRS06~Z00</P><P> ( Estim. Costs = 4 , Estim. #Rows = 1 )</P><P> Search Columns: 7</P><P></P><P> 4 TABLE ACCESS BY INDEX ROWID ZRS06</P><P> ( Estim. Costs = 1 , Estim. #Rows = 1 )</P><P></P><P> 3 INDEX RANGE SCAN ZRS06~Z00</P><P> ( Estim. Costs = 4 , Estim. #Rows = 1 )</P><P> Search Columns: 7</P><P></P><P> 6 TABLE ACCESS BY INDEX ROWID ZRS06</P><P> ( Estim. Costs = 1 , Estim. #Rows = 1 )</P><P></P><P> 5 INDEX RANGE SCAN ZRS06~Z00</P><P> ( Estim. Costs = 4 , Estim. #Rows = 1 )</P><P> Search Columns: 7</P><P></P><P> 8 TABLE ACCESS BY INDEX ROWID ZRS06</P><P> ( Estim. Costs = 1 , Estim. #Rows = 1 )</P><P></P><P> 7 INDEX RANGE SCAN ZRS06~Z00</P><P> ( Estim. Costs = 4 , Estim. #Rows = 1 )</P><P> Search Columns: 7</P><P></P><P> 10 TABLE ACCESS BY INDEX ROWID ZRS06</P><P> ( Estim. Costs = 1 , Estim. #Rows = 1 )</P><P></P><P> 9 INDEX RANGE SCAN ZRS06~Z00</P><P> ( Estim. Costs = 4 , Estim. #Rows = 1 )</P><P> Search Columns: 7</P><P></P><P></P><P>Can anyone tell me the order of these statements?and what is the meaning of table access by index rowid and index range scan?</P></BODY></HTML> Tue, 11 Nov 2008 10:17:53 GMT https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738778#M1111803 Former Member 2008-11-11T10:17:53Z Re: bind variable statement in st05 https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738779#M1111804 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P><STRONG>1. execution plan</STRONG></P><P> </P><P>You have the execution step numbers in front of the steps and </P><P>you read the explain plan from inside to outside and from bottom to top:</P><P></P><P></P><P>> 2 TABLE ACCESS BY INDEX ROWID ZRS06 *** this is the 2nd step</P><P>> 1 INDEX RANGE SCAN ZRS06~Z00 *** this is the 1st step</P><P></P><P></P><P>You can say that 2 uses 1 to execute the step (here retrieving rowids from the index and lookup in the table)</P><P></P><P></P><P>Then it branches to </P><P></P><P>> 4 TABLE ACCESS BY INDEX ROWID ZRS06</P><P>> 3 INDEX RANGE SCAN ZRS06~Z00</P><P></P><P>..... upto</P><P></P><P>> 11 CONCATENATION</P><P></P><P>wich combines all the rows retrieved and provides them to the output buffer of the SELECT</P><P></P><P></P><P><STRONG>2.Index range scan</STRONG></P><P></P><P>Index range scan is a method for accessing a range values of a particular</P><P>column. AT LEAST the leading column of the index must be supplied to </P><P>access data via the index.</P><P>Can be used for range operations (e.g. > < <> >= <= between)</P><P></P><P></P><P><STRONG>3. Documentation</STRONG></P><P></P><P>Well, you touched the area of database optimizing (here: ORACLE)</P><P></P><P>Get yourself a free account on <A href="http://www.oracle.com/technology/index.html" target="test_blank">http://www.oracle.com/technology/index.html</A></P><P>and I would encourage you to step into the docs:</P><P></P><P><A href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#721" target="test_blank">http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#721</A></P><P></P><P>i.e: Understanding Access Paths for the CBO</P><P></P><P>Bye</P><P></P><P>yk</P></BODY></HTML> Tue, 11 Nov 2008 11:03:43 GMT https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738779#M1111804 Former Member 2008-11-11T11:03:43Z Re: bind variable statement in st05 https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738780#M1111805 <HTML><HEAD></HEAD><BODY><PRE><CODE> "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 </CODE></PRE><P></P><P>I guess it is a FOR ALL ENTRIES, 5 blocks with 8 variables => 40 variables.</P><P></P><P>=> 5 Branches in the Explain, alway index scan and table access.</P><P></P><P>And there is a bug in the display, A7 comes twice.</P><P></P><P>Siegfried</P></BODY></HTML> Tue, 11 Nov 2008 12:49:59 GMT https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738780#M1111805 Former Member 2008-11-11T12:49:59Z Re: bind variable statement in st05 https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738781#M1111806 <HTML><HEAD></HEAD><BODY><P>> </P><PRE><CODE><P></P><P>> And there is a bug in the display, A7 comes twice.</P><P>> </P><P>> Siegfried</P></CODE></PRE><P>More likely a bug in the original program.</P><P></P><P>Rob</P></BODY></HTML> Tue, 11 Nov 2008 14:16:44 GMT https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738781#M1111806 Former Member 2008-11-11T14:16:44Z Re: bind variable statement in st05 https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738782#M1111807 <HTML><HEAD></HEAD><BODY><P>Hi Yukon,</P><P></P><P>Thanks for the reply.</P><P></P><P></P><P>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.</P></BODY></HTML> Wed, 12 Nov 2008 04:25:13 GMT https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738782#M1111807 Former Member 2008-11-12T04:25:13Z Re: bind variable statement in st05 https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738783#M1111808 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>Can anyone send me a detailed document on INDEX RANGE SCAN and INDEX UNIQUE SCAN.</P></BODY></HTML> Wed, 12 Nov 2008 04:30:33 GMT https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738783#M1111808 Former Member 2008-11-12T04:30:33Z Re: bind variable statement in st05 https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738784#M1111809 <HTML><HEAD></HEAD><BODY><P>> </P><PRE><CODE><P></P><P>> Hi Yukon,</P><P>> </P><P>> Thanks for the reply.</P><P>> </P><P>> </P><P>> 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.</P></CODE></PRE><P></P><P></P><P></P><P>Hi,</P><P></P><P>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.</P><P>It's the fastest way to retrieve a table row</P><P></P><P>Ex.: table </P><P></P><PRE><CODE> ROWID ZZDATIN ------------------ ---------------------------------- AAAAaoAATAAABrXAAA 20081111 AAAAaoAATAAABrXAAE 20081110 AAAAaoAATAAABrXAAG 20081109 AAAAaoAATAAABrXAAN 20081108 </CODE></PRE><P> </P><P>An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:</P><P></P><P> </P><UL><LI level="1" type="ul"><P>OOOOOO: The data object number that identifies the database segment (AAAAao in the example)</P></LI></UL><P> ( a segment could be i.e. a table or an index )</P><P> </P><UL><LI level="1" type="ul"><P>FFF: The tablespace-relative datafile number of the datafile that contains the row (file AAT in the example).</P></LI></UL><P> </P><UL><LI level="1" type="ul"><P>BBBBBB: The data block that contains the row (block AAABrX in the example). </P></LI></UL><P> </P><UL><LI level="1" type="ul"><P>RRR: The row in the block.</P></LI></UL><P></P><P></P><P>An index is storing this pointer along with the ordered values of the indexed columns.</P><P></P><P>If the field zzdatin gets selected Optimizer decides to use an index range scan (because of the <=)</P><P>The index blocks belonging to the value range are read and using the rowid's does a fast lookup into the table.</P><P></P><P>SAP provides the real values of zzdatin to the database and the database takes care of the rest (execution plan, index read, table lookup).</P><P></P><P>Did you check the original ORACLE documentation I mentioned?</P><P></P><P>You have also SAP Notes on access paths.</P><P></P><P>912620 - FAQ: Oracle Indexes</P><P>766349 - FAQ: Oracle SQL-Optimization</P><P></P><P>Bye</P><P></P><P>yk</P></BODY></HTML> Wed, 12 Nov 2008 07:10:05 GMT https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738784#M1111809 Former Member 2008-11-12T07:10:05Z Re: bind variable statement in st05 https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738785#M1111810 <HTML><HEAD></HEAD><BODY><P>> 9 INDEX RANGE SCAN ZRS06~Z00</P><P>> ( Estim. Costs = 4 , Estim. #Rows = 1 )</P><P>> Search Columns: 7</P><P></P><P>check the index Z000 on your system, the optimzer uses 7 columns of this index, </P><P></P><P>=> many records fulfill condition : INDEX RANGE SCAN</P><P></P><P>=> if all fields of a unique index are specified => INDEX UNIQUE SCAN, like SELECT SINGLE</P></BODY></HTML> Wed, 12 Nov 2008 08:11:42 GMT https://community.sap.com/t5/application-development-and-automation-discussions/bind-variable-statement-in-st05/m-p/4738785#M1111810 Former Member 2008-11-12T08:11:42Z