Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: A Question regarding null columns.
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
| | |
| 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 isnull;
> > > >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 butthere
> >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 addressReceived on Mon Jul 01 2002 - 12:44:52 CDT