Sonia,
You still have the same situation. The fastest way to select all rows when
a column is null or not is usually a full table scan. For the count(*), it
can use a fast_full_index scan. (If it is not automatically choosing this,
use the /*+ INDEX_FFS */ hint.) This does a full scan of the index.
A solution for your problem may be a bit-mapped index on the column in
question which is the only kind of index that includes nulls. This should
only be used if there is a narrow range of values in the column (low
cardinality).
Alternatively, you can try a function based index such as nvl(test,-1) (if
it's a numeric column) and then use the same function in your where clause.
I think this would work??
Good luck!
Dara
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of sonia
pajerowski
Sent: Thursday, November 30, 2000 4:21 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Index
Even if I take out the null clause and change the
select to
select count(*), test from mytest group by test
I am getting the same results from the explain plan.
Currently, I have 100 rows but once we go live we can
grow to few million in matter of days.
Is there a way to test on a test box without
inserting thousand's of rows in the table.
Thanks
Sonia P.
- Dara Vaughn <dara.vaughn_at_wcom.com> wrote:
> Sonia,
>
> It is how the index works. Whenever you check if a
> column value is null or
> is not null, that forces a full table scan, because
> null values by
> definition are NOT stored in an index. Based on the
> size you say your table
> is (100 rows) you don't need an index anyway. The
> full table scan should
> happen in one disk access and would be faster than
> reading both an index and
> the table data.
>
> Dara Vaughn
> Oracle DBA
>
> -----Original Message-----
> pajerowski
> Sent: Thursday, November 30, 2000 1:25 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hello,
> I have a table with 5 columns and also have 5 views
> based on these columns.
>
> Views basically do a
>
> select count(*), test from mytest
> where test is null
> group by test
> or
> select count(*), test from mytest
> where test is not null
> group by test
>
> Explain plan shows the same path (FULL TABLE ACCESS)
> before and after creating index on the column test.
> The tabe has less than 100 rows and that might be
> the
> reason for full table access or is it something else
> I
> might be missing.
>
> Query Plan
>
> ----------
> SELECT STATEMENT Cost =
> SORT GROUP BY
> TABLE ACCESS FULL MYTEST 1
>
> Thanks
> Sonia P.
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Shopping - Thousands of Stores. Millions of
> Products.
> http://shopping.yahoo.com/
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: sonia pajerowski
> INET: spajerowski_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Dara Vaughn
> INET: dara.vaughn_at_wcom.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products.
http://shopping.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: sonia pajerowski
INET: spajerowski_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
Received on Thu Nov 30 2000 - 17:22:00 CST
