CODEWITHSUNDEEP
sqloracle-databasetoad
Let us see.
Let us see.

Reputation: 81

select count(*) takes more time than than select(*) in oracle

I have a pipelined function . I have two sql statements as below. The first one is as select * from table and the second one is a select count(*) from table.

SELECT *
  FROM table (es_feed_api_da.invoice_daily ('10-sep-2014'));

SELECT count(*)
 FROM table (es_feed_api_da.invoice_daily ('10-sep-2014'));

i am running the two queries in toad. I find that the second one (select count(*)) takes relatively more time than first one(select *) Can someone please explain the reason to me.. Thanks

Upvotes: 1

Views: 1774

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

i am running the two queries in toad

I find that the second one (select count(*)) takes relatively more time than first one(select *) Can someone please explain the reason to me.

It is quite obvious that SELECT * would be faster than SELECT COUNT(*) because you are executing it on TOAD which is a GUI based client tool and gives you only first few rows(like only 50 rows in SQL Developer) when you simply project/select the rows. The time elapsed would keep increasing as and when you fetch more rows by scrolling down the query result.

On the other hand, when you do a SELECT COUNT(*), it must count all the rows in the table as opposed to SELECT * which only returns the first few rows in TOAD.

I don't have TOAD, but I can demonstrate the behaviour in SQL Developer.

Output of SELECT * ONLY first 50 rows:

enter image description here

After scrolling down to 500 rows:

enter image description here

The time taken to fetch further rows will increase as and when you scroll down further.

Upvotes: 6

Related Questions