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

  ---