Re: "ORA-00905: missing keyword" when tuning the sql statement using join

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 06 Oct 2005 21:29:45 +0200
Message-ID: <di3tv6$kn5$01$1@news.t-online.com>

jrefactors_at_hotmail.com schrieb:
> The following query yields the correct result, but the performance
> is extremely poor. When I run the explain plan in TOAD, it costs 167560
> and it performs the full table scan on (SELECT MAX ...) statement.
>
> I try to tune the sql statement using join, but I got the error
> "ORA-00905: missing keyword" on clause "AND p.company_seq_no =
> c.company_seq_no".
>
> I have no idea what's going on, please advise what is the best approach
> to tune the original SQL.
>
> ORIGINAL SQL
> ============
> SELECT p.finance_no, vda.finance_no, vrf_type
> FROM p_permit p,
> p_company c,
> p_vrf_details vda
> WHERE p.permit_seq_no = '1001'
> AND p.company_seq_no = c.company_seq_no
> AND c.ref_id = vda.ref_id
> AND p.finance_no = vda.finance_no
> AND vda.vrf_type IN ('M','P')
> AND vda.activity_date = (SELECT MAX(activity_date)
> FROM p_vrf_details vdb
> WHERE ref_id = vda.ref_id
> AND finance_no = vda.finance_no
> AND vrf_type = vda.vrf_type
> AND vrf_level = vda.vrf_level
> AND pstg_stmt_seq_no is not null
> )
>
> UPDATED SQL
> ===========
> SELECT p.finance_no, vda.finance_no, vrf_type
> FROM p_permit p,
> p_company c,
> p_vrf_details vda
> JOIN p p_vrf_details vdb
> ON p.permit_seq_no = '1001'
> AND p.company_seq_no = c.company_seq_no
> AND c.ref_id = vda.ref_id
> AND p.finance_no = vda.finance_no
> AND vda.vrf_type IN ('M','P')
> AND vda.activity_date = MAX(vdb.activity_date)
> AND vda.finance_no = vdb.finance_no
> AND vdb.vrf_level = vda.vrf_level
> AND vdb.pstg_stmt_seq_no is not null
>
>
> Please advise. Thanks a lot!!
>

You have to look up on the JOIN syntax in the Oracle documentation, your updated SQL is wrong on many places i.e. 1) you have to join by tableA JOIN tableB ON (...) JOIN tableC ON (...) JOIN tableD ON (...)
2)in the line JOIN *p* p_vrf_details vdb the *p* is most likely a typo 3)in the line AND vda.activity_date = MAX(vdb.activity_date) you are not allowed to use aggregate function.

You would more likely become help regarding the performance if you post the ddl of your tables as well as explain plan of your original sql. You should also mention whether the statistics are collected and are actual Last but not least, the Oracle and OS version should be mentioned as well.

Just guessing - you are on at least 9i ( because trying to use ANSI syntax ) , and , maybe it is a bad guess because i don't know your explain plan ,table structure and statistics, you could however try the following:

WITH t AS (SELECT MAX(activity_date) activity_date,

            ref_id,
            finance_no,
            vrf_type,
            vrf_level
            FROM p_vrf_details
            WHERE pstg_stmt_seq_no IS NOT NULL
            GROUP BY ref_id, finance_no, vrf_type, vrf_level)

SELECT p.finance_no, vda.finance_no, vda.vrf_type

   FROM p_permit  p,
        p_company  c,
        p_vrf_details  vda,
        t
  WHERE p.permit_seq_no = '1001'
    AND p.company_seq_no = c.company_seq_no

    AND c.ref_id = vda.ref_id
    AND p.finance_no = vda.finance_no

    AND vda.vrf_type IN ('M','P')
    AND vda.activity_date=t.activity_date
    AND vda.ref_id = t.ref_id
    AND vda.finance_no = t.finance_no
    AND vda.vrf_type = t.vrf_type
    AND vda.vrf_level = t.vrf_level

;

Best regards

Maxim Received on Thu Oct 06 2005 - 14:29:45 CDT