how can i decrese the physical reads?

[fmedina]

i have the next query

SQL> select count(*)
2 from table_case;

COUNT(*)
---------
82379

real: 5830

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Cost=3833 Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TABLE_CASE'


Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
39846 consistent gets
25244 physical reads
0 redo size
141 bytes sent via SQL*Net to client
181 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

if i want tune the query, the execution plan have a full access in table_case, this is logic by the query, but how i can decrese de physical reads?

[chrisrlong]

Create a unique index on the table - which it should, of course, have anyway - the PK :).

A unique index will have the same number of 'records' as the base table, but occupying much less space - assuming that your table has more than one column now.

The optimizer will generally recognize this fact and count using the smaller index instead of the larger table. *Sometimes*, it does not - I've never investigated why, however.

*If it does not*, then you will want to 'trick' it into using your index. In order to do that, you can add a bogus WHERE clause that uses the field.

For example, most PKs are on a single field populated via a sequence and, therefore, never have a record with a value less than 1.

So, assuming you had a PK on this table and it was a field called TABLE_CASE_ID, the following query would use the index to count the records, thereby saving you a lot of physical reads:

SELECT
---COUNT(*)
FROM
---TABLE_CASE
WHERE
---TABLE_CASE_ID > 0

Of course, your other, and probably simpler, option is to hint the query.

SELECT /*+ INDEX(TABLE_CASE,TABLE_CASE_PK) */
---COUNT(*)
FROM
---TABLE_CASE

Try both - one of them should work :)

Hope this helps,

- Chris