SELECT a.TXT_CUSTOMER_CD,
       a.TXT_CUSTOMER_NAME,
       TXT_CUSTOMER_STATUS,
       TO_CHAR (a.dat_start_dt, :"SYS_B_00") DAT_START_DT,
       :"SYS_B_01" AS Dat_Start_Time,
       DECODE (TXT_IND_CORP_FLAG,
               :"SYS_B_02", :"SYS_B_03",
               :"SYS_B_04", :"SYS_B_05",
               :"SYS_B_06", :"SYS_B_07")
          AS "Type",
       b.TXT_AREAVILLAGE,
       b.TXT_CITYDISTRICT,
       b.NUM_PINCODE,
       b.TXT_PINCODE_LOCALITY,
       b.TXT_STATE,
       DECODE (TXT_CUSTOMER_STATUS,
               :"SYS_B_08", :"SYS_B_09",
               :"SYS_B_10", :"SYS_B_11",
               :"SYS_B_12")
          AS Approval_status
  FROM GENMST_CUSTOMER a, GENMST_LOCATION b
 WHERE     b.NUM_LOCATION_CD = a.NUM_PERMANENT_LOCATION_CD
       AND a.txt_firstname LIKE :"SYS_B_13"
       AND ROWNUM < :"SYS_B_14"

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2755851763

--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |   442K|    49M|       |   107K  (1)| 00:21:27 |
|*  1 |  COUNT STOPKEY         |                 |       |       |       |            |          |
|*  2 |   HASH JOIN            |                 |   442K|    49M|    39M|   107K  (1)| 00:21:27 |
|*  3 |    TABLE ACCESS FULL   | GENMST_CUSTOMER |   444K|    33M|       | 57355   (2)| 00:11:29 |
|   4 |    INDEX FAST FULL SCAN| IDX_CD1         |    10M|   378M|       | 23157   (1)| 00:04:38 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<TO_NUMBER(:SYS_B_14))
   2 - access("B"."NUM_LOCATION_CD"="A"."NUM_PERMANENT_LOCATION_CD")
   3 - filter("A"."TXT_FIRSTNAME" LIKE :SYS_B_13)

18 rows selected.

CREATE INDEX INS.IDX_LOCATION_CD ON INS.GENMST_CUSTOMER
(NUM_PERMANENT_LOCATION_CD)
LOGGING
TABLESPACE INDX_16
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

CREATE INDEX ... ON GENMST_CUSTOMER ( txt_firstname ) ...