How to tune sql using :B1 between t.first_sn and t.last_sn

Hi,

I know it's little weird to use :B1 between t.FIRST_SN AND t.LAST_SN. The buffer gets of index range scan is very high.

SQL below is a sample for test. In prod env, we want to find rows where a serial number existing between the min serial number and the max serial number.

How can we tune the performance.

Thanks a lot.

Connected.USERNAME             INST_NAME            HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR-------------------- -------------------- ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------APPS                 xxxx                 xxxxxxxxxxxx              4328  35893    11.2.0.4.0 20180628 228915          1368  15115           000000210322C830 00000022428FE878SQL>SQL> create table object_test as  2  select o.OBJECT_NAME,  3         o.OBJECT_NAME || to_char(o.OBJECT_ID) FIRST_SN,  4         o.OBJECT_NAME ||  5         to_char(o.OBJECT_ID + round(DBMS_RANDOM.VALUE(1, 10))) LAST_SN  6    from dba_objects o;Table created.SQL> create index object_test_idx on object_test(first_sn,last_sn);Index created.SQL> @ slSession altered.SQL> select --+ index(o object_test_idx)  2  count(o.object_name) from object_test o  3  where 'UET$14' BETWEEN O.FIRST_SN AND O.LAST_SN;COUNT(O.OBJECT_NAME)--------------------                   1SQL> @ xPLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  10vhfcxwv7ngn, child number 1-------------------------------------select --+ index(o object_test_idx) count(o.object_name) fromobject_test o where 'UET$14'