Newly created index not being used

[ashish_vikas]

Hello friends,

I am observing that a newly created index on a z table with mandt & a new field is not being used. I have already rebuild index & updated stats for table.

when we query on this table with where clause having same 2 fields mandt & other.. we expected this index to be used. This table is very large. What more i can do now ?

In trace/SQL session, we can see it is going full table scan.. and takes very long time.

this new field contain no data as of now for all existing rows. Is this the reason ? or sometihng else ?

SQL Statement

SELECT

*

FROM

"ABSA"

WHERE

"MANDT" = :A0 AND "ZABCD" = :A1

Execution Plan

Explain from v$sql_plan not possible -> Explain from PLAN_TABLE is displayed !

No values in v$sql_plan for Address: 0000000166710240 Hash_value: 3891403872 Child_number: 0 Sql_id:

SELECT STATEMENT ( Estimated Costs = 905.739 , Estimated #Rows = 110.190.667 )

1 TABLE ACCESS FULL ZABSA

( Estim. Costs = 905.739 , Estim. #Rows = 110.190.667 )

Estim. CPU-Costs = 152.614.535.266 Estim. IO-Costs = 899.784

Filter Predicates

NONUNIQUE Index ZABSA~Z01

Column Name #Distinct

MANDT 1

ZABCD 1

thanks & regards

ashish

Edited by: ashish vikas on Mar 3, 2012 9:05 PM

[volker_borowski2]

> Execution Plan
> Explain from v$sql_plan not possible ->  Explain from PLAN_TABLE is displayed !
> No values in v$sql_plan for Address: 0000000166710240 Hash_value:  3891403872 Child_number:  0 Sql_id:
> 
>  SELECT STATEMENT ( Estimated Costs = 905.739 , Estimated #Rows = 110.190.667 )
> 
>         1 TABLE ACCESS FULL ZABSA
>           ( Estim. Costs = 905.739 , Estim. #Rows = 110.190.667 )
>           Estim. CPU-Costs = 152.614.535.266 Estim. IO-Costs = 899.784
>           Filter Predicates
> 
> NONUNIQUE  Index   ZABSA~Z01
> Column Name                     #Distinct
> MANDT                                          1
> ZABCD                                         1

Hi,

with this WHERE clause, both columns just one distinct value, the Full Table Scan is indded the best approach, beside

the point you only need a check to evaluate a value is NOT in the result.

In this case you should HINT the statement to the new index, because the DB will always assume that this index is non-selective.

Volker

[thiago_cavalheiro]

Hello Ashish,

Things to check:

1 - Table and index statistics - are they updated?

2 - Forcing the index to be used improves performance? Have you tried using the hint to force this index t o be used?

3 - Database parameters and patch level are updated?

Please input this information in case you want further assistance.

Best regards,

Thiago

[ashish_vikas]

Hello Thiago,

1 - Table and index statistics - are they updated?

yes, and no improvement even after this.

2 - Forcing the index to be used improves performance? Have you tried using the hint to force this index t o be used?

No. can you please tell me how to do this in ABAP ?

But I assume Index should be picked automatically without hint.

3 - Database parameters and patch level are updated?

more or less yes.. we have oracle 11.2.0.2.0.

Interesting thing is we have created 2 indexes on 2 different tables with same fields. On other table, it is working fine(after table and index statistics) however it is 1/5 in size(however size should not be a problem).

thanks & regards

ashish vikas

[thiago_cavalheiro]

Hi Ashish,

To use the hint you would need to change the SQL code in the program to achieve that. But first let's make sure your database is properly set. Kindly run the script check from note [1171650|https://service.sap.com/sap/support/notes/1171650] and see if there are no recommendations on parameters to be properly set. Also, please run the SQL code bellow and paste the output here:

select action_time, version, comments

from dba_registry_history

order by action_time desc;

Regards,

Thiago

[ashish_vikas]

and I am not able to explain this to myself.. when i select this with SE16.. for Maximum No. of Hits = 200 on this table, it give results immediately and use Index Z01.

• In both case, We are selecting ZABCD value which is not available in table.. actually its empty for this field.

SQL Statement

SELECT

/*+

FIRST_ROWS (200)

*/

*

FROM

"ZABSA"

WHERE

"MANDT" = :A0 AND "ZABCD" = :A1 AND ROWNUM <= :A2

Execution Plan

SELECT STATEMENT ( Estimated Costs = 15 , Estimated #Rows = 200 )

3 COUNT STOPKEY

Filter Predicates

2 TABLE ACCESS BY INDEX ROWID ZABSA

( Estim. Costs = 15 , Estim. #Rows = 200 )

Estim. CPU-Costs = 332.822 Estim. IO-Costs = 15

1 INDEX RANGE SCAN ZABSA~Z01

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

Search Columns: 2

Estim. CPU-Costs = 68.486 Estim. IO-Costs = 4

Access Predicates

[stefan_koehler]

Hello Ashish,

if you select your data through SE16 - the ABAP coding uses an oracle optimizer hint to tell the database, that it should choose the best execution plan to return the first 200 rows as fast as possible. ([FIRST_ROWS Hint|http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#SQLRF50302]). This is a completly different approach for the database.

To get all the necessary information about your original issue - please run the problematic SQL statement and execute the SQL script from sapnote #1257075 afterwards. But as far as i can see from your provided information - the problem is that both indexed columns have only one distinct value (by statistics). If this is reality - the execution plan itself seems to be correct on the first view.

Regards

Stefan