RE: Air pocket char fields.

Actually, that is almost twice as long, yet the #rows column dropped from 700K 
to 200K, and the bytes skyrocketed.   They both say return everything except 
spaces.


Maybe there is no good answer here.   But if you are stuck with CHAR fields, 
then one has to deal with them.

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

    <> ONE
----------
    702804

Elapsed: 00:00:26.00

Execution Plan
----------------------------------------------------------

------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    16 | 38039   (2)|
|   1 |  SORT AGGREGATE    |        |     1 |    16 |            |
|   2 |   TABLE ACCESS FULL| MMDIST |   717K|    10M| 38039   (2)|
------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     137087  consistent gets
     137043  physical reads
          0  redo size
        206  bytes sent via SQL*Net to client
        233  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

select count(*) "TRIM" from lawcrp.mmdist where length(trim(activity)) > 0;

      TRIM
----------
    702804

Elapsed: 00:00:47.40

Execution Plan
----------------------------------------------------------

------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    16 | 38087   (2)|
|   1 |  SORT AGGREGATE    |        |     1 |    16 |            |
|   2 |   TABLE ACCESS FULL| MMDIST |   217K|  3391K| 38087   (2)|
------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     137087  consistent gets
     137043  physical reads
          0  redo size
        217  bytes sent via SQL*Net to client
        233  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


Joel Patterson
Database Administrator
904 727-2546

-----Original Message-----
From: Stephane Faroult [mailto:sfaroult@xxxxxxxxxxxx] 
Sent: Thursday, December 08, 2011 9:17 AM
To: Patterson, Joel
Cc: Thomas.Mercadante@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Air pocket char fields.

length(trim(stuff)) > 0

On 12/08/2011 02:57 PM, Joel.Patterson@xxxxxxxxxxx wrote:
> 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
--
http://www.freelists.org/webpage/oracle-l


Other related posts: