Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index

RE: Index

From: Dara Vaughn <dara.vaughn_at_wcom.com>
Date: Thu, 30 Nov 2000 17:22:00 -0600
Message-Id: <10696.123367@fatcity.com>


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.


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US