Thanks a lot! You've been very helpful!. I've tried that and it works.

One thing though, do you know of Oracle stores a subset of its tables ANYWHERE? ie. for a specific table, sample maybe 5% of the rows and store those rows somewhere?

I'm asking because although the statistical information gathered by calling gather_table_stats are only stuff like Density, number of Nulls, etctec, but what I need is a subset of the data.

I'll give an example of what I need:

for this table called Patients, I'll have 1000000 records, I want to store for example the top 10 patients with the most entries. ie so let Bob be that patient who is responsible for 25000 of those records. and the 2nd highest might be Kelly who has 20000 records.

I could do this by going

select patient_name, count(*) group by count(*) or something to that effect, and pick out the top 10 entries.

BUT i am hoping that Oracle store this data (or a subset of sample representing the data) somewhere already so I don't have to do a query on 1000000 records.

I am trying to be clear but if I am not, please ask

thanks for your help!