Home » SQL & PL/SQL » SQL & PL/SQL » Taking so much time when quering
Taking so much time when quering [message #209173] Wed, 13 December 2006 10:42 Go to next message
Rajapan
Messages: 3
Registered: December 2006
Junior Member
CREATE OR REPLACE VIEW VCUSTOMER_AGL3
(AFFILIATE_ID, AFFILIATE_NAME, CUSTOMER_ID, MODULE_ID, ACCOUNT_LEVEL_THREE_VALUE,
STANDARD_NM, BILLING_NAME, LISTED_NAME, CREATE_DATE, UPDATE_DATE,
TIECODE_ACTVY_IND, FRANCHISE_TYPE, MODIFIED_BY, NBR_ACTIVE_EARNING, NBR_INACTIVE_EARNING,
NBR_ACTIVE_CLACS, NBR_INACTIVE_CLACS)
AS
SELECT
T1.AFFILIATE_ID,
A.NAME,
T1.CUSTOMER_ID,
M.MODULE_ID,
T1.ACCOUNT_LEVEL_THREE_VALUE,
C.STANDARD_NM,
T1.BILLING_NAME,
T1.LISTED_NAME,
T1.CREATE_DATE,
T1.UPDATE_DATE,
T1.TIECODE_ACTVY_IND,
T1.FRANCHISE_TYPE,
T1.MODIFIED_BY,
T2.NBR_ACTIVE_EARNING,
T2.NBR_INACTIVE_EARNING,
T2.NBR_ACTIVE_CLACS,
T2.NBR_INACTIVE_CLACS
FROM ODS_DBA.CUSTOMER C,
ODS_DBA.AFFILIATE_TYPE A,
ODS_DBA.MODULE_ASSIGNMENTS M,
(SELECT
AFFILIATE_ID,
CUSTOMER_ID,
ACCOUNT_LEVEL_THREE_VALUE,
MAX(BILLING_NAME) BILLING_NAME,
MAX(LISTED_NAME) LISTED_NAME,
MAX(CREATE_DATE) CREATE_DATE,
MAX(UPDATE_DATE) UPDATE_DATE,
MAX(MODIFIED_BY) MODIFIED_BY,
MAX(TIECODE_ACTVY_IND) TIECODE_ACTVY_IND,
MAX(FRANCHISE_TYPE) FRANCHISE_TYPE
FROM CUSTOMER_ACCT_HIERARCHY H1
WHERE UPDATE_DATE =
(SELECT MAX (UPDATE_DATE)
FROM CUSTOMER_ACCT_HIERARCHY H2
WHERE H1.AFFILIATE_ID = H2.AFFILIATE_ID
AND H1.ACCOUNT_LEVEL_THREE_VALUE = H2.ACCOUNT_LEVEL_THREE_VALUE
AND H1.CUSTOMER_ID = H2.CUSTOMER_ID)
GROUP BY AFFILIATE_ID,
CUSTOMER_ID,
ACCOUNT_LEVEL_THREE_VALUE) T1,
(SELECT AFFILIATE_ID,
CUSTOMER_ID,
ACCOUNT_LEVEL_THREE_VALUE,
COUNT ( DISTINCT (CASE WHEN EARN_ACTVY_IND = 'A' THEN H3.ACCOUNT_LEVEL_ONE_VALUE
ELSE NULL END ) ) NBR_ACTIVE_EARNING,
COUNT ( DISTINCT (CASE WHEN EARN_ACTVY_IND <> 'A' THEN H3.ACCOUNT_LEVEL_ONE_VALUE
ELSE NULL END ) ) NBR_INACTIVE_EARNING,
COUNT ( DISTINCT (CASE WHEN CLAC_ACTVY_IND = 'A' THEN H3.ACCOUNT_LEVEL_TWO_VALUE
ELSE NULL END ) ) NBR_ACTIVE_CLACS,
COUNT ( DISTINCT (CASE WHEN CLAC_ACTVY_IND <> 'A' THEN H3.ACCOUNT_LEVEL_TWO_VALUE
ELSE NULL END ) ) NBR_INACTIVE_CLACS
FROM CUSTOMER_ACCT_HIERARCHY H3
GROUP BY
AFFILIATE_ID,
CUSTOMER_ID,
ACCOUNT_LEVEL_THREE_VALUE ) T2
WHERE T1.AFFILIATE_ID = T2.AFFILIATE_ID
AND T1.AFFILIATE_ID = A.AFFILIATE_KEY
AND T1.ACCOUNT_LEVEL_THREE_VALUE = T2.ACCOUNT_LEVEL_THREE_VALUE
AND T1.CUSTOMER_ID = T2.CUSTOMER_ID
AND T1.CUSTOMER_ID = C.CUSTOMER_ID
AND M.CUSTOMER_ID = C.CUSTOMER_ID WITH READ ONLY
/
================================================
This is customer Hierachy table InoCUSTOMER_ACCT_HIERARCHY
CREATE TABLE CUSTOMER_ACCT_HIERARCHY
(
ACCOUNT_GRP_LVL_ONE_ID NUMBER NOT NULL,
AFFILIATE_ID NUMBER,
ACCOUNT_LEVEL_ONE_VALUE VARCHAR2(64 BYTE),
ACCOUNT_LEVEL_TWO_VALUE VARCHAR2(64 BYTE),
ACCOUNT_LEVEL_THREE_VALUE VARCHAR2(64 BYTE),
ACCOUNT_LEVEL_THREE_CRT_DT DATE,
BILLING_NAME VARCHAR2(64 BYTE),
LISTED_NAME VARCHAR2(64 BYTE),
CUSTOMER_ID NUMBER NOT NULL,
CREATE_DATE DATE,
UPDATE_DATE DATE,
EARN_ACTVY_IND CHAR(1 BYTE),
CLAC_ACTVY_IND CHAR(1 BYTE),
TIECODE_ACTVY_IND CHAR(1 BYTE),
FRANCHISE_TYPE VARCHAR2(64 BYTE),
MODIFIED_BY VARCHAR2(40 BYTE),
LCID VARCHAR2(22 BYTE),
LINKING_REV_SOURCE VARCHAR2(8 BYTE),
MANUAL_LCID VARCHAR2(22 BYTE),
MANUAL_LCID_DATE DATE,
LCID_DATE DATE
)
TABLESPACE ODS_FINAL_DATA_V4
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 200M
NEXT 20M
MINEXTENTS 1
MAXEXTENTS 100
)
NOLOGGING
PARTITION BY HASH (ACCOUNT_GRP_LVL_ONE_ID)
(
PARTITION CUSTOMER_ACCT_HIERARCHY_01
TABLESPACE CUSTOMER_ACCT_HIERARCHY_DAT01,
PARTITION CUSTOMER_ACCT_HIERARCHY_02
TABLESPACE CUSTOMER_ACCT_HIERARCHY_DAT02,
PARTITION CUSTOMER_ACCT_HIERARCHY_03
TABLESPACE CUSTOMER_ACCT_HIERARCHY_DAT03,
PARTITION CUSTOMER_ACCT_HIERARCHY_04
TABLESPACE CUSTOMER_ACCT_HIERARCHY_DAT04
)
NOCOMPRESS
NOCACHE
PARALLEL ( DEGREE 4 INSTANCES 1 )
NOMONITORING;


CREATE UNIQUE INDEX IDX_ACCT_HIER_LVL1 ON CUSTOMER_ACCT_HIERARCHY
(AFFILIATE_ID, ACCOUNT_LEVEL_ONE_VALUE)
NOLOGGING
TABLESPACE ODS_FINAL_IDX_V4
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 20M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
PARALLEL ( DEGREE 4 INSTANCES 1 );


CREATE INDEX FK_ACCT_HIER_CUST ON CUSTOMER_ACCT_HIERARCHY
(CUSTOMER_ID)
NOLOGGING
TABLESPACE ODS_FINAL_IDX_V4
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 15M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
PARALLEL ( DEGREE 4 INSTANCES 1 );


CREATE INDEX IDX_ACCT_HIER_AFFILIATE ON CUSTOMER_ACCT_HIERARCHY
(AFFILIATE_ID)
NOLOGGING
TABLESPACE ODS_FINAL_IDX_V4
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
PARALLEL ( DEGREE 4 INSTANCES 1 );


CREATE INDEX IDX_ACCT_HIER_LVL2 ON CUSTOMER_ACCT_HIERARCHY
(ACCOUNT_LEVEL_TWO_VALUE)
NOLOGGING
TABLESPACE ODS_FINAL_IDX_V4
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 20M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
PARALLEL ( DEGREE 4 INSTANCES 1 );


CREATE INDEX IDX_ACCT_HIER_LVL3 ON CUSTOMER_ACCT_HIERARCHY
(ACCOUNT_LEVEL_THREE_VALUE)
NOLOGGING
TABLESPACE ODS_FINAL_IDX_V4
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 20M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
PARALLEL ( DEGREE 4 INSTANCES 1 );


CREATE UNIQUE INDEX PK_CUSTOMER_ACCT_HIERARCHY ON CUSTOMER_ACCT_HIERARCHY
(ACCOUNT_GRP_LVL_ONE_ID)
NOLOGGING
TABLESPACE ODS_FINAL_IDX_V4
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 20M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE BITMAP INDEX CUST_ACCT_HIER_IDX_EARN ON CUSTOMER_ACCT_HIERARCHY
(EARN_ACTVY_IND)
TABLESPACE ODS_FINAL_IDX_V4
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 4M
NEXT 4M
MINEXTENTS 1
MAXEXTENTS 1024
PCTINCREASE 0
)
LOGGING
LOCAL (
PARTITION CUST_ACCT_HIER_IDX_EARN_01
TABLESPACE CUSTOMER_ACCT_HIERARCHY_IDX_01,
PARTITION CUST_ACCT_HIER_IDX_EARN_02
TABLESPACE CUSTOMER_ACCT_HIERARCHY_IDX_02,
PARTITION CUST_ACCT_HIER_IDX_EARN_03
TABLESPACE CUSTOMER_ACCT_HIERARCHY_IDX_03,
PARTITION CUST_ACCT_HIER_IDX_EARN_04
TABLESPACE CUSTOMER_ACCT_HIERARCHY_IDX_04
)
NOPARALLEL;


CREATE BITMAP INDEX CUST_ACCT_HIER_IDX_TC ON CUSTOMER_ACCT_HIERARCHY
(TIECODE_ACTVY_IND)
TABLESPACE ODS_FINAL_IDX_V4
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 4M
NEXT 4M
MINEXTENTS 1
MAXEXTENTS 1024
PCTINCREASE 0
)
LOGGING
LOCAL (
PARTITION CUST_ACCT_HIER_IDX_TC_01
TABLESPACE CUSTOMER_ACCT_HIERARCHY_IDX_01,
PARTITION CUST_ACCT_HIER_IDX_TC_02
TABLESPACE CUSTOMER_ACCT_HIERARCHY_IDX_02,
PARTITION CUST_ACCT_HIER_IDX_TC_03
TABLESPACE CUSTOMER_ACCT_HIERARCHY_IDX_03,
PARTITION CUST_ACCT_HIER_IDX_TC_04
TABLESPACE CUSTOMER_ACCT_HIERARCHY_IDX_04
)
NOPARALLEL;


CREATE BITMAP INDEX CUST_ACCT_HIER_IDX_CLAC ON CUSTOMER_ACCT_HIERARCHY
(CLAC_ACTVY_IND)
TABLESPACE ODS_FINAL_IDX_V4
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 4M
NEXT 4M
MINEXTENTS 1
MAXEXTENTS 1024
PCTINCREASE 0
)
LOGGING
LOCAL (
PARTITION CUST_ACCT_HIER_IDX_CLAC_01
TABLESPACE CUSTOMER_ACCT_HIERARCHY_IDX_01,
PARTITION CUST_ACCT_HIER_IDX_CLAC_02
TABLESPACE CUSTOMER_ACCT_HIERARCHY_IDX_02,
PARTITION CUST_ACCT_HIER_IDX_CLAC_03
TABLESPACE CUSTOMER_ACCT_HIERARCHY_IDX_03,
PARTITION CUST_ACCT_HIER_IDX_CLAC_04
TABLESPACE CUSTOMER_ACCT_HIERARCHY_IDX_04
)
NOPARALLEL;


CREATE OR REPLACE TRIGGER TRG_DT_CUSTOMER_ACCT_HIERARCHY
BEFORE INSERT OR UPDATE
ON ODS_DBA.CUSTOMER_ACCT_HIERARCHY
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF INSERTING THEN
:NEW.MODIFIED_BY := USER; -- For MMDW V8.0
ELSE
:NEW.UPDATE_DATE := SYSDATE;
:NEW.MODIFIED_BY := USER; -- For MMDW V8.0
END IF;
END;
/
SHOW ERRORS;



CREATE PUBLIC SYNONYM CUSTOMER_ACCT_HIERARCHY FOR CUSTOMER_ACCT_HIERARCHY;


ALTER TABLE CUSTOMER_ACCT_HIERARCHY ADD (
CONSTRAINT PK_CUSTOMER_ACCT_HIERARCHY
PRIMARY KEY
(ACCOUNT_GRP_LVL_ONE_ID)
USING INDEX
TABLESPACE ODS_FINAL_IDX_V4
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 20M
NEXT 8M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
));


ALTER TABLE CUSTOMER_ACCT_HIERARCHY ADD (
CONSTRAINT FK_ACCT_HIER_CUST
FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMER (CUSTOMER_ID));
Re: Taking so much time when quering [message #209178 is a reply to message #209173] Wed, 13 December 2006 10:57 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
What sort of response time were you expecting ? You are joining the result of two group by operations together. Every time you select from the view, Oracle will have to do both "group bys", put the results into temporary tables and then merge join/hash join them together. This is a complex and costly operation. Were you really expecting a subsecond response time ?
Re: Taking so much time when quering [message #209206 is a reply to message #209178] Wed, 13 December 2006 15:30 Go to previous messageGo to next message
Rajapan
Messages: 3
Registered: December 2006
Junior Member
Now it's taking 5 mnts. Is there any way to improve performance to execute with in one mnt. Or what could be the other way to re-write this query which meets same requirement.
Re: Taking so much time when quering [message #209269 is a reply to message #209173] Thu, 14 December 2006 00:37 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post your query and EXPLAIN.
icon2.gif  Re: Taking so much time when quering [message #209626 is a reply to message #209206] Sat, 16 December 2006 00:14 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
there are a lot..which one is taking the most time?
Re: Taking so much time when quering [message #209998 is a reply to message #209626] Mon, 18 December 2006 20:30 Go to previous message
Rajapan
Messages: 3
Registered: December 2006
Junior Member
This is query taking much time
SELECT
T1.AFFILIATE_ID,
A.NAME,
T1.CUSTOMER_ID,
M.MODULE_ID,
T1.ACCOUNT_LEVEL_THREE_VALUE,
C.STANDARD_NM,
T1.BILLING_NAME,
T1.LISTED_NAME,
T1.CREATE_DATE,
T1.UPDATE_DATE,
T1.TIECODE_ACTVY_IND,
T1.FRANCHISE_TYPE,
T1.MODIFIED_BY,
T2.NBR_ACTIVE_EARNING,
T2.NBR_INACTIVE_EARNING,
T2.NBR_ACTIVE_CLACS,
T2.NBR_INACTIVE_CLACS
FROM ODS_DBA.CUSTOMER C,
ODS_DBA.AFFILIATE_TYPE A,
ODS_DBA.MODULE_ASSIGNMENTS M,
(SELECT
AFFILIATE_ID,
CUSTOMER_ID,
ACCOUNT_LEVEL_THREE_VALUE,
MAX(BILLING_NAME) BILLING_NAME,
MAX(LISTED_NAME) LISTED_NAME,
MAX(CREATE_DATE) CREATE_DATE,
MAX(UPDATE_DATE) UPDATE_DATE,
MAX(MODIFIED_BY) MODIFIED_BY,
MAX(TIECODE_ACTVY_IND) TIECODE_ACTVY_IND,
MAX(FRANCHISE_TYPE) FRANCHISE_TYPE
FROM CUSTOMER_ACCT_HIERARCHY H1
WHERE UPDATE_DATE =
(SELECT MAX (UPDATE_DATE)
FROM CUSTOMER_ACCT_HIERARCHY H2
WHERE H1.AFFILIATE_ID = H2.AFFILIATE_ID
AND H1.ACCOUNT_LEVEL_THREE_VALUE = H2.ACCOUNT_LEVEL_THREE_VALUE
AND H1.CUSTOMER_ID = H2.CUSTOMER_ID)
GROUP BY AFFILIATE_ID,
CUSTOMER_ID,
ACCOUNT_LEVEL_THREE_VALUE) T1,
(SELECT AFFILIATE_ID,
CUSTOMER_ID,
ACCOUNT_LEVEL_THREE_VALUE,
COUNT ( DISTINCT (CASE WHEN EARN_ACTVY_IND = 'A' THEN H3.ACCOUNT_LEVEL_ONE_VALUE
ELSE NULL END ) ) NBR_ACTIVE_EARNING,
COUNT ( DISTINCT (CASE WHEN EARN_ACTVY_IND <> 'A' THEN H3.ACCOUNT_LEVEL_ONE_VALUE
ELSE NULL END ) ) NBR_INACTIVE_EARNING,
COUNT ( DISTINCT (CASE WHEN CLAC_ACTVY_IND = 'A' THEN H3.ACCOUNT_LEVEL_TWO_VALUE
ELSE NULL END ) ) NBR_ACTIVE_CLACS,
COUNT ( DISTINCT (CASE WHEN CLAC_ACTVY_IND <> 'A' THEN H3.ACCOUNT_LEVEL_TWO_VALUE
ELSE NULL END ) ) NBR_INACTIVE_CLACS
FROM CUSTOMER_ACCT_HIERARCHY H3
GROUP BY
AFFILIATE_ID,
CUSTOMER_ID,
ACCOUNT_LEVEL_THREE_VALUE ) T2
WHERE T1.AFFILIATE_ID = T2.AFFILIATE_ID
AND T1.AFFILIATE_ID = A.AFFILIATE_KEY
AND T1.ACCOUNT_LEVEL_THREE_VALUE = T2.ACCOUNT_LEVEL_THREE_VALUE
AND T1.CUSTOMER_ID = T2.CUSTOMER_ID
AND T1.CUSTOMER_ID = C.CUSTOMER_ID
AND M.CUSTOMER_ID = C.CUSTOMER_ID WITH READ ONLY
Previous Topic: collection pool and global temporary table problem
Next Topic: CHECK data type in SQL
Goto Forum:
  


Current Time: Sat Dec 10 10:30:36 CST 2016

Total time taken to generate the page: 0.13686 seconds