explain plan for
select distinct(extractvalue(xmltype(a.email_variables), '/CalliopeData/Attributes/HOTEL_BRAND')) as ThisBrand
from hh.t_ecomm_mem_relations a
where extractvalue(xmltype(a.email_variables), '/CalliopeData/Attributes/HOTEL_BRAND') not in (select b.code_brand from hh.t_pr_brand b)
and a.code_corr_ecat = 'PREA'
and a.status = 'S'
and a.audit_time > sysdate - 1;


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1904775187

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |    14 | 32018 |    25   (4)| 00:00:01 |       |       |
|   1 |  HASH UNIQUE                         |                        |    14 | 32018 |    25   (4)| 00:00:01 |       |       |
|*  2 |   FILTER                             |                        |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR          |                        |    14 | 32018 |    17   (0)| 00:00:01 |   KEY |    13 |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_ECOMM_MEM_RELATIONS  |    14 | 32018 |    17   (0)| 00:00:01 |   KEY |    13 |
|*  5 |      INDEX RANGE SCAN                | X_ECOMM_MEM_RELATIONS3 |    15 |       |     3   (0)| 00:00:01 |   KEY |    13 |
|*  6 |    INDEX FULL SCAN                   | I_PR_BRAND             |     1 |     3 |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "HH"."T_PR_BRAND" "B" WHERE
              LNNVL("B"."CODE_BRAND"<>EXTRACTVALUE("XMLTYPE"."XMLTYPE"(:B1),'/CalliopeData/Attributes/HOTEL_BRAND'))))
   4 - filter("A"."STATUS"='S')
   5 - access("A"."AUDIT_TIME">SYSDATE@!-1 AND "A"."CODE_CORR_ECAT"='PREA')
       filter("A"."CODE_CORR_ECAT"='PREA')
   6 - filter(LNNVL("B"."CODE_BRAND"<>EXTRACTVALUE("XMLTYPE"."XMLTYPE"(:B1),'/CalliopeData/Attributes/HOTEL_BRAND')))