View previous topic :: View next topic
|
Author |
Message |
pshongal
New User
Joined: 14 Jun 2012 Posts: 96 Location: India
|
|
|
|
Hi,
I have a query as below. Which is more efficient? using BETWEEN or '<=, >='
Code: |
SELECT field
FROM table1
WHERE fld1 = 'abc'
AND fld2 IN ('aaa','bbb')
AND CREAT_TS BETWEEN
'2016-03-14-13.43.04.577117' AND
'2016-03-14-13.43.33.882498'
WITH UR;
SELECT field
FROM table1
WHERE fld1 = 'abc'
AND fld2 IN ('aaa','bbb')
AND CREAT_TS >='2016-03-14-00.00.00.000000'
AND CREAT_TS <='2016-03-15-00.00.00.000000'
WITH UR;
|
|
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
Well, what are your thoughts, and your references to back those up?
You realise you have different values on your conditions, so even if you knew the "general" answer, the specific case with your data could not be answered. You may get 200m results from one, and only seven from the other, if your data happened to be like that. |
|
Back to top |
|
|
pshongal
New User
Joined: 14 Jun 2012 Posts: 96 Location: India
|
|
|
|
Hi Bill,
Sorry, value in query is same, it was typo.
My thought was BETWEEN is not as efficient as other one.
Code: |
AND CREAT_TS BETWEEN
'2016-03-14-00.00.00.000000' AND
'2016-03-15-00.00.00.000000'
AND CREAT_TS >='2016-03-14-00.00.00.000000'
AND CREAT_TS <='2016-03-15-00.00.00.000000'
|
Expected O/P from the query is around 5MM and it runs daily.[/code] |
|
Back to top |
|
|
pshongal
New User
Joined: 14 Jun 2012 Posts: 96 Location: India
|
|
|
|
One more thing is, CREAT_TS is also part of index |
|
Back to top |
|
|
Awanti
New User
Joined: 11 Mar 2016 Posts: 4 Location: India
|
|
|
|
I think in your case BETWEEN is better |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
I always found BETWEEN takes more time than <= as per the MIPS of our many Stored Procedures and DBA. However, you could run a parallel tests in the region and let you DBA gives you SQL stats and then you make your decision.
Code: |
SQL provides two ways to request a search for values that fall within a range (range predicates): by using the relational predicates >= and <= or by using BETWEEN. These predicates are functionally equivalent but may perform differently.
The optimizer uses default filter factors when determining the access path for range predicates. A filter factor (FF) is an estimate the optimizer uses to estimate how many rows will qualify for the predicate. Default FFs are used for range predicates because the search values are not known until execution time when they are provided by the user and can vary from one execution of the program to another. The default FF is lower for BETWEEN than for other range predicates.
For example, compare the following two statements:
SELECT SN, PN, JN, QTY
FROM SPJ
WHERE PN >= 'P2';
SELECT SN, PN, JN, QTY
FROM SPJ
WHERE PN BETWEEN 'P2' AND 'ZZZZZZ';
The optimizer can choose to use an index on PN for the search in either case, but it is more likely to use an index for the statement using BETWEEN, particularly when using host variables. This is because the optimizer uses default FFs unless reoptimization is specified when binding a plan or package. Use the highest possible value for a column, 'ZZZZZZ' as in the example. Do not use X'00' and X'FF' as the low and high values because they are treated as the same value. They are outside the range of A-Z and 0-9, and blank considered in filter factor calculations which makes the use of indexes likely even though it may not be best.
The optimizer automatically transforms >= AND <= predicates to a BETWEEN predicate. For example, the WHERE clause:
WHERE PN >= 'P2'
AND PN <= 'P9'
is transformed to:
WHERE PN BETWEEN 'P2' AND 'P9'
|
|
|
Back to top |
|
|
pshongal
New User
Joined: 14 Jun 2012 Posts: 96 Location: India
|
|
|
|
Thanks for the input guys. |
|
Back to top |
|
|
|