In the view object Tuning, in the frame called Retrieve from the database, I set
Only up to row number =10
I was expecting that only 10 rows would be retrieved from database by a filter like rownum<=10
but when I check the log (and database sql) I see that normal select is send to database:
[1214] SELECT Employees.EMPLOYEE_ID, Employees.DEPARTMENT_ID, Employees.FIRST_NAME, Employees.LAST_NAME, Employees.EMAIL, Employees.PHONE_NUMBER, Employees.HIRE_DATE, Employees.JOB_ID, Employees.SALARY, Employees.COMMISSION_PCT, Employees.MANAGER_ID, Departments.DEPARTMENT_NAME, Departments.DEPARTMENT_ID AS DEPARTMENT_ID1 FROM EMPLOYEES Employees, DEPARTMENTS Departments WHERE Employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID(+)
[1215] Bind params for ViewObject: EmployeesView1
[1216] DBTransactionImpl.mDefaultSparseArrayThreshold is 20
[1217] No Resource Bundle found but null value id:LOVUIHints_NullValueId is defined in an ListBinding Definition.
[1218] DCUtil, returning:oracle.adf.model.binding.DCParameter, for AppModuleDataControl
[1219] Changing iterator range size from :25 to :10
From what I know this select retrieves all rows from database and then somehow ADF shows only 10.
So wouldn't it be better performance for me to just put rownum<=10 in the where clause?
So wouldn't it be better performance for me to just put rownum<=10 in the where clause?
ADF only fetches the first 10 rows when you set this setting. It doesn't require adding an additional clause to limit the rows. When using our RangePaging mode, described in section "38.1.5 Efficiently Scrolling Through Large Result Sets Using Range Paging" of the 11.1.1.1 developer's guide, we do augment the query with a lower/upper bound to do what you're thinking, but it's not required to achieve the "first 10". If you want, you add a "FIRST_ROWS" query optimizer hint to the view object so that the database optimizer knows it should give priority to retrieving the first rows.
ReplyDelete