-
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
-
Hi,
This will do a full table scan as you are checking a field for not null condition.
Baliga
-
creating index (unique or duplicate) on case_reporter2site will help.
- Rajeev
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|