| cartesian product ? [message #40847] |
Thu, 07 November 2002 10:49  |
Anthony
Messages: 48
Registered: September 2000
|
Member |
|
|
Hi everyone,
Iīve got real problems with this one, is the following returning a cartesian product?
SELECT count(*)
FROM TBL_CR, tmp_ta_tci, TBL_CM, TBL_MDI
WHERE TBL_CM.CM_ID = tmp_ta_tci.MR_MODEL_ID
AND EXISTS (SELECT 'X' FROM TBL_ALG
WHERE TBL_ALG.ALG_ID = TBL_CM.CM_ALGORITHM_ID
AND TBL_ALG.ALG_FUNCTION_NAME = 'ALGO_1')
AND TBL_CM.CM_ID = TBL_MDI.MDI_MODEL_ID
AND TBL_MDI.MDI_START_DATE < tmp_ta_tci.MR_PERIOD_END_DATE
AND (TBL_MDI.MDI_END_DATE >= tmp_ta_tci.MR_PERIOD_END_DATE) OR (TBL_MDI.MDI_END_DATE IS NULL)
AND TBL_CR.CR_IR_UNIQUE_NUMBER = tmp_ta_tci.IR_IR_UNIQUE_NUMBER
AND TBL_CR.CR_TCI_UNIQUE_NUMBER = tmp_ta_tci.TCI_UNIQUE_NUMBER
AND TBL_CM.CM_START_DATE < tmp_ta_tci.MR_PERIOD_END_DATE
AND (TBL_CM.CM_END_DATE >= tmp_ta_tci.MR_PERIOD_END_DATE) OR (TBL_CM.CM_END_DATE IS NULL)
and tmp_ta_tci.MR_MODEL_ID = 'Permanpre1000 '
and tmp_ta_tci.TCI_UNIQUE_NUMBER = '17771042'
and tmp_ta_tci.MR_SALES_FORCE_ID = '29048005'
and tmp_ta_tci.MR_VO_ID = 'tmp_ta_tci'
and tmp_ta_tci.IR_IR_UNIQUE_NUMBER = '198167710051'
and tmp_ta_tci.MR_PERIOD_END_DATE = to_date('30/09/2001','DD/MM/YYYY')
Canīt even return the quantity as it comes up with the "ORA-01652: unable to extend temp segment by 256 in tablespace TEMP" error (thereīs about a million records in tmp_ta_tci)
TMP_TA_TCI is temp table populated before the select (really an insert into) is executed to hold the values which will be used get a monetary value from TBL_CR (a big fat table) and descriptions from TBL_MDI and TBL_DIM small reference tables.
When I do:
select *
from TBL_CR
where TBL_CR.CR_TCI_UNIQUE_NUMBER = '17771042' --Values held in tmp_ta_tci
and TBL_CR.CR_IR_UNIQUE_NUMBER = '198167710051'
It returns zero, (CR_TCI_UNIQUE_NUMBER & CR_IR_UNIQUE_NUMBER however donīt uniquely identify a row in TBL_CR).
Why isnīt the first select count(*) returning zero? the values being used in its select donīt exist in TBL_CR. What am I not seeing here?
Any help would be very much appreciated.
Thanks
|
|
|
|
| Re: cartesian product ? [message #40848 is a reply to message #40847] |
Thu, 07 November 2002 12:07  |
Rick Cale
Messages: 111
Registered: February 2002
|
Senior Member |
|
|
I cannot see anyway the result set is a cartesian product. Have you tried increasing temp space?
Perhaps others with good knowledge of set theory can
better answer.
|
|
|
|
| Re: cartesian product ? [message #40854 is a reply to message #40847] |
Thu, 07 November 2002 23:34 |
F. Tollenaar
Messages: 64
Registered: November 2002
|
Member |
|
|
Looks like you got your parentheses wrong:
I believe you mean :
(AND TBL_CM.CM_END_DATE >= tmp_ta_tci.MR_PERIOD_END_DATE OR TBL_CM.CM_END_DATE IS NULL)
because of the way you placed your parentheses you created a carthesian product if there is at least 1 row in TBL_MDI or TBL_CM with END_DATE is null
If you want to ask whether or not you created a carthesian product next time, please give us the columns in your primary and foreign keys.
HTH
Frank
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
