Abhijat wrote:
> Hi group,
> I have a simple create table query.
> CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
> SELECT *
> FROM rd_at2.AUTH_MAY06 A LEFT JOIN
> dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_V1 B
> ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
> GROUP BY B.MCC_CTGY_KEY, B.SLS_GRP_LVL_6_NM
> HAVING A.RSPNS_CDE = "00"
> AND A.TST_ACCT_CDE = ""
> AND A.AUTH_TRAN_CDE IN ("0110","0120")
> AND A.AUTH_PROC_1_CDE = "09";
>
> The data table AUTH_MAY06 is of size 25 GB and the other table is about
> 500MB!
> I have two questions regarding the performance tuning of this query.
> 1. What should be the order of having clause so that the processing
> time is decreased ?
> 2. Is there another way out (ex. using some options in oracle/sas)
> which would further decrease the processing speed (assuming we have
> fixed the "having" issue, if any)?
>
- What are you grouping/having? There is nothing to group: no sum,
average, count or anything like that.
- Are you table indexed? With the condition like
A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
I would expect both involved columns to have indexes.
- Are the tables analyzed? Do all the columns mentioned in the query
have histograms?
- Do you have an execution plan? Database will tell you "master, I have
a cunning plan". Then, you have to revise that plan and approve it.
It's known as the Baldrick mechanism. Cunning plans are visible in
V$SQL_PLAN table.
- Are you really, really sure that you need @#$%! ANSI syntax? Every
patchset installation notes mention bugs of @#$%! ANSI joins and CBO
combination. What are you trying to create? An application that will
suck equally on every platform? That's what "portable SQL" is for.
--
Mladen Gogala
http://www.mgogala.com
Received on Wed Jul 19 2006 - 11:35:04 CDT
