-
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
-
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')
-
I have about 10 million rows
2 years data
mat_type is not indexed
mat_type has four diffrent types
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|