 value BETWEEN x AND y [message #391440] |
Thu, 12 March 2009 05:01  |
wakula
Messages: 150
Registered: February 2008
Location: Poland
|
Senior Member |
|
|
Hello,
Lately I have optimized a very slow query, however I would like to know the details of the cause - to know what was so bad in the older version.
Source table: CREATE s_table (d DATE,x NUMBER,v ANYDATA)
Data are inserted every day: INSERT INTO s_table(d,x,v) VALUES (TRUNC(SYSDATE),0,:bound_var)
Index (B-tree): CREATE INDEX s_idx ON s_table(d,x)
Partitioning: Table has 1 partition per month
Slow query: SELECT v FROM s_table WHERE d>=TRUNC(SYSDATE-1) AND d<=TRUNC(SYSDATE) AND x=0
Slow query: SELECT v FROM s_table WHERE d BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE) AND x=0
Fast query: SELECT v FROM s_table WHERE d IN (TRUNC(SYSDATE-1),TRUNC(SYSDATE))
Some stats:
Slow queries: 250k consistent gets
Fast query: 2k consistent gets
Additional information:
Oracle is reading the data using acces predicates.
Slow queries could be explained as "read >=TRUNC(SYSDATE-1) and then cut some part from it". This should read 1-2 partitions per query. Index was used in both cases.
Fast query is reading 1-2 partitions per query as well.
My guess:
Probably the index is accessed in a different way - but I do not know how to check that.
Comment:
The partitioning is done using DATE - probably this could be replaced with an integer (since we are not using HH:MM:SS part of the date). In the real-life table some additional condition exists: The table is quite big and column update on it is not an option (someone already added 1 column to the table - it took several days).
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: value BETWEEN x AND y [message #400937 is a reply to message #391910] |
Thu, 30 April 2009 07:37  |
wakula
Messages: 150
Registered: February 2008
Location: Poland
|
Senior Member |
|
|
Maybe this will be useful for someone - stats info from the existing (quite complex) table and some query.
AFAIK the problem was that the query did a lot of filtering instead of access predicates.
----------------------------------------------------
| Id | Operation |
----------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | COUNT |
| 2 | VIEW |
| 3 | SORT ORDER BY |
| 4 | HASH GROUP BY |
| 5 | VIEW |
| 6 | HASH GROUP BY |
| 7 | NESTED LOOPS SEMI |
| 8 | INLIST ITERATOR |
| 9 | PARTITION RANGE ITERATOR |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID|
|* 11 | INDEX RANGE SCAN |
| 12 | INLIST ITERATOR |
| 13 | PARTITION RANGE ITERATOR |
|* 14 | INDEX RANGE SCAN |
----------------------------------------------------
[FONT=Courier]Predicate Information (identified by operation id):
---------------------------------------------------
11 - access(...)
14 - access(...)
Statistics
----------------------------------------------------
1 recursive calls
0 db block gets
2521 consistent gets
0 physical reads
0 redo size
1325 bytes sent via SQL*Net to client
232 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
----------------------------------------------------
| Id | Operation |
----------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | COUNT |
| 2 | VIEW |
| 3 | SORT ORDER BY |
| 4 | HASH GROUP BY |
| 5 | VIEW |
| 6 | HASH GROUP BY |
|* 7 | FILTER |
| 8 | NESTED LOOPS SEMI |
| 9 | PARTITION RANGE ITERATOR |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID|
|* 11 | INDEX RANGE SCAN |
| 12 | PARTITION RANGE ITERATOR |
|* 13 | INDEX RANGE SCAN |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter(...)
11 - access(...)
filter(...)
13 - access(...)
filter(...)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
259282 consistent gets
52598 physical reads
0 redo size
1317 bytes sent via SQL*Net to client
232 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
|
|
|
|
|
|
| Re: value BETWEEN x AND y [message #401146 is a reply to message #401006] |
Fri, 01 May 2009 17:33 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
you have removed table and index names, so it is difficult to compare the plans.
You will need to run a trace and TKPROF to find out the real reason for the difference, but it seems likely that the previous suggestions are right: that you have data with non-midnight times.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
