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

Thread: sql statement

  1. #1
    Join Date
    Feb 2001
    Posts
    119
    The below statement takes me a long time to execute ...
    Moreover the cost and physical reads are high ... How do i
    reduce them ..

    SQL> SELECT COUNT(*) FROM SENT_MATERIALS WHERE
    2 SENT_DATE >= to_date('11/24/2001 00:00:00','MM/DD/YYYY HH24:MI:SS')
    3 AND SENT_DATE <= to_date('11/30/2001 23:59:59','MM/DD/YYYY HH24:MI:SS')
    4 AND mat_type='XX';

    COUNT(*)
    ----------
    18689


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9268 Card=1 Bytes=12
    )

    1 0 SORT (AGGREGATE)
    2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'SENT_MATERIALS'
    (Cost=9268 Card=2414 Bytes=28968)

    3 2 INDEX (RANGE SCAN) OF 'IDX_SENT_DATE' (NON-UNIQUE) (Co
    st=689 Card=2414)





    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    32587 consistent gets
    19592 physical reads
    0 redo size
    203 bytes sent via SQL*Net to client
    315 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    1 rows processed

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    well, looks like you have a lot of lines in your table.
    is the date really interesting to be indexed ?? how many days do you have online ??

    is mat_type interesting to be indexed ??? how many different values, how many nulls, what are the maximum and average number of lines you get for 1 mat_type ???

    note that you should not bother including hours, minuts and so on :

    SENT_DATE >= to_date('11/24/2001','MM/DD/YYYY')
    AND SENT_DATE < to_date('11/31/2001','MM/DD/YYYY')


  3. #3
    Join Date
    Feb 2001
    Posts
    119
    I have about 10 million rows
    2 years data
    mat_type is not indexed
    mat_type has four diffrent types

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    10Million rows? Maybe time to start thinking about a Materialized View...
    Jeff Hunter

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