From: nronayette@alphacsp.com (Nicolas Ronayette)
Newsgroups: comp.databases.oracle.server
Subject: using INDEX or not with same WHERE clause
Date: 12 Dec 2001 07:08:39 -0800
Organization: http://groups.google.com/
Lines: 27
Message-ID: <8d9a759a.0112120708.587de1f@posting.google.com>
NNTP-Posting-Host: 62.23.82.50
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1008169720 26819 127.0.0.1 (12 Dec 2001 15:08:40 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 12 Dec 2001 15:08:40 GMT


Hello group

I'm Working on Oracle parallel Server (2 nodes) 8.1.7.

I've got a big table (>500 000 rows)

I'm using EXPLAIN PLAN and plan_table to understand how CBO execute my 
query. I've done an "analyze table tlogs estimate statistics" before.

Here is my query : select count(*) from tlogs where column1='A VALUE';

This give me the result '9' in six second.

and when i do a

select column2 from tlogs where column1='A VALUE';

This give me the nine rows in more than 2 minutes.

Explain PLAN tell me that in the first query, the index on column1 is 
used, and in the second case, the index on column1 is NOT use and there 
is a full table scan.

I don't understand why Oracle choosing an FTS in the second case (with 
the same WHERE condition)

Thanks for your help.