select distnct limitations
Date: Thu, 12 Jun 2014 12:35:32 -0700 (PDT)
Message-ID: <3a5a1979-c420-4952-973f-d937cb7f7bc4_at_googlegroups.com>
I'm somewhat confused by the Oracle 12c documentation on SELECT DISTINCT.
Specifically this extract:
"When you specify DISTINCT or UNIQUE, the total number of bytes in all select list expressions is limited to the size of a data block minus some overhead. This size is specified by the initialization parameter DB_BLOCK_SIZE."
which is in the SQL User's Guide and Reference under SELECT.
I'm assuming it means you add up all the number of characters in the TEXT of the SQL statement in the DISTINCT list:
so "select distinct col1, col2, col3, col1+col3 ..."
has 21 bytes in its "select list expressions", which is less than most DB_BLOCK_SIZE values. This interpretation makes the restriction on DISTINCT like the restriction on the ORDER BY list to 255 expressions, only more obscure.
Is this what the quoted extract above means? Or does it mean the total #bytes in each row of data retrieved must be a bit less than DB_BLOCK_SIZE? Received on Thu Jun 12 2014 - 21:35:32 CEST