Home » RDBMS Server » Performance Tuning » how to optimize sql for query big table? (merged 3)
| how to optimize sql for query big table? (merged 3) [message #396879] |
Wed, 08 April 2009 04:48  |
kevin_ye
Messages: 37
Registered: December 2007
Location: Shanghai,China
|
Member |
|
|
The following sql is used to query sales quality for every stock_id per week.
There have more than 6,000,000 records in so table, and more than 20,000,000 records in so_items table.
Both the 2 tables are huge, so it cost me about 3 seconds to query per time.
If I query a month(about 6 weeks) sales quality for 100 stock_id,
it will cost me 3*6*100=1800seconds, about half an hour. It's a long time.
Can anyone help me to optimize the sql. Any suggestion appreciated.
select si.stock_id, sum(si.qty) all_qty_tot
from so_items si
where si.stock_id='29003786'
and exists
(
select 1 from so s
where si.so_id=s.so_id
and s.order_dt>=to_date('20090105','yyyymmdd')
and s.order_dt<=to_date('20090111','yyyymmdd')
and s.so_status_cd=8
)
group by si.stock_id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4172 Card=1 Bytes=21)
1 0 SORT (GROUP BY NOSORT) (Cost=4172 Card=1 Bytes=21)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SO_ITEMS' (Cost=4172 Card=5910 Bytes=124110)
3 2 INDEX (RANGE SCAN) OF 'SO_ITEMS_STOCK_ID_I' (NON-UNIQUE) (Cost=845 Card=5910)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'SO' (Cost=3 Card=1 Bytes=19)
5 4 INDEX (UNIQUE SCAN) OF 'SO_PK' (UNIQUE) (Cost=2 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
76362 consistent gets
2363 physical reads
0 redo size
237 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
If I used the natual join syntax, then it will cost me for more time to query the sales quality.
Because then it used range scan for so table instead of unique scan.
I have try to run a month(about 6 weeks) sales quality for 100 stock_id,
I cannot see the result in 2 hours.
SQL> select si.stock_id, sum(si.qty) all_qty_tot
2 from so s, so_items si
3 where si.so_id=s.so_id
4 and si.stock_id='29003786'
5 and s.order_dt>=to_date('20090105','yyyymmdd')
6 and s.order_dt<=to_date('20090111','yyyymmdd')
7 and s.so_status_cd=8
8 group by si.stock_id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23356 Card=1 Bytes=4
0)
1 0 SORT (GROUP BY NOSORT) (Cost=23356 Card=1 Bytes=40)
2 1 NESTED LOOPS (Cost=23356 Card=2289 Bytes=91560)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'SO' (Cost=14280 Card
=2269 Bytes=43111)
4 3 INDEX (RANGE SCAN) OF 'SO_ORDER_DT_PAYMENT_WHS_ID_I'
(NON-UNIQUE) (Cost=157 Card=2269)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'SO_ITEMS' (Cost=4 Ca
rd=118188 Bytes=2481948)
6 5 INDEX (RANGE SCAN) OF 'SO_ITEMS_STOCK_ID_I' (NON-UNI
QUE) (Cost=3 Card=118188)
Appendix
###################################################################################################
1 Table Index
---------------------------------------------------------------------------------------------
(1) so order: so, more than 6,000,000 records, including the following indexes:
Primary Key: SO_PK(so_id)
Normal index: SO_ORDER_DT_PAYMENT_WHS_ID_I(order_dt, payment_whs_id)
(2) so order detail: so_items, more than 20,000,000 records, including the following indexes:
Primary Key: SO_PK(so_id, so_item_order)
Normal index: SO_ITEMS_STOCK_ID_I(stock_id,so_id)
2 Table structure
---------------------------------------------------------------------------------------------
-- Create table
create table SO
(
SO_ID VARCHAR2(10) not null,
GROUP_ID VARCHAR2(10),
SO_TYPE_CD NUMBER not null,
SO_STATUS_CD NUMBER not null,
BUYER_ID VARCHAR2(12),
SERVICER_ID VARCHAR2(12),
REFERRER_ID VARCHAR2(12),
BUYER_NAME VARCHAR2(60),
SERVICER_NAME VARCHAR2(60),
REFERRER_NAME VARCHAR2(60),
ORDER_TOTAL NUMBER not null,
ORDER_PV NUMBER not null,
PAYMENT_WHS_ID VARCHAR2(10) not null,
DELIVERY_WHS_ID VARCHAR2(10) not null,
DELIVERY_TYPE_CD NUMBER not null,
CIPM_YN VARCHAR2(1) not null,
DISCOUNT_TYPE_CD NUMBER,
CREATE_DTM DATE default SYSDATE not null,
EMP_ID VARCHAR2(8) default 'user' not null,
SHIFT_CD NUMBER not null,
SO_COMMENT VARCHAR2(2000),
COMM_DT DATE,
ORDER_DT DATE not null,
NRSV_TOTAL NUMBER not null,
ENTRY_SO_TYPE_CD NUMBER,
RECEIPT_STATUS_CD NUMBER,
MANUAL_SO_ID VARCHAR2(9),
COPS_SO_ID VARCHAR2(11),
SOENTRY_BEGIN_DTM DATE,
SOENTRY_END_DTM DATE,
ARO_ID VARCHAR2(10),
RMA_EXCEPTION_YN VARCHAR2(1) default 'N',
FREIGHT_AMOUNT NUMBER,
FREIGHT_ORDER_TOTAL NUMBER default 0,
FREIGHT_CREDIT_AMOUNT NUMBER,
COMM_SELLER_ID VARCHAR2(12)
);
alter table SO
add constraint SO_PK primary key (SO_ID);
create index SO_ORDER_DT_PAYMENT_WHS_ID_I on SO (ORDER_DT, PAYMENT_WHS_ID);
create table SO_ITEMS
(
SO_ID VARCHAR2(10) not null,
SO_ITEM_ORDER NUMBER(2) not null,
STOCK_ID VARCHAR2(8) not null,
INV_ Price_TYPE_CD VARCHAR2(2) not null,
INV_ Price_TYPE_DESCRIPTION VARCHAR2(80),
QTY NUMBER(4) not null,
PV NUMBER not null,
UNIT_ Price NUMBER not null,
RETAIL_ Price NUMBER not null,
DISCOUNT NUMBER,
QTY_RETURNED NUMBER(6),
STOCK_DESCRIPTION VARCHAR2(80) not null,
DELIVERY_WHS_ID VARCHAR2(10) not null,
AREA_ID VARCHAR2(15),
NRSV NUMBER not null,
REDEEM_CREDIT NUMBER,
FREIGHT_YN VARCHAR2(1) default 'Y',
DS_APPROVE_YN VARCHAR2(1)
);
alter table SO_ITEMS
add constraint SO_ITEMS_PK primary key (SO_ID, SO_ITEM_ORDER);
alter table SO_ITEMS
add constraint SO_SO_ITEMS foreign key (SO_ID)
references SO (SO_ID);
create index SO_ITEMS_STOCK_ID_I on SO_ITEMS (STOCK_ID, SO_ID);
3 Initialize
---------------------------------------------------------------------------------------------
--2 初始数据
--------------------------------------------------------------------------
delete from so_items;
delete from so;
commit;
insert into so values('CN06426536', 'CN06426536', 2, 8, 'CN2442437', 'CN2442437', '', 'TOM', 'TOM', '', 1008, 93.45, 'CN22', 'CN22', 2, 'N', 23, to_date('05-01-2009 10:54:01', 'dd-mm-yyyy hh24:mi:ss'), 'ERPADMIN', 1, 'orig emp_id :CN01297 shift_cd :1', to_date('31-01-2009', 'dd-mm-yyyy'), to_date('05-01-2009', 'dd-mm-yyyy'), 775.41, 2, 2, '', '', to_date('05-01-2009 10:49:59', 'dd-mm-yyyy hh24:mi:ss'), to_date('05-01-2009 11:28:05', 'dd-mm-yyyy hh24:mi:ss'), 'AR00652524', 'N', null, 1008, null, 'CN2442437');
insert into so values('CN06426776', 'CN06426776', 2, 8, 'CN0271174', 'CN0271174', '', 'ADAM', 'ADAM', '', 94.5, 8.75, 'CN22', 'CN22', 2, 'N', 3, to_date('05-01-2009 11:56:00', 'dd-mm-yyyy hh24:mi:ss'), 'ERPADMIN', 1, 'orig emp_id :CNA0005 shift_cd :1', to_date('31-01-2009', 'dd-mm-yyyy'), to_date('05-01-2009', 'dd-mm-yyyy'), 72.69, 2, 2, '', '', to_date('05-01-2009 11:55:22', 'dd-mm-yyyy hh24:mi:ss'), to_date('05-01-2009 12:28:07', 'dd-mm-yyyy hh24:mi:ss'), '', 'N', null, 94.5, null, 'CN0271174');
insert into so values('CN06427609', 'CN06427609', 2, 8, 'CN2481885', 'CN2481885', '', 'BRUCE', 'BRUCE', '', 3929, 364.22, 'CN22', 'CN22', 2, 'N', 23, to_date('05-01-2009 13:40:28', 'dd-mm-yyyy hh24:mi:ss'), 'ERPADMIN', 1, 'orig emp_id :CNA0005 shift_cd :1', to_date('31-01-2009', 'dd-mm-yyyy'), to_date('05-01-2009', 'dd-mm-yyyy'), 3022.37, 2, 2, '', '', to_date('05-01-2009 13:36:12', 'dd-mm-yyyy hh24:mi:ss'), to_date('05-01-2009 14:13:08', 'dd-mm-yyyy hh24:mi:ss'), 'AR00582389', 'N', null, 3929, null, 'CN2481885');
insert into so values('CN06428697', 'CN06428697', 2, 9, 'CN0313182', '', '', 'NO NAME', '', '', 2064, 191.29, 'CN22', 'CN22', 2, 'N', 23, to_date('13-05-2009 16:14:43', 'dd-mm-yyyy hh24:mi:ss'), 'CNA0005', 1, '', null, to_date('05-01-2009', 'dd-mm-yyyy'), 1587.69, 2, 1, '', '', to_date('05-01-2009 16:08:21', 'dd-mm-yyyy hh24:mi:ss'), null, 'AR00557506', 'N', null, 2064, null, '');
insert into so values('CN06429043', 'CN06429043', 2, 8, 'CN1442146', 'CN1442146', '', 'JACKY', 'JACKY', '', 1204, 111.59, 'CN22', 'CN22', 2, 'N', 23, to_date('13-05-2009 17:16:42', 'dd-mm-yyyy hh24:mi:ss'), 'CN01254', 1, '', to_date('31-01-2009', 'dd-mm-yyyy'), to_date('05-01-2009', 'dd-mm-yyyy'), 926.14, 2, 2, '', '', to_date('05-01-2009 17:15:19', 'dd-mm-yyyy hh24:mi:ss'), to_date('05-01-2009 17:39:55', 'dd-mm-yyyy hh24:mi:ss'), 'AR00175930', 'N', null, 1204, null, 'CN1442146');
insert into so_items values ('CN06426536', 1, '29003103', 'RG', 'Redeem Price', 1, 13.72, 148, 185, 20, null, 'AA', 'CN22', 'CN22SHP', 113.85, 0, 'Y', '');
insert into so_items values ('CN06426536', 2, '29138082', 'RG', 'ARO Price', 1, 20.76, 224, 280, 20, null, 'BB', 'CN22', 'CN22SHP', 172.31, 0, 'Y', '');
insert into so_items values ('CN06426536', 3, '29003441', '23', 'ARO Price', 1, 20.39, 220, 275, 20, null, 'CC', 'CN22', 'CN22SHP', 169.23, 0, 'Y', '');
insert into so_items values ('CN06426536', 4, '29110312', '23', 'ARO Price', 1, 9.27, 100, 125, 20, null, 'DD', 'CN22', 'CN22SHP', 76.92, 0, 'Y', '');
insert into so_items values ('CN06426536', 5, '29110115', '23', 'ARO Price', 1, 2.97, 32, 40, 20, null, 'EE', 'CN22', 'CN22SHP', 24.62, 0, 'Y', '');
insert into so_items values ('CN06426536', 6, '29003817', '23', 'ARO Price', 2, 5.2, 28, 35, 20, null, 'FF', 'CN22', 'CN22SHP', 43.08, 0, 'Y', '');
insert into so_items values ('CN06426536', 7, '29003786', '23', 'ARO Price', 1, 8.9, 96, 120, 20, null, 'GG(SPF30)', 'CN22', 'CN22SHP', 73.85, 0, 'Y', '');
insert into so_items values ('CN06426536', 8, '29110125', '23', 'ARO Price', 2, 5.94, 32, 40, 20, null, 'HH', 'CN22', 'CN22SHP', 49.24, 0, 'Y', '');
insert into so_items values ('CN06426536', 9, '29110123', '23', 'ARO Price', 1, 6.3, 68, 85, 20, null, 'II', 'CN22', 'CN22SHP', 52.31, 0, 'Y', '');
insert into so_items values ('CN06426776', 1, '29110121', 'P3', '', 1, 5, 54, 60, 10, null, 'JJ', 'CN22', 'CN22SHP', 41.54, 0, 'Y', 'Y');
insert into so_items values ('CN06426776', 2, '29110118', 'P3', '', 1, 3.75, 40.5, 45, 10, null, 'KK', 'CN22', 'CN22SHP', 31.15, 0, 'Y', 'Y');
insert into so_items values ('CN06427609', 1, '29003829', 'RG', 'Redeem Price', 2, 73.4, 396, 495, 20, null, 'LL', 'CN22', 'CN22SHP', 609.24, 0, 'Y', '');
insert into so_items values ('CN06427609', 2, '29102730', 'RG', 'Redeem Price', 1, 34.85, 376, 470, 20, null, 'MM', 'CN22', 'CN22SHP', 289.23, 0, 'Y', '');
insert into so_items values ('CN06427609', 3, '29102733', '23', 'ARO Price', 1, 21.5, 232, 290, 20, null, 'NN', 'CN22', 'CN22SHP', 178.46, 0, 'Y', '');
insert into so_items values ('CN06427609', 4, '29131311', '23', 'ARO Price', 2, 144.58, 780, 975, 20, null, 'OO', 'CN22', 'CN22SHP', 1200, 0, 'Y', '');
insert into so_items values ('CN06427609', 5, '29102711', '23', 'ARO Price', 1, 15.57, 168, 210, 20, null, 'PP', 'CN22', 'CN22SHP', 129.23, 0, 'Y', '');
insert into so_items values ('CN06427609', 6, '29110115', '23', 'ARO Price', 5, 14.85, 32, 40, 20, null, 'QQ', 'CN22', 'CN22SHP', 123.1, 0, 'Y', '');
insert into so_items values ('CN06427609', 7, '29110125', '23', 'ARO Price', 5, 14.85, 32, 40, 20, null, 'RR', 'CN22', 'CN22SHP', 123.1, 0, 'Y', '');
insert into so_items values ('CN06427609', 8, '29138049', '23', 'ARO Price', 1, 3.71, 40, 50, 20, null, ' SS', 'CN22', 'CN22SHP', 30.77, 0, 'Y', '');
insert into so_items values ('CN06427609', 9, '29003817', '23', 'ARO Price', 7, 18.2, 28, 35, 20, null, 'TT', 'CN22', 'CN22SHP', 150.78, 0, 'Y', '');
insert into so_items values ('CN06427609', 10, '29100403', '23','ARO Price', 1, 22.71, 245, 310, 20, null, 'UU', 'CN22', 'CN22SHP', 188.46, 0, 'Y', '');
insert into so_items values ('CN06428697', 1, '29003441', '23', 'ARO Price', 2, 40.78, 220, 275, 20, null, 'VV', 'CN22', 'CN22SHP', 338.46, 0, 'Y', '');
commit; |
|
|
|
| how to optimize sql for query big table? [message #396881 is a reply to message #396879] |
Wed, 08 April 2009 04:55  |
kevin_ye
Messages: 37
Registered: December 2007
Location: Shanghai,China
|
Member |
|
|
The following sql is used to query sales quality for every stock_id per week.
There have more than 6,000,000 records in so table, and more than 20,000,000 records in so_items table.
Both the 2 tables are huge, so it cost me about 3 seconds to query per time.
If I query a month(about 6 weeks) sales quality for 100 stock_id,
it will cost me 3*6*100=1800seconds, about half an hour. It's a long time.
Can anyone help me to optimize the sql. Any suggestion appreciated.
select si.stock_id, sum(si.qty) all_qty_tot
from so_items si
where si.stock_id='29003786'
and exists
(
select 1 from so s
where si.so_id=s.so_id
and s.order_dt>=to_date('20090105','yyyymmdd')
and s.order_dt<=to_date('20090111','yyyymmdd')
and s.so_status_cd=8
)
group by si.stock_id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4172 Card=1 Bytes=21)
1 0 SORT (GROUP BY NOSORT) (Cost=4172 Card=1 Bytes=21)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SO_ITEMS' (Cost=4172 Card=5910 Bytes=124110)
3 2 INDEX (RANGE SCAN) OF 'SO_ITEMS_STOCK_ID_I' (NON-UNIQUE) (Cost=845 Card=5910)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'SO' (Cost=3 Card=1 Bytes=19)
5 4 INDEX (UNIQUE SCAN) OF 'SO_PK' (UNIQUE) (Cost=2 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
76362 consistent gets
2363 physical reads
0 redo size
237 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
If I used the natual join syntax, then it will cost me for more time to query the sales quality.
Because then it used range scan for so table instead of unique scan.
I have try to run a month(about 6 weeks) sales quality for 100 stock_id,
I cannot see the result in 2 hours.
SQL> select si.stock_id, sum(si.qty) all_qty_tot
2 from so s, so_items si
3 where si.so_id=s.so_id
4 and si.stock_id='29003786'
5 and s.order_dt>=to_date('20090105','yyyymmdd')
6 and s.order_dt<=to_date('20090111','yyyymmdd')
7 and s.so_status_cd=8
8 group by si.stock_id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23356 Card=1 Bytes=4
0)
1 0 SORT (GROUP BY NOSORT) (Cost=23356 Card=1 Bytes=40)
2 1 NESTED LOOPS (Cost=23356 Card=2289 Bytes=91560)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'SO' (Cost=14280 Card
=2269 Bytes=43111)
4 3 INDEX (RANGE SCAN) OF 'SO_ORDER_DT_PAYMENT_WHS_ID_I'
(NON-UNIQUE) (Cost=157 Card=2269)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'SO_ITEMS' (Cost=4 Ca
rd=118188 Bytes=2481948)
6 5 INDEX (RANGE SCAN) OF 'SO_ITEMS_STOCK_ID_I' (NON-UNI
QUE) (Cost=3 Card=118188)
|
|
|
|
|
|
| Re: how to optimize sql for query big table? (merged 3) [message #397092 is a reply to message #397051] |
Wed, 08 April 2009 21:25 |
kevin_ye
Messages: 37
Registered: December 2007
Location: Shanghai,China
|
Member |
|
|
1 there are some indexes in where clause.
Table Index
---------------------------------------------------------------------------------------------
(1) so order: so, more than 6,000,000 records, including the following indexes:
Primary Key: SO_PK(so_id)
Normal index: SO_ORDER_DT_PAYMENT_WHS_ID_I(order_dt, payment_whs_id)
(2) so order detail: so_items, more than 20,000,000 records, including the following indexes:
Primary Key: SO_PK(so_id, so_item_order)
Normal index: SO_ITEMS_STOCK_ID_I(stock_id,so_id)
2 statistics current on all tables and some indexes.
|
|
|
|
Goto Forum:
Current Time: Wed Jun 26 11:47:44 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
