Re: count(distinct) and SORT_AREA_SIZE

I think your hypothesis of pre-pending a column id and then doing a single sort is correct.

create table t1 as
select rownum id1, rownum id2, rownum id3 from all_objects;

select count(distinct id1) from t1;
select count(distinct id1), count(distinct id2) from t1;

Run with event 10032, the number of comparisons for the first query is one less than the number of rows in the table - which makes sense. The number of comparisons for the second query is MUCH larger, (in my case about 20 times the size). If this were running at two concurrent but separate sorts, then the number of comparisons would simply be twice the count of the first sort.

I think this behaviour may have changed since v7 - but I'm not sure of that. One thing to consider - the complexity of doing a single sort is probably a lot less than the potential error (and space wastage) in handling several concurrent sorts. If you have to handle several sorts, then you have to allocate each set of data to a separate chain of memory locations and make sure you don't lose any segments.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

R T wrote in message <94f01c2b.0109301145.1eda57b5_at_posting.google.com>...


>In the select statement

>select count(distinct col1), count(distinct col2), count(distinct

>col3),... count(distinct col25)

>from table;

>

>How many sorts are performed? If I set the event 10032, it shows only

>1 sort. It shows the total number of input records as sum of all the

>non-null column values. If so, does it do a single sort by prefixing

>with column id or column position and sort. Won't it increase the

>complexity of the sort? Or for a select with 25 columns, does it do 25

>sorts simultaneously? If that is the case SORT_AREA_SIZE being a fixed

>size how does it use it effectively? Idea is to come up with a correct

>value for SORT_AREA_SIZE parameter.

>