DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: how can i decrese the physical reads?

  1. #1
    Join Date
    Jan 2001
    Posts
    32

    Question

    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?

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
  •  


Click Here to Expand Forum to Full Width