RE: Air pocket char fields.

From: <Joel.Patterson_at_crowley.com>
Date: Thu, 8 Dec 2011 08:57:20 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1B8BF18968_at_JAXMSG01.crowley.com>



I suspect oracle is padding the literal automatically because it knows it is a char().

So it returns 3million when = '<any#ofSpaces>' and 700k when <> '<any#ofspaces>'.

So if somebody only wants a result set that has data in that field, (no blanks), and the field is a NOT NULL column, then the best way to eliminate those records is?

Joel Patterson
Database Administrator
904 727-2546

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joel.Patterson_at_crowley.com Sent: Thursday, December 08, 2011 8:42 AM To: Thomas.Mercadante_at_labor.ny.gov; oracle-l_at_freelists.org Subject: RE: Air pocket char fields.

I am not counting the spaces. I am working on a query--which is not part of the question.

This is just a question on behavior. Why is oracle returning what it is returning when considering the below query examples, and only the below query examples. There is no higher purpose.

Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----

From: Mercadante, Thomas F (LABOR) [mailto:Thomas.Mercadante_at_labor.ny.gov] Sent: Thursday, December 08, 2011 7:36 AM To: Patterson, Joel; oracle-l_at_freelists.org Subject: RE: Air pocket char fields.

Joel,

I hate to ask the obvious question.

Why are you counting the number of blank spaces? Seems like a questionable design to me.

If you want a count where the column contains all spaces, why not just:

Select count(*) from table where rtrim(column,' ') is null

Just asking.

Tom

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joel.Patterson_at_crowley.com Sent: Wednesday, December 07, 2011 3:51 PM To: oracle-l_at_freelists.org
Subject: FW: Air pocket char fields.

I sent this to a couple DBAs, which warranted it to be posted for comment.

Do you know why this is? Shouldn't a CHAR field size 15 return all the records where the field is 15 spaces when requested? Should it not return all rows if <> anything but 15 spaces?

Oracle 11.2.0.1
Field is CHAR(15) NOT NULL
Min(length(activity)), max(length(activity)) = 15

714196 + 3744244 = total rows in table.

select count(*) SIXTEEN from lawcrp.mmdist where activity = ' ';

   SIXTEEN


   3744244

select count(*) FIFTEEN from lawcrp.mmdist where activity = ' ';

   FIFTEEN


   3744244

select count(*) FOURTEEN from lawcrp.mmdist where activity = ' ';

   FOURTEEN
---------- THIRTEEN, TWELVE, ELEVEN, ... THREE, TWO,
   3744244

select count(*) ONE from lawcrp.mmdist where activity = ' ';

       ONE


   3744244

select count(*) ZERO from lawcrp.mmdist where activity = '';

      ZERO


         0

select count(*) "<> ZERO" from lawcrp.mmdist where activity <> '';

   <> ZERO


         0

select count(*) "<> ONE" from lawcrp.mmdist where activity <> ' ';

    <> ONE


    714196

select count(*) "<> TWO" from lawcrp.mmdist where activity <> ' ';

    <> TWO
---------- THREE, FOUR, FIVE, SIX, ... THIRTEEN, FOURTEEN,
    714196

select count(*) "<> FIFTEEN" from lawcrp.mmdist where activity <> ' ';

<> FIFTEEN


    714196

select count(*) "<> SIXTEEN" from lawcrp.mmdist where activity <> ' ';

<> SIXTEEN


    714196

Joel Patterson
Database Administrator
904 727-2546

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Dec 08 2011 - 07:57:20 CST

Original text of this message