---"reboot" drug_concept_stage
---set concept_class_id = 'Supplier' where concept_class_id = 'Manufacturer', find this and set supplier in the beginning 
-drop table drug_concept_stage; 
-;
-create table drug_concept_stage as select * from
-drug_concept_stage_existing
- -- ok, what the hell? seems that this step gave most of mistakes, cause I use some misterious drug_concept_stage_existing -- check why I do so
-;
-select * from drug_concept_stage_existing
+
+CREATE INDEX drug_cnc_st_l_ix ON drug_concept_stage (lower (concept_name))
+;
+CREATE INDEX drug_cnc_st_ix ON drug_concept_stage ( concept_name)
+;
+CREATE INDEX drug_cnc_st_c_ix ON drug_concept_stage ( concept_code)
+;
+exec DBMS_STATS.GATHER_TABLE_STATS (ownname => USER, tabname  => 'drug_concept_stage', cascade  => true)
 ;
 --work with manual table 
 drop table ingred_to_ingred_FINAL_BY_Lena;

 b.concept_code as concept_code_2, b.concept_name as concept_name_2, b.invalid_reason 
 from drug_concept_stage a 
 join drug_concept_stage b on a.concept_name like  '%('||b.concept_name||')%'
 where b.concept_class_id ='Manufacturer'
 and a.concept_class_id like 'Branded Drug%' and a.concept_code not in (select concept_code from non_drug_full)
 ;
 */
 --CLinical Drug to ingredients using existing relationship, later this relationship will be updated with ds_stage table
 drop table Clinical_to_Ingred;
 create table Clinical_to_Ingred as
select distinct a.concept_code as concept_code_1, a.concept_name as concept_name_1, cs.concept_code as concept_code_2, cs.concept_name as concept_name_2, cs.INSERT_ID 

concept_class_id like 'Device')
and invalid_reason is null
or concept_code in (select concept_code_1 from ingred_to_ingred_FINAL_BY_Lena where concept_code_2 is null) --"standard ingredient"

  ;
  --add newly created ingredients
  insert into  drug_concept_stage (CONCEPT_NAME,DOMAIN_ID,VOCABULARY_ID,CONCEPT_CLASS_ID,STANDARD_CONCEPT,CONCEPT_CODE,VALID_START_DATE,VALID_END_DATE,INVALID_REASON, source_concept_class_id)
