RE: Air pocket char fields.
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <sfaroult@xxxxxxxxxxxx>
- Date: Thu, 8 Dec 2011 10:39:39 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
