How to make hint to a statement on partitioning table

[Former Member]

Dear all

Here is my statement, and i would like to make table /BIC/AZA_32S00 to use index ~ZA rather than the table full scan by hint

the SQL statement could be seen in attachment

I tried with the following /*+ */but failed..

index_asc(01 "/BIC/AZA_32S00~010" "/BIC/AZA_32S00^010")

index_asc(/BIC/AZA_32S00 "/BIC/AZA_32S00~010" "/BIC/AZA_32S00^010")

index(01 "/BIC/AZA_32S00~010" "/BIC/AZA_32S00^010")

index(/BIC/AZA_32S00 "/BIC/AZA_32S00~010" "/BIC/AZA_32S00^010")

SELECT STATEMENT ( Estimated Costs = 54.675 , Estimated #Rows = 1.823.860 )

  |

  ---   7 HASH GROUP BY

      |   ( Estim. Costs = 54.675 , Estim. #Rows = 1.823.860 )

      |   Estim. CPU-Costs = 12.088.720.565 Estim. IO-Costs = 54.232

      |

      ---   6 HASH JOIN

          |   ( Estim. Costs = 27.303 , Estim. #Rows = 1.823.860 )

          |   Estim. CPU-Costs = 9.824.409.444 Estim. IO-Costs = 26.943

          |   Access Predicates

          |

          |-----1 TABLE ACCESS FULL /B972/SPRCG

          |       ( Estim. Costs = 3 , Estim. #Rows = 350 )

          |       Estim. CPU-Costs = 141.572 Estim. IO-Costs = 3

          |       Filter Predicates

          ---   5 HASH JOIN

              |   ( Estim. Costs = 27.293 , Estim. #Rows = 1.810.814 )

              |   Estim. CPU-Costs = 9.641.229.373 Estim. IO-Costs = 26.940

              |   Access Predicates

              |

              |-----2 TABLE ACCESS FULL /BI0/SCURRENCY

              |       ( Estim. Costs = 2 , Estim. #Rows = 186 )

              |       Estim. CPU-Costs = 60.106 Estim. IO-Costs = 2

              ---   4 PARTITION RANGE SINGLE

                  |   ( Estim. Costs = 27.284 , Estim. #Rows = 1.810.814 )

                  |   Pstart: 10 Pstop: 10

                  |   Estim. CPU-Costs = 9.459.459.967 Estim. IO-Costs = 26.938

                  |

                  ------3 TABLE ACCESS FULL /BIC/AZA_32S00

                          ( Estim. Costs = 27.284 , Estim. #Rows = 1.810.814 )

                          Pstart: 10 Pstop: 10

                          Estim. CPU-Costs = 9.459.459.967 Estim. IO-Costs = 26.938

                          Filter Predicates

Thank you very much!

Kate

statement.txt.zip

[salome_szanto · Accepted solution]

In this case I guess INDEX("O1" "AZA_32S00~ZA").

Does it work like that?

[Former Member · Accepted solution]

Hi Salome,

Thank you , that works, it is O1 not 01.... quite confusing.

Best regards,

kate

[stefan_koehler · Accepted solution]

Hi Kate,

just as an additional information. It is not recommended to use the (internal) generated aliases by SAP DBSL. For example refer to SAPnote #2029981 - it can be changed or removed at any time and your code may not work anymore.

Better use the outline section syntax from DBMS_XPLAN (e.g. which can be also the new column based hinting syntax), if you need to implement it permanently.

Regards

Stefan

[salome_szanto]

Hi

how about trying something like

INDEX("table" "index")
or

INDEX("alias" "index")

regards,

Salome

[stefan_koehler]

Hi Kate,

please post the whole execution plan in DBMS_XPLAN output (can be done via ST05 in text mode as well) and the corresponding table and index definition of table "/BIC/AZA_32S00" (DBMS_METADATA via SE14).

You are already using partition pruning (only partition 10 is accessed) so it is important to know the exact structure (e.g. partition key, index definition, etc.).

Regards

Stefan

[Former Member]

Hi Stefan,

Yes the structure is a bit conmplex, they currently also have the index partitioned

My idea is to compare the cost with and without index and make index more attractive.(i ask them to de-partitioning the index  hours ago, so the tuning will be easier for me)

I collect the info as attachment,

Best regards,

execution plan.txt.zip

se14.txt.zip

[stefan_koehler]

Hi Kate,

unfortunately the provided information is lacking (e.g. index details like partition key, index type, columns are missing) and it is not the DBMS_XPLAN output. However we know that the partition pruning is based on the fiscal year.

You can not make any statement about the original business case, if you change the underlying data structure (global index). De-Partitioning the index does not really make it easier anyway.

>> My idea is to compare the cost with and without index and make index more attractive.

Why don't you do a CBO trace and compare the costs, if that is the reason for hinting the statement?

Regards

Stefan

P.S.: Please think about the 80/20 rule, if the index is a bitmap index.

[Former Member]

Hi Stefan,

Yes, I want to compare the cost with and without index.

But the problem is the hint doesn't work. do you have any idea, why the normal hint can't work here?

By the way what do you mean 80/20 rule? an link I could refer to ?

[stefan_koehler]

Hi Kate,

you already found the solution below. However a CBO trace would be the best way (and possibly why), if you just want to compare / dig into the costs.

>> By the way what do you mean 80/20 rule? an link I could refer to ?

The "80/20 rule" is about the clustering factor of bitmap indexes (and the corresponding costing of course). The algorithm is explained in book "Cost-Based Oracle Fundamentals" by Jonathan Lewis - however you can crosscheck it on freelists as well, if you don't have that book: http://www.freelists.org/post/oradebug/BITMAP-index-cost-10053-trace,5

Regards

Stefan

[Former Member]

Hi Stefan,

Thank you so much you really help to provide a deep insight  for me:)

I will try if i could manage it.

Best regards,

Kate