SQL & PL/SQL

Hai all,

Am running a query :


sql advisor is advising on the following :

findings :
An expensive cartesian product operation was found at line ID 2 of the execution plan.
recommendations :

Consider removing the disconnected table or view from this statement or add a join condition which refers to it.
Rationale:
A cartesian product should be avoided whenever possible because it is an expensive operation and might produce a large amount of data.

query :

SELECT   REPLACE (wm_concat (rec), ',', '|')
  FROM   (SELECT      A.medno
                   || '|'
                   || A.APPLIED
                   || '|'
                   || A.FIRST_NAME
                   || '|'
                   || A.SECOND_NAME
                   || '|'
                   || A.THIRD_NAME
                   || '|'
                   || A.LAST_NAME
		   || '|'
                   || A.STREET
		   || '|'
                   || A.BUILDING
		   || '|'
                   || A.PO_BOX
		   || '|'
                   || C.CC_LIMIT
		   || '|'
                   || D.mednot
		   || '|'
                   || B.APPDATE
		   || '|'
                   || A.PHONE_HOME
		   || '|'
                   || A.PHONE_WORK
		   || '|'
                   || E.CARDNO
		   || '|'
                   || A.CATEGORY
		   || '|'
                   || D.PREPOST_PAID
		   || '|'
                   || A.BIRTH_DATE_HIJ
		   || '|'
                   || (SELECT APPDATE FROM med_ACTION_HISTORY WHERE SOTYPE IN ('50','55') AND mednot =D.mednot)
		   || '|'
                   || G.UNBILLED
		   || '|'
                   || G.PAST_DUE
		   || '|'
                   || A.BILLGROUP
		   || '|'
                   || (SELECT ORDERBY FROM med_ACTION_HISTORY WHERE SOTYPE =10 AND mednot =D.mednot)
		   || '|'
                   || D.STATUS
		   || '|'
                   || C.ID_NO,H.POINTS_BAL
		   || '|'
                   || G.TOTAL
		    AS rec
           FROM  
med_DEPARTEMENT A, med_ACTION_HISTORY B, CCM_test_INFO C, med_test_INFO D, med_COMMDEVICE E, med_test_EQUIPMENTS F, MED_BALANCE G,medst.subs_t H WHERE
A.medno=B.medno AND
B.medno=C.PARENT_ENTITY AND
C.PARENT_ENTITY=D.medno AND
D.medno=G.medno AND
B.mednot=C.test_CODE AND
C.test_CODE=D.mednot AND
D.mednot=E.mednot AND
D.CARDNO=E.CARDNO AND
D.mednot=F.mednot AND
D.mednot=G.mednot)

explain plan :

SELECT STATEMENT
SORT AGGREGATE
MERGE JOIN CARTESIAN
MERGE JOIN
SORT JOIN
NESTED LOOPS
NESTED LOOPS
MERGE JOIN
SORT JOIN
MERGE JOIN
SORT JOIN
MERGE JOIN
SORT JOIN
INDEX FAST FULL SCAN
SORT JOIN
TABLE ACCESS FULL
SORT JOIN
TABLE ACCESS FULL
SORT JOIN
TABLE ACCESS FULL
TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN
INDEX UNIQUE SCAN
SORT JOIN
VIEW
HASH GROUP BY
VIEW
UNION-ALL
INDEX FAST FULL SCAN
FILTER
HASH GROUP BY
TABLE ACCESS BY INDEX ROWID
NESTED LOOPS
TABLE ACCESS FULL
INDEX RANGE SCAN
INDEX FAST FULL SCAN
TABLE ACCESS BY INDEX ROWID
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX FULL SCAN
BUFFER SORT
REMOTE

Ka