What are the Different Optimisation Techniques?

Question

Below is the one of the interview question which was held by Oracle

If a transaction is very large, and the rollback segment is not able to hold the rollback information, then will the transaction span across different rollback segments or will it terminate?

Answer 1

The Various Optimization techniques are

Execute Plan: we can see the plan of the query and change it accordingly based on the indexes

Optimizer hint: set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept
where (Deptno > 25)

Optimize_Sql: By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for
all SQL statements. This slow downs the processing because for evertime the SQL must
be parsed whenever they are executed.
F45run module = my_firstform userid = scott/tiger optimize_sql = No

Optimize_Tp: By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only
for each query SELECT statement. All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger optimize_Tp = No