RE: Air pocket char fields.

From: <Joel.Patterson_at_crowley.com>
Date: Thu, 8 Dec 2011 10:39:39 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1B8BF18B4F_at_JAXMSG01.crowley.com>

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_at_roughsea.com] Sent: Thursday, December 08, 2011 9:17 AM To: Patterson, Joel
Cc: Thomas.Mercadante_at_labor.ny.gov; oracle-l_at_freelists.org Subject: Re: Air pocket char fields.

length(trim(stuff)) > 0

On 12/08/2011 02:57 PM, Joel.Patterson_at_crowley.com 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

Received on Thu Dec 08 2011 - 09:39:39 CST