Hi,

Following Outerjoin Query, when used RULE based, gives exact result, when COST base is used giving Inconsistant result.

SQL> desc IACLINK
Name Type Nullable Default Comments
----------------------- ---------- -------- ------- --------
IACLINK_INTERNAL_ACNUM NUMBER 14)
IACLINK_INTERNAL_BK_NUM NUMBER(2) Y
IACLINK_BRN_CODE NUMBER(6) Y
IACLINK_CIF_NUMBER NUMBER(7) Y
IACLINK_AC_SEQ_NUM NUMBER(5) Y

SQL> desc HOVERING_SODEOD
Name Type Nullable Default Comments
----------------------------- ------------ -------- ------- --------
HOVSODEOD_INTERNAL_BK_NUM NUMBER(2)
HOVSODEOD_BRN_CODE NUMBER(6)
HOVSODEOD_CR_INTERNAL_AC_NUM NUMBER(14)
HOVSODEOD_CR_CONTRACT_NO NUMBER(8)
HOVSODEOD_CR_GL VARCHAR2(15)
HOVSODEOD_ANY_OR_SPECIFIC_AC CHAR(1)
HOVSODEOD_DATE DATE
HOVSODEOD_INSTALL_SL NUMBER(6)
HOVSODEOD_DB_AC_SL NUMBER(2)
HOVSODEOD_DB_AC_BRN NUMBER(6) Y
HOVSODEOD_DB_AC_INT_AC_NUM NUMBER(14) Y
HOVSODEOD_DB_AC_PROD_CODE NUMBER(4) Y
HOVSODEOD_DB_AC_TYPE CHAR(6) Y
HOVSODEOD_DB_AC_SUB_TYPE NUMBER(3) Y
HOVSODEOD_DB_AC_IS_SALARY CHAR(1) Y
HOVSODEOD_DB_AC_CURR_CODE CHAR(3) Y
HOVSODEOD_DB_AC_TRAN_AMT NUMBER(18,3) Y
HOVSODEOD_CR_AC_CURR_CODE CHAR(3) Y
HOVSODEOD_CR_AC_TRAN_AMT NUMBER(18,3) Y
HOVSODEOD_SPECIFIC_NOTATION VARCHAR2(25) Y
HOVSODEOD_REC_TRANCODE CHAR(1) Y
HOVSODEOD_PARTIAL_REC_ALLOWED CHAR(1) Y
HOVSODEOD_START_DATE DATE Y
HOVSODEOD_END_DATE DATE Y
HOVSODEOD_REC_SOD_EOD CHAR(1) Y
HOVSODEOD_REC_MODULE CHAR(3) Y
HOVSODEOD_TYPE_OF_AMOUNT CHAR(1) Y
HOVSODEOD_PRIORITY_TYPE VARCHAR2(6) Y


SQL> SELECT COUNT(1) FROM hovering_sodeod;

COUNT(1)
----------
11

SQL> SELECT COUNT(1) FROM iaclink;

COUNT(1)
----------
20829


SQL> select /*+ Choose */ HOVSODEOD_CR_INTERNAL_AC_NUM,HOVSODEOD_DATE
from hovering_sodeod, iaclink
where HOVSODEOD_CR_INTERNAL_AC_NUM = IACLINK_INTERNAL_ACNUM(+)

HOVSODEOD_CR_INTERNAL_AC_NUM HOVSODEOD_DATE
---------------------------- --------------
1050200000263 13-03-2004
1050200000263 13-03-2004

2 rows selected

SQL> select /*+ RULE */ HOVSODEOD_CR_INTERNAL_AC_NUM,HOVSODEOD_DATE from hovering_sodeod, iaclink
where HOVSODEOD_CR_INTERNAL_AC_NUM = IACLINK_INTERNAL_ACNUM(+)

HOVSODEOD_CR_INTERNAL_AC_NUM HOVSODEOD_DATE
---------------------------- --------------
0 13-03-2004
0 13-03-2004
0 13-03-2004
0 13-03-2004
0 13-03-2004
0 13-03-2004
0 13-03-2004
0 13-03-2004
0 13-03-2004
1050200000263 13-03-2004
1050200000263 13-03-2004

11 rows selected

regards

daya/kums.