| Large Record Set [message #54657] |
Tue, 26 November 2002 04:11  |
man
Messages: 5
Registered: November 2002
|
Junior Member |
|
|
Hi All,
The following Query is returning more than 10000 ( may be more than that but when i interrupted the process the count was 1001)rows where as MEASURE_DATA is having only 86 records
What Could be the problem ?
SELECT
ME.PLAN_ID
FROM
ADW505.MEASURE_DATA MEA,
ADW505.SELLER_HRCY_LVL SEL,
ADW505.SELLER_NODE SLN,
SELLER_NODE SN,
SELLER_HRCY_LVL SL,
MEASURE_DATA ME
WHERE
( MEA.SEL_DIM_KEY_ID=SL.SEL_DIM_KEY_ID ) AND
( SEL.LVL_NUM=SLN.LVL_NUM ) AND
( SEL.SEL_NODE_ID=SLN.SEL_NODE_ID ) AND
( SLN.SEL_NODE_NAME=SN.SEL_NODE_NAME ) AND
( SN.SEL_NODE_ID=SL.SEL_NODE_ID ) AND
( SN.LVL_NUM=SL.LVL_NUM ) AND
( SL.SEL_DIM_KEY_ID=ME.SEL_DIM_KEY_ID );
Pls Help me out
TIA
Mangesh
|
|
|
|
| Re: Large Record Set [message #54665 is a reply to message #54657] |
Tue, 26 November 2002 12:15  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
When you encounter too many rows like you explain then I guess you have a cartesean join, so you need to make sure your joins are made across the whole PK of each table. Confirm what the PK/UK of each table is by querying (all_indexs where...) or (all_constraints where constraint_type in ('P', 'U') and status = 'ENABLED').
manual way of confirming that you don't have duplicates in column(s)e.g.
SELECT 'duplicates forund'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM adw505.measure_data mea
GROUP BY sel_dim_key_id
HAVING COUNT (*) > 1);
SELECT 'duplicates found'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM adw505.measure_data mea
GROUP BY sel_dim_key_id
HAVING COUNT (*) > 1);
SELECT 'duplicates found'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM seller_node sn
GROUP BY sn.sel_node_id, sn.lvl_num
HAVING COUNT (*) > 1);
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
