Re: restristions for DISTINCT usage

Matthisa, in Oracle the size of the sort keys is limited to approximately 3/4 the Oracle block size. In the case of a DISTINCT operator the entire select list makes up the sort keys. So if you have a 2k Oracle block size the total length of the select list cannot exceed around 1500 bytes while with a 4k block the limitation moves to around 3000 and so on.

In the past Oracle listed the restriction as about 1/2 the Oracle block size but looking the in both the 9.2 and 10g Reference manual chapter on limitations it would appear the keys in an index can be about 75% of the Oracle block size. The limiting factor for this length has been the sort size limit so this limitation should apply to distinct, group by, and order by as all three statements require sorting.

HTH -- Mark D Powell -- Received on Wed Jul 06 2005 - 09:59:54 CDT