Skip to Content
3

Oracle Optimization chosing wrong index instead of TABLE FULL SCAN

Feb 06 at 08:54 PM

519

avatar image

When we change workcenter, it is extremely slow. It takes more than 10 minutes and time out. There is a OSS Note 415031 which talks about index on AFRU table. The query on AFRU itself takes around 9 minutes. AFRU table has around 5 million rows.

I have couple of question, when I look at my explain statement, it is choosing a custom index which does not have the 'WHERE' field. I am curious, why isn't oracle optimizer not choosing table scan? I would expect a full table scan of AFRU table (with more than 5 million rows to be still faster than using index which does not have the appropriate field). Is it possible that it is getting delayed due to choosing of the wrong index?

I was trying to put HINT in a custom program and see if I can force TABLE FULL SCAN and it did seem faster.

I am perplexed. This is a one off scenario, I was thinking table scan may not be all that bad. but the optimizer is never choosing table scan. I would like to avoid creating new index for such an exception scenario, but timing out is not an option.

STATISTICS

Parse Timestamp: 20180208 17:57:45

System: RQ1

SQL_ID 18fyjygmadgtd, child number 1

-------------------------------------

SELECT /*+ FIRST_ROWS (1) */ * FROM "AFRU" WHERE "MANDT"=:A0 AND

"ARBID"=:A1 AND ROWNUM <=:A2

Plan hash value: 4214817510

-------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 256 (100)| |

|* 1 | COUNT STOPKEY | | | | | |

|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| AFRU | 1 | 585 | 256 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | AFRU~Z01 | | | 8 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1

2 - SEL$1 / AFRU@SEL$1

3 - SEL$1 / AFRU@SEL$1

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=:A2)

2 - filter("ARBID"=:A1)

3 - access("MANDT"=:A0)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - "MANDT"[VARCHAR2,9], "AFRU"."RUECK"[VARCHAR2,30], "AFRU"."RMZHL"[VARCHAR2,24],

"AFRU"."GRUND"[VARCHAR2,12], "AFRU"."PERNR"[VARCHAR2,24], "AFRU"."ISDD"[VARCHAR2,24],

"AFRU"."ISDZ"[VARCHAR2,18], "AFRU"."IERD"[VARCHAR2,24], "AFRU"."IERZ"[VARCHAR2,18],

"AFRU"."ISBD"[VARCHAR2,24], "AFRU"."ISBZ"[VARCHAR2,18], "AFRU"."IEBD"[VARCHAR2,24],

"AFRU"."IEBZ"[VARCHAR2,18], "AFRU"."ISAD"[VARCHAR2,24], "AFRU"."ISAZ"[VARCHAR2,18],

"AFRU"."KAPTPROG"[VARCHAR2,12], "AFRU"."OBMAT"[VARCHAR2,54],

"AFRU"."OBCHA"[VARCHAR2,30], "AFRU"."LICHA"[VARCHAR2,45], "AFRU"."MYEAR"[VARCHAR2,12],

"AFRU"."ME_SFCID"[VARCHAR2,180], "AFRU"."ROLE_ID"

3 - "AFRU".ROWID[ROWID,10], "MANDT"[VARCHAR2,9], "AFRU"."CATSBELNR"[VARCHAR2,30],

"AFRU"."STZHL"[VARCHAR2,24]

Table AFRU

Last statistics date 02/06/2018 23:35
Analyze Method ample 5,775,114 Rows
Number of rows 5,775,114
Number of blocks allocated 490,556
Number of empty blocks 0
Average space 0
Chain count 0
Average row length 585
Partitioned NO
Parallel degree 1


NONUNIQUE Index AFRU~Z01

Column Name #Distinct

MANDT 2
CATSBELNR 964,686
STZHL 11,121

NONUNIQUE Index AFRU~Z02

Column Name #Distinct

BUDAT 5,792
WERKS 104


UNIQUE Index AFRU~0

Column Name #Distinct

MANDT 2
RUECK 3,146,901
RMZHL 80,338

Column statistics

Column #Distinct U #BU AVGL Data Type Len

ABARB 101 1 4 VARCHAR2 9
AENAM 632 1 3 VARCHAR2 36
ANZMA 1 1 2 NUMBER 22
APLFL 2 1 5 VARCHAR2 18
APLZL 541 1 9 VARCHAR2 24
ARBID 1146 1 9 VARCHAR2 24
AUERU 3 1 2 VARCHAR2 3
AUFNR 2167229 1 13 VARCHAR2 36
AUFPL 2203561 1 11 VARCHAR2 30
AUSOR 2 1 2 VARCHAR2 3
BELNR_IST 1 1 2 VARCHAR2 30
BELNR_UMB 1 1 2 VARCHAR2 30
BEMOT 2 1 3 VARCHAR2 6
BUDAT 5792 1 9 VARCHAR2 24
CANUM 1 1 5 VARCHAR2 12
CATSBELNR 964686 1 4 VARCHAR2 30
STZHL 11121 1 9 VARCHAR2 24

10 |10000 characters needed characters left characters exceeded

We can't tell you because Oracle does a decision based on your data. Please post the EXPLAIN (do a trace via ST05, display it, and click Explain), the statistics on your table and indexes (click on the table name from the EXPLAIN).

You may get more information in Oracle forums.

1
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Mike Pokraka Feb 10 at 08:40 AM
5

There has been some excellent input already, and I also agree there is some peculiarity about your data and statistics that's causing Oracle to choose the index.

I see your statistics are fairly recent, so they are likely not at fault. But I have another theory to offer: Is the ARBID field field generally filled or are many empty? It boils down to data distribution, which the statistics can sometimes misinterpret. If the uniqueness is 100% then it should choose a full table scan. But if 90% of rows are empty or you have a few values that occur many times (typical of status fields) then the uniqueness is very low, and statistically speaking the optimiser may decide that we're likely to hit a match on a non-indexed field quite easily because most of the values are similar. This is particularly an issue when searching for values in a mostly empty column.

Also, just for interest, see if "UP TO 1 ROWS" behaves different to SELECT SINGLE. While we're at it, also try selecting without restrictions and variations on SELECT ... UP TO 10/100/1000 ROWS.

Show 17 Share
10 |10000 characters needed characters left characters exceeded

SELECT SINGLE and UP TO 1 ROWS have always been the same at Oracle side (you can see it in the EXPLAIN above too).

0

In theory yes, but when faced with unexpected problems I take nothing for granted and like to verify assumptions.

I am reasonably sure this will behave differently with a full SELECT and somewhere at different numbers we will find a switch. What this tells us, I don't know yet :-) But it's easy enough to test and adds to the body of information.

2

I fully agree with your assumption about data distribution.

If that's true, the OP may calculate histograms on that column, so that Oracle can choose better between a full scan and an index scan. You must use the hint &SUBSTITUTE VALUES& along with histograms, so that Oracle evaluates the actual value, not just the "placeholder" (cf SAP notes and Oracle documentation about histograms).

3

Sandra,

If you can throw some light on How/When to decide using histograms, that will add a lot of value to this discussion ?

Should we wait till we see a performance issue and then decide to tweak the query using histograms or is there a better way of approach for using histograms.

Thanks,

K.Kiran.

0

Histograms answer the scenario explained by Mike. As I said, there is lots of discussions about it in Oracle and StackOverflow forums, and also in SAP notes.

If you don't see any performance issue, then it's useless spending time.

Just a quick example what histogram is: let's say the column ARBID has 4 distinct values A, B and C, and the table has 1 million rows, the histogram might be this one (there are many types of histograms, but this one gives a good idea what it is):

Value in column ARBID    Number of rows (estimated or computed)
A                          30.000
B                         800.000
C                         170.000

This histogram is very helpful to help Oracle choose the best execution plan. For instance, if all rows and all columns are to be returned with condition based on ARBID only, if there's an index on ARBID, and don't forget the hint &SUBSTITUTE_VALUES& or &SUBSTITUTE_LITERALS&, then for value "B" Oracle will probably do a full table scan, but for value "A" it will use an index range scan + table index by rowid.

2

Sandra,

I am waiting on basis to generate histograms for a specific column !

Also, in your example, what if we are querying the above table with value 'D', I presume that it will choose table scan? To confirm, even with Histograms, I will have to do core modifications to ensure that correct access path is chosen, Is that correct?

would it have made sense for SAP to use SELECT COUNT(*) in this scenario? If all they are doing is to find out if a row exist and they are not using the value from SELECT statement?

0

"all they are doing is to find out if a row exist"

Woah, that's a completely new piece of info.

In that case the more appropriate statement for a 7.4+ system would be:

SELECT SINGLE @abap_true FROM... 

Please run that through your test as well and let us know the result. Sometimes the index itself is used as a data source, but - I'm speculating here - since data is not needed there is no longer a point to going via the index. If that works, please let SAP know. I think you'd have a reasonable chance of a fix as this is a performance enhancement for everyone on 7.4+.

0

I wish I could use that command.. Unfortunately , We are on 7.3! We will be upgrading to 7.4+ later this year. Further, not sure if SAP has changed the code to leverage this Again this is SAP code, would you recommend core mode (with table scan hint) or just create an index (even if it will be used only few times a month)?

This is code that gets executed during CR02 (Include LCRA0F25) ! They are not using AFRU in the subroutine and I am making a guess that it is not used outside this subroutine.

* Rückmeldungen
SELECT SINGLE * FROM AFRU
WHERE
ARBID = OBJID_CRHD.
IF SY-SUBRC = 0.
FLG-ARBPL_USED = KREUZ.
EXIT.
ENDIF.

0
Show more comments

I guess Oracle will consider that D has around 0 rows, so same choice as A.

Yes, it's what I said, add the hint. There's another way, at Oracle side, called baseline (more complex, ask DBA or search the web).

No, SELECT COUNT(*) will extract all the lines to count them (Oracle doesn't save a counter of rows), while a "SINGLE" or "UP TO 1 ROWS" stops after 1 row. But you can mix SELECT COUNT(*) and UP TO 1 ROWS (the count will be 1).

Be careful, all this is speculation, probably not so far from reality, till I can't be sure which execution plan Oracle will determine. As I said, do some tests. Faster than asking.

0
Yes, Mike. With full SELECT, it does choose TABLE FULL SCAN. With my dev system (200k records), the switch happened at 999 rows.

Sandra, do you want me to run histogram with method auto?

At this time, what would be the recommendation?

1) Create index ! I am just trying to rein down on indexes (we do have quiet a few in our system and I have been trying to eliminate them) , so the hesitancy. but if the consensus is that index is better in this situation, I can go with recommendation.

2) Make core modifications to SAP code (to force table full scan) ?

I do have a message with SAP and waiting to see what their recommendation is as well.

0

Looking at your stats, there are 1146 unique values among the 5.7 million records. So even though it means there are probably 5.699 million empty entries and 1146 filled fields, to the optimiser with simple stats it looks like each value occurs 5000 times.

Sandra is spot on, histograms are there refine this type of statistical simplification and I believe this would solve your issue.

0

I don't remember what "auto" is (selection of columns? estimate of sample?). You may also create the histograms directly in Oracle, but then maybe you need to deactivate the statistics in DBSTATC.

You can choose the default for a first try. Don't forget to use the hint &SUBSTITUTE VALUES& so that the histogram can be chosen by Oracle.

There are queries given in Oracle forums to display the estimated/computed histograms. Sometimes it helps to understand. And of course there's the Oracle documentation about the concept.

0
Matthew Billingham
Feb 08 at 07:01 AM
2

What's wrong with creating a new index? Especially if its recommended in a note?

It seems to me the issue is with your custom index. If you create your own index it can severely screw things up if it's not wisely chosen. Find out when the custom index was added, and for what purpose - maybe there's a single custom program where the developer decided it would be a good idea. Perhaps that program just needs rewriting and you can delete the custom index.

You should contact Basis and make sure that the database statistics are up-to-date. If they're not, that can also slow things down.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Matt,

I understand that custom index does mess up searches. I have seen multiple scenarios where it happens , but in all those scenarios, there were at least fields in index matching with the WHERE clause fields. In this case, My custom index has two fields (CATSBELNR and STZHL). My query (std sap program) select * from AFRU with ARBID. Why would the optimizer even choose my custom index? I have seen this behavior with other tables as well. I am curious to understand why would oracle optimizer consider an index without matching fields be a better access path than table full scan?

I did run statistics in QA system and it made no difference.

I am trying to validate if this is a default behavior of optimizer , in which case I have no alternative but create new index or modify std. pgm to force TABLE FULL scan (I hate core mods).

I am also curious as to how oracle optimizer is behaving with other clients. Does your system have index on AFRU~ARBID? If not, what happen to SE16N select on this field? Does it choose TABLE FULL SCAN or arbitrary index?

1

My system is BW and runs with HANA.

0
Janos Mucsi-Besze
Feb 14 at 11:24 AM
2

Hi,

If you want to create a custom index specifically for this query use the columns in the given order: MANDT,ARBID.

Oracle rarely chooses FULL TABLE SCAN, especially in a case of a big table, like this. There is a parameter that influences the choiche between index access and table access, this is optimizer_index_cost_adj. In the SAP world, we do not really tweak or play around with this. We have our own recommendation depending in case of OLTP or OLAP System Type is used.
At the first look, for me it seems to be there is no corresponding index exists yet (if only AFRU~Z01 and AFRU~0 exists), so the optimizer is choosing a BAD index access, because it 'thinks' it is better to use a bad index rather then scanning all the table blocks.

So, creating a new index with the given columns would help you, especially because all columns from the where condition is covered, so first filtering of the data can be done by scanning much less blocks, and by using the index all other values can be read from the table.


Show 6 Share
10 |10000 characters needed characters left characters exceeded

...and if the SELECT is changed to only read ARBID as per my earlier comment, there would be no table access at all.

This made me wonder about something though:

Conventional wisdom is that each additional index adds a little update overhead. How does an index physically look like for a case like this where the column is mostly empty? Does Oracle create additional nodes or does it only index that which actually contain data?

What I'm getting at is that if it's 98% empty, then an additional index has negligible performance impact if Oracle ignores blanks and only updates the index for those rows where ARBID is actually filled. Or does Oracle still index each row? And how does MANDT influence this theory?

1

You are right. Each and every index adds a little overhead to the administration. Especially for Insert, update and delete, but as far as our exerience goes, it does not really put a huge overhead as long as there are 8 ~10 indexes existing for the table. After 8 index already existing, one should really investigate the cause why any of the available indexes are not used. But as far as you provided, only 2 indexes existing right now. But there may be others existing as well.

The empty values are also getting recorded in the index, but in reality does not occupy space within the index. As for the indexed columns, all indexed columns in the index will contain the actual data plus a pointer to the table where the other data ca be read.

As for my recommendation towards creating a new index, since the index will contain only two columns, it will not really occupy lots of space in the database. Not mentioning, that if the index gets created with compression, it will need much less space. At least MANDT can be compressed as there are not that many different values for this, so a good candidate to compress the index with "compress 1". The MANDT column is pretty much a colum that always needs to be included in the SAP indexes, since the SAP queries are always using this field in the first place.

1
Janos Mucsi-Besze

Thanks for the feedback. To summarise my understanding:

Having a lot of null values will not reduce the indexing overhead because it will still create an index node/leaf/link/whatever entry for each row.

But because index data is deduplicated, the index should be highly compressible because we have only one mandt value and a handful (2%) of non-null other field values.

0

Yes Mike, that is true.

1

Thanks Janos! Sorry for the delayed reply. At present, we have decided to go with FULL TABLE SCAN using core modification. I will definitely consider your recommendation going forward.

I was curious about your statement in italics below. When you say we do not really tweak or play with this. What do you mean? This parameter was set to '100' in our system and every EWA report recommends setting it to '20'. What is the basis for deciding to set this parameter?

There is a parameter that influences the choiche between index access and table access, this is optimizer_index_cost_adj. In the SAP world, we do not really tweak or play around with this.

0

Hi Krish,

The parameter mentioned me has a great influence on how CBO decides between Full Table Scans and INdex Scans.

More information is documented in below link:

https://docs.oracle.com/database/121/REFRN/GUID-BA25DBA7-3826-48CA-849B-6D8E3326A1B4.htm#REFRN10143

Based on our experience in SAP, there is 2 accepted values existing for this parameter.

Either 20 or 100, depending on the SAP System type. For OLAP we set 20, and for OLTP we do not set (means the Oracle default is taken, which is 100).

With my statement I wanted to say, that we have only these 2 values in SAP. The reason why we do not set any other value that, this parameter influence the whole system and any deviation from the SAP standard makes the whole SAP System crazy and so the performance goes down the toilett.

The values 20 and 100 had been tested for years within SAP and only with these two values (again, depending on the SAP System Type) can we make absolutely sure that the owerall system perfomance is at the closest as we expects.

But of course this does not mean that there won't be any performance problems happening. There are always exceptions, especially in IT and so in SAP as well.

0
Jelena Perfiljeva
Feb 07 at 08:13 PM
1

Please discuss this with your Basis admin / DBA and if they have no other input then open an incident with SAP Support.

With 5 mil records I'd also strongly suggest to consider data archiving some time soon.

Show 4 Share
10 |10000 characters needed characters left characters exceeded

Jelena,

Thanks for your response. Yes, I do have a ticket with SAP support.

In your systems, Does the Optimizer choose an arbitrary index (without satisfying where clause fields) instead of TABLE FULL SCAN ? I am trying to figure out if this is a default behavior of the optimizer.

When I run "Number of entries" from SE16 for AFRU-ARBID, it choose table scan and query is executed in 22 seconds . I was also able to force the custom program to choose table scan and it did appear to be faster than indexed search.

regards

Krish

0

Well, optimizer tries to optimize, I guess. We have no such performance issues in our system, to my knowledge. But I'm not the person in charge of it nor do I have access to such information in Production, so maybe I just don't know. This question was tagged with ABAP but it doesn't look like this is actually an ABAP question. If you'd like to learn more about the DB behavior then it should be posted with Netweaver or respective DB tag.

Edit: just noticed it is tagged with Oracle. It doesn't look like a lot of people are answering questions in that tag. You'd have better luck with SAP Netweaver tag, I suspect. (Right, Matt Fraser ?) Also update the question title. It doesn't seem to match the actual question right now.

1
Jelena Perfiljeva

I see that the question is tagged "ABAP Development", "Oracle Database", and "NetWeaver Application Server for ABAP," and in my opinion that's an appropriate selection of tags (perhaps those latter two came later, after Jelena's comment?). Even if "Oracle Database" isn't that busy, it is directly related to the question, since this is specifically about how the Oracle optimizer is choosing appropriate indexes, right? So I would definitely keep the tag on the question.

Cheers,
Matt

3

Yes, it was tagged with Netweaver later. There are many Netweaver tags though, I'm not sure where most of the Basis folks congregate these days on SCN...

Thank you!

1
Raymond Giuseppi
Feb 09 at 08:41 AM
1

Could you run a SQL trace and post the "Explanation of SQL access path" display with the "Explain" button (Alternative, use ST04 or ST04n)

Show 14 Share
10 |10000 characters needed characters left characters exceeded

This is from the explain statement (DB02). Let me know if you need additional information

SELECT
/*+
FIRST_ROWS (1)
*/
*
FROM
"AFRU"
WHERE
"MANDT"=:A0 AND "ARBID"=:A1 AND ROWNUM <=:A2


Execution Plan

Explain from gv$sql_plan: Address: 00000003D73C9240 Hash_value: 3869687597 Child_
Sql_id: 18fyjygmadgtd Parse Timestamp: 20180208 17:29:04


SELECT STATEMENT ( Estimated Costs = 178 , Estimated #Rows = 0 )

3 COUNT STOPKEY
Filter Predicates

2 TABLE ACCESS BY INDEX ROWID BATCHED AFRU
( Estim. Costs = 178 , Estim. #Rows = 1 )
Estim. CPU-Costs = 3,992,431 Estim. IO-Costs = 178
Filter Predicates

1 INDEX RANGE SCAN AFRU~Z01
( Estim. Costs = 6 , Estim. #Rows = 0 )
Search Columns: 1
Estim. CPU-Costs = 210,929 Estim. IO-Costs = 6
Access Predicates

0

I don't understand. I see that you are accessing one line only (FIRST_ROWS (1)) with ARBID being part of the index.

Why do you want a full table scan for that?

Please post the full EXPLAIN view (do a trace via ST05, display it, click Explain and then click Display as text, so that we know the filter and predicates), plus the statistics on your table and indexes (click on the table name from the EXPLAIN).

0

I have posted EXPLAIN DISPLAY AS TEXT. It seems counter intuitive to me that index scan (without matching columns) will be faster than FULL TABLE scan.

I wrote a custom program and forced table scan . it was complete in 42 seconds and used TABLE FULL scan. On other the std. program using a custom index took 400 seconds. The cost of table scan was definitely more than indexed scan. But from a time perspective, it is a big difference.

SELECT SINGLE * FROM afru INTO ls_afru
WHERE
arbid = p_arbid
%_HINTS ORACLE 'FULL("AFRU")'.

0

Ok, so the index access looks cheap but takes a longer time than just reading the whole table.

Yet, Oracle decides to use the index.

Without all data (like index statistics or instance parameter settings) it's not possible to say exactly what happens.

But here are a few possible reasons:

  • Oracle on SAP standard parameter setting has optimizer_cost_adj set to 10, which makes an index access appear 10x cheaper than a full table scan.
  • the first_rows(n) hint favours index accesses when at least part of the predicates can be covered by a cheap index access. The equal condition on MANDT looks promising here since MANDT's statistic indicate that there are two distinct values. To the optimizer without histograms, this means, using an index that covers MANDT reduces the total matching record count by 50%.
  • since MANDT, CATSBELNR and STZHL are not the only columns in the index, the remaining row data needs to be fetched from the table blocks - leading up to many logical IOs as it's rather likely that the Z01 index has a relatively high clustering factor.

One good next step would be to run the statement with gather_plan_statistics to see what the actual costs are.

Also, reviewing the index/table statistics and checking if they need to get reorganised might be worthwhile.

Finally, check the DB parameter setup and patch-level.

3

With Gather_Plan_statistics, Cost was 2553!

Parse Timestamp: 20180210 19:25:00

System: RQ1

SQL_ID 2626pc2twmsry, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM "AFRU" WHERE "MANDT"=:A0

AND "ARBID"=:A1 AND ROWNUM <=:A2

Plan hash value: 4214817510

-------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2533 (100)| |

|* 1 | COUNT STOPKEY | | | | | |

|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| AFRU | 10 | 5850 | 2533 (1)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | AFRU~Z01 | | | 54 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1

2 - SEL$1 / AFRU@SEL$1

3 - SEL$1 / AFRU@SEL$1

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=:A2)

2 - filter("ARBID"=:A1)

3 - access("MANDT"=:A0)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

0

While you ran the statement with plan statistics, the explain output you provided is still the standard output without plan statistics. Can you provide the correct one?

0
Show more comments

"#Rows = 0" with index Z01, look suspicious, did someone forget to provide MANDT as first key of index?

1

Z01 does have mandt.

0

Parse Timestamp: 20180208 17:57:45

System: RQ1

SQL_ID 18fyjygmadgtd, child number 1

-------------------------------------

SELECT /*+ FIRST_ROWS (1) */ * FROM "AFRU" WHERE "MANDT"=:A0 AND

"ARBID"=:A1 AND ROWNUM <=:A2

Plan hash value: 4214817510

-------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 256 (100)| |

|* 1 | COUNT STOPKEY | | | | | |

|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| AFRU | 1 | 585 | 256 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | AFRU~Z01 | | | 8 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1

2 - SEL$1 / AFRU@SEL$1

3 - SEL$1 / AFRU@SEL$1

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=:A2)

2 - filter("ARBID"=:A1)

3 - access("MANDT"=:A0)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - "MANDT"[VARCHAR2,9], "AFRU"."RUECK"[VARCHAR2,30], "AFRU"."RMZHL"[VARCHAR2,24],

"AFRU"."GRUND"[VARCHAR2,12], "AFRU"."PERNR"[VARCHAR2,24], "AFRU"."ISDD"[VARCHAR2,24],

"AFRU"."ISDZ"[VARCHAR2,18], "AFRU"."IERD"[VARCHAR2,24], "AFRU"."IERZ"[VARCHAR2,18],

"AFRU"."ISBD"[VARCHAR2,24], "AFRU"."ISBZ"[VARCHAR2,18], "AFRU"."IEBD"[VARCHAR2,24],

"AFRU"."IEBZ"[VARCHAR2,18], "AFRU"."ISAD"[VARCHAR2,24], "AFRU"."ISAZ"[VARCHAR2,18],

"AFRU"."KAPTPROG"[VARCHAR2,12], "AFRU"."OBMAT"[VARCHAR2,54],

"AFRU"."OBCHA"[VARCHAR2,30], "AFRU"."LICHA"[VARCHAR2,45], "AFRU"."MYEAR"[VARCHAR2,12],

"AFRU"."ME_SFCID"[VARCHAR2,180], "AFRU"."ROLE_ID"

3 - "AFRU".ROWID[ROWID,10], "MANDT"[VARCHAR2,9], "AFRU"."CATSBELNR"[VARCHAR2,30],

"AFRU"."STZHL"[VARCHAR2,24]

Table AFRU

Last statistics date 02/06/2018 23:35
Analyze Method ample 5,775,114 Rows
Number of rows 5,775,114
Number of blocks allocated 490,556
Number of empty blocks 0
Average space 0
Chain count 0
Average row length 585
Partitioned NO
Parallel degree 1


NONUNIQUE Index AFRU~Z01

Column Name #Distinct

MANDT 2
CATSBELNR 964,686
STZHL 11,121

NONUNIQUE Index AFRU~Z02

Column Name #Distinct

BUDAT 5,792
WERKS 104


UNIQUE Index AFRU~0

Column Name #Distinct

MANDT 2
RUECK 3,146,901
RMZHL 80,338

0

(please add these statistics in your question, as it's of major importance)

Could you also post the statistics of the column ARBID (on table AFRU, button "column statistics"). I suspect that the NDV (number of distinct values) of ARBID is low enough to make Oracle think that it should quickly reach one row with the searched value -> cf answer of Mike Pokraka.

0