It helps to format a query properly to see what's going on. I studied your query and found suspicious SQL:
CREATE VIEW "Swiss_client_wise_minutes_and_profit" AS
SELECT ROW_NUMBER() OVER (ORDER BY rp.country, rp.destination) AS id
, (SELECT company_id
FROM "Corporate_companyalias" AS cc
WHERE cc."Alias_name" = client_name_id) AS client_name
, (SELECT company_id
FROM "Corporate_companyalias" AS cc
WHERE cc."Alias_name" = vendor_name_id) AS vendor_name
, cs.c_prefix_id AS c_prefix
, cs.v_prefix_id AS v_prefix
, rp.country
, rp.destination
, cs.c_total_calls
, cs.v_total_calls
, cs.successful_calls
, cs.billed_duration
, cs.v_billed_amount AS cost
, cs.c_billed_amount AS revenue
, cs.c_pdd AS pdd
, cs.profit
, cs.start_time
, cs.end_time
, cs.switch_name
FROM "Swiss_buz_scdr" AS cs
LEFT JOIN "Corporate_companyalias" AS cc ON cs.client_name_id = cc."Alias_name"
LEFT JOIN "RateManagement_prefix_and_client_wise_destinationgroup" AS rp
ON rp.client_name = cc.company_id AND rp.prefix = cs.c_prefix_id
WHERE cs.customer_name = 'SSP Root';
Don't use the same table alias cc in the outer and inner SELECT. While that's not illegal, it helps to confuse you.
Without table qualification for the reference to the outer query I am not sure, where the columns client_name_id and vendor_name_id bind. Would need the table definitions to know, but I suspect it results in CROSS JOINs - which is probably not what you intended and the root of the problem.
I suspect the correlated subqueries can be rewritten as plain expressions. Maybe it needs another JOIN. Here is my ...
Educated guess what you actually want:
CREATE VIEW "Swiss_client_wise_minutes_and_profit" AS
SELECT ROW_NUMBER() OVER (ORDER BY r.country, r.destination) AS id
, c.company_id AS client_name
, v.company_id AS vendor_name
, s.c_prefix_id AS c_prefix
, s.v_prefix_id AS v_prefix
, r.country
, r.destination
, s.c_total_calls
, s.v_total_calls
, s.successful_calls
, s.billed_duration
, s.v_billed_amount AS cost
, s.c_billed_amount AS revenue
, s.c_pdd AS pdd
, s.profit
, s.start_time
, s.end_time
, s.switch_name
FROM "Swiss_buz_scdr" s
LEFT JOIN "Corporate_companyalias" c ON c."Alias_name" = s.client_name_id
LEFT JOIN "RateManagement_prefix_and_client_wise_destinationgroup" r
ON r.client_name = c.company_id AND r.prefix = s.c_prefix_id
LEFT JOIN "Corporate_companyalias" v ON v."Alias_name" = s.vendor_name_id
WHERE s.customer_name = 'SSP Root';
Aside: I would aim for shorter names than "RateManagement_prefix_and_client_wise_destinationgroup". And preferably legal, lower-case names that don't need double-quotes.
Primary and unique keys in all(?) RDBMSes use indexes in order to quickly be able to determine whether a newly inserted value is indeed unique.
The side effect of this is that queries via primary and unique keys are usually "fast".
Now if you haven't defined primary or unique keys on your tables,
- You don't have a relational table but you have junk (OK, this is a contentious opinion, but a relational model needs keys on all tables).
- queries on this table (in the absence of any other indexes) will become slower as more data is inserted into the table.
So yes, the absence of primary keys will cause this!
Best Answer
Create a table with a select * from a large table and where 1=0. Gather stats, save them using dbms_stats and then apply those stats to the large table. You will essentially tell Oracle that the large table has no data, which would favor full table scans. You can also try switching the optimizer mode to first_rows_1 or invalidating some indexes.