use a generated column in a where clause

External Since: Apr 26, 2010 Posts: 4

I have a huge table and I have the following requirement :
Table1
---------
client
day_of_sale
qty

Table2
---------
same ddl

Now table2 needs to have the same data except for the first
day_of_sale value for each client.

So I have the query
select client,day_of_sale,qty from table(select
client,day_of_sale,qty, row_number() over(partition by client order by
day_of_sale) as rownum ) from table1 where rownum>1

This gives me a huge cost since its going to build up the inner table
and then select the 'rest' of the columns in the outer table.
To stop doing the two scans (use lesser memory to store the whole
inner table in buffer and then loop through it), what we did was
1. add the row number column in table 2
2. put in a check constraint in the table 2 that has only >1
3. load to table 2
4. set integrity

This eats up the concurrency of our project since the loaded table
can't be used while the load happens.
So I would be very happy if someone can give me a single sql without
CTE to do the above.
We are playing with the OLAP and windowing functions, but no joy till
now.

Any help or suggestions plz

External Since: Apr 26, 2010 Posts: 22

On 2010-04-26 14:28, Arun Srini wrote:
> I have a huge table and I have the following requirement :
> Table1
> ---------
> client
> day_of_sale
> qty
>
> Table2
> ---------
> same ddl
>
> Now table2 needs to have the same data except for the first
> day_of_sale value for each client.
>
> So I have the query
> select client,day_of_sale,qty from table(select
> client,day_of_sale,qty, row_number() over(partition by client order by
> day_of_sale) as rownum ) from table1 where rownum>1
>
> This gives me a huge cost since its going to build up the inner table
> and then select the 'rest' of the columns in the outer table.
> To stop doing the two scans (use lesser memory to store the whole
> inner table in buffer and then loop through it), what we did was
> 1. add the row number column in table 2
> 2. put in a check constraint in the table 2 that has only >1
> 3. load to table 2
> 4. set integrity
>
> This eats up the concurrency of our project since the loaded table
> can't be used while the load happens.
> So I would be very happy if someone can give me a single sql without
> CTE to do the above.
> We are playing with the OLAP and windowing functions, but no joy till
> now.
>
> Any help or suggestions plz

Just a thought, if I get it right the set of data NOT included in
"table2" is fairly stable, it only grows when a new client is added,
correct?

create table table3 (
client ...,
day_of_sale ...,

primary key (client, day_of_sale)
);

insert into table3 (client,day_of_sale)
select client, min( day_of_sale )
from table1
group by client;

If you can maintain table3 somehow (triggers for example), table 2 would be:

select client,day_of_sale,qty
from table1 x
left join table3 y
on (x.client, x.day_of_sale)
= (y.client, y.day_of_sale)
where y.client is null;

since table3 should be much smaller than table1, it might be an
improvement. As mentioned, just a thought.

/Lennart