productstr这张表有1千万条记录。
product这张表有200万条记录。
想根据product.pcode和product.keyno去匹配productstr表中的最近一条记录。我的做法如下:
--建立中间表对productstr进行降序排列
create table zh_tmp_product_str_0702 as
select * from productstr a order by a.recid desc;
create index index_zh_tmp_product_str_0702 on zh_tmp_product_str_0702(pcode);
create index index_zh_tmp_product_str1_0702 on zh_tmp_product_str_0702(keyno);
--下面再建立这张表效率好低啊。等了好久SQL都没跑完
--怎么提高效率呢?
drop table product_old;
create table product_old nologging as
select b.*,
(select t.recid from zh_tmp_product_str_0702 t where t.pcode=b.pcode and t.keyno=b.keyno and rownum=1) recid,
(select t.keyno from zh_tmp_product_str_0702 t where t.pcode=b.pcode and t.keyno=b.keyno and rownum=1) keyno
from zhboss.product b;
product这张表有200万条记录。
想根据product.pcode和product.keyno去匹配productstr表中的最近一条记录。我的做法如下:
--建立中间表对productstr进行降序排列
create table zh_tmp_product_str_0702 as
select * from productstr a order by a.recid desc;
create index index_zh_tmp_product_str_0702 on zh_tmp_product_str_0702(pcode);
create index index_zh_tmp_product_str1_0702 on zh_tmp_product_str_0702(keyno);
--下面再建立这张表效率好低啊。等了好久SQL都没跑完
--怎么提高效率呢?
drop table product_old;
create table product_old nologging as
select b.*,
(select t.recid from zh_tmp_product_str_0702 t where t.pcode=b.pcode and t.keyno=b.keyno and rownum=1) recid,
(select t.keyno from zh_tmp_product_str_0702 t where t.pcode=b.pcode and t.keyno=b.keyno and rownum=1) keyno
from zhboss.product b;
select b.*,row_number()OVER(PARTITION BY a.pcode,a.keyno ORDER BY statedate DESC) RN
from productstr a,product b WHERE a.pcode=b.pcode AND a.keyno=b.keyno) t WHERE RN=1