Re:Index

From: <dgoulet_at_vicr.com>
Date: Thu, 30 Nov 2000 15:44:00 -0500
Message-Id: <10696.123354@fatcity.com>

Sonia,

    Indexes do not contain references to null values, also putting the is/is not null in the where clause invalidates the usage of the index.

Dick Goulet

____________________Reply Separator____________________

Subject: Index
Author: sonia pajerowski <spajerowski_at_yahoo.com> Date: 11/30/2000 11:25 AM

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). Received on Thu Nov 30 2000 - 14:44:00 CST