Sonia,
unless you specify a particular <test> in the WHERE condition then this
statement
is always going to do a full table scan. Think about it, you're asking
Oracle
to give you a count of the number of rows for each <test> value in the
table, it has no choice
but to scan the whole table....
could you not pass in a <test> value into the select, eg. using a bind
variable??
For example :- SELECT count(*)
FROM mytest
WHERE test = v_test;
Where v_test is the <test> value you're interested in....
Regards,
Kieran
-----Original Message-----
From: sonia pajerowski [mailto:spajerowski_at_yahoo.com]
Sent: 30 November 2000 22:21
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
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Received on Fri Dec 01 2000 - 03:25:24 CST
