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

Thread: how can optimize this query without touch it

  1. #1
    Join Date
    Jan 2001
    Posts
    32

    Unhappy

    i have the next query, this is executed by an application, the table table_case have a index in case_wip2wipbin
    but not in case_reporter2site, this query make to many physical reads.

    hos can optimiza this query without change.

    SQL> ED
    Wrote file afiedt.buf
    1 select /*+RULE*/ count(*) from table_case
    2 where ( ( case_wip2wipbin IS NOT NULL ) )
    3* AND case_reporter2site IN (268483034)
    SQL> /

    COUNT(*)
    ---------
    1


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=HINT: RULE
    1 0 SORT (AGGREGATE)
    2 1 TABLE ACCESS (FULL) OF 'TABLE_CASE'


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

  2. #2
    Join Date
    Jan 2001
    Posts
    126
    Hi,

    This will do a full table scan as you are checking a field for not null condition.

    Baliga

  3. #3
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    creating index (unique or duplicate) on case_reporter2site will help.

    - Rajeev

  4. #4
    Join Date
    Jan 2001
    Posts
    32
    thanks rsuri, yes only i create an index in case_reporter2site and

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=HINT: RULE
    1 0 SORT (AGGREGATE)
    2 1 TABLE ACCESS (BY ROWID) OF 'TABLE_CASE'
    3 2 INDEX (RANGE SCAN) OF 'CASE_REPORTER2SITE'


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


    only 1 physical reads, that's tuning

    thanks


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