CODEWITHSUNDEEP
sqloracle-databasequery-performance
Saket
Saket

Reputation: 33

Query regarding select clause

I Need to know which of the below query will complete fastest, considering:

a) both have same rows i.e 500 million and do not have any primary key and indexes.

b) table has 30 columns (dept,name,address,-------- buyer)

c) both queries are executed independently and first time in Oracle database.

Query 1:

select * from department;

Query 2 (selecting 4 columns out of 30):

select dept,name,address,buyer from department;

If query 2 will complete first, can anyone explain the reason in detail? and if it completes in same time, then why?

Upvotes: 1

Views: 46

Answers (3)

Roger Cornejo
Roger Cornejo

Reputation: 1547

I'd say they will both execute the same on the DB side, but your client will experience slower performance on the "select *" version because it is returning more data, and over 500,000,000 rows you're more likely to feel the network delay due to larger volume of data. That can be measured from client side or from DBA_HIST_ACTIVE_SESS_HISTORY total elapsed time for the session.

Upvotes: 0

Jon Heller
Jon Heller

Reputation: 36798

The queries will probably run equally fast because of the way Oracle stores table data as rows, not individual columns. Whether you select one column or all the columns, Oracle must read the entire row from disk or memory. (And since Oracle stores data in 8 kilobyte blocks, it will likely also read more than one row.)

But there are many exceptions to the above statement. The following features may make selecting a small number of columns run faster than selecting all the columns:

  • Indexes - If all the columns are part of an index, Oracle can read that index like a skinny version of the table.
  • Materialized views - Like with indexes, a smaller version of the table may be available in a materialized view.
  • LOB - Large Objects are typically stored out of line, in a separate segment. When Oracle reads a row of data, if the CLOB, BLOB, XML, etc., is not projected, Oracle does not need to access that separate segment.
  • In-memory columnar storage - Another way to read only the necessary columns.
  • Chained rows - If the rows are too large to fit in the (default 8 kilobyte) blocks, then the row will span multiple blocks, and reading only a subset of columns may avoid reading the other block. (I'm not 100% sure if this is true in practice.)
  • Result cache - If result cache is used, a smaller result set is more likely to stay in the result cache.
  • Intermediate result sets - If the query is more complicated, and contains joins and other operations, then Oracle will only create intermediate results sets of the necessary columns. Oracle will use less temporary tablespace for sorting and hashing if less columns are used.
  • Network overhead - As Alex Poole pointed out, your overall performance for such a simple query is likely to depend more on the network than on database performance.

There are so many exceptions it's hard to make a general rule about even simple queries. And as alby98 pointed out, using * isn't always a good idea in production code.

Upvotes: 2

alby98
alby98

Reputation: 47

you can find out which of the two queries is faster just by running it. However the first one could be slower because you take all the fields of the table, having entered *. The second method is better in my opinion because in general it is advisable to insert only the fields you need. I always advise you to write the fields that you want the query to return to you because if one day the table changes its structure you will have no problems. Conversely, entering *, if one day new fields will be inserted to the table, your query will take them and if they are not managed in the backend you could have problems. Therefore it is good practice to enter only the fields you need.

Upvotes: 3

Related Questions