471,075 Members | 910 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

How to improve a simple SQL performance

Hi Dear All,

I have a large query as below:
select count (distinct b.bus_acct_id) from
M_DATE M1,
M_BIZ_ACCT M2,
BIZ_ACCT B,
C_PRDT_PKG C
where
M1.month_id = M2.month_id
and M2.CDS_PROD_PKG_ID = c.CDS_PROD_PKG_ID
and M2.Bus_Acct_Id = B.Bus_Acct_Id

There are 4 tables , M_DATE , has 100 rows, M1 stands for it,
M_BIZ_ACCT, Has 2.5 Million rows, M2 stands for it,
BIZ_ACCT, Has 1 Million rows, B stands for it,
C_PRDT_PKG , has 20 rows, C atands for it.

This SQL take about 10-15 minutes, it is Oracle 8.1.7.4.0,

Is there anyway to improve performance by change
the SQL I used, such as change order of tables list,

because 2 of the them are small, the other 2 are very large,
Thanks
Jul 19 '05 #1
2 4791
"ALex_1998" <mi******@yahoo.com> wrote in message
news:4b**************************@posting.google.c om...
Hi Dear All,

I have a large query as below:
select count (distinct b.bus_acct_id) from
M_DATE M1,
M_BIZ_ACCT M2,
BIZ_ACCT B,
C_PRDT_PKG C
where
M1.month_id = M2.month_id
and M2.CDS_PROD_PKG_ID = c.CDS_PROD_PKG_ID
and M2.Bus_Acct_Id = B.Bus_Acct_Id

There are 4 tables , M_DATE , has 100 rows, M1 stands for it,
M_BIZ_ACCT, Has 2.5 Million rows, M2 stands for it,
BIZ_ACCT, Has 1 Million rows, B stands for it,
C_PRDT_PKG , has 20 rows, C atands for it.

This SQL take about 10-15 minutes, it is Oracle 8.1.7.4.0,

Is there anyway to improve performance by change
the SQL I used, such as change order of tables list,

because 2 of the them are small, the other 2 are very large,
Thanks


What is the explain plan, what is the tkprof output , are the tables
analyzed, what indexes do you have?
Jim
Jul 19 '05 #2
mi******@yahoo.com (ALex_1998) wrote in message news:<4b**************************@posting.google. com>...
Hi Dear All,

I have a large query as below:
select count (distinct b.bus_acct_id) from
M_DATE M1,
M_BIZ_ACCT M2,
BIZ_ACCT B,
C_PRDT_PKG C
where
M1.month_id = M2.month_id
and M2.CDS_PROD_PKG_ID = c.CDS_PROD_PKG_ID
and M2.Bus_Acct_Id = B.Bus_Acct_Id

There are 4 tables , M_DATE , has 100 rows, M1 stands for it,
M_BIZ_ACCT, Has 2.5 Million rows, M2 stands for it,
BIZ_ACCT, Has 1 Million rows, B stands for it,
C_PRDT_PKG , has 20 rows, C atands for it.

This SQL take about 10-15 minutes, it is Oracle 8.1.7.4.0,

Is there anyway to improve performance by change
the SQL I used, such as change order of tables list,

Run explain plan for the query. Make sure that m_biz_acct is a
driving table and Oracle uses full table scan on it. That is,
if you don't have an index which includes all three fields
mentioned in the query. If such an index exists, full index
scan on it is the best solution.
Actually, cost based optimized should've figured it all out,
if the tables and indexes are analyzed.
You may also try to increase hash area size for the session,
use full table scan on both big tables and use hash join
to join them.
because 2 of the them are small, the other 2 are very large,
Thanks

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Peng Jian | last post: by
1 post views Thread by Lakesider | last post: by
6 posts views Thread by Jéjé | last post: by
1 post views Thread by Oberfuhrer | last post: by
5 posts views Thread by Gilles Ganault | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.