How to improve a simple SQL performance

[ALex_1998]

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

[Jim Kennedy]

"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

[Alex Filonov]

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