SOME USEFUL INFORMATION (PERFORMANCE TUNING)

Some Useful Information (Performance Tuning)

1)What is the difference between truncate and delete

2)if the synonym is exists for the table , if we re-create the table the existing synonym will work?

3)select count(1) from dual will it goes to NO_DATA_FOUND exception when data would not match the criteria.

4)union all and union which one is better

5) INSERT INTO /*+ APPEND PARALLEL(AEXP_FCH_BALANCES_FACT_TEMP1,DEFAULT) */ aexp_fch_balances_fact_temp1

6) SELECT /*+ ALL_ROWS NO_MERGE */ from table_name

7)while inserting the data into one table into another table Better mention column list

8)fnd_request.submit_request It will kicked off with the sysadmin user, instead of sysadmin we want the program kicked off user name.

9)Gather stats table schema (table name) available in sysadmin responsibility

10) UPDATE /*+ INDEX(FACT AEXP_FCH_BALANCES_FACT_IND25) */ AEXP_FCH_BALANCES_FACT FACT SET user_dim6_id = l_geo_id, user_dim5_id = l_lob_id, user_dim7_id = l_seg_id WHERE user_dim1_id = rec_geo_lob.user_dim1_id --and enabled_flag = 'Y' -- AND report_to_use_flag = 'Y' and category_code = rec_category_dim.category_code and cal_period_id = p_cal_period_id and entity_cons_id = p_hierarchy_id;

11) How to analyze the tables Analyze table schem_name.table_name compute STATISTICS;