Database Software

Oracle Database 12.2

I have a people search application where I can use name, email, phone, address, or date of birth. The information for each person is stored in a XML document with attributes. I use ndata, mdata, and sdata sections on the XML document. Now I am trying to limit the scope of the ndata, mdata, or sdata search to sections where a specific attribute exists.

A person can have zero or more name, email, phone, or address, Each of these can be "current" or "previous" and each can be "preferred" or "not preferred". What I am trying to do is limit, for example, an email search to sections that are "current" and "preferred".

Here is a test case

drop table person_search purge;
create table person_search (
  id        number  not null
, xml_info  xmltype not null
, constraint person_search_pk primary key (id)
)
/
insert into person_search values ( 
  1
, xmltype('
<data>
  <names>
    <full_name status="C" pref="Y">Homer Simpson</full_name>
  </names>
  <dob>1956-05-12</dob>
  <emails>
    <email status="P" pref="Y">homer.simpson@domain_1.com</email>
    <email status="C" pref="N">simpsons@domain_1.com</email>
  </emails>
</data>') )
/
insert into person_search values ( 
  2
, xmltype('
<data>
  <names>
    <full_name status="C" pref="Y">Marge Simpson</full_name>
    <full_name status="P" pref="N">Marge Bouvier</full_name>
  </names>
  <dob>1958-03-19</dob>
  <emails>
    <email status="C" pref="Y">simpsons@domain_1.com</email>
    <email status="C" pref="N">marge.simpson@domain_2.com</email>
    <email status="P" pref="N">marge.bouvier@domain_2.com</email>
  </emails>
</data>') )
/
insert into person_search values ( 
  3
, xmltype('
<data>
  <names>
    <full_name status="C" pref="Y">Bart Simpson</full_name>
  </names>
  <dob>1980-05-01</dob>
  <phones>
    <phone status="C" pref="N">1-CRANKCALLS</phone>
  </phones>
</data>') )
/
exec ctx_ddl.drop_preference('ps_store');
exec ctx_ddl.create_preference('ps_store', 'DIRECT_DATASTORE');
exec ctx_ddl.drop_section_group( 'ps_sg');
exec ctx_ddl.create_section_group('ps_sg', 'XML_SECTION_GROUP');
--names
exec ctx_ddl.add_ndata_section('ps_sg', 'full_name', 'full_name');
exec ctx_ddl.add_attr_section('ps_sg', 'full_name_status', 'full_name@status');
exec ctx_ddl.add_attr_section('ps_sg', 'full_name_pref', 'full_name@pref');
--email
exec ctx_ddl.add_mdata_section('ps_sg', 'email', 'email');
exec ctx_ddl.add_attr_section('ps_sg', 'email_status', 'email@status');
exec ctx_ddl.add_attr_section('ps_sg', 'email_pref', 'email@pref');
--phone
exec ctx_ddl.add_mdata_section('ps_sg', 'phone', 'phone');
exec ctx_ddl.add_attr_section('ps_sg', 'phone_status', 'phone@status');
exec ctx_ddl.add_attr_section('ps_sg', 'phone_pref', 'phone@pref');
--dob
exec ctx_ddl.add_sdata_section('ps_sg', 'dob', 'dob', 'date');
exec ctx_ddl.set_section_attribute('ps_sg', 'dob', 'optimized_for', 'search');
exec ctx_ddl.drop_preference('ps_wl');
exec ctx_ddl.create_preference('ps_wl', 'BASIC_WORDLIST');
exec ctx_ddl.set_attribute('ps_wl', 'PREFIX_INDEX', 'TRUE');
exec ctx_ddl.set_attribute('ps_wl', 'SUBSTRING_INDEX', 'YES');
exec ctx_ddl.set_attribute('ps_wl', 'NDATA_ALTERNATE_SPELLING', 'FALSE');
exec ctx_ddl.set_attribute('ps_wl', 'NDATA_BASE_LETTER', 'TRUE');
exec ctx_ddl.set_attribute('ps_wl', 'NDATA_THESAURUS', 'NICKNAMES');
exec ctx_ddl.set_attribute('ps_wl', 'NDATA_JOIN_PARTICLES','de:di:la:da:el:del:qi:abd:los:la:dos:do:an:li:yi:yu:van:jon:un:sai:ben:al');
drop index person_search_ix01;
create index person_search_ix01 on person_search(xml_info) indextype is ctxsys.context
  parameters('datastore  ps_store 
              section    group ps_sg 
              wordlist   ps_wl' )
/
select score(1) as score, id, (xml_info).getclobval()  as xml_info
from person_search
where contains(xml_info,'
ndata(full_name,simpson) and mdata(email,simpsons@domain_1.com) and y within email_pref
',1) > 0
order by score desc
/

Here Homer Simpson's record is also returned because he has email_pref=Y but for a different email. What I like to do is limit the emai_pref=Y for email=simpsons@domain_1.com.

Questions

1. How do I return only those records that have an email=simpsons@domain_1.com and attribute of email_pref=Y for that email?

2. Why the score returned is a constant = 3 whenever, I include an attribute in the query? If I use only ndata, sdata, or mdata then the score ranges upto 100.

3. Are there any improvements I should make to my index preferences?

Thanks