oracle 10 performance issue with Select * from ...

Question

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 ?

sorry for my bad english .

Answer 1

Bear in mind that wall clock performance 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. SQL*Plus repeats the column headers every time it throws a page. You can suppress this behaviour with either of these SQL*Plus commands:

set heading off

or

set pages n  

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

Answer 2

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