Developing and Implementing Applications, concluded - Iterative Column Definitions (Page 5 of 7 )
During the development process, your column definitions may change frequently. You can drop columns from existing tables. You can drop a column immediately, or you can mark it as “unused,” to be dropped at a later time. If the column is dropped immediately, the action may impact performance. If the column is marked as unused, there will be no impact on performance. The column can actually be dropped at a later time when the database is less heavily used.
To drop a column, use either the set unused clause or the drop clause of the alter table command. You cannot drop a pseudocolumn, a column of a nested table, or a partition key column.
In the following example, column Col2 is dropped from a table named TABLE1:
alter table TABLE1 drop column Col2;
You can mark a column as unused, as shown here:
alter table TABLE1 set unused column Col3;
Marking a column as unused does not release the space previously used by the column. You can also drop any unused columns:
alter table TABLE1 drop unused columns;
You can query USER_UNUSED_COL_TABS, DBA_UNUSED_COL, and ALL_UNUSED_ COL_TABS to see all tables with columns marked as unused.
NOTE
Once you have marked a column as unused, you cannot access that column. If you export the table after designating a column as unused, the column will not be exported.
You can drop multiple columns in a single command, as shown in the following example:
alter table TABLE1 drop (Col4, Col5);
NOTE
When dropping multiple columns, you should not use the column keyword of the alter table command. The multiple column names must be enclosed in parentheses, as shown in the preceding example.
If the dropped columns are part of primary keys or unique constraints, you will also need to use the cascade constraints clause as part of your alter table command. If you drop a column that belongs to a primary key, Oracle will drop both the column and the primary key index.
If you cannot immediately arrange for a maintenance period during which you can drop the columns, mark them as unused. During a later maintenance period, you can complete the maintenance from the SYS or SYSTEM account.
Forcing Cursor Sharing
Ideally, application developers should use bind variables in their programs to maximize the reuse of their previously parsed commands in the shared SQL area. If bind variables are not in use, you may see many very similar statements in the library cache—queries that differ only in the literal value in the where clause.
Statements that are identical except for their literal value components are called similar statements. Similar statements can reuse previously parsed commands in the shared SQL area if the CURSOR_SHARING initialization parameter is set to SIMILAR or FORCE. In general, you should favor using SIMILAR over FORCE, because SIMILAR will allow for a new execution plan to be generated reflecting any histogram data known about the literal value.
Setting CURSOR_SHARING to EXACT (the default setting) reuses previously parsed commands only when the literal values are identical.
To use stored outlines with CURSOR_SHARING set to FORCE or SIMILAR, the outlines must have been generated with that CURSOR_SHARING setting in effect.
NOTE
Dynamic SQL commands are always parsed, essentially bypassing the value of the shared SQL area.
Managing Package Development
Imagine a development environment with the following characteristics:
- None of your standards are enforced.
- Objects are created under the SYS or SYSTEM account.
- Proper distribution and sizing of tables and indexes is only lightly considered.
- Every application is designed as if it were the only application you intend to run in your database.
As undesirable as these conditions are, they are occasionally encountered during the implementation of purchased packaged applications.
Properly managing the implementation of packages involves many of the same issues that were described for the application development processes in the previous sections. This section will provide an overview of how packages should be treated so they will best fit with your development environment.
Generating Diagrams
Most CASE tools have the ability to reverse engineer packages into a physical database diagram. Reverse engineering consists of analyzing the table structures and generating a physical database diagram that is consistent with those structures, usually by analyzing column names, constraints, and indexes to identify key columns. However, normally there is no one-to-one correlation between the physical database diagram and the entity relationship diagram. Entity relationship diagrams for packages can usually be obtained from the package vendor; they are helpful in planning interfaces to the package database.
Space Requirements
Most Oracle-based packages provide fairly accurate estimates of their database resource usage during production usage. However, they usually fail to take into account their usage requirements during data loads and software upgrades. You should carefully monitor the package’s undo requirements during large data loads. A spare DATA tablespace may be needed as well if the package creates copies of all its tables during upgrade operations.
Tuning Goals
Just as custom applications have tuning goals, packages must be held to tuning goals as well. Establishing and tracking these control values will help to identify areas of the package in need of tuning (see Chapters 8 and 9).
Next: Security Requirements >>
More Oracle Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter five of the book Oracle Database 10g DBA Handbook, written by Kevin Loney and Bob Bryla (McGraw-Hill/Osborne, 2005; ISBN: 0072231459). Buy this book now.
|
|
/ 6 
