>
I issued a query and found that 0 row was retrived but logical ios on "IN_BPMDNPMTACT_DX1" index is over 25000 blocks.
>
Query? What query? I don't see any query.
>
I would like to know where 25006 block io on the index came from. (operation ID = 12)
>
Not sure what you are asking. The blocks are in the index. Oracle queries the index using a range scan looking for rows that meet your undisclosed query criteria and doesn't find any.

I can do a select from a table with no indexes and a full table scan will read every block whether any rows are returned or not.

>
I wonder why oracle need 25006 block io in order to find out there is no row for the where predicate.
>
Query? What query? I don't see any query.

Predicate? What predicate? I don't see any predicate.

Index DDL that shows the columns in the index and the column order? I don't see any index DDL.
>
--> 5 block for one partition, and thus, 5*6 partitioned ( about 30 index block io is necessary for operation ID=12)

This is my expectation.
>
Your expectation is clashing with Oracle's reality. But we can't see Oracle's reality for these reasons
>
Query? What query? I don't see any query.

Predicate? What predicate? I don't see any predicate.

Index DDL that shows the columns in the index and the column order? I don't see any index DDL.
>
The only part of Oracle's reality that you posted is the number of blocks and this
>
INDEX RANGE SCAN
>
So you should ask yourself: how many blocks must Oracle read to do the ENTIRE RANGE SCAN? Gee, perhaps that depends on how many keys have to be looked at and how many keys fit into each block (key size). Unfortunately, as noted above, that is a part of Oracle's reality that we can't see from here.

You are using three different hints. Why? hints should generally be used only for troubleshooting to find out WHY an execution plan is being used and what the effect would be if a different plan was used. In production code there should not be any hints. If your hints are being used in production code it is likely a sign that there is a problem with your query that needs to be addressed.

Of course there are exceptions to the 'no hints' rule. The common exceptions are for bulk processing when you use the APPEND hint and for remote queries where you might use a DRIVING_SITE hint.

That shows a discrete set of PROC_RSLT values and that is the leading column of your index.
>
IN_BPMDNPMTACT_DX1 index column order (PROC_RSLT, SEQ, TOP_SA_ID ,IA_ID)
>
So now you should try to answer that querion of my that you didn't answer
>
So you should ask yourself: how many blocks must Oracle read to do the ENTIRE RANGE SCAN? Gee, perhaps that depends on how many keys have to be looked at and how many keys fit into each block (key size).
>
One thought experiment to use is to EXAGGERATE things. Oracle has decided to do an INDEX RANGE SCAN. The leading column in the index is PROC_RSLT. So let's exaggerate and say that EVERY row has a PROC_RSLT value of 1. Oracle would have to read EVERY INDEX BLOCK to scan all of those values and check SEQ > 0 so how many blocks would that be? Naturally your < ROWNUM clause will artificially limit that.

Get the idea? You have a 'PROC_RSLT IN (2, 94, 95, 96, 97, 98, 99)' clause that means Oracle needs to look for those values also.

There could be ANY NUMBER of blocks that have a value of 1 and seq > 0. That is why your expectation is far off the mark.

If you do some testing you can get a better idea how many values there really are for your PROC_RSLT values. Just query the count of records for the values in your predicate. Do a count of '1's and a separate count of the '2, 94, ...' list.

The fact that you don't get any rows possibly means you have a conflict between the PROC_RSLT = 1 part and the NOT EXISTS part but I have no way of knowing. Zero rows might be the correct result for your query. Odds are though that your < ROWNUM clause that limits the first part of the query to 1000 rows is what causes the zero rows in the result set since the NOT EXISTS may filter all of those rows out.

Remove the ROWID hint since it is deprecated. And, if I were you, I would remove the other hints as well and see what the result is. It's possible that your query logic is not even correct so that problem, if it exists, needs to be fixed first. It matters not how fast a query is if it doesn't return the correct results.

After you confirm that your query actually returns the correct results then you can see if there are any performance issues that need to be addressed.

>
I still do not understand why 25006 block read is necessary for "where proc_rslt=1" predicate.
>
Are the stats up to date? What do the stats look like for that table and index? It looks like Oracle doesn't know that there are no index entries that match that predicate and that the buffers (logical reads) figure is an estimate and not the actual.

Can you add the /*+ gather_plan_statistics */ hint to the query and do a trace to see what the actual io is?

So, searching for the "1" in the index seems to take time. What are the distinct values of PROC_RSLT and counts for each value ? Are the values all integers ? Were there entries as "1" earlier and having been deleted since then ?
My guess is that Oracle is re-reading blocks for a consistent read. The tablespace is built with segment space management AUTO and there has been a curious pattern of inserts and deletes to the index.