-
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?
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|