--
-- Type: MATERIALIZED_VIEW; Owner: SEARCHAPP; Name: SEARCH_BIO_MKR_CORREL_FAST_MV
--
  CREATE MATERIALIZED VIEW "SEARCHAPP"."SEARCH_BIO_MKR_CORREL_FAST_MV" ("DOMAIN_OBJECT_ID", "ASSO_BIO_MARKER_ID", "CORREL_TYPE", "VALUE_METRIC", "MV_ID")
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS NOLOGGING
  TABLESPACE "TRANSMART" 
  BUILD IMMEDIATE
  USING INDEX 
  REFRESH COMPLETE ON COMMIT
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS SELECT   i.SEARCH_GENE_SIGNATURE_ID AS domain_object_id,
        i.BIO_MARKER_ID AS asso_bio_marker_id,
        'GENE_SIGNATURE_ITEM' AS correl_type,
        CASE
           WHEN i.FOLD_CHG_METRIC IS NULL THEN 1
           ELSE i.FOLD_CHG_METRIC
        END
           AS value_metric,
        3 AS mv_id
 FROM   SEARCH_GENE_SIGNATURE_ITEM i, SEARCH_GENE_SIGNATURE gs
WHERE   i.SEARCH_GENE_SIGNATURE_ID = gs.SEARCH_GENE_SIGNATURE_ID
AND gs.DELETED_FLAG = 0;
 
