View previous topic :: View next topic
|
Author |
Message |
pkmurali Warnings : 1 Active User
Joined: 15 Dec 2005 Posts: 271
|
|
|
|
Hi All,
I have a query as shown below,
Code: |
Select c1 from tab
where c2 <> spaces;
|
C2 CHAR(8) NOT NULL
I need to select the row from tab where C2 greater than space and less than high value. Is that below query is fine or is there any efficient methods available to query.
Code: |
Select c1 from tab
where c2 between hex('40') and hex('FF');
|
[/b] |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
You say "greater than" and "less than" but BETWEEN is inclusive so you need hex('41') and hex('fe').
BETWEEN is, apparently, more efficient than using "a >= ' ' AND a <= x'FF'" |
|
Back to top |
|
|
pkmurali Warnings : 1 Active User
Joined: 15 Dec 2005 Posts: 271
|
|
|
|
Thanks Nick. Is it enough to compare 1 byte hex'41' and hex'fe' or it should be 8 bytes ? |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
It's Nic.
Try it. |
|
Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
Quote: |
Is it enough to compare 1 byte hex'41' and hex'fe' or it should be 8 bytes ? |
This really depends upon your data and the requirement. In most cases, checking the first character is enough since X'4100000000000000' is going to be greater than X'40FFFFFFFFFFFFFF'. |
|
Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
pkmurali wrote: |
Is it enough to compare 1 byte hex'41' and hex'fe' or it should be 8 bytes ? |
It is not:
The lowest key you will fetch is x'41000000...' and the highest is x'FEFFFFFF...'
What about x'4041404040...' or x'FF000000..' for example ?
The first one is higher than all spaces but lower than your lowest key, the second is higher than your highest key but lower than high-values.
And none of them will be fetched. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
why would You care about specifying the HIGH_VALUE
should' nt be enough to specify
YOUR_8_BYTES_COLUMN > x'4040404040404040' |
|
Back to top |
|
|
pkmurali Warnings : 1 Active User
Joined: 15 Dec 2005 Posts: 271
|
|
|
|
Thanks to everyone addressed for this issue. This query cam when one of my colleague used <> which is not recommended. In my project I don't have any db2 tools to measure the query will go under sequential scan. Can I use only EXPLAIN provided if I have access to validate? I have to show how the.comparison of > spaces query with <> . Will both will impose sequential scan ? |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3051 Location: NYC,USA
|
|
|
|
Quote: |
This query cam when one of my colleague used <> which is not recommended. |
Don't go too fancy. How much time are you trying to save here? How many rows are in context? What is explain results? have you run anything yet in QA with real data volume? Is the column part of the index?
Keep it simple and easy maintainability for others in future when they see the code. |
|
Back to top |
|
|
pkmurali Warnings : 1 Active User
Joined: 15 Dec 2005 Posts: 271
|
|
|
|
I thought of writhing a optimized query. Since <> is stage 1 predicate I believe > spaces would be indexable. There 5 million rows in e table. The column is not a part of index. In my previoous post I have mentioned that the query is not used with EXPLAIN. Please suggest if a non indexable column is used both <> and > will have the same performance ? |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3051 Location: NYC,USA
|
|
|
|
You still did not answer , have you run the query yet and measured the performance? All you are talking is in air.
if you want something > spaces then code that way or if you want something <> SPACES then code that way. If you get any performance issues, then get DBA advice. Also what harms you to add index on this column? |
|
Back to top |
|
|
pkmurali Warnings : 1 Active User
Joined: 15 Dec 2005 Posts: 271
|
|
|
|
Hi Rohit
I dont have access to execute Explain query. I have executed both queries in db2 spufi . So i am not able to measure the performance.
Thanks
Murali |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3051 Location: NYC,USA
|
|
|
|
Quote: |
I dont have access to execute Explain query. I have executed both queries in db2 spufi . So i am not able to measure the performance
|
Do you have DBA's? If not then forum cannot help, otherwise ask them to give you the results of explain, they should be able to give you. However, by having proper cardinality and indexing this is non issue also, there is no time out when you ran in SPUFI, so why do you consider it as an issue ? |
|
Back to top |
|
|
|