RE: Index

From: Dara Vaughn <dara.vaughn_at_wcom.com>
Date: Thu, 30 Nov 2000 15:03:43 -0600
Message-Id: <10696.123357@fatcity.com>

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-----

From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of sonia pajerowski
Sent: Thursday, November 30, 2000 1:25 PM To: Multiple recipients of list ORACLE-L Subject: Index

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 - 15:03:43 CST