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 Go to next message
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 Go to previous messageGo to next message
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 #397051 is a reply to message #396879] Wed, 08 April 2009 13:34 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Are all the columns in the WHERE clauses indexed?
Are statistics current on both tables & all indexes?
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 Go to previous message
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.
Previous Topic: SQL Tuning Help
Next Topic: Report Taking So Much Of Time and goes into ORA-01555
Goto Forum:
  


Current Time: Sun Dec 04 12:31:58 CST 2016

Total time taken to generate the page: 0.10279 seconds