|Previous||Table of Contents||Next|
Oracles System Global Area ( SGA ) is essentially short-term memory for the database. As the database references data and stored PL/SQL objects, the database stores information in the SGA on the assumption that you will be referencing this information again.
Figure 2.6 illustrates an SGA composed of these four distinct areas:
Figure 2.6 The structure of Oracle's system global area.
When the database needs to add more information to the SGA and no more memory is available, the database removes the oldest data and objects from the SGA until there is sufficient free memory to hold the new data and objects.
Oracle also allows you to pin data and stored PL/SQL objects into the SGA. For instance, lookup tables are often very small; the contents of these tables can often be pinned entirely in the SGA to make accesses to the descriptions and codes contained in the table extremely fast. Objects that are pinned in the SGA are not removed from the SGA when Oracle needs to make room for new data and code.
TIP: Standardizing SQL And PL/SQL Code
Oracle allows you to pin data and stored PL/SQL objects into the SGA. To take maximum advantage of this ability, youll need to create some common stored PL/SQL objects for your system that you and other developers can reuse. Have the DBA modify the database startup script so that these common objects get pinned into the SGA when your database is started. By making frequent use of these objects, you can improve the performance of your applications.
In addition, you can improve your system performance by implementing and following a set of coding standards for your system. Appendix D contains a sample SQL and PL/SQL coding standard, which can be modified to suit the needs of your organization.
Structured Query Language (SQL) is a relatively young language compared to languages like COBOL and Fortran, and its quite different from those early ancestors.
SQL is a fourth-generation programming language (4GL), which means that it allows the user to describe the data he or she wants without giving precise instructions to the computer about how to retrieve the data. This was a remarkable idea when SQL was first introduced, and SQL remains the only 4GL in widespread use today. All of the major relational database systems use SQL for accessing data (although all of them, like Oracles SQL*Plus, include proprietary modifications to the language).
SQL statements come in two varieties: data definition language and data manipulation language.
Data definition language (DDL) is language that defines how data is stored within the relational database. A simple example of a DDL statement is shown in Listing 2.5.
Listing 2.5 A simple DDL statement.
CREATE TABLE STUDENTS AS (ssn NOT NULL number(9), first_name NOT NULL varchar2 (10), last_name NOT NULL varchar2 (12), middle_name varchar2 (10), street_address NOT NULL varchar2 (30), apartment_number varchar2 (4), city NOT NULL varchar2 (30), state NOT NULL varchar2 (2), zip_code NOT NULL number (5), home_phone NOT NULL number (10));
This statement creates a table within the Oracle database to hold information about students. Obviously, this is a very simple example. As you become a more advanced developer, youll learn more about DDL.
Data manipulation language (DML) is the most common type of SQL that youll encounter. In case you havent already figured it out by reading the name, this is the SQL that creates, deletes, reads, and modifies data. Listing 2.6 displays a simple DML statement.
Listing 2.6 A simple DML statement.
SELECT last_name || ', ' || first_name || ' ' || middle_name FROM STUDENTS WHERE ssn = 999999999;
This statement returns the name of the student whose social security number is 999-99-9999 from the STUDENTS table, in the format Doe, John Adam. In addition to querying data from tables using the SELECT statement, you can add data to tables with the INSERT statement, modify the data in tables with the UPDATE statement, and remove data from tables with the DELETE statement.
|Previous||Table of Contents||Next|