Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Optimisation of a sql query
Abhijat wrote:
> Hi Sybrandb,
> You are right and I must confess, I am kind of novice in this field.
> I should have looked into the options. Here is my explanation to each
> of your query.
> Since I m working in SAS, I will provide you the answers with respect
> to it.
> 1 No version. How do you expect help
> Ans - SAS version 9.1
> 2 No explain plan. How do you expect help
> Ans. I don't know how do we get explain plan in SAS.
> 3 How did you establish there is a 'having issue'? Did you look in a
> crystal ball, or went to your work on a broom stick?
> Ans. I concluded to this issue because I had a rough idea that the
> execution of a sql query starts from the last mapping, hence, we should
> ideally give the biggest filter at the bottom moving upwards ! Please
> correct me if I m wrong here.
> 4 Did you check where your bottlenecks are located? Disk? Logwr?
> Database archiving like hell? If you didn't do any of those things,
> how do you expect anyone can help you out.
> Ans. The disk space is enough and so is the processor speed. In fact,
> its not in my hands to alter them as well. I have to work in the given
> disk space (which is enough) and processor speed.
>
> Please let me know if the provided suffices.
>
> Thanks and Regards,
> Abhijat.
>
Not really, no. We don't care which SAS version you're using, what Sybrand asked for (and you have yet to provide) is the Oracle release/version information, such as 9.2.0.6, 10.2.0.2, etc.
SAS has nothing, really, to do with this issue outside of how the schema objects were created. Also, your query is ... wrong ... in many ways, including using GROUP BY and HAVING without any aggregate values in your SELECT statement. This query should be rewritten using WHERE:
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 WHERE A.RSPNS_CDE = "00" AND A.TST_ACCT_CDE = "" AND A.AUTH_TRAN_CDE IN ("0110","0120") AND A.AUTH_PROC_1_CDE = "09";
GROUP BY and HAVING have no place in this query of yours; of course SELECT * isn't the most intelligent of choices for this query, either. Place the columns you want in the SELECT list explicitly to avoid ambiguous column errors and to prevent duplicate data in your record (NOVUS_MRCH_NBR contains the same data as NOVS_MRCH_NBR, so why have both in the same record?).
Explain plan is fairly straightforward:
explain plan
set statement_id = 'myid' for
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 WHERE A.RSPNS_CDE = "00" AND A.TST_ACCT_CDE = "" AND A.AUTH_TRAN_CDE IN ("0110","0120") AND A.AUTH_PROC_1_CDE = "09";
Presuming you're using 9i or later you would then:
select *
from table(dbms_xplan.display);
and output the results. When you generate your explain plan you'll see how the query executes. And what problems, if any, my be present.
David Fitzjarrell Received on Wed Jul 19 2006 - 13:04:19 CDT