SELECT a3.address_id, a3.address_id_2,l.customer_id FROM 
       FPS_PROP_CUST_LINK l
     ,(SELECT row_number() OVER (PARTITION BY customer_id ORDER BY address_id) seq 
             ,address_id 
             ,lead(address_id) OVER (PARTITION BY customer_id ORDER BY address_id) address_id_2 
             ,customer_id 
       FROM   FPS_ADDRESSES) a3 
WHERE l.link_type = 'C'
AND a3.customer_id = l.customer_id 
AND a3.seq = 1 
--AND l.customer_id = 377672
AND l.proposal_id = 363640

a3.seq = 1

CREATE TABLE "FPS"."FPS_PROP_CUST_LINK" 
   (	"PROPOSAL_ID" NUMBER(8,0), 
	"CUSTOMER_ID" NUMBER(8,0), 
	"BUSINESS_ID" NUMBER(8,0), 
	"LINK_TYPE" VARCHAR2(1), 
	"SEQ" NUMBER(2,0), 
	"RELATION" VARCHAR2(1))

CREATE TABLE "FPS"."FPS_ADDRESSES" 
   (	"ADDRESS_ID" NUMBER(10,0) NOT NULL,
	"FLAT" VARCHAR2(100), 
	"HOUSE_NAME" VARCHAR2(100), 
	"HOUSE_NUMBER" VARCHAR2(40), 
	"STREET_NAME" VARCHAR2(100), 
	"DISTRICT" VARCHAR2(100), 
	"TOWN" VARCHAR2(100), 
	"COUNTY" VARCHAR2(100), 
	"POSTCODE" VARCHAR2(10), 
	"TIME_AT_ADDRESS" NUMBER(10,0), 
	"ADDRESS_SEQ" NUMBER(10,0), 
	"CUSTOMER_ID" NUMBER(10,0), 
	"QAS_MATCHED" VARCHAR2(1));

SELECT STATEMENT Optimizer Mode=CHOOSE                  3               3224   
  MERGE JOIN                                            3       192     3224   
    VIEW                                                961 K   47 M    3219   
      WINDOW BUFFER                                     961 K   23 M    3219   
        INDEX FULL SCAN FPS.FPS_ADDR_CUST_ID_IDX        961 K   23 M    3219   
    SORT JOIN                                           1       12      5      
      INDEX RANGE SCAN  FPS.TEMP_PCL_PROP_CUST_IDX      1       12      3

SELECT STATEMENT Optimizer Mode=CHOOSE                          1               17    
  NESTED LOOPS                                                  1       59      17    
    TABLE ACCESS BY INDEX ROWID FPS.FPS_PROP_CUST_LINK          1       7       2     
      INDEX UNIQUE SCAN FPS.TEMP_PCL_UNQ_CUST_IDX               1               1     
    VIEW                                                        1       52      15    
      WINDOW BUFFER                                             9 K     244 K   15    
        INDEX RANGE SCAN        FPS.FPS_ADDR_CUST_ID_IDX        9 K     244 K   15    

Dbms_stats,gather_table_stats(ownname => null
                             ,tabname => '<table name>'
                             ,method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO'
                             ,cascade => true);

SELECT a3.address_id, a3.address_id_2,l.customer_id FROM 
       FPS_PROP_CUST_LINK l
     ,(SELECT row_number() OVER (PARTITION BY customer_id ORDER BY address_id) seq 
             ,address_id 
             ,lead(address_id) OVER (PARTITION BY customer_id ORDER BY address_id) address_id_2 
             ,customer_id 
       FROM   FPS_ADDRESSES
       WHERE  customer_id = 377672) a3
WHERE l.link_type = 'C'
AND a3.customer_id = l.customer_id 
AND a3.seq = 1 
AND l.customer_id = 377672

SELECT /*+ use_nl(l, a1, a2)*/
       a1.address_id
,      a2.address_id_2
,      l.customer_id 
FROM   FPS_PROP_CUST_LINK l
,      FPS_ADDRESSES a1
,      FPS_ADDRESSES a2
WHERE  l.proposal_id = 363640
AND    a1.address_id = (
       SELECT min(address_id)
       FROM   FPS_ADDRESSES 
       WHERE  customer_id = l.customer_id)
AND    a2.address_id = (
       SELECT min(address_id)
       FROM   FPS_ADDRESSES 
       WHERE  customer_id = a1.customer_id
       AND    address_id > a1.address_id)

AND    a2.address_id = (
       SELECT nvl(min(address_id), a1.address_id)
       ...

SELECT table_name
,    (
       SELECT min(index_name)
       FROM   user_indexes
       WHERE  table_name = t.table_name
     ) AS index1
,    (
       SELECT min(index_name)
       FROM   user_indexes
       WHERE  table_name = t.table_name
       AND    index_name > (
              SELECT min(index_name)
              FROM   user_indexes
              WHERE  table_name = t.table_name
       )
     ) AS index2
FROM   user_tables t
/