Count is slow Oracle

this is my query :

SELECT Count(last_upd)
FROM S_CONTACT
WHERE last_upd_by = '0-1' AND LAST_NAME <> 'Wait' AND last_upd  +  1/24 > SYSDATE - (1 / (24 * 60));

It takes 84 seconds to return me this result :

COUNT(LAST_UPD)
---------------
43

I don't understand why it's too much slow for only 43 returned row...

This is the execution plan :

Can anyone help me? this is the V$STATNAME

active txn count during cleanout 1
bytes received via SQL*Net from client 497
bytes sent via SQL*Net to client 14803
calls to get snapshot scn: kcmgss 23
calls to kcmgas 45
cleanout - number of ktugct calls 12
cleanouts and rollbacks - consistent read gets 2
cleanouts only - consistent read gets 10
commit txn count during cleanout 11
consistent changes 450
consistent gets 1458915
consistent gets - examination 458
consistent gets from cache 1458915
CPU used by this session 4485
CPU used when call started 4493
CR blocks created 44
current blocks converted for CR 1
cursor authentications 1
data blocks consistent reads - undo records applied 59
db block changes 12
DB time 8383
dirty buffers inspected 31
enqueue releases 1
enqueue requests 1
execute count 2
free buffer inspected 1442014
free buffer requested 1436612
heap block compress 12
hot buffers moved to head of LRU 25764
immediate (CR) block cleanout applications 12
no work - consistent read gets 1458380
opened cursors cumulative 2
OS Involuntary context switches 18069
OS Maximum resident set size 1956
OS Page faults 4
OS Page reclaims 410
OS System time used 1868
OS User time used 2625
OS Voluntary context switches 63594
parse count (hard) 1
parse count (total) 2
physical read bytes 11768365056
physical read IO requests 55724
physical read total bytes 11768365056
physical read total IO requests 55724
physical read total multi block requests 54675
physical reads 1436568
physical reads cache 1436568
physical reads cache prefetch 1380844
prefe

• the query does not return 43 rows but 1 row that contains the number 43

• the execution time of a query does not depend on the number of rows it return but on the number of rows it inspects.

• from your query plan you can see your query makes a full table scan. This means it reads the table from begin to the end and reads all 16 millions of rows of the table (table scan rows gotten 16656961).

• for this it has to read about 11G data (physical read total bytes 11768365056) and executes 55,000 read operations (physical read total IO requests 55724). 55,000 reads in 60 seconds (user I/O wait time 6172) means 1.1 ms per read. This is a good value.

You have to decrease the number of row your query has to process. An index may help Oracle to count efficiently the number of rowa you are interested in.

• The most selective subclause of your where-clause will be the last_upd subclause. So make an index on this column.

• You should add the columns from the other where-clauses (and the select-clause) to the index, too. So Oracle does not have to lookup each fitting row in that is found by the index in the table to get the last_upd_by and last_name values.

• The last_upd_by subclause is the next selective clause so it schould be the next column in the index. So you should make an inex on the columns (last_upd, last_upd_by, last_name) in this order.

StackExchange Database Administrators Q#128870, answer score: 4