I am trying to fogure out what would be the underlying cause for not able to retrieve any records while doing a select. Just to give a little background .
We have a table called table_consumer in which the primary index defined for that table is a surrogate key(Sequencly generated). To be frank i am not sure why they define this to be the primary index. There is no other or any index defined on that table.
The table is having a record count of 90million records.
while i am doing this check to see if this table has any duplicates using count(*) group by1,2,3,4 having count(*)>1, it throws me with the spool space issue.
Also while i am firing the query below it again throws me with the spool space issue. Appreciate any help as to what could be done here or tweeked.
For the above query which you are facing spool space issue, i have few questions?
1. Any idea why there is no alias used in TIME_PERIOD- EXTRACT(DAY FROM TIME_PERIOD? Even for this also NUMBER_OF_MONTHS, BUSINESS_OWNER, START_DATA_DATE, DATA_REFRESH_TYPE. Try to use alias since you are joining two tables and the same column shouldnot exist in both tables.
SELECT B.SOURCE_ID, B.DATA_MONTH, (TIME_PERIOD- EXTRACT(DAY FROM TIME_PERIOD) + 1) TIME_PERIOD,
NUMBER_OF_MONTHS, BUSINESS_OWNER, START_DATA_DATE, DATA_REFRESH_TYPE
FROM dbv.view_CONSUMER SRC
2. You have selected A.SOURCE_ID in the select list but it's not used in group by. I am not sure without having source id if you are able to run successfully?
For the initial question regarding surrogate key, do you have any master table which holds consumer details and that has consumer id as PI?