Hi
While executing the below query we got no rows:-
select * from
MTH_EQUIP_PROD_SCHEDULE_F EQUIP_PROD,
MTH_SHIFT_GREGORIAN_DENORM_MV TIME_D
WHERE EQUIP_PROD.SHIFT_WORKDAY_FK_KEY = TIME_D.SHIFT_WORKDAY_PK_KEY
But while executing the below query it consume 10GB of Temp space and give error
select * from
MTH_EQUIP_PROD_SCHEDULE_F EQUIP_PROD,
MTH_SHIFT_GREGORIAN_DENORM_MV TIME_D,
MTH_ITEM_DENORM_D IT_DENORM,
MTH_EQUIPMENT_DENORM_D DEPT_HIER,
MTH_EQUIPMENT_DENORM_D CUST_HIER
WHERE EQUIP_PROD.SHIFT_WORKDAY_FK_KEY = TIME_D.SHIFT_WORKDAY_PK_KEY AND
IT_DENORM.HIERARCHY_ID = -5 AND
IT_DENORM.ITEM_FK_KEY = EQUIP_PROD.ITEM_FK_KEY AND
DEPT_HIER.EQUIPMENT_HIERARCHY_KEY = -2 AND
DEPT_HIER.EQUIPMENT_FK_KEY = EQUIP_PROD.EQUIPMENT_FK_KEY AND
TIME_D.FROM_DATE BETWEEN DEPT_HIER.EQUIPMENT_EFFECTIVE_DATE AND NVL(DEPT_HIER.EQUIPMENT_EXPIRATION_DATE,TO_DATE('01/01/2100','MM/DD/YYYY')) AND
CUST_HIER.EQUIPMENT_HIERARCHY_KEY = -3 AND
CUST_HIER.EQUIPMENT_FK_KEY = EQUIP_PROD.EQUIPMENT_FK_KEY AND
TIME_D.FROM_DATE BETWEEN CUST_HIER.EQUIPMENT_EFFECTIVE_DATE AND NVL(CUST_HIER.EQUIPMENT_EXPIRATION_DATE,TO_DATE('01/01/2100','MM/DD/YYYY'))
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP1
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2720
ORA-06512: at "APPS.MTH_UTIL_PKG", line 1215
ORA-06512: at line 1