Hi Tom,
Below is two tables where initial data will be near 47 Millon and all foreign table data will max 10000 thousand ,then per day transaction will be 1 millon. Is my below structure is ok or need some changes to support this process?Also I want to load data from source to these two tables. Please advise what tune I can add in sql for fastest insertion.
CREATE TABLE "C360_DWH_VER"."F_CUSTOMER_TRANSACTION"
( "TRANSACTION_ID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"D_STORE_KEY" NUMBER(20,0) NOT NULL ENABLE,
"TRANSACTION_TIMESTAMP" VARCHAR2(14 BYTE) NOT NULL ENABLE,
"D_MODE_OF_PAYMENT_KEY" NUMBER(20,0) NOT NULL ENABLE,
"D_TIME_KEY" NUMBER(5,0) NOT NULL ENABLE,
"TRANSACTION_DATE_KEY" NUMBER(20,0) NOT NULL ENABLE,
"TRANSACTION_VALUE" NUMBER(18,4) NOT NULL ENABLE,
"VAT_AMOUNT" NUMBER(18,4) NOT NULL ENABLE,
"MARGIN" NUMBER(18,4) NOT NULL ENABLE,
"D_TRANSACTION_CHANNEL_KEY" NUMBER(20,0) NOT NULL ENABLE,
"HASHED_LOYALTY_ID" VARCHAR2(100 BYTE),
"D_CUSTOMER_KEY" NUMBER(20,0),
"D_RECEIPT_TYPE_KEY" NUMBER(20,0),
"TOTAL_DISCOUNT" NUMBER(18,4),
"DA_INSERT" TIMESTAMP (6) NOT NULL ENABLE,
"DA_LAST_UPDT" TIMESTAMP (6) NOT NULL ENABLE,
"INSERTED_BY" VARCHAR2(100 BYTE),
"LAST_UPDT_BY" VARCHAR2(100 BYTE),
"ETL_ID" VARCHAR2(20 BYTE),
"ETL_PROC_TS" TIMESTAMP (3),
CONSTRAINT "PK_F_CUSTOMER_TRANSACTION" PRIMARY KEY ("D_STORE_KEY", "TRANSACTION_ID", "TRANSACTION_TIMESTAMP")
USING INDEX PCTFREE 10 INITRANS 30 MAXTRANS 255 COMPUTE STATISTICS
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 "VER_INDEX" ENABLE,
CONSTRAINT "FK_CUST_TRANS" FOREIGN KEY ("D_TRANSACTION_CHANNEL_KEY")
REFERENCES "C360_DWH_VER"."D_TRANSACTION_CHANNEL" ("D_TRANSACTION_CHANNEL_KEY") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 30 MAXTRANS 255
NOCOMPRESS LOGGING
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 "TBS_DWH_VER"
PARALLEL 32 ;
----------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE "C360_DWH_VER"."F_CUST_TRANSACTION_LINE_ITEMS"
( "TRANSACTION_ID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"TRANSACTION_TIMESTAMP" VARCHAR2(14 BYTE) NOT NULL ENABLE,
"STORE_KEY" NUMBER(20,0) NOT NULL ENABLE,
"OFFER_SEQUENCE_ID" NUMBER(13,0) NOT NULL ENABLE,
"D_PRODUCT_KEY" NUMBER(20,0) NOT NULL ENABLE,
"PRICE" NUMBER(18,4) NOT NULL ENABLE,
"QUANTITY" NUMBER(11,4) NOT NULL ENABLE,
"EVENT_IND" NUMBER(1,0) NOT NULL ENABLE,
"SLIP_TYPE" NUMBER(1,0),
"D_CENTRAL_PROMOTION_KEY" NUMBER(20,0),
"D_PERSONAL_OFFER_KEY" NUMBER(20,0),
"D_LINE_ITEM_TYPE_KEY" NUMBER(20,0),
"D_SLIP_KEY" NUMBER(20,0),
"DATE_KEY" NUMBER(20,0),
"D_TIME_KEY" NUMBER(5,0),
"D_TRANSACTION_CHANNEL_KEY" NUMBER(20,0),
"CUSTOMER_KEY" NUMBER(20,0),
"DISCOUNT_QUANTITY" NUMBER(11,4),
"DISCOUNT_VALUE" NUMBER(18,4),
"MECHANIC_ID" NUMBER(15,0),
"VOUCHER_VALUE" NUMBER(18,4),
"INSERTED_BY" VARCHAR2(100 BYTE),
"ETL_ID" VARCHAR2(20 BYTE),
"ETL_PROC_TS" TIMESTAMP (3),
CONSTRAINT "PK_F_CUST_TRANSACTION_LINE" PRIMARY KEY ("TRANSACTION_ID", "TRANSACTION_TIMESTAMP", "STORE_KEY", "OFFER_SEQUENCE_ID", "D_PRODUCT_KEY")
USING INDEX PCTFREE 10 INITRANS 30 MAXTRANS 255 COMPUTE STATISTICS
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 "VER_INDEX" ENABLE,
CONSTRAINT "FK_CUST_TRAN_STORE" FOREIGN KEY ("STORE_KEY")
REFERENCES "C360_DWH_VER"."D_STORE" ("D_STORE_KEY") ENABLE,
CONSTRAINT "FK_CUST_TRAN_LINE" FOREIGN KEY ("D_LINE_ITEM_TYPE_KEY")
REFERENCES "C360_DWH_VER"."D_LINE_ITEM_TYPE" ("D_LINE_ITEM_TYPE_KEY") ENABLE,
CONSTRAINT "FK_CUST_TRAN_TIME" FOREIGN KEY ("D_TIME_KEY")
REFERENCES "C360_DWH_VER"."D_TIME" ("D_TIME_KEY") ENABLE,
CONSTRAINT "FK_CUST_TRAN_CHANNEL" FOREIGN KEY ("D_TRANSACTION_CHANNEL_KEY")
REFERENCES "C360_DWH_VER"."D_TRANSACTION_CHANNEL" ("D_TRANSACTION_CHANNEL_KEY") ENABLE,
CONSTRAINT "FK_F_CUST_TRANSACTION_DATE" FOREIGN KEY ("DATE_KEY")
REFERENCES "C360_DWH_VER"."D_DATE" ("D_DATE_KEY") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 30 MAXTRANS 255
NOCOMPRESS LOGGING
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 "TBS_ODS_VER"
PARALLEL 32 ;
Is your table "ok"? Hmmm, let's see...
I'm not really a fan of quoted identifiers. And I wouldn't bother listing
all the storage parameters. Just the ones you want to use non-default values for. I like Egyptian brackets and lowercase too. So my script would look more like:
create table c360_dwh_ver.f_cust_transaction_line_items (
transaction_id varchar2(20 byte) not null enable,
transaction_timestamp varchar2(14 byte) not null enable,
...
);
It's good you've used varchar2(xx byte) though - no risk of char vs. byte semantics mishaps!
But seriously, we can't tell you if your table is "ok". To do that we need to understand your full business requirements. Something not possible on a site like this.
You need to do this by speaking with your users. Assuming it captures everything you need and is properly normalized,
test your application with the expected workload.
If this meets your performance goals (you've defined what they are, right?) you're done. Only start "tweaking" if it turns out performance is not good enough.