Re: How to improve a simple SQL performance

"ALex_1998" <mingx_98_at_yahoo.com> wrote in message news:4b149eee.0310020624.5d6cb921_at_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,
>
> 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 Received on Thu Oct 02 2003 - 16:33:13 CEST