Access and Filter Predicate on same execution plan line

From: Amit Saroha <eramitsaroha_at_gmail.com>
Date: Tue, 17 Aug 2021 12:24:56 -0400
Message-ID: <CAG67e6SNOiCtuKj00B+b36bbSAHrmY_6m0bsEzaqCRPELdfOpg_at_mail.gmail.com>

Dear All,

Please help me understand why the access and filter predicates are present in the below plan on the same index. There is an index present on the (*process_flag,
request_id, item_id*) column and what I am aware of is that Oracle doesn't make use of the second column in the index if the first column is not present in the where clause (in my case process_flag is not present in the statement).

Plan hash value: 4207199320

---

| Id | Operation

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


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


|   0 | SELECT STATEMENT                                          |
                  |     5 |   535 |     3   (0)  | 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | XXOM01T         |     5 |
535 |     3   (0)  | 00:00:01 |
|*  2 |   INDEX SKIP SCAN                                            |
XXOM01T_N1  |     5 |          |     1   (0)  | 00:00:01 |


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

Predicate Information (identified by operation id):

---

   2 - access("REQUEST_ID"=92830170)

       filter("REQUEST_ID"=92830170)
15 rows selected.

Best Regards,
AMIT

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 17 2021 - 18:24:56 CEST