CODEWITHSUNDEEP
oracle11gcountgroup-byfull-table-scan
Minnu perinchery
Minnu perinchery

Reputation: 11

Ways to optimize memory usage for query with aggregate functions on very large tables in Oracle

I have a table with large amount of data(Say 1,000,000,000 rows).

Table Structure:

Id(Primary Key)
Name
...

I have omitted other fields from the table as I cannot use any of those to limit the data I fetch.

Here primary key is Id. I do not have any index on, only the name column.

I need to find a list of Names which occur more than n number of times(Say n=10).

I have tried the below options:

SELECT /+full(T)/ Name,COUNT(Id) AS CNT FROM T GROUP BY Name HAVING COUNT(ID) >10;

select distinct Name, COUNT(ID) OVER (PARTITION BY Name) AS CNT FROM T where CNT>10;

Both of them are taking large temp space.

Any other suggestions to optimize the query to use less temp space. Speed is not the primary concern here for me. It is fine even if the query takes around 2 hours.

Upvotes: 0

Views: 200

Answers (1)

ArtBajji
ArtBajji

Reputation: 960

WITH names AS ( select Name, COUNT(ID) OVER (PARTITION BY Name) AS CNT FROM T) select DISTINCT name from names where names.CNT > 10

Upvotes: 0

Related Questions