SQL & PL/SQL

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Hi All,

I am trying to rewrite a query so that index can be used, but when we use use an OR condition, it goes for full table scan:

select  *    FROM  account c
   INNER JOIN xref x ON ltrim(c.acct_no, '0') = ltrim(x.acct_no,'0') 
                         WHERE 
                           AND (
                             :str_branch = 'ALL'
                             or 
                             c.branch_id in 
                                       (
                                          select regexp_substr(:str_branch,'[^,]+', 1, level) from dual
                                          connect by regexp_substr(:str_branch, '[^,]+', 1, level) is not null
                                          )
                           );