Try to avoid using following in triggers
1) UTL_ packages . They do not roll back
2) Pragma autonomous_transaction only use if it is unavoidable
Wrong cardinality = Wrong Plan
Check Constraints can rewrite queries
If you want to limit the creation of index however want to increase performance degredated query by refering explain plan . One main point is that you want to check the the highest cardinality which is having FULL TABLE ACCESS in the explain plan , for that particular cardinality check the access predicates in the explain plan and try to create index for those columns in the join (access predicates)
Creating index will help in the performance of a query even though the join is right or left outer join .
Dimensions convey information to the optimizer.The presence of Dimensions open up access paths that would not be otherwise available.
The Forms' PL/SQL code runs in the Forms PL/SQL engine, which is different in PL/SQL engine in database , this is why you cannot access database package's variable, which result in the following error
Implementation Restriction: 'Cannot directly access remote package variable or cursor while compiling oracle forms