performance on rownum and inline view

From: lsllcm <lsllcm_at_gmail.com>
Date: 3 Apr 2006 02:20:23 -0700
Message-ID: <1144056023.931882.268490@i40g2000cwc.googlegroups.com>

Hi All,

I have one question about performance on rownum and inline view.

The following are my query.

SELECT * FROM ( SELECT

		B1PERMIT.B1_PER_ID1,
		B1PERMIT.B1_PER_ID2,
		B1PERMIT.B1_PER_ID3
			 , B1PERMIT.B1_SPECIAL_TEXT AS PROJECTNAME ,B1PERMIT.B1_ALT_ID ,
B1PERMIT.B1_PER_GROUP, B1PERMIT.B1_PER_TYPE, B1PERMIT.B1_PER_SUB_TYPE,
B1PERMIT.B1_PER_CATEGORY , B1PERMIT.B1_APPL_STATUS ,

B3OWNERS.B1_OWNER_FULL_NAME , BWORKDES.B1_WORK_DESC , B1PERMIT.B1_FILE_DD , B3ADDRES.B1_HSE_NBR_START,B3ADDRES.B1_HSE_NBR_END , B3ADDRES.B1_STR_DIR , B3ADDRES.B1_STR_NAME , B3ADDRES.B1_STR_SUFFIX ,

B3ADDRES.B1_UNIT_TYPE , B3ADDRES.B1_UNIT_START,B3ADDRES.B1_UNIT_END ,
B3ADDRES.B1_SITUS_CITY , B3ADDRES.B1_SITUS_STATE ,
B3ADDRES.B1_SITUS_ZIP , B3PARCEL.B1_PARCEL_NBR ,

B3CONTACT.B1_CONTACT_TYPE , B3CONTACT.B1_FNAME , B3CONTACT.B1_LNAME , B3CONTACT.B1_BUSINESS_NAME , B3CONTRA.B1_LICENSE_NBR , B3CONTRA.B1_LICENSE_TYPE , B3CONTRA.B1_CAE_FNAME , B3CONTRA.B1_CAE_LNAME , B3CONTRA.B1_BUS_NAME , BSTRUCTURE.B1_STRUCTURE_TYPE , BSTRUCTURE.B1_STRUCTURE_NAME , BSTRUCTURE.B1_STRUCTURE_STATUS , BSTRUCTURE.B1_STRUCTURE_STATUS_DATE ,

B1_SEC_TWN_RNG.B1_SECTION, B1_SEC_TWN_RNG.B1_TOWNSHIP,
B1_SEC_TWN_RNG.B1_RANGE, B1_SEC_TWN_RNG.PLSS_TYPE,
B1_SEC_TWN_RNG.ENTITY_CODE
	FROM
	   B1PERMIT,
		  B3CONTRA,
	     B3OWNERS,
	         B3ADDRES,
	      BCHCKBOX,
	      B3PARCEL,
	      BWORKDES,
	      BSTRUCTURE,
	      B3CONTACT,
	      B1_SEC_TWN_RNG
	WHERE	B1PERMIT.SERV_PROV_CODE = 'NOLA'
		AND		B1PERMIT.REC_STATUS = 'A'
		AND  	B1PERMIT.SERV_PROV_CODE = B3CONTRA.SERV_PROV_CODE(+)
		AND  	B1PERMIT.B1_PER_ID1 = B3CONTRA.B1_PER_ID1(+)
		AND  	B1PERMIT.B1_PER_ID2 = B3CONTRA.B1_PER_ID2(+)
		AND  	B1PERMIT.B1_PER_ID3 = B3CONTRA.B1_PER_ID3(+)
		AND  	B1PERMIT.SERV_PROV_CODE =  B3OWNERS.SERV_PROV_CODE(+)
		AND  	B1PERMIT.B1_PER_ID1 =  B3OWNERS.B1_PER_ID1(+)
		AND  	B1PERMIT.B1_PER_ID2 =  B3OWNERS.B1_PER_ID2(+)
		AND  	B1PERMIT.B1_PER_ID3 =  B3OWNERS.B1_PER_ID3(+)
		AND  	B1PERMIT.SERV_PROV_CODE = B3ADDRES.SERV_PROV_CODE(+)
		AND  	B1PERMIT.B1_PER_ID1 = B3ADDRES.B1_PER_ID1(+)
		AND  	B1PERMIT.B1_PER_ID2 = B3ADDRES.B1_PER_ID2(+)
		AND  	B1PERMIT.B1_PER_ID3 = B3ADDRES.B1_PER_ID3(+)
		AND  	B1PERMIT.SERV_PROV_CODE = BCHCKBOX.SERV_PROV_CODE(+)
		AND  	B1PERMIT.B1_PER_ID1 = BCHCKBOX.B1_PER_ID1(+)
		AND  	B1PERMIT.B1_PER_ID2 = BCHCKBOX.B1_PER_ID2(+)
		AND  	B1PERMIT.B1_PER_ID3 = BCHCKBOX.B1_PER_ID3(+)
		AND  	B1PERMIT.SERV_PROV_CODE = B3PARCEL.SERV_PROV_CODE(+)
		AND  	B1PERMIT.B1_PER_ID1 = B3PARCEL.B1_PER_ID1(+)
		AND  	B1PERMIT.B1_PER_ID2 = B3PARCEL.B1_PER_ID2(+)
		AND  	B1PERMIT.B1_PER_ID3 = B3PARCEL.B1_PER_ID3(+)
		AND  	B1PERMIT.SERV_PROV_CODE = BWORKDES.SERV_PROV_CODE(+)
		AND  	B1PERMIT.B1_PER_ID1 = BWORKDES.B1_PER_ID1(+)
		AND  	B1PERMIT.B1_PER_ID2 = BWORKDES.B1_PER_ID2(+)
		AND  	B1PERMIT.B1_PER_ID3 = BWORKDES.B1_PER_ID3(+)
		AND  	B1PERMIT.SERV_PROV_CODE = BSTRUCTURE.SERV_PROV_CODE(+)
		AND  	B1PERMIT.B1_PER_ID1 = BSTRUCTURE.B1_PER_ID1(+)
		AND  	B1PERMIT.B1_PER_ID2 = BSTRUCTURE.B1_PER_ID2(+)
		AND  	B1PERMIT.B1_PER_ID3 = BSTRUCTURE.B1_PER_ID3(+)
		AND  	B1PERMIT.SERV_PROV_CODE = B3CONTACT.SERV_PROV_CODE(+)
		AND  	B1PERMIT.B1_PER_ID1 = B3CONTACT.B1_PER_ID1(+)
		AND  	B1PERMIT.B1_PER_ID2 = B3CONTACT.B1_PER_ID2(+)
		AND  	B1PERMIT.B1_PER_ID3 = B3CONTACT.B1_PER_ID3(+)
		AND  	B1PERMIT.SERV_PROV_CODE = B1_SEC_TWN_RNG.SERV_PROV_CODE(+)
		AND  	B1PERMIT.B1_PER_ID1 = B1_SEC_TWN_RNG.B1_PER_ID1(+)
		AND  	B1PERMIT.B1_PER_ID2 = B1_SEC_TWN_RNG.B1_PER_ID2(+)
		AND  	B1PERMIT.B1_PER_ID3 = B1_SEC_TWN_RNG.B1_PER_ID3(+)
  	AND 	SUBSTR(B1PERMIT.B1_MODULE_NAME,1) != '#'
--  	AND B3CONTACT.B1_FNAME like 'A%'
		ORDER BY B1PERMIT.B1_FILE_DD DESC
	)
	WHERE ROWNUM<101

The query result has duplicate rows. But if I use distinct in inline view, the optimizer will not merge the rownum and inline view as whole to run.

Anyone can help me on this one. I want to the query returns 100 distinct rows.

Thanks in advance.

Thanks
Jacky Received on Mon Apr 03 2006 - 04:20:23 CDT