exec ctx_ddl.drop_preference ('ds')

exec ctx_ddl.drop_section_group ('sg')

create table olympic_sports as    
  select distinct city, edition, sport, discipline, event    
  from   olym.olym_medals_view 

begin    
  ctx_ddl.create_preference('ds', 'MULTI_COLUMN_DATASTORE');    
  ctx_ddl.set_attribute    ('ds', 'COLUMNS',  'event, city, edition, sport, discipline');    
    
  ctx_ddl.create_section_group ('sg', 'BASIC_SECTION_GROUP');    
    
  ctx_ddl.add_sdata_section    ('sg', 'sport', 'sport', 'VARCHAR2');    
  ctx_ddl.add_sdata_section    ('sg', 'discipline', 'discipline', 'VARCHAR2');    
  ctx_ddl.add_sdata_section    ('sg', 'city', 'city', 'VARCHAR2');    
  ctx_ddl.add_sdata_section    ('sg', 'edition', 'edition', 'NUMBER');    
    
  ctx_ddl.set_section_attribute('sg', 'sport', 'optimized_for', 'SEARCH');    
  ctx_ddl.set_section_attribute('sg', 'discipline',  'optimized_for', 'SEARCH');    
  ctx_ddl.set_section_attribute('sg', 'city',  'optimized_for', 'SEARCH');    
  ctx_ddl.set_section_attribute('sg', 'edition',  'optimized_for', 'SORT_AND_SEARCH');    
      
end;  

create index olym_sports_i on olympic_sports (event)    
  indextype is ctxsys.context parameters ('datastore ds section group sg') 

declare     
  displayrs clob;    
  rs        clob;    
begin    
   ctx_query.result_set('olym_sports_i', '100m', '<ctx_result_set_descriptor>    
    <count/>    
    <hitlist start_hit_num="1" end_hit_num="5" order="EDITION DESC">    
    <snippet />    
    </hitlist>    
    <group sdata="sport" topn="5" sortby="count" order="desc">    
    <count exact="true"/>    
    </group>    
    <group sdata="discipline" topn="5" sortby="count" order="asc">    
    <count exact="true"/>    
    </group>    
    <group sdata="edition" topn="5" sortby="value" order="desc">    
    <count exact="true"/>    
    </group>    
    <group sdata="city" topn="5" sortby="value" order="asc">    
    <count exact="true"/>    
    </group>    
    </ctx_result_set_descriptor>',    
    rs);    
    
/* Pretty-print the result set for display purposes.     
   It is not required if you are going to manipulate it in XML.*/    
    
   select xmlserialize(Document XMLType(rs) as clob indent size=2)     
   into   displayrs     
   from   dual;    
       
   dbms_lob.freetemporary(rs);    
   dbms_output.put_line(displayrs);    
end;