oracle 10 performance issue with Select * from

sql : select * from user_info where userid='1100907' and status='1'

userid is indexed, the table has less than 10000 rows and has a LOB column.

The sql takes 1 second (I got this by using "set timing on" in sqlplus). I tried to use all columns names to replace *,开发者_如何转开发but still 1 second. After I removed the LOB column ,the sql takes 0.99 secs . When I reduced the number of columns by half, the time goes to halved too.

Finally, select userid from user_info where userid='1100907' and status='1' takes 0.01 seconds.

Can someone figure it out ?

Bear in mind that "wall clock performance testing" is unreliable. It is subject to ambient database conditions, and - when outputting to SQL*Plus - dependent on how long it takes to physically display the data. That might explain why selecting half the columns really has such a substantial impact on elapsed time. How many columns does this table have?

Tuning starts with EXPLAIN PLAN. This tool will show you how the database will execute your query. Find out more.

For instance, it is quicker to service this query

select userid from user_info

then this one

select * from user_info

because the database can satisfy the first query with information from the index on userid, without touching the table at all.

edit

"Can you tell me why sqlplus print column names many many times other than just returning result"

This is related to paging. SQLPlus repeats the column headers every time it throws a page. You can suppress this behaviour with either of these SQLPlus commands:

set heading off

or

set pages n  

In that second case, make n very big (e.g. 2000) or zero.

Perhaps you have 100 columns in the user_info table? If so, how many of those columns do you actually need in the query?