Re: select distnct limitations
From: Ken Quirici <kquirici_at_yahoo.com>
Date: Sat, 14 Jun 2014 09:45:30 -0700 (PDT)
Message-ID: <4580a047-a71e-4728-be15-3f3a98b28e28_at_googlegroups.com>
On Thursday, June 12, 2014 3:56:42 PM UTC-4, Michel Cadot wrote:
> "Ken Quirici" <kquirici_at_yahoo.com> a �crit dans le message de news: 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?
>
>
>
> It is the size of the data in each row not the size of the text.
>
>
>
> Regards
>
> Michel
Date: Sat, 14 Jun 2014 09:45:30 -0700 (PDT)
Message-ID: <4580a047-a71e-4728-be15-3f3a98b28e28_at_googlegroups.com>
On Thursday, June 12, 2014 3:56:42 PM UTC-4, Michel Cadot wrote:
> "Ken Quirici" <kquirici_at_yahoo.com> a �crit dans le message de news: 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?
>
>
>
> It is the size of the data in each row not the size of the text.
>
>
>
> Regards
>
> Michel
OK, thanks Received on Sat Jun 14 2014 - 18:45:30 CEST