Home » SQL & PL/SQL » SQL & PL/SQL » Counting overlapping ID's in intersection tables. (Oracle, 12.1.0.1, AIX 7.1)
| Counting overlapping ID's in intersection tables. [message #650229] |
Wed, 20 April 2016 07:23  |
 |
posefant
Messages: 13
Registered: April 2016
|
Junior Member |
|
|
Hi all.
I have an intersection table: W_CAMP_HIST_F and the left table W_SOURCE_D.
The intersection table key to W_SOURCE_D is SOURCE_WID.
The other key in the intersection table is CONTACT_WID.
In my W_SOURCE_D table I have the following rows:
SA
SB
In my W_CONTACT_ID table I have the following rows:
CA
CB
CC
CD
In my intersection table I have the following rows:
SA, CA
SA, CB
SA, CC
SB, CB
SB, CC
SB, CD
I would like to count the CONTACT_WID for each distinct overlapping SOURCE_WID
Result should be:
SA, SB, 2 - Because CB and CC are present in SB as well as in SA.
The SQL I have for this is:
SELECT OVERLAP.CAMP_WID, OVERLAP.OVRLP_CAMP_WID, COUNT (OVERLAP.CONTACT_WID) AS NUM_OVRLP_ACCNTS
FROM (SELECT DISTINCT CHF.CONTACT_WID AS CONTACT_WID, SRC.ROW_WID AS CAMP_WID, OSRC.ROW_WID AS OVRLP_CAMP_WID
FROM ( SELECT CONTACT_WID, SOURCE_WID
FROM W_CAMP_HIST_F
WHERE LAUNCH_DT_WID = 0 AND SOURCE_WID > 0 AND CONTACT_WID > 0
GROUP BY CONTACT_WID, SOURCE_WID) CHF,
( SELECT CONTACT_WID, SOURCE_WID
FROM W_CAMP_HIST_F
WHERE LAUNCH_DT_WID = 0 AND SOURCE_WID > 0 AND CONTACT_WID > 0
GROUP BY CONTACT_WID, SOURCE_WID) OCHF,
W_SOURCE_D SRC,
W_SOURCE_D OSRC
WHERE CHF.SOURCE_WID = SRC.ROW_WID
AND OCHF.SOURCE_WID = OSRC.ROW_WID
AND CHF.SOURCE_WID <> OCHF.SOURCE_WID
AND CHF.CONTACT_WID = OCHF.CONTACT_WID
AND SRC.SRC_TYPE = OSRC.SRC_TYPE
AND SRC.SRC_TYPE = 'Campaign'
AND OSRC.SRC_TYPE = 'Campaign') OVERLAP
GROUP BY OVERLAP.CAMP_WID, OVERLAP.OVRLP_CAMP_WID
ORDER BY 1, 2;
Working as expected, but when you have 57 million rows in the W_CAMP_HIST_F table, the intermediate tempspace and calculations takes a huge amount of space and time.
I'd like to know if I could use DWH functions like LAG/PARTITION OVER etc to improve the cost and ofcourse reduce the size of the tempspace.
Thank you in advance
with Regards
posefant
*BlackSwan added {code} tags. Please do so yourself in the future
How to use {code} tags and make your code easier to read
[Updated on: Wed, 20 April 2016 08:19] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Counting overlapping ID's in intersection tables. [message #650243 is a reply to message #650231] |
Thu, 21 April 2016 02:27  |
|
posefant
Messages: 13
Registered: April 2016
|
Junior Member |
|
|
--------------------------------------------------------
-- DDL for Table W_CAMP_HIST_F
--------------------------------------------------------
CREATE TABLE "DWSIEBEL"."W_CAMP_HIST_F"
( "ACCNT_WID" NUMBER(10,0) DEFAULT 0,
"BNCE_REASON_WID" NUMBER(10,0) DEFAULT 0,
"BNCE_TYPE_WID" NUMBER(10,0) DEFAULT 0,
"CALL_STATUS_WID" NUMBER(10,0) DEFAULT 0,
"CAMPAIGN_WID" NUMBER(10,0) DEFAULT 0,
"CAMP_END_DT_WID" NUMBER(10,0) DEFAULT 0,
"CAMP_LNCH_DT_WID" NUMBER(10,0) DEFAULT 0,
"CAMP_OWNER_ORG_WID" NUMBER(10,0) DEFAULT 0,
"CAMP_PARTNER_WID" NUMBER(10,0) DEFAULT 0,
"CAMP_ST_DT_WID" NUMBER(10,0) DEFAULT 0,
"COMPLETED_DT_WID" NUMBER(10,0) DEFAULT 0,
"CONTACTED_DT_WID" NUMBER(10,0) DEFAULT 0,
"CONTACT_WID" NUMBER(10,0) DEFAULT 0,
"CON_OUTCOME_WID" NUMBER(10,0) DEFAULT 0,
"CREATED_DT_WID" NUMBER(10,0) DEFAULT 0,
"DATASOURCE_NUM_ID" NUMBER(10,0) DEFAULT 0,
"ETL_PROC_WID" NUMBER(10,0) DEFAULT 0,
"INTEGRATION_ID" VARCHAR2(30 CHAR) DEFAULT '0',
"LAUNCH_DT_WID" NUMBER(10,0) DEFAULT 0,
"LD_DT_WID" NUMBER(10,0) DEFAULT 0,
"LD_WAVE_WID" NUMBER(10,0) DEFAULT 0,
"MKT_REGN_WID" NUMBER(10,0) DEFAULT 0,
"PR_EMP_WID" NUMBER(10,0) DEFAULT 0,
"ROW_WID" NUMBER(10,0) DEFAULT 0,
"SCHEDULE_DT_WID" NUMBER(10,0) DEFAULT 0,
"SEGMENT_WID" NUMBER(10,0) DEFAULT 0,
"SOURCE_WID" NUMBER(10,0) DEFAULT 0,
"VENDOR_WID" NUMBER(10,0) DEFAULT 0,
"WAVE_WID" NUMBER(10,0) DEFAULT 0,
"COMPLETED_DT" DATE,
"CONTACTED_FLG" CHAR(1 CHAR),
"MSG_OPEN_TS" DATE,
"NUM_ATTEMPTS" NUMBER(10,0),
"NUM_DAYS_TOCONTACT" NUMBER(10,0),
"KEY01" VARCHAR2(30 CHAR),
"KEY02" VARCHAR2(30 CHAR),
"KEY03" VARCHAR2(30 CHAR),
"KEY04" VARCHAR2(30 CHAR),
"KEY05" VARCHAR2(30 CHAR),
"KEY06" VARCHAR2(30 CHAR),
"KEY07" VARCHAR2(30 CHAR),
"SOURCE_CODE" VARCHAR2(75 CHAR),
"X_CUSTOM" VARCHAR2(10 CHAR),
"PR_OWNER_BU_WID" NUMBER(10,0),
"OFFER_WID" NUMBER(10,0) DEFAULT 0,
"PARTY_WID" NUMBER(10,0) DEFAULT 0
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F4
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F4" ON "DWSIEBEL"."W_CAMP_HIST_F" ("WAVE_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F3
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F3" ON "DWSIEBEL"."W_CAMP_HIST_F" ("SEGMENT_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F5
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F5" ON "DWSIEBEL"."W_CAMP_HIST_F" ("VENDOR_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F6
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F6" ON "DWSIEBEL"."W_CAMP_HIST_F" ("CREATED_DT_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F7
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F7" ON "DWSIEBEL"."W_CAMP_HIST_F" ("COMPLETED_DT_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F8
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F8" ON "DWSIEBEL"."W_CAMP_HIST_F" ("CONTACTED_DT_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F9
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F9" ON "DWSIEBEL"."W_CAMP_HIST_F" ("CAMP_PARTNER_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_M1
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_M1" ON "DWSIEBEL"."W_CAMP_HIST_F" ("ETL_PROC_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F1
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F1" ON "DWSIEBEL"."W_CAMP_HIST_F" ("CONTACT_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F10
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F10" ON "DWSIEBEL"."W_CAMP_HIST_F" ("CALL_STATUS_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F11
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F11" ON "DWSIEBEL"."W_CAMP_HIST_F" ("CAMP_OWNER_ORG_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F12
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F12" ON "DWSIEBEL"."W_CAMP_HIST_F" ("PARTY_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F2
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F2" ON "DWSIEBEL"."W_CAMP_HIST_F" ("CAMPAIGN_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F20
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F20" ON "DWSIEBEL"."W_CAMP_HIST_F" ("CAMP_LNCH_DT_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F21
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F21" ON "DWSIEBEL"."W_CAMP_HIST_F" ("CAMP_END_DT_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F22
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F22" ON "DWSIEBEL"."W_CAMP_HIST_F" ("CAMP_ST_DT_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F25
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F25" ON "DWSIEBEL"."W_CAMP_HIST_F" ("MKT_REGN_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F26
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F26" ON "DWSIEBEL"."W_CAMP_HIST_F" ("PR_OWNER_BU_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F27
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F27" ON "DWSIEBEL"."W_CAMP_HIST_F" ("OFFER_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F16
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F16" ON "DWSIEBEL"."W_CAMP_HIST_F" ("SCHEDULE_DT_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F14
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F14" ON "DWSIEBEL"."W_CAMP_HIST_F" ("LD_WAVE_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F19
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F19" ON "DWSIEBEL"."W_CAMP_HIST_F" ("BNCE_TYPE_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F17
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F17" ON "DWSIEBEL"."W_CAMP_HIST_F" ("LAUNCH_DT_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F24
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F24" ON "DWSIEBEL"."W_CAMP_HIST_F" ("CON_OUTCOME_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F23
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F23" ON "DWSIEBEL"."W_CAMP_HIST_F" ("PR_EMP_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F15
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F15" ON "DWSIEBEL"."W_CAMP_HIST_F" ("LD_DT_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F13
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F13" ON "DWSIEBEL"."W_CAMP_HIST_F" ("SOURCE_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_F18
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_CAMP_HIST_F_F18" ON "DWSIEBEL"."W_CAMP_HIST_F" ("BNCE_REASON_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_P1
--------------------------------------------------------
CREATE UNIQUE INDEX "DWSIEBEL"."W_CAMP_HIST_F_P1" ON "DWSIEBEL"."W_CAMP_HIST_F" ("ROW_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_CAMP_HIST_F_U1
--------------------------------------------------------
CREATE UNIQUE INDEX "DWSIEBEL"."W_CAMP_HIST_F_U1" ON "DWSIEBEL"."W_CAMP_HIST_F" ("INTEGRATION_ID", "DATASOURCE_NUM_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- Constraints for Table W_CAMP_HIST_F
--------------------------------------------------------
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("PARTY_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("OFFER_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("WAVE_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("VENDOR_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("SOURCE_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("SEGMENT_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("SCHEDULE_DT_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("ROW_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("PR_EMP_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("MKT_REGN_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("LD_WAVE_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("LD_DT_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("LAUNCH_DT_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("INTEGRATION_ID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("ETL_PROC_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("DATASOURCE_NUM_ID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("CREATED_DT_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("CON_OUTCOME_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("CONTACT_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("CONTACTED_DT_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("COMPLETED_DT_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("CAMP_ST_DT_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("CAMP_PARTNER_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("CAMP_OWNER_ORG_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("CAMP_LNCH_DT_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("CAMP_END_DT_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("CAMPAIGN_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("CALL_STATUS_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("BNCE_TYPE_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_CAMP_HIST_F" MODIFY ("BNCE_REASON_WID" NOT NULL ENABLE);
|
|
|
|
| Re: Counting overlapping ID's in intersection tables. [message #650244 is a reply to message #650243] |
Thu, 21 April 2016 02:30 |
|
posefant
Messages: 13
Registered: April 2016
|
Junior Member |
|
|
--------------------------------------------------------
-- DDL for Table W_SOURCE_D
--------------------------------------------------------
CREATE TABLE "DWSIEBEL"."W_SOURCE_D"
( "DATASOURCE_NUM_ID" NUMBER(10,0) DEFAULT 0,
"ETL_PROC_WID" NUMBER(10,0) DEFAULT 0,
"INTEGRATION_ID" VARCHAR2(30 CHAR) DEFAULT '0',
"ROW_WID" NUMBER(10,0) DEFAULT 0,
"BDGT_AMT" NUMBER(22,7),
"BUDGET_REQ" NUMBER(22,7),
"CAMP_END_DT" DATE,
"CAMP_LNCH_DT" DATE,
"CAMP_START_DT" DATE,
"CAMP_TGT_CALLS" NUMBER(10,0),
"EVTPLN_END_DT" DATE,
"EVTPLN_START_DT" DATE,
"EVT_END_DT" DATE,
"EVT_START_DT" DATE,
"MKTPLN_END_DT" DATE,
"MKTPLN_START_DT" DATE,
"PROG_END_DT" DATE,
"PROG_START_DT" DATE,
"SA_CAMP_FLG" CHAR(1 CHAR),
"SESN_END_DT" DATE,
"SESN_START_DT" DATE,
"CAMP_BU" VARCHAR2(100 CHAR),
"CAMP_CALL_SCR" VARCHAR2(100 CHAR),
"CAMP_DNIS" VARCHAR2(40 CHAR),
"CAMP_ID" VARCHAR2(30 CHAR),
"CAMP_LANG" VARCHAR2(50 CHAR),
"CAMP_NAME" VARCHAR2(100 CHAR),
"CAMP_OU" VARCHAR2(100 CHAR),
"CAMP_PERIOD" VARCHAR2(50 CHAR),
"CAMP_PRIORITY" VARCHAR2(30 CHAR),
"CAMP_PROMO_NUM" VARCHAR2(30 CHAR),
"CAMP_REGN" VARCHAR2(50 CHAR),
"CAMP_REGN_ID" VARCHAR2(30 CHAR),
"CAMP_SRC_NUM" VARCHAR2(30 CHAR),
"CAMP_STATUS" VARCHAR2(30 CHAR),
"CAMP_STATUS_I" VARCHAR2(50 CHAR),
"CAMP_TYPE" VARCHAR2(30 CHAR),
"CAMP_TYPE_I" VARCHAR2(50 CHAR),
"EVTPLN_BU" VARCHAR2(100 CHAR),
"EVTPLN_ID" VARCHAR2(30 CHAR),
"EVTPLN_NAME" VARCHAR2(100 CHAR),
"EVTPLN_OU" VARCHAR2(100 CHAR),
"EVTPLN_PERIOD" VARCHAR2(50 CHAR),
"EVTPLN_REGN" VARCHAR2(50 CHAR),
"EVTPLN_REGN_ID" VARCHAR2(30 CHAR),
"EVTPLN_STATUS" VARCHAR2(30 CHAR),
"EVTPLN_STATUS_I" VARCHAR2(50 CHAR),
"EVTPLN_TYPE" VARCHAR2(30 CHAR),
"EVTPLN_TYPE_I" VARCHAR2(50 CHAR),
"EVT_BU" VARCHAR2(100 CHAR),
"EVT_ID" VARCHAR2(30 CHAR),
"EVT_NAME" VARCHAR2(100 CHAR),
"EVT_OU" VARCHAR2(100 CHAR),
"EVT_PERIOD" VARCHAR2(50 CHAR),
"EVT_REGN" VARCHAR2(50 CHAR),
"EVT_REGN_ID" VARCHAR2(30 CHAR),
"EVT_STATUS" VARCHAR2(30 CHAR),
"EVT_STATUS_I" VARCHAR2(50 CHAR),
"EVT_TYPE" VARCHAR2(30 CHAR),
"EVT_TYPE_I" VARCHAR2(50 CHAR),
"MKTPLN_BU" VARCHAR2(100 CHAR),
"MKTPLN_ID" VARCHAR2(30 CHAR),
"MKTPLN_NAME" VARCHAR2(100 CHAR),
"MKTPLN_OU" VARCHAR2(100 CHAR),
"MKTPLN_PERIOD" VARCHAR2(50 CHAR),
"MKTPLN_PROMO_NUM" VARCHAR2(30 CHAR),
"MKTPLN_REGN" VARCHAR2(50 CHAR),
"MKTPLN_REGN_ID" VARCHAR2(30 CHAR),
"MKTPLN_SRC_NUM" VARCHAR2(30 CHAR),
"MKTPLN_STATUS" VARCHAR2(30 CHAR),
"MKTPLN_STATUS_I" VARCHAR2(50 CHAR),
"MKTPLN_TYPE" VARCHAR2(30 CHAR),
"MKTPLN_TYPE_I" VARCHAR2(50 CHAR),
"PROG_BU" VARCHAR2(100 CHAR),
"PROG_ID" VARCHAR2(30 CHAR),
"PROG_NAME" VARCHAR2(100 CHAR),
"PROG_OU" VARCHAR2(100 CHAR),
"PROG_PERIOD" VARCHAR2(50 CHAR),
"PROG_PROMO_NUM" VARCHAR2(30 CHAR),
"PROG_REGN" VARCHAR2(50 CHAR),
"PROG_REGN_ID" VARCHAR2(30 CHAR),
"PROG_SRC_NUM" VARCHAR2(30 CHAR),
"PROG_STATUS" VARCHAR2(30 CHAR),
"PROG_STATUS_I" VARCHAR2(50 CHAR),
"PROG_TYPE" VARCHAR2(30 CHAR),
"PROG_TYPE_I" VARCHAR2(50 CHAR),
"SESN_BU" VARCHAR2(100 CHAR),
"SESN_ID" VARCHAR2(30 CHAR),
"SESN_NAME" VARCHAR2(100 CHAR),
"SESN_OU" VARCHAR2(100 CHAR),
"SESN_PERIOD" VARCHAR2(50 CHAR),
"SESN_REGN" VARCHAR2(50 CHAR),
"SESN_REGN_ID" VARCHAR2(30 CHAR),
"SESN_STATUS" VARCHAR2(30 CHAR),
"SESN_STATUS_I" VARCHAR2(50 CHAR),
"SESN_TYPE" VARCHAR2(30 CHAR),
"SESN_TYPE_I" VARCHAR2(50 CHAR),
"SRC_TYPE" VARCHAR2(30 CHAR),
"STG_ID" VARCHAR2(30 CHAR),
"STG_NAME" VARCHAR2(100 CHAR),
"STG_PROMO_NUM" VARCHAR2(30 CHAR),
"STG_SRC_NUM" VARCHAR2(30 CHAR),
"VIS_PR_BU_ID" VARCHAR2(30 CHAR),
"VIS_PR_POS_ID" VARCHAR2(30 CHAR),
"VIS_PR_POSTN_DH_WID" NUMBER(10,0) DEFAULT 0,
"X_CUSTOM" VARCHAR2(10 CHAR)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M16
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M16" ON "DWSIEBEL"."W_SOURCE_D" ("PROG_REGN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M15
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M15" ON "DWSIEBEL"."W_SOURCE_D" ("PROG_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M14
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M14" ON "DWSIEBEL"."W_SOURCE_D" ("PROG_BU")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M13
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M13" ON "DWSIEBEL"."W_SOURCE_D" ("PROG_PERIOD")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M12
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M12" ON "DWSIEBEL"."W_SOURCE_D" ("STG_SRC_NUM")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M11
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M11" ON "DWSIEBEL"."W_SOURCE_D" ("STG_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M10
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M10" ON "DWSIEBEL"."W_SOURCE_D" ("SA_CAMP_FLG")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M1
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M1" ON "DWSIEBEL"."W_SOURCE_D" ("CAMP_PERIOD")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M42
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M42" ON "DWSIEBEL"."W_SOURCE_D" ("MKTPLN_BU")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M9
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M9" ON "DWSIEBEL"."W_SOURCE_D" ("CAMP_TYPE_I")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M53
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M53" ON "DWSIEBEL"."W_SOURCE_D" ("MKTPLN_OU")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M52
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M52" ON "DWSIEBEL"."W_SOURCE_D" ("EVTPLN_OU")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M51
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M51" ON "DWSIEBEL"."W_SOURCE_D" ("EVT_OU")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M50
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M50" ON "DWSIEBEL"."W_SOURCE_D" ("SESN_OU")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M5
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M5" ON "DWSIEBEL"."W_SOURCE_D" ("CAMP_SRC_NUM")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M49
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M49" ON "DWSIEBEL"."W_SOURCE_D" ("PROG_OU")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M48
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M48" ON "DWSIEBEL"."W_SOURCE_D" ("CAMP_OU")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M47
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M47" ON "DWSIEBEL"."W_SOURCE_D" ("MKTPLN_TYPE_I")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M46
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M46" ON "DWSIEBEL"."W_SOURCE_D" ("MKTPLN_TYPE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M45
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M45" ON "DWSIEBEL"."W_SOURCE_D" ("MKTPLN_SRC_NUM")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M44
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M44" ON "DWSIEBEL"."W_SOURCE_D" ("MKTPLN_REGN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M43
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M43" ON "DWSIEBEL"."W_SOURCE_D" ("MKTPLN_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M8
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M8" ON "DWSIEBEL"."W_SOURCE_D" ("CAMP_TYPE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M41
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M41" ON "DWSIEBEL"."W_SOURCE_D" ("MKTPLN_PERIOD")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_P1
--------------------------------------------------------
CREATE UNIQUE INDEX "DWSIEBEL"."W_SOURCE_D_P1" ON "DWSIEBEL"."W_SOURCE_D" ("ROW_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_U1
--------------------------------------------------------
CREATE UNIQUE INDEX "DWSIEBEL"."W_SOURCE_D_U1" ON "DWSIEBEL"."W_SOURCE_D" ("INTEGRATION_ID", "DATASOURCE_NUM_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_F1
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_F1" ON "DWSIEBEL"."W_SOURCE_D" ("VIS_PR_POSTN_DH_WID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL"
PARALLEL ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M7
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M7" ON "DWSIEBEL"."W_SOURCE_D" ("CAMP_STATUS_I")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M62
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M62" ON "DWSIEBEL"."W_SOURCE_D" ("MKTPLN_STATUS_I")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M61
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M61" ON "DWSIEBEL"."W_SOURCE_D" ("MKTPLN_STATUS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M60
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M60" ON "DWSIEBEL"."W_SOURCE_D" ("SESN_TYPE_I")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M6
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M6" ON "DWSIEBEL"."W_SOURCE_D" ("CAMP_STATUS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M59
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M59" ON "DWSIEBEL"."W_SOURCE_D" ("SESN_TYPE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M58
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M58" ON "DWSIEBEL"."W_SOURCE_D" ("EVT_TYPE_I")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M57
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M57" ON "DWSIEBEL"."W_SOURCE_D" ("EVT_TYPE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M56
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M56" ON "DWSIEBEL"."W_SOURCE_D" ("EVTPLN_TYPE_I")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M55
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M55" ON "DWSIEBEL"."W_SOURCE_D" ("EVTPLN_TYPE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M54
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M54" ON "DWSIEBEL"."W_SOURCE_D" ("SRC_TYPE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M40
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M40" ON "DWSIEBEL"."W_SOURCE_D" ("EVTPLN_STATUS_I")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M4
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M4" ON "DWSIEBEL"."W_SOURCE_D" ("CAMP_REGN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M39
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M39" ON "DWSIEBEL"."W_SOURCE_D" ("EVTPLN_STATUS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M38
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M38" ON "DWSIEBEL"."W_SOURCE_D" ("EVTPLN_REGN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M36
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M36" ON "DWSIEBEL"."W_SOURCE_D" ("EVTPLN_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M35
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M35" ON "DWSIEBEL"."W_SOURCE_D" ("EVTPLN_BU")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M34
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M34" ON "DWSIEBEL"."W_SOURCE_D" ("EVTPLN_PERIOD")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M33
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M33" ON "DWSIEBEL"."W_SOURCE_D" ("EVT_STATUS_I")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M32
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M32" ON "DWSIEBEL"."W_SOURCE_D" ("EVT_STATUS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M31
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M31" ON "DWSIEBEL"."W_SOURCE_D" ("EVT_REGN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M30
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M30" ON "DWSIEBEL"."W_SOURCE_D" ("EVT_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M3
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M3" ON "DWSIEBEL"."W_SOURCE_D" ("CAMP_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M29
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M29" ON "DWSIEBEL"."W_SOURCE_D" ("EVT_BU")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M28
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M28" ON "DWSIEBEL"."W_SOURCE_D" ("EVT_PERIOD")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M27
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M27" ON "DWSIEBEL"."W_SOURCE_D" ("SESN_STATUS_I")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M26
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M26" ON "DWSIEBEL"."W_SOURCE_D" ("SESN_STATUS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M25
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M25" ON "DWSIEBEL"."W_SOURCE_D" ("SESN_REGN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M24
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M24" ON "DWSIEBEL"."W_SOURCE_D" ("SESN_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M23
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M23" ON "DWSIEBEL"."W_SOURCE_D" ("SESN_BU")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M22
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M22" ON "DWSIEBEL"."W_SOURCE_D" ("SESN_PERIOD")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M21
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M21" ON "DWSIEBEL"."W_SOURCE_D" ("PROG_TYPE_I")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M20
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M20" ON "DWSIEBEL"."W_SOURCE_D" ("PROG_TYPE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M2
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M2" ON "DWSIEBEL"."W_SOURCE_D" ("CAMP_BU")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M19
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M19" ON "DWSIEBEL"."W_SOURCE_D" ("PROG_STATUS_I")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M18
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M18" ON "DWSIEBEL"."W_SOURCE_D" ("PROG_STATUS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- DDL for Index W_SOURCE_D_M17
--------------------------------------------------------
CREATE BITMAP INDEX "DWSIEBEL"."W_SOURCE_D_M17" ON "DWSIEBEL"."W_SOURCE_D" ("PROG_SRC_NUM")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DWSIEBEL" ;
--------------------------------------------------------
-- Constraints for Table W_SOURCE_D
--------------------------------------------------------
ALTER TABLE "DWSIEBEL"."W_SOURCE_D" MODIFY ("VIS_PR_POSTN_DH_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_SOURCE_D" MODIFY ("ROW_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_SOURCE_D" MODIFY ("INTEGRATION_ID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_SOURCE_D" MODIFY ("ETL_PROC_WID" NOT NULL ENABLE);
ALTER TABLE "DWSIEBEL"."W_SOURCE_D" MODIFY ("DATASOURCE_NUM_ID" NOT NULL ENABLE);
|
|
|
|
|
|
| Re: Counting overlapping ID's in intersection tables. [message #650248 is a reply to message #650245] |
Thu, 21 April 2016 02:59 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
This may be (yet another) case of my favourite mantra:
"You must understand your data! You must understand your queries!"
I may be wrong, because I don't know your data or the query, but I would immediately ask these questions:
This lineSELECT DISTINCT CHF.CONTACT_WID AS CONTACT_WID, SRC.ROW_WID AS CAMP_WID, OSRC.ROW_WID AS OVRLP_CAMP_WID GROUP BY OVERLAP.CAMP_WID, OVERLAP.OVRLP_CAMP_WID
Then you are doing this twice,SELECT CONTACT_WID, SOURCE_WID
FROM W_CAMP_HIST_F
WHERE LAUNCH_DT_WID = 0 AND SOURCE_WID > 0 AND CONTACT_WID > 0
GROUP BY CONTACT_WID, SOURCE_WID
To repeat:Quote:"You must understand your data! You must understand your queries!"
|
|
|
|
|
|
|
|
| Re: Counting overlapping ID's in intersection tables. [message #650251 is a reply to message #650249] |
Thu, 21 April 2016 03:23 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
posefant wrote on Thu, 21 April 2016 09:07I don't have access to the server so I'm waiting for the formatted trace dump.
The PLAN you see here is from the QA environment, the table in Production has 129mill rows compared to the one in QA with 57mill.
Also, this SQL statement is part of a WorkFlow, the initial steps are to delete all indexes, then they are recreated afterwords.
I have tried to insert a hint but it seems my client is not cathing it.
I'm using SQL Developer and the hit was inserted as:
/*+ NO_INDEX(SRC) NO_INDEX(OSRC) */
/pF
This calls for my second favourite mantra:Quote:Developers who use hints should be shot First, you are using the hint incorrectly. How do I know this? Because it didn't work. Second, the sections of code where it would work are the inline views materialized at operations 6 and 12. These are costed at 4. Do you think that reducing this cost would be significant?
If you don't understand your data, your queries, or hints, you should leave the tuning to your DBA. Throwing hints at code usually causes more problems than solutions. You should probably be glad that your error prevented the hint from working..
|
|
|
|
| Re: Counting overlapping ID's in intersection tables. [message #650253 is a reply to message #650248] |
Thu, 21 April 2016 03:44 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
John Watson wrote on Thu, 21 April 2016 08:59
Then you are doing this twice,SELECT CONTACT_WID, SOURCE_WID
FROM W_CAMP_HIST_F
WHERE LAUNCH_DT_WID = 0 AND SOURCE_WID > 0 AND CONTACT_WID > 0
GROUP BY CONTACT_WID, SOURCE_WID
the aggregation is preventing view merging, but in the execution plan if you look at operations 19 and 22 you'll see that the CBO does not think that the aggregation will remove any rows. You need to think about whether there are any duplicates, and if so whether they could be handled differently.
It doesn't matter if there are duplicates since both the distinct at the level above and the group by at the level above that will remove them anyway.
As I said initially, though I probably wasn't clear enough, the nested, and associated distincts and group bys do nothing useful.
I'm pretty sure this will give the same result:
SELECT SRC.ROW_WID, OSRC.ROW_WID, COUNT (CHF.CONTACT_WID) AS NUM_OVRLP_ACCNTS
FROM W_CAMP_HIST_F CHF,
W_CAMP_HIST_F OCHF
W_SOURCE_D SRC,
W_SOURCE_D OSRC
WHERE CHF.SOURCE_WID = SRC.ROW_WID
AND OCHF.SOURCE_WID = OSRC.ROW_WID
AND CHF.SOURCE_WID <> OCHF.SOURCE_WID
AND CHF.CONTACT_WID = OCHF.CONTACT_WID
AND SRC.SRC_TYPE = OSRC.SRC_TYPE
AND OCHF.LAUNCH_DT_WID = 0
AND OCHF.SOURCE_WID > 0
AND OCHF.CONTACT_WID > 0
AND CHF.LAUNCH_DT_WID = 0
AND CHF.SOURCE_WID > 0
AND CHF.CONTACT_WID > 0
AND SRC.SRC_TYPE = 'Campaign'
AND OSRC.SRC_TYPE = 'Campaign'
GROUP BY SRC.ROW_WID, OSRC.ROW_WID
ORDER BY 1, 2;
And that should give oracle more flexibilty to come up with an efficient plan.
|
|
|
|
| Re: Counting overlapping ID's in intersection tables. [message #650254 is a reply to message #650251] |
Thu, 21 April 2016 03:44 |
|
posefant
Messages: 13
Registered: April 2016
|
Junior Member |
|
|
John Watson: Thank you for your reply, I will report it as a possible bug in the SR. Do you have more information regarding the hint ? It is inserted as a variable before the SQL is generated in PowerCenter, I don't know how it is parsed though, but I could not see any difference in the PLAN. The statement with possible hints looks like this:
SELECT
OVERLAP.CAMP_WID,
OVERLAP.OVRLP_CAMP_WID,
COUNT (OVERLAP.PARTY_WID) AS NUM_OVRLP_ACCNTS
FROM
(SELECT $$Hint1
DISTINCT CHF.PARTY_WID AS PARTY_WID,
SRC.ROW_WID AS CAMP_WID,
OSRC.ROW_WID AS OVRLP_CAMP_WID
FROM
(select $$Hint2 PARTY_WID, SOURCE_WID
from W_CAMP_HIST_F
where
LAUNCH_DT_WID = 0 AND
SOURCE_WID > 0 AND
PARTY_WID >0
group by PARTY_WID, SOURCE_WID
) CHF,
(select $$Hint3 PARTY_WID, SOURCE_WID
from W_CAMP_HIST_F
where
LAUNCH_DT_WID = 0 AND
SOURCE_WID > 0 AND
PARTY_WID >0
group by PARTY_WID, SOURCE_WID
) OCHF,
W_SOURCE_D SRC,
W_SOURCE_D OSRC
WHERE
CHF.SOURCE_WID = SRC.ROW_WID AND
OCHF.SOURCE_WID = OSRC.ROW_WID AND
CHF.SOURCE_WID <> OCHF.SOURCE_WID AND
CHF.PARTY_WID = OCHF.PARTY_WID AND
SRC.SRC_TYPE = 'Campaign' AND
OSRC.SRC_TYPE = 'Campaign'
)
OVERLAP
GROUP BY
OVERLAP.CAMP_WID,
OVERLAP.OVRLP_CAMP_WID
ORDER BY
1,2
The hint was inserted as refered in OBI Performance Recommendations page 69 for the WorkFlow SIL_Agg_OverlappingCampaignAccounts:
$$HINT1 /*+ NO_INDEX(SRC) NO_INDEX(OSRC) */
/pF
Edit: Wrong Hint copied from the guide.
[Updated on: Thu, 21 April 2016 03:46] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Counting overlapping ID's in intersection tables. [message #650257 is a reply to message #650253] |
Thu, 21 April 2016 04:17 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Perhaps it can be simplified further.
W_SOURCE_D is joined to itself on SRC.SRC_TYPE = OSRC.SRC_TYPE and both times filtered on SRC_TYPE = 'Campaign'. It must be possible to hit the table only once instead of twice.
Also, W_SOURCE_D.ROW_WID is effectively a primary key (though it isn't declared as such) so perhaps there is still an unnecessary aggregation. I think the CBO has realized this and not costed it, but again removing it might give more options.
|
|
|
|
|
|
|
|
|
|
| Re: Counting overlapping ID's in intersection tables. [message #650262 is a reply to message #650257] |
Thu, 21 April 2016 05:04 |
|
posefant
Messages: 13
Registered: April 2016
|
Junior Member |
|
|
John Watson wrote on Thu, 21 April 2016 11:17Perhaps it can be simplified further.
W_SOURCE_D is joined to itself on SRC.SRC_TYPE = OSRC.SRC_TYPE and both times filtered on SRC_TYPE = 'Campaign'. It must be possible to hit the table only once instead of twice.
Also, W_SOURCE_D.ROW_WID is effectively a primary key (though it isn't declared as such) so perhaps there is still an unnecessary aggregation. I think the CBO has realized this and not costed it, but again removing it might give more options.
I assume this is a standard way of avoiding cartesian joins for OBIEE when you have one fact and two dimensions. I think it's called a "Trap Fan". Instead of having one statement, it is invoked as two inline views and then aggregated to get the counts correctly.
In this particular context, all the Campaigns and all the Accounts/Contacts for the campaign have their keys stored in W_CAMP_HIST_F.
When we create a new campaign, we need to see how many of the Accounts/Contacts have been a member of prior campaigns.
Camp_1, Camp_n Count_Duplicates_from_Camp_n_in_Camp_1
Camp_1+n, Camp_n+1 Count_Duplicates_from_Camp_n+1_in_Camp_1+n
etc
I do not understand why they are doing it like this when we only have two tables, but it works auto-magically.
I have tried to play around a little bit with the basic SQL skills I have, but anything I do increase the estimated cost and execution time or estimates a less rows than expected.
Because the tables are quite large and the actual challenge here is the hash join, it takes hours to do a simple test on actual data.
You are right that ROW_ID is the primary key, in the Siebel world all the relations are moved to the business layer and not reflected in the schema itself, although on MSSQL and Oracle you can assume it by the clustering and the name of the index xxx_P1, although in the OLAP schema it is only unique and not clustered.
|
|
|
|
|
|
| Re: Counting overlapping ID's in intersection tables. [message #650264 is a reply to message #650263] |
Thu, 21 April 2016 05:15 |
|
posefant
Messages: 13
Registered: April 2016
|
Junior Member |
|
|
TKPROF: Release 12.1.0.2.0 - Development on Thu Apr 21 12:02:46 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Trace file: QDWS_ora_40801_48641_CAMPAIGN_CONTACT_WID.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: 47r1y8yn34jmj Plan Hash: 2191121161
select default$
from
col$ where rowid=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 8 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.00 0 8 0 4
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 3
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY USER ROWID COL$ (cr=1 pr=0 pw=0 time=5 us cost=1 size=15 card=1)
********************************************************************************
SQL ID: frjd8zfy2jfdq Plan Hash: 987782979
SELECT executions, end_of_fetch_count, elapsed_time/px_servers
elapsed_time, cpu_time/px_servers cpu_time,
buffer_gets/executions buffer_gets
FROM
(SELECT sum(executions) as executions, sum(case
when px_servers_executions > 0 then
px_servers_executions else executions end)
as px_servers, sum(end_of_fetch_count) as end_of_fetch_count,
sum(elapsed_time) as elapsed_time,
sum(cpu_time) as cpu_time, sum(buffer_gets) as
buffer_gets FROM gv$sql
WHERE executions > 0 AND sql_id = :1
AND parsing_schema_name = :2)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 VIEW (cr=0 pr=0 pw=0 time=37 us)
1 1 1 SORT AGGREGATE (cr=0 pr=0 pw=0 time=37 us)
1 1 1 FIXED TABLE FIXED INDEX X$KGLCURSOR_CHILD (ind:2) (cr=0 pr=0 pw=0 time=31 us)
********************************************************************************
SELECT OVERLAP.CAMP_WID, OVERLAP.OVRLP_CAMP_WID, COUNT (OVERLAP.CONTACT_WID) AS NUM_OVRLP_ACCNTS
FROM (SELECT DISTINCT CHF.CONTACT_WID AS CONTACT_WID, SRC.ROW_WID AS CAMP_WID, OSRC.ROW_WID AS OVRLP_CAMP_WID
FROM ( SELECT CONTACT_WID, SOURCE_WID
FROM W_CAMP_HIST_F
WHERE LAUNCH_DT_WID = 0 AND SOURCE_WID > 0 AND CONTACT_WID > 0
GROUP BY CONTACT_WID, SOURCE_WID) CHF,
( SELECT CONTACT_WID, SOURCE_WID
FROM W_CAMP_HIST_F
WHERE LAUNCH_DT_WID = 0 AND SOURCE_WID > 0 AND CONTACT_WID > 0
GROUP BY CONTACT_WID, SOURCE_WID) OCHF,
W_SOURCE_D SRC,
W_SOURCE_D OSRC
WHERE CHF.SOURCE_WID = SRC.ROW_WID
AND OCHF.SOURCE_WID = OSRC.ROW_WID
AND CHF.SOURCE_WID <> OCHF.SOURCE_WID
AND CHF.CONTACT_WID = OCHF.CONTACT_WID
AND SRC.SRC_TYPE = 'Campaign'
AND OSRC.SRC_TYPE = 'Campaign') OVERLAP
GROUP BY OVERLAP.CAMP_WID, OVERLAP.OVRLP_CAMP_WID
ORDER BY 1, 2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 80980 5546.39 5560.18 8541802 259250 0 1214678
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 80982 5546.39 5560.19 8541802 259252 0 1214678
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 69 (DWSIEBEL)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1214678 1214678 1214678 SORT GROUP BY (cr=259250 pr=8541802 pw=8541771 time=1264952811 us cost=6541658 size=248327150 card=9933086)
2453235748 2453235748 2453235748 VIEW (cr=259250 pr=8541802 pw=8541771 time=3139842307 us cost=6541658 size=11315982225 card=452639289)
2453235748 2453235748 2453235748 HASH UNIQUE (cr=259250 pr=8541802 pw=8541771 time=2861387650 us cost=6541658 size=25347800184 card=452639289)
2453235748 2453235748 2453235748 HASH JOIN RIGHT SEMI (cr=259250 pr=297879 pw=297848 time=1925032581 us cost=373103 size=25347800184 card=452639289)
4197 4197 4197 VIEW index$_join$_007 (cr=17 pr=0 pw=0 time=2740 us cost=5 size=66560 card=4160)
4197 4197 4197 HASH JOIN (cr=17 pr=0 pw=0 time=2232 us)
4197 4197 4197 BITMAP CONVERSION TO ROWIDS (cr=1 pr=0 pw=0 time=13 us cost=1 size=66560 card=4160)
1 1 1 BITMAP INDEX SINGLE VALUE W_SOURCE_D_M54 (cr=1 pr=0 pw=0 time=9 us)(object id 22436)
4280 4280 4280 INDEX FAST FULL SCAN W_SOURCE_D_P1 (cr=16 pr=0 pw=0 time=394 us cost=5 size=66560 card=4160)(object id 21555)
2453235748 2453235748 2453235748 HASH JOIN RIGHT SEMI (cr=259233 pr=297879 pw=297848 time=1294738133 us cost=371951 size=18105571560 card=452639289)
4197 4197 4197 VIEW index$_join$_006 (cr=17 pr=0 pw=0 time=2842 us cost=5 size=66560 card=4160)
4197 4197 4197 HASH JOIN (cr=17 pr=0 pw=0 time=2333 us)
4197 4197 4197 BITMAP CONVERSION TO ROWIDS (cr=1 pr=0 pw=0 time=261 us cost=1 size=66560 card=4160)
1 1 1 BITMAP INDEX SINGLE VALUE W_SOURCE_D_M54 (cr=1 pr=0 pw=0 time=5 us)(object id 22436)
4280 4280 4280 INDEX FAST FULL SCAN W_SOURCE_D_P1 (cr=16 pr=0 pw=0 time=136 us cost=5 size=66560 card=4160)(object id 21555)
2453235748 2453235748 2453235748 HASH JOIN (cr=259216 pr=297879 pw=297848 time=658209395 us cost=370798 size=10863342936 card=452639289)
34828352 34828352 34828352 VIEW (cr=129608 pr=64356 pw=64356 time=28870272 us cost=145261 size=413494404 card=34457867)
34828352 34828352 34828352 HASH GROUP BY (cr=129608 pr=64356 pw=64356 time=25752836 us cost=145261 size=585783739 card=34457867)
37556584 37556584 37556584 INDEX RANGE SCAN W_CAMP_HIST_F_N1 (cr=129608 pr=0 pw=0 time=5907724 us cost=145261 size=585783739 card=34457867)(object id 23715)
34828352 34828352 34828352 VIEW (cr=129608 pr=68107 pw=68231 time=25749826 us cost=145261 size=413494404 card=34457867)
34828352 34828352 34828352 HASH GROUP BY (cr=129608 pr=68107 pw=68231 time=22579910 us cost=145261 size=585783739 card=34457867)
37556584 37556584 37556584 INDEX RANGE SCAN W_CAMP_HIST_F_N1 (cr=129608 pr=0 pw=0 time=6134675 us cost=145261 size=585783739 card=34457867)(object id 23715)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1214678 SORT (GROUP BY)
2453235748 VIEW
2453235748 HASH (UNIQUE)
2453235748 HASH JOIN (RIGHT SEMI)
4197 VIEW OF 'index$_join$_007' (VIEW)
4197 HASH JOIN
4197 BITMAP CONVERSION (TO ROWIDS)
1 BITMAP INDEX (SINGLE VALUE) OF 'W_SOURCE_D_M54'
(INDEX (BITMAP))
4280 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'W_SOURCE_D_P1' (INDEX (UNIQUE))
2453235748 HASH JOIN (RIGHT SEMI)
4197 VIEW OF 'index$_join$_006' (VIEW)
4197 HASH JOIN
4197 BITMAP CONVERSION (TO ROWIDS)
1 BITMAP INDEX (SINGLE VALUE) OF 'W_SOURCE_D_M54'
(INDEX (BITMAP))
4280 INDEX MODE: ANALYZED (FAST FULL SCAN) OF
'W_SOURCE_D_P1' (INDEX (UNIQUE))
2453235748 HASH JOIN
34828352 VIEW
34828352 HASH (GROUP BY)
37556584 INDEX MODE: ANALYZED (RANGE SCAN) OF
'W_CAMP_HIST_F_N1' (INDEX)
34828352 VIEW
34828352 HASH (GROUP BY)
37556584 INDEX MODE: ANALYZED (RANGE SCAN) OF
'W_CAMP_HIST_F_N1' (INDEX)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 80980 5546.39 5560.18 8541802 259250 0 1214678
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 80982 5546.39 5560.19 8541802 259252 0 1214678
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 8 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.00 0.00 0 8 0 5
Misses in library cache during parse: 2
Misses in library cache during execute: 2
1 user SQL statements in session.
4 internal SQL statements in session.
5 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: QDWS_ora_40801_48641_CAMPAIGN_CONTACT_WID.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
1 user SQL statements in trace file.
4 internal SQL statements in trace file.
5 SQL statements in trace file.
3 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
DWSIEBEL.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
81143 lines in trace file.
5579 elapsed seconds in trace file.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 17:23:57 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
