Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: A Question regarding null columns.

Re: A Question regarding null columns.

From: Telemachus <telemachus_at_ulysseswillreturn.net>
Date: Mon, 1 Jul 2002 18:44:52 +0100
Message-ID: <oy0U8.611$i5.5462@news.indigo.ie>


Thanks Sybrand but

SQL>
SQL> exec
DBMS_STATS.GATHER_TABLE_STATS('DATA_HOLDER','ERASEME',NULL,NULL,FALSE, 'FOR ALL COLUMNS SIZE 1',NULL,'DEFAULT',TRUE); PL/SQL procedure successfully completed.

SQL> explain plan for select count (*) from eraseme where object_id is null;

Explained.

SQL> set long 32000
SQL> set linesize 32000;
SQL> @ ?/rdbms/admin/utlxplp.sql

Plan Table





| Operation | Name | Rows | Bytes| Cost | TQ
|IN-OUT|

PQ Distrib | Pstart| Pstop |



| SELECT STATEMENT | | 1 | 4 | 1 | |
|
           |       |       |

| SORT AGGREGATE | | 1 | 4 | | |
|
| | |
| TABLE ACCESS FULL |ERASEME | 1 | 4 | 1 | |
|
| | | ----------------------------------------------------------------------------


6 rows selected.

SQL> > even tried cleaning out plan_table to see if xplp was looking at old rows (No change)

Anyone want to try it on their own database on a larger table and see what they get ?
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:nr21iuoa6tnfg7a37fpeii8me5v53u4sj5_at_4ax.com...

> On Mon, 1 Jul 2002 15:45:13 +0100, "Telemachus"
> <telemachus_at_ulysseswillreturn.net> wrote:
>
> >Consider :
> >
> >(Oracle 8173)
> >Create table eraseme as select object_id from all_objects;
> >
> >Connected to:
> >Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
> >JServer Release 8.1.7.3.0 - Production
> >
> >SQL> desc eraseme;
> > Name                                      Null?    Type
> > ----------------------------------------- -------- ----------
> >
> > OBJECT_ID                                 NOT NULL NUMBER
> >
> >
> >SQL> explain plan for select count (*) from eraseme where object_id is
null;
> >
> >Explained.
> >
> >SQL> @ ?/rdbms/admin/utlxplp.sql
> >
> >Plan Table
>
>---------------------------------------------------------------------------
-
> >---
> >----------------------------
> >| Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ
> >|IN-OUT|
> >PQ Distrib | Pstart| Pstop |
>
>---------------------------------------------------------------------------
-
> >---
> >----------------------------
> >| SELECT STATEMENT           |          |       |      |        |      |
> >|
> >           |       |       |
> >|  SORT AGGREGATE            |          |       |      |        |      |
> >|
> >           |       |       |
> >|   TABLE ACCESS FULL        |ERASEME   |       |      |        |      |
> >|
> >           |       |       |
>
>---------------------------------------------------------------------------
-
> >---
> >----------------------------
> >
> >6 rows selected.
> >
> >
> >Without wishing to cause major fuss my question would be -
> >
> >since object_id is known to be not null from the data dictionary why does
> >the optimizer ask for an FTS rather than immediate 0  ?
> >
> > or is it a 'fiddled' FTS ? Checking the waits (there aren't many  but
there
> >are a few consistent reads )
> >
> >Just part of an  issue I am trying to solve with distributed tables (the
> >above is self-contained on one instance though)
> >
> >TIA
> >
> >T
> >
>
>
> No sign of any ANALYZE TABLE so rule-based optimizer.
> Refer to the IS NULL clause in the select statement.
> In RBO IS NULL will *NEVER* use an index.
>
> Hth
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Mon Jul 01 2002 - 12:44:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US