Re: How to improve a simple SQL performance
Date: 3 Oct 2003 07:45:22 -0700
Message-ID: <336da121.0310030645.1533ad98_at_posting.google.com>
mingx_98_at_yahoo.com (ALex_1998) 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,
>
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
Received on Fri Oct 03 2003 - 16:45:22 CEST