Home » RDBMS Server » Performance Tuning » value BETWEEN x AND y (Oracle 10.2G)
icon3.gif  value BETWEEN x AND y [message #391440] Thu, 12 March 2009 05:01 Go to next message
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 #391446 is a reply to message #391440] Thu, 12 March 2009 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
BETWEEN use a range scan.
IN use an equality.

If you are not making date arithmetic you can convert to integer, for instance julian day.

You can also index on "trunc(d)" and always use "where trunc(d) ..." in queries. You will then have the same performances.

Regards
Michel
Re: value BETWEEN x AND y [message #391466 is a reply to message #391446] Thu, 12 March 2009 05:59 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Michel Cadot wrote on Thu, 12 March 2009 11:09
BETWEEN use a range scan.
IN use an equality.


In other words - range-scan on a distinct set of data is not equivalent with equality-scan on the same set of data.

Why is it like that? I thought that partition pruning should use range scan and index performance should be the same on a B-tree index when there are only several values of the first indexed column.... I guess that I was wrong Smile
Re: value BETWEEN x AND y [message #391471 is a reply to message #391466] Thu, 12 March 2009 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Scan for all values between 2 in a continuum is more expensive than checking 2 values, even if the result is the same, the algorirthm is not.

Now it depends on many factors like data life (inserts, deletes, updates), storage, number of rows/values...

Regards
Michel
Re: value BETWEEN x AND y [message #391539 is a reply to message #391440] Thu, 12 March 2009 08:44 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Michelle,

Thanks for explaining!
Re: value BETWEEN x AND y [message #391910 is a reply to message #391440] Sat, 14 March 2009 09:37 Go to previous messageGo to next message
alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com
Senior Member
Hi,

If you show the execution plans, that will be easy to understand.
Regards,
Alex

[Updated on: Sat, 14 March 2009 10:04] by Moderator

Report message to a moderator

Re: value BETWEEN x AND y [message #400937 is a reply to message #391910] Thu, 30 April 2009 07:37 Go to previous messageGo to next message
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 #401006 is a reply to message #391440] Fri, 01 May 2009 01:34 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Your queries are not equivalent.

If you have a value d with a time attached to it, it will fail the IN query, but would have passed the BETWEEN query.
Re: value BETWEEN x AND y [message #401146 is a reply to message #401006] Fri, 01 May 2009 17:33 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: keep pool
Next Topic: Hint is not working
Goto Forum:
  


Current Time: Sat Dec 03 20:12:25 CST 2016

Total time taken to generate the page: 0.28521 seconds