CODEWITHSUNDEEP
optimizationnetezza
learning_spark
learning_spark

Reputation: 669

Query optimization (Netezza)

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,

Upvotes: 0

Views: 1819

Answers (2)

Nirav Mehta
Nirav Mehta

Reputation: 7063

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)

Upvotes: 0

redcayuga
redcayuga

Reputation: 1251

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

Upvotes: 2

Related Questions