CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Baba
Baba

Reputation: 73

Index Created but doesn't speed-up on retrieval process

I have created table as bellow

create table T1(num varchar2(20))

then I inserted 3 lac numbers in above table so now it looks like below

num
1
2
3
.
.
300000

Now if I do 

select * from T1

then it takes 1min 15sec to completely fetch the records and as I created index on column num and if I use below query then it should be faster to fetch 3 lac records but it takes also 1min15sec for fetch the records

select * from T1 where num between '1' and '300000'

So how the index has improved my retrieval process?

Upvotes: 0

Views: 192

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50077

To get a test of the effects of having an index in place you might want to try a query such as

SELECT *
  FROM T1
  WHERE NUM IN ('288888', '188888', '88888')

both with with the index in place, and again after removing the index. You should also collect statistics on the table prior to running the query with the index in place or you may still get a query which performs a full table scan. Share and enjoy.

Upvotes: 0

Codo
Codo

Reputation: 79033

An index can help if you want to retrieve a few rows from a large table. But since you retrieve all rows and since your index contains all the columns of your table, it won't speed up the query.

Furthermore, you don't tell us what tool you use to retrieve the data. I guess you use SQL Developer or Toad. So what you measure is the time it takes SQL Developer or Toad to store 300,000 rows in memory in such a way that they can be easily displayed on screen in a scrollable table. You aren't really measuring how long it takes to retrieve them.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

The index does not improve the retrieval process when you are trying to fetch all rows.

The index makes it possible to find a subset of rows much more quickly.

Upvotes: 2

Related Questions