Re: kkqjpdpvpd: No join pred found

On Sunday, February 28, 2016 at 10:51:35 PM UTC-8, mfw..._at_gmail.com wrote:
> I have the following SQL statement:-
>
> SELECT v02.start_date,
> SUM(ELAPSED_TIME) AS create_tm,
> COUNT(DISTINCT v01.REF_ID) AS create_app_cnt,
> tbl_A.STEP_OWNER,
> tblStaff.STAFF_NAME,
> tblStaff.STAFF_CODE
> FROM VIEW_01 v01
> LEFT JOIN VIEW_02 v02
> ON v02.ref_id = v01.ref_id
> LEFT JOIN
> (SELECT ref_id, STEP_OWNER FROM
> ( SELECT ref_id, STEP_OWNER,
> ROW_NUMBER() over (partition BY REF_ID order by step_name DESC,
> start_date ASC nulls last) AS ROW_NO
> FROM v03
> WHERE STEP_NAME IN ('001','003')
> )
> WHERE row_no=1
> ) tbl_A
> ON tbl_A.ref_id=v02.ref_id <<--- LINE 01
> LEFT JOIN code_staff tblStaff
> ON tblStaff.staff_id = tbl_A.step_owner
> WHERE v01.STEP_NAME IN ('001','003')
> GROUP BY v02.start_date,
> tbl_A.STEP_OWNER,
> tblStaff.STAFF_NAME,
> tblStaff.STAFF_CODE;
>
> Once I run it against the SQLDeveloper it prompts :-
>
> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> ORA-00600: internal error code, arguments: [kkqjpdpvpd: No join pred found.], [], [], [], [], [], [], [], [], [], [], []
> 00600. 00000 - "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
> *Cause: This is the generic internal error number for Oracle program
> exceptions. It indicates that a process has encountered a low-level,
> unexpected condition. The first argument is the internal message
> number. This argument and the database version number are critical in
> identifying the root cause and the potential impact to your system.
> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
>
> But if I change the statement in LINE 01 (above) to the following it can runs properly "ON tbl_A.ref_id=v01.ref_id"
>
> May I know why?
>
> Thanks
> Woo

The ora-600 tool on MOS shows several bugs related to this, and they seem to have fixes in patch sets. Are you using an unpatched base release?

If you don't have support, get it. You might also try a NO_PUSH_PRED on either view, that works around at least one of the bugs.

jg

-- 
_at_home.com is bogus.
http://www.databasejournal.com/features/oracle/the-one-security-feature-in-oracle-you-probably-shouldnt-use.html