Query optimization (Netezza)

Question

I am trying to solve a problem in Netezza for a friend of mine. My friend is trying to do something really simple in Netezza:

select * 
from 
test
where 
ID1 = 12345
and date >= 12/1/2012 and event date <= 12/31/2012
and ID2 in
(x1, x2, ..., x150000)

This query never returns.

In Oracle, we could have tried something like this:

/*******************************************************************/
/*To reduce the size of the table...*/

create table t_test as
select * 
from 
test
where 
ID1 = 12345
and date >= 12/1/2012 and event date <= 12/31/2012;

/*To make the search on ID2 faster...*/
create index i_ID2 on t_test (ID2);

select * 
from 
t_test
where 
ID2 in
(x1, x2, ..., x150000)

/*Alternative: People say "IN" may be very inefficient at times...*/

select * 
from 
t_test
where 
ID2 = x1
or ID2 = x2
.
.
.
or ID2 = x150000
/*******************************************************************/

However, this is not possible in Netezza, since it does not have any index concept.

How do I solve this problem?

Thanks and regards,

Aginity workbench for netezza Version 3.0.791.17116 (build 4/19/2012) Comunity version — learning_spark, Feb 15 '13 at 15:33
What is the distribution on table "test"? What data type is ID and ID2? And fix your date logic as @redcayuga mentioned... — N West, Mar 07 '13 at 13:47

score 2 · Answer 1 · answered Feb 17 '13 at 23:19

Exactly how is the line "and date >= 12/1/2012 and event date <= 12/31/2012" coded when you run it? Do you really have a column named "DATE"? Is an underscore missing in "event date"?

12/1/2012 evaluates to integer zero, not a date. Use:

date '2012-12-01'
date '2012-12-31'

or to_date('12/1/2012', 'mm/dd/yyyy') like Oracle

score 0 · Answer 2 · answered Jun 08 '15 at 19:55

0

Try this way

select * 
from 
test
where 
ID1 = 12345
and eventdate BETWEEN DATE('12/1/2012') DATE('12/31/2012')
and ID2 in
(x1, x2, ..., x150000)

answered Jun 08 '15 at 19:55

Nirav Mehta

6,943
9
42
51

Query optimization (Netezza)

2 Answers2