Introduction To Oracle Server

Oracle SQL

CONTENTS INTRODUCTION TO ORACLE SERVER .............................................................................................. 3 ORACLE SERVER CONCEPTS .................................................................................................................. 4 Topics ..................................................................................................................................................... 4 Oracle Server Features .......................................................................................................................... 5 Data Access ............................................................................................................................................ 6 The Oracle Server................................................................................................................................... 9 The Database Structures ........................................................................................................................ 9 The Data Dictionary............................................................................................................................. 13 An Oracle Instance ............................................................................................................................... 13 Communications Software and Net8 .................................................................................................... 13 Oracle Memory Structures ................................................................................................................... 14 Processes .............................................................................................................................................. 16 Introduction to Rollback Segments....................................................................................................... 20 Data Concurrency and Consistency ..................................................................................................... 21 Transactions and Data Concurrency.................................................................................................... 23 Database Security................................................................................................................................. 25 Database Backup and Recovery ........................................................................................................... 26 Introduction to Oracle9i Data Guard................................................................................................... 28 Replication............................................................................................................................................ 30 Materialized Views ............................................................................................................................... 30 The Object-Relational Model for Database Management.................................................................... 31 Introduction to Partitioning.................................................................................................................. 32 Review Questions.................................................................................................................................. 33 SQL.............................................................................................................................................................. 35 INTRODUCTORY CONCEPTS .................................................................................................................... 36 Topics ................................................................................................................................................... 36 Elements of Oracle8i SQL .................................................................................................................... 37 Datatypes.............................................................................................................................................. 39 User-Defined Types .............................................................................................................................. 43 Nulls ..................................................................................................................................................... 43 Pseudocolumns..................................................................................................................................... 44 CREATING, ALTERING, AND DROPPING TABLES .................................................................................... 48 Topics ................................................................................................................................................... 48 Creating a Table : CREATE TABLE Command................................................................................... 49 Constraints in CREATE TABLE ........................................................................................................... 49 Dropping a Table: DROP TABLE........................................................................................................ 53 Altering a table..................................................................................................................................... 54 Sample Tables....................................................................................................................................... 56 Review Questions.................................................................................................................................. 57 Exercises............................................................................................................................................... 57 INDEXING, CLUSTERING, AND SEQUENCES ............................................................................................. 59 Topics ................................................................................................................................................... 59 Indexes.................................................................................................................................................. 60 Reverse Key Indexes ............................................................................................................................. 62 Bitmap Indexes ..................................................................................................................................... 62 Index-Organized Tables ....................................................................................................................... 64 Clusters................................................................................................................................................. 66 Sequences ............................................................................................................................................. 70 Review Questions.................................................................................................................................. 72 Exercises............................................................................................................................................... 73

Jayashree S

Page 1 of 127

Oracle SQL

CHANGING DATA : INSERTING, UPDATING AND DELETING DATA......................................................... 74 Topics ................................................................................................................................................... 74 The INSERT Command......................................................................................................................... 75 The UPDATE Command ...................................................................................................................... 76 The DELETE Command ....................................................................................................................... 77 Exercises............................................................................................................................................... 77 QUERYING TABLES .................................................................................................................................. 78 Topics ................................................................................................................................................... 78 The SELECT statement......................................................................................................................... 79 WHERE Clause .................................................................................................................................... 79 SQL Functions...................................................................................................................................... 81 ORDER BY Clause ............................................................................................................................... 88 GROUP BY Clause............................................................................................................................... 88 Querying Multiple Tables : Joins ......................................................................................................... 89 SET Operators ...................................................................................................................................... 90 Nested Queries ..................................................................................................................................... 91 Using Special Operators in Sub queries............................................................................................... 91 Exercises............................................................................................................................................... 93 VIEWS ....................................................................................................................................................... 96 Topics ................................................................................................................................................... 96 What is a View? .................................................................................................................................... 97 How Views are Used ............................................................................................................................ 97 Creating Views ..................................................................................................................................... 98 Creating Views with Errors................................................................................................................ 101 Replacing Views ................................................................................................................................. 101 Using Views ........................................................................................................................................ 102 Modifying a Join View........................................................................................................................ 103 Partition Views ................................................................................................................................... 106 The ALTER VIEW Command ............................................................................................................. 107 Dropping a View................................................................................................................................. 108 Review Questions................................................................................................................................ 108 Exercises............................................................................................................................................. 108 CONTROLLING TRANSACTIONS............................................................................................................. 109 Topics ................................................................................................................................................. 109 What are Transaction Control Commands?....................................................................................... 110 The COMMIT Command .................................................................................................................... 110 The ROLLBACK Command................................................................................................................ 110 The SET TRANSACTION Command .................................................................................................. 111 The Auto Commit Feature .................................................................................................................. 112 Review Questions................................................................................................................................ 113 SQL TUNING...................................................................................................................................... 114 Approaches to SQL Statement Tuning................................................................................................ 114 Using Indexes ..................................................................................................................................... 117 Keeping Shared SQL and PL/SQL in the Shared Pool....................................................................... 121 Using EXPLAIN PLAN....................................................................................................................... 123 Creating the Output Table.................................................................................................................. 123 How to Run EXPLAIN PLAN ............................................................................................................. 124 Bibliography....................................................................................................................................... 127

Jayashree S

Page 2 of 127

Oracle SQL

INTRODUCTION TO ORACLE SERVER

Oracle Server Concepts

Jayashree S

Page 3 of 127

Oracle SQL

ORACLE Server Concepts Topics • • • • • • • • • • • • •

Oracle Server Features Data Access The Oracle Server The Database Structures The Data Dictionary An Oracle Instance Communications Software and Net8 Oracle Memory Structures Processes Data Concurrency and Consistency Database Security Database Backup and Recovery The Object-Relational Model for Database Management

Jayashree S

Page 4 of 127

Oracle SQL

The Oracle8i Server is an Object-Relational Database Management System (ORDBMS) designed to allow simultaneous access into large amounts of stored information. Oracle Server Features •

Client / Server (Distributed Processing) environments: Oracle allows processing to be split between the database server and the client application programs. The computer running the DBMS handles all of the database server responsibilities while the workstations running the application concentrate on the interpretation and display of data.

•

Large databases and Space management: Oracle supports the largest of the databases, potentially hundreds of gigabytes in size. To make efficient use of expensive hardware devices, it allows full control of space usage.

•

Many concurrent database users: Oracle supports large number of concurrent users executing a variety of database applications operating on the same data. It minimizes data contention and guarantees data concurrency.

•

High transaction processing performance: Oracle maintains the preceding features with a high degree of overall system performance.

•

High availability of Oracle database: At some sites, Oracle works 24 hours per day with no downtime to limit database throughput. Normal system operations such as database backup and partial computer system failures do not interrupt database use.

•

Controlled availability of data: Oracle can selectively control the availability of data, at the database level and sub-database level. For example, an administrator can disallow use of a specific application so that the application’s data can be reloaded, without affecting other applications.

•

Openness, industry standards: Oracle adheres to industry accepted standards for the data access language, operating systems, user interfaces, and network communication protocols.

•

Manageable security: To protect against unauthorized database access and use, Oracle provides fail-safe security features to limit and monitor data access.

•

Database enforced integrity: Oracle enforces data integrity, “business rules” that dictate the standards for acceptable data. As a result, the costs of coding and managing checks in many database applications are eliminated.

Jayashree S

Page 5 of 127

Oracle SQL

•

Distributed database systems: For networked, distributed environments, Oracle combines the data physically located on different computers into one logical database that can be accessed by all network users. Distributed systems have the same degree of user transparency and data consistency as non-distributed systems, yet receive the advantages of local database management.

•

Portability: Oracle software is ported to work under different operating systems and is the same on all systems. Applications developed for Oracle can be ported to any operating system with little or no modification.

•

Compatibility: Oracle software is compatible with industry standards, including most industry standard operating systems. Applications developed for Oracle can be used on virtually any system with little or no modification.

•

Connectibility: Oracle software allows different types of computers and operating systems to share information across networks.

•

Replicated Environments: Oracle Software lets you replicate groups of tables and their supporting objects to multiple sites. Oracle supports replication of both data- and schema-level changes to these sites.

Data Access

Applications Tools SQL Oracle

Data O/S Server SQL*PLUS PL/SQL

Jayashree S

Page 6 of 127

Oracle SQL

Jayashree S

Page 7 of 127

Oracle SQL

SQL • Structured Query Language • Non-procedural language - Processes sets of records rather than just one at a time - Provides automatic navigation to the data • Unified language: provides commands for-- querying data (DML) - inserting, updating and deleting rows in a table (DML) - creating, replacing, altering and dropping objects (DDL) - controlling access to the database and its objects (DCL) - guaranteeing database consistency and integrity • Common language for all relational databases Embedded SQL • Refers to the use of standard SQL commands embedded within a procedural language • Embedded SQL is supported by the Oracle pre-compilers • The Oracle pre-compilers interpret embedded SQL statements and translate them into statements that can be understood by procedural language compilers • Pre-compilers - Pro*C/C++ - Pro*COBOL - Pro*FORTRAN - Pro*PASCAL - Pro*PL/I SQL*PLUS • SQL*PLUS can be used in conjunction with the SQL database language and its procedural language extensions, PL/SQL • SQL*PLUS allows you to - enter, edit, store, retrieve and run SQL commands and PL/SQL blocks - format, perform calculations on, store, and print query results in the form of reports - list column definitions for any table - access and copy data between SQL databases - send messages to and accept responses from an end user Oracle Tools • Allow users to develop applications that automatically run on a variety of operating environments and automatically take on the native look-and-feel of that operating environment. • Oracle Tools include : - Oracle Forms - Oracle Reports - Oracle Graphics

Jayashree S

Page 8 of 127

Oracle SQL

PL/SQL • PL/SQL is Oracle Corporation's procedural language extension to SQL, the standard data access language for relational databases. • With PL/SQL, you can use SQL statements to manipulate Oracle data and flow-ofcontrol statements to process the data.. • You can also declare constants and variables, define procedures and functions, and trap runtime errors. • Thus, PL/SQL combines the data manipulating power of SQL with the data processing power of procedural languages. The Oracle Server •

Oracle Server consists of - Oracle Database (the information) - Oracle Instance (the embodiment of the system)

The Database Structures The Oracle database consists of both logical database structure and the physical database structure. • -

Physical Database Structure : determined by the operating system files that constitute the database three types of files :
one or more datafiles : The datafiles contain the information stored in the database. You can have a single datafile or many datafiles. The information for a single table can span many datafiles or many tables can share a set of data files.
two or more redo log files : The redo log files, known collectively as the redo log, store a log of all changes made to the database. This information is used in the event of a system failure to reapply changes that have been made and committed but that may not have been made to the datafiles. If redo log information is lost, you cannot recover the system. Redo log files are critical in protecting a database against failures. To protect against a failure involving the redo log itself, Oracle allows a multiplexed redo log so that two or more copies of the redo log can be maintained on different disks.
one or more control files : The control files are used to store information such as the locations of data and redo log files. Oracle needs this information to start up the database instance. It is essential that the control files be protected. Oracle provides a mechanism for storing multiple copies of the control files. Like the redo log, Oracle allows the control file to be multiplexed for protection of the control file.

Jayashree S

Page 9 of 127

Oracle SQL

• -

Logical Database Structure : determined by :
one or more tablespaces
the database’s schema objects ( tables, views, indexes, sequences, clusters, stored procedures, database triggers, and so on)
data blocks, extents, and segments

Tablespaces A database is divided into logical storage units called tablespaces. A tablespace can be online or offline.

Database SYSTEM Tablespace

DATA1.O

USER Tablespace

DATA3.O DATA2.O

A tablespace is used to logically group data together. For example, you can have a tablespace for accounting and a separate tablespace for purchasing. Segmenting groups into different tablespaces simplifies the administration of these groups. Tablespaces are made up of one or more datafiles. By using more than one datafile per tablespace, you can spread the data over many different disks to distribute the I/O load and improve performance. As part of the process of creating the database, Oracle automatically creates the SYSTEM tablespace. You can create additional separate tablespace for user data. The SYSTEM tablespace is where the data dictionary is kept. Schemas and Schema Objects
Schema is a collection of objects.
Schema objects are logical structures directly referring to the database’s data.
There is no relationship between a tablespace and a schema .
Objects in the same schema can be on different tablespaces.
A tablespace can hold objects from different schemas.

Jayashree S

Page 10 of 127

Oracle SQL

Schema Objects -

Tables: A table is the basic unit of data storage in an Oracle database. The tables hold all of the user-accessible data. Nested Tables: In the Oracle object-relational database, you can create a table with a column whose datatype is another table. That is, tables can be nested within other tables as values in a column. The Oracle Server stores nested table data "out of line" from the rows of the parent table, using a store table which is associated with the nested table column. The parent row contains a unique set identifier value associated with a nested table instance.

-

Views: A view is a custom-tailored presentation of the data in one or more tables (or other views). A view can also be thought as a “stored query”.

-

Sequences: A sequence generates a serial list of unique numbers for numeric columns of a database’s tables.

-

Program Units (stored procedures, functions and packages): A stored procedure or a function is a set of SQL and PL/SQL statements grouped together as an executable unit to perform a specific task, and stored in the database. Packages provide a method of encapsulating and storing related procedures, functions, and other package constructs together as a unit in the database.

-

Synonyms: A synonym is an alias for a table, view, sequence, or program unit. A synonym is not actually an object, but instead is a direct reference to an object.

-

Indexes, Clusters, and Hash Clusters: Indexes, Clusters, and Hash Clusters are optional structures associated with tables, which can be created to increase the performance of data retrieval.

Data Blocks • At the finest level of granularity, an Oracle database’s data is stored in data blocks. • One data block corresponds to a specific number of bytes of physical database space on disk. • A data block size is specified for each Oracle database when the database is created. Extents • Extent is the next level of logical database space. • Extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information. Segments • Segment is the level of logical database storage above an extent. • Segment is a set of extents allocated for a certain logical structure. • Types of segments :

Jayashree S

Page 11 of 127

Oracle SQL

Data Segment: Each non-clustered table has a data segment. All of the table’s data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster’s data segment. Index Segment: Each index has an index segment that stores all of its data. Rollback Segment: One or more rollback segments are created by the DBA for a database to temporarily store “undo” information. This information is used : • to generate read-consistent database information • during database recovery • to rollback uncommitted transactions for users Temporary Segment: These are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment’s extents are returned to the system. Oracle allocates space for all types of segments in extents. Therefore, when the existing extents of a segment are full, Oracle allocates another extent for that segment as needed.

Segment 112 K

Extent 28 K

Extent 84 K 2k 2k 2k 2k 2k 2k 2k

2k 2k 2k 2k 2k 2k 2k

2k 2k 2k 2k 2k 2k 2k

2k 2k 2k 2k 2k 2k 2k

Data Blocks

Jayashree S

Page 12 of 127

Oracle SQL

The Data Dictionary • •

Each database has a data dictionary. Data dictionary stores information about both logical and physical structure of the database, plus - the valid users of an Oracle database - information about integrity constraints defined for tables in the database - how much space is allocated for a schema object and how much of it is being used

An Oracle Instance •

Every time a database is started, an SGA is allocated and Oracle background processes are started. The combination of these processes and memory buffers is called an Oracle Instance:

Use

Use

Use

Use

User Process es

..

System Global Area

REC

PMO

SMO

DBW

LGW

ARC

Backgroun d Processes

An Oracle instance has two types of processes: user processes and Oracle processes. • •

A user process executes the code of an application program (such as an Oracle Forms application) or an Oracle Tool (such as Enterprise Manager). Oracle processes are server processes that perform work for the user processes and background processes that perform maintenance work for the Oracle Server.

Communications Software and Net8 If the user and server processes are on different computers of a network or if the user processes connect to shared server processes through dispatcher processes, the user

Jayashree S

Page 13 of 127

Oracle SQL

process and server process communicate using Net8. Dispatchers are optional background processes, present only when a multi-threaded server configuration is used. Net8 is Oracle's interface to standard communications protocols that allows for the proper transmission of data between computers. Oracle Memory Structures Oracle uses shared memory for several purposes, including caching of data and indexes as well as storing shared program code. This shared memory is used for several functions and is broken into various pieces, or memory structures. The basic memory structures associated with Oracle are - System Global Area ( SGA ) - Program Global Area ( PGA )

System Global Area (SGA) • • • •

It is a shared memory region containing data and control information for one Oracle instance. Oracle allocates the SGA when an instance starts and deallocates it when the instance shuts down. Each instance has its own SGA. SGA is divided into memory structures : - Database Buffer Cache - Redo Log Buffer - Shared Pool - Statement Handles or Cursors

•

Database Buffer Cache : - Database buffers of the SGA store the most recently used blocks of database data. - The set of database buffers in an instance is referred as database buffer cache. - These buffers can contain modified data that has not yet been permanently written to disk (sometimes called dirty blocks), blocks that have not been modified, or blocks that have been written to disk since modification (sometimes called clean blocks). - Because the buffer cache keeps blocks based on a most recently used algorithm, the most active buffers stay in memory to reduce I/O and improve performance.

•

Redo Log Buffer : - Stores redo entries - a log of changes made to the database. - Its size is static. - The contents are written to an online redo log file.

Jayashree S

Page 14 of 127

Oracle SQL

•

Shared Pool : - Contains shared memory constructs such as shared SQL areas. - Shared SQL area is used to process every unique SQL statement issued. - Shared SQL area contains parse tree and execution plan for the SQL statement. If multiple applications issue the same SQL statement, the shared SQL area can be accessed by each of them to reduce the amount of memory needed and to reduce the processing time used for parsing and execution planning.

• -

Statement Handles or Cursors : A handle (name or pointer) for memory associated with a specific statement. Although most Oracle users rely on the automatic cursor handling of the Oracle utilities, the programmatic interfaces offer application designers more control over cursors.

Program Global Area • It is a buffer that contains data and control information for a server process. • A PGA is created by Oracle when a server process starts. Figure: Memory Structures and Processes in Oracle LCK

System Global Area Database Buffer Cache

User Proces

Shared Server Process

Redo Log Buffer

Offlin e Storag

Dedicat ed Server CKPT

ARCH

DBW D000

LGW User Proces

Contro l Files Redo Log

Jayashree S

Databas e Files

Page 15 of 127

Oracle SQL

Processes •

A process is a “thread of control” or mechanism in an operating system that can execute a series of steps. Also called job or task.

•

A process normally has its own private memory area in which it runs.

•

Oracle Server has
User ( Client )Processes
Oracle Processes Server Processes Background Processes

•

User Process (Client Process): - Created and maintained to execute the software code of an application program or an Oracle tool. - Manages the communication with the server process.

•

Oracle Processes : - These are called by other processes to perform function on behalf of the invoking process. Oracle processes can be split into two groups : server processes (which perform functions for the invoking process) and background processes (which perform functions on behalf of the entire RDBMS).

Server Processes (Shadow Processes) •

These are created to handle requests from connected user processes.

•

These are in charge of communicating with Oracle to carry out requests of the associated user process. Eg. If a user queries data not in SGA currently, server process fetches data into SGA from datafiles.

•

Oracle can be configured to vary the number of user processes per server process. In a dedicated server configuration, a server process handles requests for a single user process. A multi-threaded server configuration allows many user processes to share a small number of server processes, minimizing the number of server processes and maximizing the utilization of available system resources.

Jayashree S

Page 16 of 127

Oracle SQL

Figure : Multi-Threaded Server Configuration Applicati on Code

Applicati on Code

Applicati on Code

User Processes

...

Client Database Server

Dispatcher Process Shared Server Processes

Oracle Server Code Request Queues

Response Queues SGA

Oracle Background Processes

The processes involved in a Multi-Threaded Server Configuration : -

-

A Network Listener Process (of Net8), that waits for incoming connection request from a user process and connects it to dispatcher, so that the shared process can be used; if the user process requests a dedicated server, listener process creates a dedicated server process and connects the user process to it. One or more Dispatcher Processes that routes the user request to the next available shared server process. One or more Shared Server processes.

Jayashree S

Page 17 of 127

Oracle SQL

Background Processes •

Background processes are the Oracle processes used to perform various tasks within the RDBMS system. Oracle creates a set of background processes for each instance.

•

Background processes asynchronously perform I/O and monitor other Oracle processes to provide increased parallelism for better performance and reliability.

•

Background Processes : - DBWR - LGWR - CKPT - SMON - PMON - ARCH - RECO - Dnnn - LCKn

•

Database Writer ( DBWR ) : The Database Writer writes modified blocks from the database buffer cache to the datafiles. Since Oracle uses write-ahead logging, DBWR does not need to write blocks when a transaction commits. Instead, DBWR is designed to perform batched writes with high efficiency. In the most common case, DBWR writes only when more data needs to be read into the system global area and too few database buffers are free. The least recently used data is written to the datafiles first. DBWR also performs writes for other functions such as checkpointing.

•

Log Writer ( LGWR ) : The Log Writer writes redo log entries to disk. Redo log data is generated in the redo log buffer of the system global area. As transactions commit and the log buffer fills, LGWR writes redo log entries into an online redo log file.

•

Checkpoint ( CKPT ) : At specific times, all modified database buffers in the system global area are written to the datafiles by DBWR; this event is called a checkpoint. The Checkpoint process is responsible for signaling DBWR at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint.

•

System Monitor ( SMON ) : The system monitor performs instance recovery at instance startup. In a multiple instance system (one that uses the Parallel Server), SMON of one instance can also perform instance recovery for other instances that have failed. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or offline errors. These transactions are eventually recovered by SMON when the tablespace or file is brought

Jayashree S

Page 18 of 127

Oracle SQL

back online. SMON also coalesces free extents within the database to make free space contiguous and easier to allocate. •

Process Monitor ( PMON ) : The process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using. PMON also checks on dispatcher and server processes and restarts them if they have failed.

•

Archiver ( ARCH ) : The archiver copies the online redo log files to archival storage when they are full. ARCH is active only when a database's redo log is used in ARCHIVELOG mode.

•

Recoverer ( RECO ) : The recoverer is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

•

Dispatcher ( Dnnn ) : Dispatchers are optional background processes, present only when a multi-threaded server configuration is used. At least one dispatcher process is created for every communication protocol in use (D000, . . ., Dnnn). Each dispatcher process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes.

•

Lock ( LCKn ) : The lock processes (LCK0, . . ., LCK9) are used for inter-instance locking in the Oracle Parallel Server

How Oracle Works : An Example Assume an Oracle Configuration with user and server on different machines, connected via a Network. 1. An instance is currently running on the computer that is executing Oracle (often called the host or database server). 2. A computer running an application (a local machine or client workstation) runs the application in a user process. The client application attempts to establish a connection to the server using the proper Net8 driver. 3. The server is running the proper Net8 driver. The server detects the connection request from the application and creates a (dedicated) server process on behalf of the user process.

Jayashree S

Page 19 of 127

Oracle SQL

4. The user executes a SQL statement and commits the transaction. For example, the user changes a name in a row of a table. 5. The server process receives the statement and checks the shared pool for any shared SQL area that contains an identical SQL statement. If a shared SQL area is found, the server process checks the user's access privileges to the requested data and the previously existing shared SQL area is used to process the statement; if not, a new shared SQL area is allocated for the statement so that it can be parsed and processed. 6. The server process retrieves any necessary data values from the actual datafile (table) or those stored in the system global area. 7. The server process modifies data in the system global area. The DBWR process writes modified blocks permanently to disk when doing so is efficient. Because the transaction committed, the LGWR process immediately records the transaction in the online redo log file. 8. If the transaction is successful, the server process sends a message across the network to the application. If it is not successful, an appropriate error message is transmitted. 9. Throughout this entire procedure, the other background processes run, watching for conditions that require intervention. In addition, the database server manages other users' transactions and prevents contention between transactions that request the same data. Introduction to Rollback Segments Each database contains one or more rollback segments. A rollback segment records the old values of data that were changed by each transaction (whether or not committed). Rollback segments are used to provide read consistency, to roll back transactions, and to recover the database. Contents of a Rollback Segment Information in a rollback segment consists of several rollback entries. Among other information, a rollback entry includes block information (the file number and block ID corresponding to the data that was changed) and the data as it existed before an operation in a transaction. Oracle links rollback entries for the same transaction, so the entries can be found easily if necessary for transaction rollback. Neither database users nor administrators can access or read rollback segments. Only Oracle can write to or read them. (They are owned by the user SYS, no matter which user creates them.) How Rollback Entries Are Logged Rollback entries change data blocks in the rollback segment, and Oracle records all changes to data blocks, including rollback entries, in the redo log. This second recording of the rollback information is very important for active transactions (not yet committed or rolled back) at the time of a system crash. If a system crash occurs, Oracle automatically restores the rollback segment information, including the rollback entries for active transactions, as part of instance or media recovery. Once the recovery is complete, Oracle

Jayashree S

Page 20 of 127

Oracle SQL

performs the actual rollbacks of transactions that had been neither committed nor rolled back at the time of the system crash. When Rollback Information Is Required For each rollback segment, Oracle maintains a transaction table—a list of all transactions that use the associated rollback segment and the rollback entries for each change performed by these transactions. Oracle uses the rollback entries in a rollback segment to perform a transaction rollback and to create read-consistent results for queries. Rollback segments record the data prior to change for each transaction. For every transaction, Oracle links each new change to the previous change. If you must roll back the transaction, Oracle applies the changes in a chain to the data blocks in an order that restores the data to its previous state. Similarly, when Oracle needs to provide a readconsistent set of results for a query, it can use information in rollback segments to create a set of data consistent with respect to a single point in time. Transactions and Rollback Segments Each time a user’s transaction begins, the transaction is assigned to a rollback segment in one of two ways: 1. Oracle can assign a transaction automatically to the next available rollback segment. The transaction assignment occurs when you issue the first DML or DDL statement in the transaction. Oracle never assigns read-only transactions (transactions that contain only queries) to a rollback segment, regardless of whether the transaction begins with a SET TRANSACTION READ ONLY statement. 2. An application can assign a transaction explicitly to a specific rollback segment. At the start of a transaction, an application developer or user can specify a particular rollback segment that Oracle should use when executing the transaction. This lets the application developer or user select a large or small rollback segment, as appropriate for the transaction. For the duration of a transaction, the associated user process writes rollback information only to the assigned rollback segment. When you commit a transaction, Oracle releases the rollback information but does not immediately destroy it. The information remains in the rollback segment to create readconsistent views of pertinent data for queries that started before the transaction committed. To guarantee that rollback data is available for as long as possible for such views, Oracle writes the extents of rollback segments sequentially. When the last extent of the rollback segment becomes full, Oracle continues writing rollback data by wrapping around to the first extent in the segment. A long-running transaction (idle or active) can require a new extent to be allocated for the rollback segment. Data Concurrency and Consistency A primary concern of a multi-user database management system is how to control concurrency, or the simultaneous access of the same data by many users. Without adequate concurrency controls, data could be updated or changed improperly, compromising data integrity. Read Consistency

Jayashree S

Page 21 of 127

Oracle SQL

Read consistency, as supported by Oracle, does the following: • guarantees that the set of data seen by a statement is consistent with respect to a single point-in-time and does not change during statement execution (statement-level read consistency) • ensures that readers of database data do not wait for writers or other readers of the same data • ensures that writers of database data do not wait for readers of the same data • ensures that writers only wait for other writers if they attempt to update identical rows in concurrent transactions The simplest way to think of Oracle's implementation of read consistency is to imagine each user operating a private copy of the database, hence the multi-version consistency model. Read Consistency, Rollback Segments, and Transactions •

•

•

To manage the multi-version consistency model, Oracle must create a read-consistent set of data when a table is being queried (read) and simultaneously updated (written). When an update occurs, the original data values changed by the update are recorded in the database's rollback segments. As long as this update remains part of an uncommitted transaction, any user that later queries the modified data views the original data values - Oracle uses current information in the system global area and information in the rollback segments to construct a read-consistent view of a table's data for a query. Only when a transaction is committed are the changes of the transaction made permanent. Statements that start after the user's transaction is committed only see the changes made by the committed transaction. Note that a transaction is key to Oracle's strategy for providing read consistency. This unit of committed (or uncommitted) SQL statements: - dictates the start point for read-consistent views generated on behalf of readers - controls when modified data can be seen by other transactions of the database for reading or updating.

Read-Only Transactions •

•

By default, Oracle guarantees statement-level read consistency. The set of data returned by a single query is consistent with respect to a single point in time. However, in some situations, you may also require transaction-level read consistency - the ability to run multiple queries within a single transaction, all of which are readconsistent with respect to the same point in time, so that queries in this transaction do not see the effects of intervening committed transactions. If you want to run a number of queries against multiple tables and if you are doing no updating, you may prefer a read-only transaction. After indicating that your transaction is read-only, you can execute as many queries as you like against any

Jayashree S

Page 22 of 127

Oracle SQL

table, knowing that the results of each query are consistent with respect to the same point in time.

Locking Mechanisms Oracle also uses locks to control concurrent access to data. Locks are mechanisms intended to prevent destructive interaction between users accessing Oracle data. Locks are used to achieve two important database goals: • Consistency: Ensures that the data a user is viewing or changing is not changed (by other users) until the user is finished with the data. • Integrity: Ensures that the database's data and structures reflect all changes made to them in the correct sequence. Locks guarantee data integrity while allowing maximum concurrent access to the data by unlimited users. Automatic Locking • • •

Oracle locking is performed automatically and requires no user action. Implicit locking occurs for SQL statements as necessary, depending on the action requested. Oracle's sophisticated lock manager automatically locks table data at the row level. By locking table data at the row level, contention for the same data is minimized. Oracle's lock manager maintains several different types of row locks, depending on what type of operation established the lock. In general, there are two types of locks: exclusive locks and share locks. Only one exclusive lock can be obtained on a resource (such as a row or a table); however, many share locks can be obtained on a single resource. Both exclusive and share locks always allow queries on the locked resource, but prohibit other activity on the resource (such as updates and deletes).

Manual Locking Under some circumstances, a user may want to override default locking. Oracle allows manual override of automatic locking features at both the row level (by first querying for the rows that will be updated in a subsequent statement) and the table level. Transactions and Data Concurrency Oracle provides data concurrency and integrity between transactions using its locking mechanisms. Because the locking mechanisms of Oracle are tied closely to transaction control, application designers need only define transactions properly, and Oracle automatically manages locking. • Keep in mind that Oracle locking is fully automatic and requires no user action. • Implicit locking occurs for all SQL statements so that database users never need to lock any resource explicitly. •

Jayashree S

Page 23 of 127

Oracle SQL

Oracle’s default locking mechanisms lock data at the lowest level of restrictiveness to guarantee data integrity while allowing the highest degree of data concurrency. Modes of Locking Oracle uses two modes of locking in a multiuser database: Lock Duration All locks acquired by statements within a transaction are held for the duration of the transaction, preventing destructive interference including dirty reads, lost updates, and destructive DDL operations from concurrent transactions. • exclusive lock mode Prevents the associates resource from being shared. This lock mode is obtained to modify data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released. • share lock mode Allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who needs an exclusive lock). Several transactions can acquire share locks on the same resource. changes made by the SQL statements of one transaction become visible only to other transactions that start after the first transaction is committed. •

Oracle releases all locks acquired by the statements within a transaction when you either commit or roll back the transaction. Oracle also releases locks acquired after a savepoint when rolling back to the savepoint. However, only transactions not waiting for the previously locked resources can acquire locks on the now available resources. Waiting transactions will continue to wait until after the original transaction commits or rolls back completely. Data Lock Conversion Versus Lock Escalation A transaction holds exclusive row locks for all rows inserted, updated, or deleted within the transaction. Because row locks are acquired at the highest degree of restrictiveness, no lock conversion is required or performed. • Oracle automatically converts a table lock of lower restrictiveness to one of higher restrictiveness as appropriate. • For example, assume that a transaction uses a SELECT statement with the FOR UPDATE clause to lock rows of a table. As a result, it acquires the exclusive row locks and a row share table lock for the table. If the transaction later updates one or more of the locked rows, the row share table lock is automatically converted to a row exclusive table lock. Lock escalation occurs when numerous locks are held at one level of granularity (for example, rows) and a database raises the locks to a higher level of granularity (for example, table). • For example, if a single user locks many rows in a table, some databases automatically escalate the user’s row locks to a single table. The number of locks is reduced, but the restrictiveness of what is being locked is increased. Oracle never escalates locks. Lock escalation greatly increases the likelihood of deadlocks. Imagine the situation where the system is trying to escalate locks on behalf of

Jayashree S

Page 24 of 127

Oracle SQL

transaction T1 but cannot because of the locks held by transaction T2. A deadlock is created if transaction T2 also requires lock escalation of the same data before it can proceed. Database Security • -

Security mechanisms do the following: prevent unauthorized database access prevent unauthorized access to schema objects control disk usage control system resource usage (such as CPU time) audit user actions

•

Database security can be classified into two distinct categories: system security and data security.

•

System security includes the mechanisms that control the access and use of the database at the system level. For example, system security includes: - valid username/password combinations - the amount of disk space available to the objects of a user - the resource limits for a user System security mechanisms check: - whether a user is authorized to connect to the database - whether database auditing is active - which system operations a user can perform

•

Data security includes the mechanisms that control the access and use of the database at the object level. For example, data security includes - which users have access to a specific schema object and the specific types of actions allowed for each user on the object (for example, user SCOTT can issue SELECT and INSERT statements but not DELETE statements using the EMP table) - the actions, if any, that are audited for each schema object

Security Mechanisms The Oracle Server provides discretionary access control, which is a means of restricting access to information based on privileges. Oracle manages database security using several different facilities: • database users and schemas • privileges • roles • storage settings and quotas • profiles and resource limits • auditing: statement, privilege, object

Jayashree S

Page 25 of 127

Oracle SQL

Database Backup and Recovery

Structures Used for Recovery Oracle uses several structures to provide complete recovery from an instance or disk failure: the redo log, rollback segments, a control file, and necessary database backups. The Redo Log The redo log is a set of files that protect altered database data in memory that has not been written to the datafiles. The redo log may consist of two parts: the online redo log and the archived redo log. The Online Redo Log • The online redo log is a set of two or more online redo log files that record all committed changes made to the database. Whenever a transaction is committed, the corresponding redo entries temporarily stored in redo log buffers of the system global area are written to an online redo log file by the background process LGWR. •

•

•

The online redo log files are used in a cyclical fashion; for example, if two files constitute the online redo log, the first file is filled, the second file is filled, the first file is reused and filled, the second file is reused and filled, and so on. Each time a file is filled, it is assigned a log sequence number to identify the set of redo entries. To avoid losing the database due to a single point of failure, Oracle can maintain multiple sets of online redo log files. A multiplexed online redo log consists of copies of online redo log files physically located on separate disks; changes made to one member of the group are made to all members. If a disk that contains an online redo log file fails, other copies are still intact and available to Oracle. System operation is not interrupted and the lost online redo log files can be easily recovered using an intact copy.

The Archived Redo Log • Optionally, filled online redo files can be archived before being reused, creating an archived redo log. Archived (offline) redo log files constitute the archived redo log. • The presence or absence of an archived redo log is determined by the mode that the redo log is using: ARCHIVELOG The filled online redo log files are archived before they are reused in the cycle. NOARCHIVELOG The filled online redo log files are not archived. •

In ARCHIVELOG mode, the database can be completely recovered from both instance and disk failure. The database can also be backed up while it is open and available for use. However, additional administrative operations are required to maintain the archived redo log.

Jayashree S

Page 26 of 127

Oracle SQL

•

If the database's redo log is operated in NOARCHIVELOG mode, the database can be completely recovered from instance failure, but not from a disk failure. Additionally, the database can be backed up only while it is completely closed. Because no archived redo log is created, no extra work is required by the database administrator.

Control Files • The control files of a database keep, among other things, information about the file structure of the database and the current log sequence number being written by LGWR. During normal recovery procedures, the information in a control file is used to guide the automated progression of the recovery operation. • Multiplexed Control Files: This feature is similar to the multiplexed redo log feature: a number of identical control files may be maintained by Oracle, which updates all of them simultaneously. Rollback Segments • Rollback segments record rollback information used by several functions of Oracle. During database recovery, after all changes recorded in the redo log have been applied, Oracle uses rollback segment information to undo any uncommitted transactions. Because rollback segments are stored in the database buffers, this important recovery information is automatically protected by the redo log. Database Backups • Whole Database Backups: A whole database backup is an operating system backup of all datafiles, online redo log files, and the control file that constitutes an Oracle database. Full backups are performed when the database is closed and unavailable for use. • Partial Backups: A partial backup is an operating system backup of part of a database. The backup of an individual tablespace's datafiles or the backup of a control file are examples of partial backups. Partial backups are useful only when the database's redo log is operated in ARCHIVELOG mode.

Basic Recovery Steps Two potential situations can result after a failure: • Blocks containing committed modifications were not written to the datafiles, so the changes may only appear in the redo log. Therefore, the redo log contains committed data that must be applied to the datafiles. • Since the redo log may have contained data that was not committed, uncommitted transaction changes applied by the redo log during recovery must be erased from the datafiles. To solve this situation, two separate steps are always used by Oracle during recovery from an instance or media failure: rolling forward and rolling back. Rolling Forward

Jayashree S

Page 27 of 127

Oracle SQL

•

•

The first step of recovery is to roll forward, that is, reapply to the datafiles all of the changes recorded in the redo log. Rolling forward proceeds through as many redo log files as necessary to bring the datafiles forward to the required time. If all needed redo information is online, Oracle performs this recovery step automatically when the database starts. After roll forward, the datafiles contain all committed changes as well as any uncommitted changes that were recorded in the redo log.

Rolling Back • The roll forward is only half of recovery. After the roll forward, any changes that were not committed must be undone. After the redo log files have been applied, then the rollback segments are used to identify and undo transactions that were never committed, yet were recorded in the redo log. This process is called rolling back. Oracle completes this step automatically. Introduction to Oracle9i Data Guard Oracle’s Standby Database is the most frequently used and, for most environments, the most effective disaster recovery solution for Oracle databases. Oracle9i, Release 1 (9.0.1), provides enhancements that do much more than meet essential disaster recovery requirements. By automating complex tasks and providing dramatically enhanced monitoring, alert, and control mechanisms, Standby Database and a number of new modules now help you to survive mistakes, corruptions and other disasters that might otherwise destroy your database. Also, the downtime required for upgrades, such as hardware and operating system maintenance, can be significantly reduced using Oracle9i standby databases. As well as enhancing Standby Database, several entirely new components have been added that provide further protection against user errors and corruptions. These new components, together with the enhanced Standby Database, are contained in a new Oracle9i database feature called Oracle9i Data Guard. Oracle8 Automated Standby Database • The Oracle8 Automated Standby Database feature provides the means to create and automatically maintain copies of a production database to protect against disasters. • Oracle8 Automated Standby Database configuration performs the following functions: 1. Updates the standby databases by automatically shipping archive logs as the primary database creates them. 2. Includes a production (primary) database linked to one to four standby databases that are identical copies of the production database. Each can be used to take over production processing from the production database. 3. Applies the archived redo logs to each standby using standard Oracle recovery. Logs are applied automatically, or they can accumulate for manual updating. 4. The primary database is open and active. The standby database is either in recovery or open read-only.

Jayashree S

Page 28 of 127

Oracle SQL

Overview of Oracle9i Data Guard Oracle9i provides a number of new features that help to prevent or minimize losses due to human errors, disasters, and data corruption. Physical Standby Database • Physical standby database is the Oracle9i version of the Oracle8 Automated Standby Database feature, with one difference. The log transport services are now a separate component. • The log transport services have been enhanced to support the new logical standby database feature and other features as well as physical standby database. • We call this "physical" standby because of this feature’s roots in recovery. A physicalstandby is physically identical to the primary. • Put another way, standby on-disk data structures are identical to the primary’s on a block-for-block basis, because recovery applies changes block-for-block using the physical ROWID. • The database schema, including indexes, must be the same, and the database cannot be opened read/write. Logical Standby Database • Logical standby database is a new feature that takes standard Oracle archive logs, transforms them back into SQL transactions, and then applies them to an open standby database. Because the database is open, it is physically different from the primary database. • As the standby is logically the same as the primary, it can be used to take over processing if the primary database is mistakenly harmed by human error, a corruption, or a disaster. Because transactions are applied using SQL, the standby database can be used concurrently for other tasks. • Decision support can be optimized by using different indexes and materialized views than those on the primary. • Logical standby database is first and foremost a data protection feature. Just like physical standby database, it uses archive logs shipped the moment they are created on the primary system, performing all related processing on the standby database, out of harm’s way in the case of a primary database failure. • Updates recorded in the log always include the previous values as well as the new, updated values. Logical standby database compares these previous values to the previous values in the logical standby database. Log Transport Services • Log transport services are used by both physical and logical standby database components. • The functions it provides include control of different log shipping mechanisms, log shipping error handling and reporting, and retrieving "lost" logs after a system failure. • Guaranteed data protection is now possible using one of the new log transport modes.

Jayashree S

Page 29 of 127

Oracle SQL

Replication • • • • • •

Replication is the process of copying and maintaining database objects, such as tables, in multiple databases that make up a distributed database system. Changes applied at one site are captured and stored locally before being forwarded and applied at each of the remote locations. Oracle replication is a fully integrated feature of the Oracle server. It is not a separate server. Replication uses distributed database technology to share data between multiple sites, but a replicated database and a distributed database are not the same. In a distributed database, data is available at many locations, but a particular table resides at only one location. For example, the EMP table can reside at only the db1 database in a distributed database system that also includes the db2 and db3 databases. Replication means that the same data is available at multiple locations. For example, the EMP table may be available at db1, db2, and db3.

Table Replication Distributed database systems often locally replicate remote tables that are frequently queried by local users. By having copies of heavily accessed data on several nodes, the distributed database does not need to send information across a network repeatedly, thus helping to maximize the performance of the database application. Data can be replicated using materialized views. Multitier Materialized Views Oracle9i, supports materialized views that are hierarchical and updatable. Multitier replication provides increased flexibility of design for a distributed application. Using multitier materialized views, applications can manage multilevel data subsets where there is no direct connection between levels. An updatable materialized view lets you insert, update, and delete rows in the materialized view and propagate the changes to the target master table. Synchronous and asynchronous replication is supported. Materialized Views • •

Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. They are suitable in various computing environments such as data warehousing, decision support, and distributed or mobile computing: o In data warehouses, materialized views are used to precompute and store aggregated data such as sums and averages. o Materialized views in these environments are typically referred to as summaries because they store summarized data. They can also be used to precompute joins with or without aggregations. o If compatibility is set to Oracle9i, or higher, materialized views can be used for queries that include filter selections.

Jayashree S

Page 30 of 127

Oracle SQL

o Cost-based optimization can use materialized views to improve query performance by automatically recognizing when a materialized view can and should be used to satisfy a request. o The optimizer transparently rewrites the request to use the materialized view. Queries are then directed to the materialized view and not to the underlying detail tables or views. • In distributed environments, materialized views are used to replicate data at distributed sites and synchronize updates done at several sites with conflict resolution methods. The materialized views as replicas provide local access to data that otherwise has to be accessed from remote sites. • In mobile computing environments, materialized views are used to download a subset of data from central servers to mobile clients, with periodic refreshes from the central servers and propagation of updates by clients back to the central servers. Materialized views are similar to indexes in several ways: • They consume storage space. • They must be refreshed when the data in their master tables changes. • They improve the performance of SQL execution when they are used for query rewrites. • Their existence is transparent to SQL applications and users. Unlike indexes, materialized views can be accessed directly using a SELECT statement. Depending on the types of refresh that are required, they can also be accessed directly in an INSERT, UPDATE, or DELETE statement. A materialized view can be partitioned. You can define a materialized view on a partitioned table and one or more indexes on the materialized view. The Object-Relational Model for Database Management Oracle extends the relational model to an object-relational model, which makes it possible to store complex business models in a relational database.

The Object-Relational Model •

The object-relational model allows users to define object types, specifying both the structure of the data and the methods of operating on the data, and to use these datatypes within the relational model. • -

Object types are abstractions of the real-world entities-for example, purchase orders-that application programs deal with. An object type has three kinds of components: A name, which serves to identify the object type uniquely. Attributes, which are built-in datatypes or other user-defined types. Attributes model the structure of the real world entity. Methods, which are functions or procedures written in PL/SQL and stored in the database, or written in a language like C and stored externally. Methods implement specific operations that an application can perform on the data. Every

Jayashree S

Page 31 of 127

Oracle SQL

object type has a constructor method that makes a new object according to the datatype's specification. Introduction to Partitioning Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions. • SQL queries and DML statements do not need to be modified in order to access partitioned tables. • However, once partitions are defined, DDL statements can access and manipulate individuals partitions rather than entire tables or indexes. • This is how partitioning can simplify the manageability of large database objects. • Also, partitioning is entirely transparent to applications. Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces. • Partitioning is useful for many different types of applications, particularly applications that manage large volumes of data. OLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from performance and manageability. Partitioning offers these advantages: • Partitioning enables data management operations such data loads, index creation and rebuilding, and backup/recovery at the partition level, rather than on the entire table. This results in significantly reduced times for these operations. • Partitioning improves query performance. In many cases, the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique (called partition pruning) can provide order-ofmagnitude gains in performance. • Partitioning can significantly reduce the impact of scheduled downtime for maintenance operations. Partition independence for partition maintenance operations lets you perform concurrent maintenance operations on different partitions of the same table or index. You can also run concurrent SELECT and DML operations against partitions that are unaffected by maintenance operations. • Partitioning increases the availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures. • Partitioning can be implemented without requiring any modifications to your applications. For example, you could convert a nonpartitioned table to a partitioned table without needing to modify any of the SELECT statements or DML statements which access that table. You do not need to rewrite your application code to take advantage of partitioning. •

Jayashree S

Page 32 of 127

Oracle SQL

Review Questions 1. __________________________________ is the interface to Oracle Server, and lets you enter and execute commands. 2. Each RDBMS has its own specific SQL. (T / F) 3. The Oracle tools which help in developing applications are ____________________ ___________________________________ 4. The Oracle Server consists of _________________________________________ and ___________________________________________________________ 5. The physical database structures in Oracle are _______________________________ _____________________________________________________________________ 6. The database structure which records all the transactions on tables is termed ________________________________________ 7. An Oracle instance is made up of __________________________________ and _________________________________________________ 8. The structures used to enhance the performance of Oracle by providing faster access to data are ____________________________________________________________ 9. The Oracle object which allows you to generate serial numbers is called a _____________________________________ 10. The types of segments are _______________________________________________ _____________________________________________________________________ 11. The database structure which stores the ‘meta-data’, which means ______________________________ is called _______________________________ 12. The area in the SGA used to process SQL statements is called ___________________ 13. Multi-threaded server configuration means __________________________________ _____________________________________________________________________ 14. The ARCH process is active only when the redo log operates in _____________________________________________ mode. 15. The product of Oracle which is a procedural language extension of SQL is _______________________________________

Jayashree S

Page 33 of 127

Oracle SQL

16. By default, Oracle provides ______________________________-level read consistency. 17. When an update on the data occurs, the original data values changed by the update are recorded in the database’s _____________________________ segments. 18. Oracle allows manual locking of rows or tables. (T / F) 19. The two basic recovery steps are _________________________________ and _____________________________________ 20. The object-relational model of Oracle8i allows users to define ___________________ that specify the attributes and methods.

Jayashree S

Page 34 of 127

Oracle SQL

SQL

• • • • • • •

Introductory Concepts Creating, Altering, and Dropping Tables Indexing, Clustering, and Sequences Changing Data: Inserting, Updating, and Deleting Data Querying Tables Views Controlling Transactions

Jayashree S

Page 35 of 127

Oracle SQL

Introductory Concepts Topics • • • • •

Elements of Oracle8i SQL Datatypes User-Defined Datatypes Nulls Pseudocolumns

Jayashree S

Page 36 of 127

Oracle SQL

Elements of Oracle8i SQL Database Objects 1. Schema Objects A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects: • Clusters • Database Links • Database Triggers • External Procedure Libraries • Index-only Tables • Indexes • Object Tables • Object Types • Object Views • Packages • Sequences • Snapshots • Snapshot Logs • Stored Functions • Stored Procedures • Synonyms • Tables • Views 2. Non-Schema Objects Other types of objects are also stored in the database and can be created and manipulated with SQL, but are not contained in a schema: • directories • profiles • roles • rollback segments • tablespaces • users

Jayashree S

Page 37 of 127

Oracle SQL

Partitioned Tables and Indexes Tables and indexes can be partitioned. When partitioned, these schema objects consist of a number of parts, called partitions, all of which have the same logical attributes. For example, all partitions in a table share the same column and constraint definitions, and all partitions in an index share the same index columns. Partition-Extended Table Names Partitions can be used as tables, providing users with a shortcut method of performing some partition-level operations that would otherwise require using a WHERE clause predicate. Partition-level bulk operations, such as deleting all of the rows from a partition, restrict the operation to one partition. These types of operations are easily expressed with the partition-extended table name syntax. Trying to phrase the same operation with a WHERE clause predicate can be cumbersome, especially when the range partitioning key uses more than one column. Table specification syntax has been extended for the following DML statements to allow an optional partition specification for non-remote partitioned tables: • DELETE • INSERT • LOCK TABLE • SELECT • UPDATE Namespaces A name must be unique across its namespace. Objects in the same namespace must have different names. Each schema in the database has its own namespaces for the objects it contains.

Jayashree S

Page 38 of 127

Oracle SQL

Namespaces for Schema Objects: INDEXES

TABLES VIEWS

CONSTRAINTS

SEQUENCES

PRIVATE SYNONYMS STAND-ALONE PROCEDURES STAND-ALONE STORED

CLUSTERS DATABASE TRIGGERS

FUNCTIONS PRIVATE DATABASE Namespaces For Other Objects USER ROLES

TABLESPACES

PUBLIC SYNONYMS

ROLLBACK SEGMENTS

PUBLIC DATABASE

PROFILES

Datatypes Internal Datatype VARCHAR2(size)

Description Variable length character string having maximum length size bytes. Maximum size is 4000, and minimum is 1. You must specify size for a VARCHAR2.

NUMBER(p,s)

Number having precision p and scale s.

LONG

Character data of variable length up to 2 gigabytes, or 231 -1 bytes.

DATE

Valid date range from January 1, 4712 BC to December 31, 4712 AD.

RAW(size)

Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.

LONG RAW

Raw binary data of variable length up to 2 gigabytes.

Jayashree S

Page 39 of 127

Oracle SQL

ROWID

Hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.

CHAR(size)

Fixed length character data of length size bytes. Maximum size is 2000 bytes. Default and minimum size is 1 byte.

CLOB

A character large object containing single byte characters. Maximum size is 4 gigabytes.

BLOB

A binary large object. Maximum size is 4 gigabytes.

BFILE

Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server.

Character Datatypes: Character datatypes are used to manipulate words and free-form text. These datatypes are used to store character (alphanumeric) data in the database. The CHAR datatype specifies a fixed length character string. When you create a table with a CHAR column, you supply the column length in bytes. Oracle subsequently ensures that all values stored in that column have this length. If you insert a value that is shorter than the column length, Oracle blank-pads the value to column length. The VARCHAR2 datatype specifies a variable length character string. When you create a VARCHAR2 column, you can supply the maximum number of bytes of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided it does not exceed the column's maximum length. Number Datatype: The NUMBER datatype is used to store zero, positive and negative fixed and floating point numbers with magnitudes between 1.0 x 10-130 and 9.9...9 x 10125 (38 9s followed by 88 0s) with 38 digits of precision. You can specify a fixed point number using the form: NUMBER(p,s) You specify an integer using the form: NUMBER (p) You specify a floating point number using the form: NUMBER LONG Datatype: You can use LONG columns to store long text strings. The use of LONG values are subject to some restrictions: • A table cannot contain more than one LONG column.

Jayashree S

Page 40 of 127

Oracle SQL

• • • •

LONG columns cannot appear in integrity constraints (except for NULL and NOT NULL constraints). LONG columns cannot be indexed. A stored function cannot return a LONG value. Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.

Also, LONG columns cannot appear in certain parts of SQL statements: • WHERE, GROUP BY, ORDER BY, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements • the column datatype clause of a CREATE CLUSTER statement • SQL functions (such as SUBSTR or INSTR) • expressions or conditions • select lists of queries containing GROUP BY clauses • select lists of subqueries or queries combined by set operators • select lists of CREATE TABLE AS SELECT statements • select lists in subqueries in INSERT statements DATE Datatype: The DATE datatype is used to store the date and time information. For each DATE value the following information is stored: • century • year • month • day • hour • minute • second To specify a date value, you must convert a character or numeric value to a data value with the TO_DATE function. The date function SYSDATE returns the current date and time. RAW and LONG RAW Datatypes: The RAW and LONG RAW datatypes are used for data that is not to be interpreted (not converted when moving data between different systems) by Oracle. These datatypes are intended for binary data or byte strings. For example, LONG RAW can be used to store graphics, sound, documents, or arrays of binary data; the interpretation is dependent on the use. LONG RAW data cannot be indexed, but RAW data can be indexed. Large Object (LOB) Datatypes: Internal LOB datatypes, BLOB, CLOB, and external datatype BFILE, can store large and unstructured data such as text, image, and video, up to four gigabytes in size. Internal LOB columns contain LOB locators that can refer to out-of-line or inline LOB values. Selecting a LOB from a table actually returns the LOB's locator and not the entire

Jayashree S

Page 41 of 127

Oracle SQL

LOB value. DBMS_LOB package operations on LOBs are performed through these locators. LOBs are similar to LONG and LONG RAW types, but differ in the following ways: • Multiple LOBs are allowed in a single row. • LOBs can be attributes of a user-defined datatype (object). • The LOB locator is stored in the table column, either with or without the actual LOB value; BLOB and CLOB values can be stored in separate tablespaces and BFILE data is stored in an external file on the server. • When you access a LOB column, it is the locator which is returned. • A LOB can be up to four gigabytes in size. BFILE maximum size is operating system dependent, but cannot exceed four gigabytes. • LOBs permit efficient, random, piece-wise access to and manipulation of data. • You can define one or more LOB datatype columns in a table. • You can define one or more LOB attributes in an object. • You can declare LOB bind variables. • You can select LOB columns and LOB attributes. • You can insert a new row or update an existing row that contains one or more LOB columns and/or an object with one or more LOB attributes. (You can set the internal LOB value to NULL, empty, or replace the entire LOB with data. You can set the BFILE to NULL or so that it points to a different file.) • You can update a LOB row/column intersection or a LOB attribute with another LOB row/column intersection or LOB attribute. • You can delete a row containing a LOB column or LOB attribute and thereby also delete the LOB value. Note that for BFILEs, the actual operating system file is not deleted. ROWID Datatype: Each row in the database has an address. You can examine a row's address by querying the pseudocolumn ROWID. Values of this pseudocolumn are hexadecimal strings representing the address of each row. These strings have the datatype ROWID. Restricted ROWIDs: The Oracle8i Server incorporates an extended format for ROWIDs to efficiently support partitioned tables and indexes and tablespace-relative data block addresses (DBAs) without ambiguity. Character values representing ROWIDs in Oracle and earlier releases are as follows: block.row.file In Oracle8i, this kind of ROWID is called a restricted ROWID. Extended ROWIDs: The Oracle8i extended ROWID datatype stored in a user column includes the data in the Oracle ROWID, plus a data object number. The data object number is an identification number assigned to every database segment. You can retrieve the data object number from data dictionary views USER_OBJECTS, DBA_OBJECTS, and ALL_OBJECTS.

Jayashree S

Page 42 of 127

Oracle SQL

A supplied package, DBMS_ROWID, is used for interpreting Oracle8i extended ROWID contents. The package functions extract and provide information that would be available directly from an Oracle ROWID. You can use functions from the DBMS_ROWID package like any built-in SQL function. User-Defined Types User-defined datatypes use Oracle built-in datatypes and other user-defined datatypes as the building blocks of types that model the structure and behavior of data in applications. Object Types: Object types are abstractions of the real-world entities-for example, purchase orders-that application programs deal with. An object type is a schema object with three kinds of components: • A name, which serves to identify the object type uniquely within that schema. • Attributes, which are built-in types or other user-defined types. Attributes model the structure of the real world entity. • Methods, which are functions or procedures written in PL/SQL and stored in the database, or written in a language like C and stored externally. Methods implement operations the application can perform on the real world entity. Varrays An array is an ordered set of data elements. All elements of a given array are of the same datatype. Each element has an index, which is a number corresponding to the element's position in the array. Nested Tables: A nested table is like an object table without the object identifiers. It has a single column, and the type of that column is a built-in type or an object type. If an object type, the table can also be viewed as a multi-column table, with a column for each attribute of the object type. When a nested table appears as the type of a column in a relational table or as an attribute of the underlying object type of an object table, Oracle stores all of the nested table data in a single table, which it associates with the enclosing relational or object table. Nulls •

If a column in a row has no value, then column is said to be null, or to contain a null. Nulls can appear in columns of any datatype that are not restricted by NOT NULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful.

Jayashree S

Page 43 of 127

Oracle SQL

•

Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future versions of Oracle.

•

Do not use null to represent a value of zero, because they are not equivalent. Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand.

•

Nulls in SQL Functions: All scalar functions (except NVL and TRANSLATE) return null when given a null argument. The NVL function can be used to return a value when a null occurs. For example, the expression NVL(COMM,0) returns 0 if COMM is null or the value of COMM if it is not null. Most group functions ignore nulls. For example, consider a query that averages the five values 1000, null, null, null, and 2000. Such a query ignores the nulls and calculates the average to be (1000+2000)/2 = 1500.

•

Nulls with Comparison Operators: To test for nulls, only use the comparison operators IS NULL and IS NOT NULL. If you use any other operator with nulls and the result depends on the value of the null, the result is UNKNOWN.

Pseudocolumns A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. The Pseudocolumns supported are: • CURRVAL and NEXTVAL • LEVEL • ROWID • ROWNUM ROWID For each row in the database, the ROWID pseudocolumn returns a row's address. ROWID values contain information necessary to locate a row: • the data object number of the object • which data block in the data file • which row in the data block (first row is 0) • which data file (first file is 1). The file number is relative to the tablespace. Usually, a ROWID value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same ROWID.

Jayashree S

Page 44 of 127

Oracle SQL

ROWID values have several important uses: • They are the fastest way to access a single row. • They can show you how a table's rows are stored. • They are unique identifiers for rows in a table. Example : SELECT ROWID, ename FROM emp WHERE deptno = 20 ROWNUM •

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

•

You can use ROWNUM to limit the number of rows returned by a query, as in this example: SELECT * FROM emp WHERE ROWNUM < 10

•

You can also use ROWNUM to assign unique values to each row of a table, as in this example: UPDATE tabx SET col1 = ROWNUM

•

Oracle assigns a ROWNUM value to each row as it is retrieved, before rows are sorted for an ORDER BY clause, so an ORDER BY clause normally does not affect the ROWNUM of each row. However, if an ORDER BY clause causes Oracle to use an index to access the data, Oracle may retrieve the rows in a different order than without the index, so the ROWNUMs may differ than without the ORDER BY clause.

CURRVAL and NEXTVAL •

A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in SQL statements with these pseudocolumns: CURRVAL returns the current value of a sequence. NEXTVAL increments the sequence and returns the next value.

Jayashree S

Page 45 of 127

Oracle SQL

•

You must qualify CURRVAL and NEXTVAL with the name of the sequence: sequence.CURRVAL sequence.NEXTVAL : CREATE SEQUENCE cust_seq; SELECT cust_seq.NEXTVAL FROM dual;

LEVEL •

For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root node, 2 for a child of a root, and so on. A root node is the highest node within an inverted tree. A child node is any non-root node. A parent node is any node that has children. A leaf node is any node without children. Example :

Family A

B D

C E

F

G H

I

J

Parent A A B B C C G G G

Child B C D E F G H I J

SELECT parent, child, level FROM family START WITH parent = ‘A’ CONNECT BY parent = PRIOR child; Output:

Jayashree S

Parent A B B A C C G G G

Child B D E C F G H I J

Level 1 2 2 1 2 2 3 3 3

Page 46 of 127

Oracle SQL

SELECT parent, child, level FROM family START WITH child = ‘H’ CONNECT BY PRIOR parent = child; Output: Parent Child G H C G A C

Level 1 2 3

Example : In the emp table, to find the staff members at successively lower tiers in the organization : SELECT PRIOR ename || ‘ manages ‘ || ename || ‘ on tier’ || TO_CHAR(LEVEL – 1) FROM emp START WITH empno = 7839 CONNECT BY PRIOR empno = mgr;

Jayashree S

Page 47 of 127

Oracle SQL

Creating, Altering, and Dropping Tables Topics • • • • •

Creating a Table Constraints in CREATE TABLE Dropping a Table Altering a Table Sample Tables

Jayashree S

Page 48 of 127

Oracle SQL

Creating a Table : CREATE TABLE Command • •

Tables are created using the CREATE TABLE command Tables are owned by the user who creates them

•

Syntax CREATE TABLE table ({column datatype [DEFAULT expr] [column_constraint | table_constraint]} [,{column datatype [DEFAULT expr] [column_constraint | table_constraint]}]...) [CLUSTER cluster (column [, column] ...)] [AS query]

•

DEFAULT specifies a value to be assigned to the column if a row is inserted without a value for this column. The value can be a simple literal or the result of an expression CLUSTER includes this table in the named cluster

•

Constraints in CREATE TABLE Constraints limit the values entered into the columns of the table There are two ways to specify constraints - As part of the column definition: a column constraint - At the end of the CREATE TABLE statement: a table constraint Types Of Constraints: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK

• •

The NOT NULL constraint •

The NOT NULL constraint specifies that a column cannot contain nulls. To satisfy this constraint, every row in the table must contain a value for the column.

Example : NOT NULL Constraints CREATE TABLE dept (deptno NUMBER(2) NOT NULL, dname VARCHAR2(9) CONSTRAINT NNL_DNAME NOT NULL, loc VARCHAR2(10) ); /* column constraints */ The UNIQUE Constraint Jayashree S

Page 49 of 127

Oracle SQL

•

The UNIQUE constraint designates a column or combination of columns as a unique key. To satisfy a UNIQUE constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain nulls.

•

A unique key column cannot be of datatype LONG or LONG RAW. You cannot designate the same column or combination of columns as both a unique key and a primary key or as both a unique key and a cluster key. However, you can designate the same column or combination of columns as both a unique key and a foreign key.

Example: UNIQUE Constraints CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(9) CONSTRAINT unq_dname UNIQUE, loc VARCHAR2(10) ); /* column constraint */ CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(9), loc VARCHAR2(10), CONSTRAINT unq_dname UNIQUE (dname) ); /* table constraint */ PRIMARY KEY Constraints •

A PRIMARY KEY constraint designates a column or combination of columns as the table's primary key. To satisfy a PRIMARY KEY constraint, both of the following conditions must be true:

•

No primary key value can appear in more than one row in the table.

•

No column that is part of the primary key can contain a null.

•

A table can have only one primary key.

•

A primary key column cannot be of datatype LONG or LONG RAW. You cannot designate the same column or combination of columns as both a primary key and a unique key or as both a primary key and a cluster key. However, you can designate the same column or combination of columns as both a primary key and a foreign key.

Jayashree S

Page 50 of 127

Oracle SQL

Example: PRIMARY KEY Constraint CREATE TABLE dept (deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY, dname VARCHAR2(9), loc VARCHAR2(10) ); /* column constraint */ CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(9), loc VARCHAR2(10), CONSTRAINT pk_dept PRIMARY KEY (deptno) ); /* table constraint */

CHECK Constraints •

The CHECK constraint explicitly defines a condition. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null). For information on conditions, see the syntax description of condition. The condition of a CHECK constraint can refer to any column in the table, but it cannot refer to columns of other tables.

Example: CHECK Constraints CREATE TABLE dept (deptno NUMBER(4) CHECK (deptno BETWEEN 10 AND 99), dname VARCHAR2(9) CHECK (dname = UPPER(dname)), loc VARCHAR2(10) CONSTRAINT check_loc CHECK (loc IN ('DALLAS','BOSTON', 'NEW YORK','CHICAGO')) ); Example: CHECK Constraints CREATE TABLE emp (empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2), CHECK (sal + comm <= 5000)

Jayashree S

Page 51 of 127

Oracle SQL

); /* table constraint */ Referential Integrity Constraints •

A referential integrity constraint designates a column or combination of columns as a foreign key and establishes a relationship between that foreign key and a specified primary or unique key, called the referenced key. In this relationship, the table containing the foreign key is called the child table and the table containing the referenced key is called the parent table

•

The child and parent tables must be on the same database.

•

The foreign key and the referenced key can be in the same table. In this case, the parent and child tables are the same.

•

Before you define a referential integrity constraint in the child table, the referenced UNIQUE or PRIMARY KEY constraint on the parent table must already be defined.

Example: Referential Integrity Constraints CREATE TABLE emp (empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) CONSTRAINT fk_deptno REFERENCES dept(deptno) ); /* column constraint */ CREATE TABLE emp (empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2), FOREIGN KEY deptno REFERENCES dept(deptno) );

Jayashree S

Page 52 of 127

Oracle SQL

Maintaining Referential Integrity with the ON DELETE CASCADE Option • If you use the ON DELETE CASCADE option, Oracle permits deletions of referenced key values in the parent table and automatically deletes dependent rows in the child table to maintain referential integrity. • Example: CREATE TABLE emp (empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) CONSTRAINT fk_deptno REFERENCES dept(deptno) ON DELETE CASCADE ); Dropping a Table: DROP TABLE DROP TABLE is used to remove a table and all its data from the database Syntax: DROP TABLE table_name CASCADE CONSTRAINTS; Examples: DROP TABLE emp; DROP TABLE dept CASCADE CONSTRAINTS; • •

•

CASCADE CONSTRAINTS drops all referential integrity constraints that refer to primary and unique keys in the dropped table. If you omit this option and such referential integrity constraints exist, Oracle returns an error message and does not drop the table .

When you Drop a Table : • When you drop a table, Oracle also automatically performs the following operations: - Oracle removes all rows from the table (as if the rows were deleted). - Oracle drops all the table's indexes, regardless of who created them or whose schema contains them. - If the table is a base table for views or if it is referenced in stored procedures, functions, or packages, Oracle invalidates these objects but does not drop them. You cannot use these objects unless you recreate the table or drop and recreate the objects so that they no longer depend on the table.

Jayashree S

Page 53 of 127

Oracle SQL

Altering a table •

You can alter the definition of a table in one of the following ways: - adding a column - adding an integrity constraint - redefining a column (datatype, size, default value) - dropping an integrity constraint or trigger

ALTER TABLE Command • Syntax ALTER TABLE table { [ADD ( { column_element | table_constraint } [, column_element | table_constraint }] ... )] [ MODIFY (column_element | column_constraint [, column_element | column_constraint] ... )] [ DROP drop]... Examples ALTER TABLE emp ADD (grade CHAR(1) NOT NULL); ALTER TABLE emp MODIFY (grade NULL, sal NUMBER(10,2)); • •

ADD allows you to add a new column to the end of an existing table, or add a constraint to the table’s definition MODIFY changes an existing column, with some restrictions: • You may change the type of column or decrease its size only if every row for the column is NULL • A NOT NULL column may be added to a table with no rows • An existing column can be modified to NOT NULL only if it has a non-NULL value in every row • Increasing the length of a NOT NULL column without specifying NULL will leave it NOT NULL • Views that reference a table with SELECT * FROM … will not work after a column has been added to the table unless they are dropped and recreated

Integrity Constraints in ALTER TABLE The only type of integrity constraint that you can add to an existing column using the MODIFY clause with the column constraint syntax is a NOT NULL constraint. However, you can define other types of integrity constraints (UNIQUE, PRIMARY KEY, referential integrity, and CHECK constraints) on existing columns using the ADD clause and the table constraint syntax.

Jayashree S

Page 54 of 127

Oracle SQL

You can define a NOT NULL constraint on an existing column only if the column contains no nulls. DROP clause • To remove an integrity constraint or trigger from the database. • The DROP clause can appear in an ALTER TABLE statement. • You can drop an integrity constraint by naming it in a DROP clause of an ALTER TABLE statement. When you drop an integrity constraint, Oracle stops enforcing the integrity constraint and removes it from the data dictionary. • You cannot drop a unique or primary key that is part of a referential integrity constraint without also dropping the foreign key. You can drop the referenced key and the foreign key together by specifying the referenced key with the CASCADE option in the DROP clause. Examples ALTER TABLE emp ADD (thriftplan NUMBER(7,2), loancode CHAR(1) NOT NULL); ALTER TABLE emp MODIFY (thriftplan NUMBER(9,2)); ALTER TABLE accounts MODIFY (bal DEFAULT 0); ALTER TABLE emp MODIFY (sal NUMBER CONSTRAINT nn_sal NOT NULL); ALTER TABLE census ADD CONSTRAINT unq_city_state UNIQUE (city, state); ALTER TABLE ship_cont ADD PRIMARY KEY (ship_no, container_no); ALTER TABLE phone_calls ADD CONSTRAINT fk_areaco_phoneno FOREIGN KEY (areaco, phoneno) REFERENCES customers(areaco, phoneno); ALTER TABLE dept DROP PRIMARY KEY CASCADE; If you know that the name of the PRIMARY KEY constraint is PK_DEPT, you could also drop it with the following statement: ALTER TABLE dept DROP CONSTRAINT pk_dept CASCADE;

Jayashree S

Page 55 of 127

Oracle SQL

Sample Tables

EMP Table EMPNO 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934

ENAME SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER

JOB CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK

MGR 7902 7698 7698 7839 7698 7839 7839 7566 7698 7788 7698 7566 7782

HIREDATE 17-DEC-80 20-FEB-81 22-FEB-81 02-APR-81 28-SEP- 81 01-MAY-81 09-JUN-81 09-DEC-82 17-NOV-81 08-SEP-81 12-JAN-83 03-DEC-81 03-DEC-81 23-JAN-82

SAL 800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300

COMM 300 500 1400

0

DEPTNO 20 30 30 20 30 30 10 20 10 30 20 30 20 10

DEPT Table DEPTNO 10 20 30 40

Jayashree S

DNAME ACCOUNTING RESEARCH SALES OPERATIONS

LOC NEW YORK DALLAS CHICAGO BOSTON

Page 56 of 127

Oracle SQL

Review Questions 1. The constraints can be defined at _______________________ and _______________ levels on the tables. 2. Constraints on tables can be defined only at the time of creation of tables. (T / F) 3. If you are defining a referential integrity constraint in a child table, the corresponding column in the parent table should have ____________________________________ constraint already. 4. You can drop a table which is referenced by a foreign key in some other table, by using _____________________________ clause in the DROP TABLE command. 5. When you drop a table, the table’s indexes will not be dropped automatically. (T / F) 6. A foreign key in a table cannot reference to a column in the same table. (T / F) 7. You can delete a column from a table using the ALTER TABLE command. (T / F)

Exercises Create the following tables : Salespeople Table SNUM

SNAME

CITY

COMM

1001 1002 1004 1007

STEVE RICHARD ROBERT MIKE

0.12 0.13 0.11 0.15

1003

THOMAS

LONDON SAN JOSE LONDON BARCELON A NEW YORK

0.10

Customers Table CNUM

CNAME

CITY

RATING

SNUM

2001 2002 2003

PERIERA BILL HOFFMA N DONALD JOHN

LONDON ROME SAN JOSE

100 200 200

1001 1003 1002

BERLIN LONDON

300 100

1002 1001

2004 2006

Jayashree S

Page 57 of 127

Oracle SQL

2008 2007

HAROLD ANDREW

SAN JOSE ROME

300 100

1007 1004

Orders Table ONUM

AMT

ODATE

CNUM

SNUM

3001 3003 3002 3005 3006 3009 3007 3008 3010 3011

18.69 767.19 1900.10 5160.45 1098.16 1713.23 75.75 4723.00 1309.95 9891.88

3-OCT-90 3-OCT-90 3-OCT-90 3-OCT-90 3-OCT-90 4-OCT-90 4-OCT-90 5-OCT-90 6-OCT-90 6-OCT-90

2008 2001 2007 2003 2008 2002 2004 2006 2004 2006

1007 1001 1004 1002 1007 1003 1002 1001 1002 1001

Include the following constraints on the tables : Salespeople Table • SNUM is the primary key • SNAME should not be null • COMM should always be less than 1 Customers Table • CNUM is the primary key • CNAME should not be null • SNUM is the foreign key Orders Table • ONUM is the primary key • CNUM is the foreign key • SNUM is the foreign key • AMT should not be greater than or equal to 10000

Jayashree S

Page 58 of 127

Oracle SQL

Indexing, Clustering, and Sequences Topics • • • • • •

Indexes Reverse Key Indexes Bitmap Indexes Index Organized Tables Clusters Sequences

Jayashree S

Page 59 of 127

Oracle SQL

Indexing and clustering are two methods of enhancing performance used by Oracle by providing faster access to the data. Indexes • • • • • •

Indexes provide a fast access path to columns that are indexed. Indexes can also be used to ensure that no duplicate values are entered into a column. Indexes are stored separately from the actual data. With every data manipulation, the appropriate indexes are automatically updated. Indexes are referred to whenever the indexed columns are referenced in the WHERE clause. Syntax: CREATE [UNIQUE] INDEX index ON table (column [ASC|DESC] [, column [ASC|DESC]]...) [NOSORT] A UNIQUE index guarantees that each indexed row is unique on the values of the index columns

•

When you create an index, Oracle fetches and sorts the columns to be indexed, and stores the ROWID along with the index value for each row. Then Oracle loads the index from the bottom up. For example, consider the statement : CREATE INDEX emp_ename ON emp(ename); Oracle sorts the EMP table on the ENAME column. It then loads the index with the ENAME and corresponding ROWID values in this sorted order. When it uses the index, Oracle does a quick search through the sorted ENAME values and then uses the associated ROWID values to locate the rows having the sought ENAME value.

•

Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing: - Values are relatively unique in the column. - There is a wide range of values. - The column contains many nulls, but queries often select all rows having a value.

•

Columns with the following characteristics are less suitable for indexing: - The column has few distinct values (for example, a column for the sex of employees). - There are many nulls in the column and you do not search on the non-null values.

•

ORACLE itself creates indexes at the time of table creation if a table definition contains a PRIMARY key or UNIQUE constraint

Jayashree S

Page 60 of 127

Oracle SQL

•

Internally, Oracle uses B*Tree indexes that are balanced to equalize access times to any row.

Figure : Internal Structure of a B*-Tree Index ADAMS ALLEN


BLAKE CLARK FORD

BLAKE ROWID CLARK ROWID

JAMES JONES
KING MARTIN

KING MILLER TURNER

MILLER SCOTT SMITH

TURNER WARD

Jayashree S

Page 61 of 127

Oracle SQL

Reverse Key Indexes Creating a reverse key index, compared to a standard index, reverses the bytes of each column indexed (except the rowid) while keeping the column order. Such an arrangement can help avoid performance degradation in indexes in an Oracle Parallel Server environment where modifications to the index are concentrated on a small set of leaf blocks. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index. Example: • Creating a reverse key index: CREATE INDEX i ON t (a,b,c) REVERSE; •

You can specify the keyword NOREVERSE to REBUILD a reverse-key index into one that is not reverse keyed: ALTER INDEX i REBUILD NOREVERSE;

Bitmap Indexes What Is a Bitmap Index? •

Oracle provides five indexing schemes: B*-tree indexes (currently the most common), B*-tree cluster indexes, hash cluster indexes, reverse key indexes, and bitmap indexes. These indexing schemes provide complementary performance functionality.

•

The purpose of an index is to provide pointers to the rows in a table that contain a given key value. • In a regular index, this is achieved by storing a list of rowids for each key corresponding to the rows with that key value. (The Oracle Server stores each key value repeatedly with each stored rowid.) • In a bitmap index, a bitmap for each key value is used instead of a list of rowids. Each bit in the bitmap corresponds to a possible rowid, and if the bit is set, it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a regular index even though it uses a different representation internally. If the number of different key values is small, bitmap indexes are very space efficient.

Jayashree S

Page 62 of 127

Oracle SQL

•

Bitmap indexes are not suitable for OLTP applications with large numbers of concurrent transactions modifying the data. These indexes are primarily intended for decision support (DSS) in data warehousing applications where users typically query the data rather than update it.

•

Cardinality: The advantages of using bitmap indexes are greatest for low cardinality columns: that is, columns in which the number of distinct values is small compared to the number of rows in the table. If the values in a column are repeated more than a hundred times, the column is a candidate for a bitmap index. Note: B*-tree indexes are most effective for high-cardinality data: that is, data with many possible values, such as CUSTOMER_NAME or PHONE_NUMBER.

Bitmap Index Example: The following table shows a portion of a company's customer data. CUSTOMER # 101 102 103 104 105 106

MARITAL_ STATUS Single Married Married Divorced Single Married

REGION

GENDER

East Central West West Central Central

Male Female Female Male Female Female

INCOME_ LEVEL Bracket_1 Bracket_4 Bracket_2 Bracket_4 Bracket_2 Bracket_3

Since MARITAL_STATUS, REGION, GENDER, and INCOME_LEVEL are all lowcardinality columns it is appropriate to create bitmap indexes on these columns. Table: Bitmap index for the REGION column. It consists of three separate bitmaps, one for each region. REGION = ‘East’ REGION = ‘Central’ 1 0 0 1 0 0 0 0 0 1 0 1

Jayashree S

REGION = ‘West’ 0 0 1 1 0 0

Page 63 of 127

Oracle SQL

An analyst investigating demographic trends of the company's customers might ask, "How many of our married customers live in the central or west regions?" This corresponds to the following SQL query: SELECT COUNT(*) FROM CUSTOMER WHERE MARITAL_STATUS = 'Married' AND REGION IN ('Central','West'); Bitmap indexes can process this query with great efficiency by merely counting the number of ones in the resulting bitmap, as illustrated in the following figure: STATUS = ‘Married’ REGION = ‘Central’ 0 (0 1 1 1 0 0 AND 0 OR 0 1 1 1

REGION = ‘West’ 0 0 1 1 0 0)

=

0 1 1 0 0 1

Bitmap Indexes on Partitioned Tables Like other indexes, you can create bitmap indexes on partitioned tables. The only restriction is that bitmap indexes must be local to the partitioned table -- they cannot be global indexes. Index-Organized Tables •

An index-organized table differs from a regular table in that the data for the table is held in its associated index. Changes to the table data, such as adding new rows, updating rows, or deleting rows, result only in updating the index.

•

The index-organized table is like a regular table with an index on one or more of its columns, but instead of maintaining two separate storages for the table and the B*tree index, the database system only maintains a single B*-tree index which contains both the encoded key value and the associated column values for the corresponding row. Rather than having row ROWID as the second element of the index entry, the actual data row is stored in the B*-tree index.

•

Applications manipulate the index-organized table just like a regular table, using SQL statements. However, the database system performs all operations by manipulating the corresponding B*-tree index.

Jayashree S

Page 64 of 127

Oracle SQL

Comparison of Index-Organized Tables with Regular Tables: Regular Table ROWID uniquely identifies a row; primary key can be optionally specified Implicit ROWID column; allows building physical secondary indexes ROWID based access Sequential scan returns all rows

Index-Organized Table Primary key uniquely identifies a row; primary key must be specified No implicit ROWID column; cannot have physical secondary indexes Primary key based access Full-index scan returns all rows in primary key order UNIQUE constraint and triggers allowed UNIQUE constraint not allowed (Oracle8i restriction) but triggers are allowed A table can be stored in a cluster containing An index-organized table cannot be stored other tables. in a cluster. Distribution, Replication, and Partitioning Distribution, Replication, and Partitioning supported not supported (Oracle8i restriction) Index-Organized Table Operations 1. Creating Index-Organized Tables: The CREATE TABLE statement has the following options for creating indexorganized tables: • An ORGANIZATION INDEX qualifier to indicate that this is an index-organized table. • A primary key specified through a column constraint clause (for a single column primary key) or a table constraint clause (for a multiple column primary key). A primary key must be specified for index-organized tables. • An optional row overflow specification clause that specifies physical attributes of the overflow area. The data row will be placed in the overflow tablespace if its size exceeds the threshold. The threshold is specified as percentage of the block size. Example: CREATE TABLE docindex ( token char(20), doc_oid integer, token_frequency smallint, token_occurrence_data varchar(512), CONSTRAINT pk_docindex PRIMARY KEY (token, doc_oid)) ORGANIZATION INDEX TABLESPACE text_collection PCTTHRESHOLD 20 OVERFLOW TABLESPACE text_collection_overflow; 2. Manipulating Index-Organized Tables: No syntax changes are required to manipulate index-organized tables. However, for index-organized tables the rows are stored in the B*-tree itself and these rows do not Jayashree S

Page 65 of 127

Oracle SQL

have a row identity (ROWID). Thus, index-organized table does not support implicit ROWID column and hence you cannot perform ROWID based-retrieval on indexorganized tables. Examples: INSERT INTO docindex VALUES (`Gypsy',234,1,'17'); SELECT * FROM docindex; UPDATE docindex SET doc_oid=237 WHERE token = `Gypsy'; DELETE FROM docindex WHERE token = `Gypsy'; Clusters •

Clusters are an optional method of storing table data. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.

•

Because clusters store related rows of different tables together in the same data blocks, two primary benefits are achieved when clusters are properly used: - Disk I/O is reduced and access time improves for joins of clustered tables. - In a cluster, a cluster key value (that is, the related value) is only stored once, no matter how many rows of different tables contain the value. Therefore, less storage may be required to store related table data in a cluster than is necessary in non-clustered table format.

•

Clustered tables must have a common column (or group of columns) called the cluster key.

•

Once you create a cluster, tables can be created in the cluster. However, before you can insert any rows into the clustered tables, you must create a cluster index. The use of clusters does not affect the creation of additional indexes on the clustered tables; you can create and drop them as usual.

•

After a cluster is created, this cluster definition is specified in the CLUSTER clause of the CREATE TABLE command

•

Syntax: CREATE CLUSTER cluster (column datatype [, column datatype]...) [INDEX|[HASH IS column] HASHKEYS integer];

The Cluster Index You must create an index on the cluster key columns after you have created a cluster. A cluster index is an index defined specifically for a cluster. Such an index contains an entry for each cluster key value. To locate a new row in a cluster, the cluster index is used to find the cluster key value, which points to the data block associated with that cluster key value. Therefore, Oracle accesses a given row with a minimum of two I/Os.

Jayashree S

Page 66 of 127

Oracle SQL

You must create a cluster index before you can execute any DML statements against the clustered tables. Example: Clustering CREATE CLUSTER emp_dept (deptno NUMBER(3));

CREATE TABLE dept ( deptno NUMBER(3) PRIMARY KEY, ...) CLUSTER emp_dept (deptno);

CREATE CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, ename VARCHAR2(15) NOT NULL, ... deptno NUMBER(3) REFERENCES dept) CLUSTER emp_dept (deptno);

CREATE INDEX emp_dept_index ON CLUSTER emp_dept; Note: A cluster index cannot be unique. Furthermore, Oracle is not guaranteed to enforce uniqueness of columns in the cluster key if they have UNIQUE or PRIMARY KEY constraints.

Jayashree S

Page 67 of 127

Oracle SQL

Figure : Clustered and Unclustered Tables Cluster Key (DEPTNO) 10

20

EMP Table

DNAME SALES

LOC BOSTON

EMPNO 1000 1321 1841

ENAME SMITH JONES WARD

DNAME ADMIN

LOC NEW YORK

EMPNO 932 1139 1277

ENAME KEHR WILSON NORMAN

… … … …

… … … …

Clustered Tables Related data stored together, more efficiently

Jayashree S

EMPNO 932 100 1139 1277 1321 1841

ENAME KEHR SMITH WILSON NORMAN JONES WARD

DEPTNO … 20 … 10 … 20 … 20 … 10 … 10 …

DEPT Table DEPTNO DNAME LOC . 10 SALES BOSTON 20` ADMIN NEW YORK

Unclustered Tables Related data stored apart, taking up more space

Page 68 of 127

Oracle SQL

Dropping Clusters, Clustered Tables, and Cluster Indexes: •

When you drop a cluster, the tables within the cluster and the corresponding cluster index are dropped; all extents belonging to both the cluster's data segment and the index segment of the cluster index are returned to the containing tablespace and become available for other segments within the tablespace.

•

You can individually drop clustered tables without affecting the table's cluster, other clustered tables, or the cluster index.

•

You can drop a cluster index without affecting the cluster or its clustered tables. However, you cannot use a clustered table if it does not have a cluster index.

•

To drop a cluster that contains no tables, as well as its cluster index, if present: DROP CLUSTER emp_dept;

•

If the cluster contains one or more clustered tables and you intend to drop the tables as well, add the INCLUDING TABLES option of the DROP CLUSTER command, as in DROP CLUSTER emp_dept INCLUDING TABLES; If you do not include the INCLUDING TABLES option, and the cluster contains tables, an error is returned.

•

If one or more tables in a cluster contain primary or unique keys that are referenced by FOREIGN KEY constraints of tables outside the cluster, you cannot drop the cluster unless you also drop the dependent FOREIGN KEY constraints. Use the CASCADE CONSTRAINTS option of the DROP CLUSTER command, as in DROP CLUSTER emp_dept INCLUDING TABLES CASCADE CONSTRAINTS;

Hash Clusters: •

A hash cluster is used to store individual tables or a group of clustered tables that are static and often queried by equality queries. Once you create a hash cluster, you can create tables.

•

Hashing is an optional way of storing table data to improve the performance of data retrieval. To use hashing, create a hash cluster and load tables into the cluster. Oracle physically stores the rows of a table in a hash cluster and retrieves them according to the results of a hash function. A hash cluster stores related rows together in the same data blocks. Rows in a hash cluster are stored together based on their hash value. ( In contrast, an index cluster stores related rows of clustered tables based on each row’s cluster key value.)

Jayashree S

Page 69 of 127

Oracle SQL

•

Example: CREATE CLUSTER trial_cluster (trialno NUMBER(5,0)) HASH IS trialno HASHKEYS 100000;

CREATE TABLE trial ( trialno NUMBER(5) PRIMARY KEY, ...) CLUSTER trial_cluster (trialno); Specifying a User-Defined Hash Function You can also specify any SQL expression as the hash function for a hash cluster. If your cluster key values are not evenly distributed among the cluster, you should consider creating your own hash function that more efficiently distributes cluster rows among the hash values. For example, if you have a hash cluster containing employee information and the cluster key is the employee's home area code, it is likely that many employees will hash to the same hash value. To alleviate this problem, you can place the following expression in the HASH IS clause of the CREATE CLUSTER command: MOD((emp.home_area_code + emp.home_prefix + emp.home_suffix), 101) The expression takes the area code column and adds the phone prefix and suffix columns, divides by the number of hash values (in this case 101), and then uses the remainder as the hash value. The result is cluster rows more evenly distributed among the various hash values. Sequences •

A sequence is a database object used to generate unique integers for use as primary keys

•

Syntax: CREATE SEQUENCE seqname [INCREMENT BY n] [START WITH m] [MAXVALUE integer | NOMAXVALUE ] [MINVALUE integer | NOMINVALUE ] [CYCLE | NOCYCLE ] [CACHE integer | NOCACHE ]

Jayashree S

Page 70 of 127

Oracle SQL

•

The default INCREMENT BY is 1. A positive number will cause ascending increment and a negative number will descent.

•

The default START WITH is MAXVALUE for descending sequences and MINVALUE for ascending; use START WITH to override this default.

•

The default for MINVALUE for ascending sequences is 1.

•

The default for MAXVALUE for descending sequences is -1.

•

To restart a sequence where its MINVALUE or MAXVALUE made it begin, specify CYCLE.

•

CACHE allows a preallocated set of sequence numbers to be kept in memory. The default is 20.

•

seqname.CURRVAL returns the current value of the sequence

•

seqname.NEXTVAL returns the next value of the sequence. Also increments the value

Example: CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 20; Example: Referencing a Sequence INSERT INTO orders (orderno, custno) VALUES (order_seq.NEXTVAL, 1032); UPDATE orders SET orderno = order_seq.NEXTVAL WHERE orderno = 10112; •

Only a single sequence number can be generated per row; that is, if NEXTVAL is referenced more than once in a single statement, the first reference generates the next number and all subsequent references in the statement return the same number. •

Once a sequence number is generated, the sequence number is available only to the session that generated the number. Independent of transactions committing or rolling back, other users referencing ORDER_SEQ.NEXTVAL obtain unique values. If two users are accessing the same sequence concurrently, the sequence numbers each user receives might have gaps because sequence numbers are also being generated by the other user.

Jayashree S

Page 71 of 127

Oracle SQL

Review Questions 1. Internally the Oracle indexes are stored as ____________________________ 2. Indexes are created by Oracle automatically on tables which contain ___________________________________________________________ constraints. 3. You can query the index, but cannot modify its contents. (T / F) 4. A cluster is _________________________________________________________ ___________________________________________________________________ 5. Clusters, like indexes, can be created on any existing tables which have a common column. (T / F) 6. Bit map indexes are generally created for high-cardinality columns. (T / F) 7. You cannot perform ROWID based retrieval on index-organized tables. (T / F) 8. Sequences can be used to generate both the ascending and descending lists of numbers. (T / F)

Jayashree S

Page 72 of 127

Oracle SQL

Exercises Create the following tables as clustered tables : ITEMMAST ITTRAN ITNO ITEMNO NAME TRANTYPE QOH (Quantity on hand) TRANQTY CLASS TRANDATE UOM (Unit of measurement) UPDT ROL (Re-order level) ROQ (Re-order quantity) RATE •

Include the following additional constraints :
CLASS can one of : A, B or C
UOM can be only : KGS, MTS, LTS, PCS, or DOZ
ROL and ROQ should always be > 1
The default value for ROL is to be 10
TRANTYPE can be only : I or R (corresponding to ‘issue’ or ‘receipt’ respectively)

•

Create a sequence itno_seq, which starts the counting from 1000, and will be used to insert the ITNO value into the table ITEMMAST.

Jayashree S

Page 73 of 127

Oracle SQL

Changing Data : Inserting, Updating and Deleting Data Topics • • •

The INSERT Command The UPDATE Command The DELETE Command

Jayashree S

Page 74 of 127

Oracle SQL

The INSERT Command •

INSERT Command is used to add rows to a table or to a view's base table.

•

Syntax : INSERT INTO table [ ( column [, column ]… ) ] { VALUES ( expression [, expression ]… ) | query }

•

An INSERT statement with a VALUES clause adds a single row to the table.

•

An INSERT statement with a subquery instead of a VALUES clause adds to the table all rows returned by the subquery.

Examples INSERT INTO dept VALUES (50, 'PRODUCTION', 'SAN FRANCISCO'); INSERT INTO emp (empno, ename, job, sal, comm, deptno) VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40); INSERT INTO bonus SELECT ename, job, sal, comm FROM emp WHERE comm > 0.25 * sal OR job IN ('PRESIDENT', 'MANAGER'); INSERT INTO emp VALUES (empseq.nextval, 'LEWIS', 'CLERK', 7902, SYSDATE, 1200, NULL, 20); Inserting Values : Parameter Substitution Parameter substitution provides an easy way to enter data into a table. The ‘&’ symbol is used as the substitution operator. When the substitution operator is used, SQL*PLUS prompts for the value of the variable, accepts it and then substitutes it in place of the variable. Example : • • •

INSERT INTO emp VALUES ( &1, ‘&2’, ‘&3’, &4, ‘&5’, &6, NULL, &7); This INSERT statement can be used to accept values interactively, and any number of rows can be inserted one after the other by using the “ / “, which executes the last executed SQL statement.

Jayashree S

Page 75 of 127

Oracle SQL

The UPDATE Command •

UPDATE Command is used to change existing values in a table or in a view's base table.

•

Syntax : UPDATE table SET {column = expression [, column = expression]… | ( column [, column]…) = (subquery ) } [ WHERE condition ];

•

The SET clause determines which columns are updated and what new values are stored in them.

•

The WHERE clause determines the rows in which values are updated. If the WHERE clause is not specified, all rows are updated.

Examples • Cancel the commission for all trainees UPDATE emp SET comm = NULL WHERE job = 'TRAINEE'; •

•

Modify the job title, salary and dept. number of JONES UPDATE emp SET job = 'MANAGER', sal = sal + 1000, deptno = 20 WHERE ename = 'JONES'; All employees having more than 2 people reporting to them, are to directly report to the PRESIDENT UPDATE emp SET mgr = 7839 WHERE mgr <> 7839 AND empno IN ( SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(*) > 2 );

Jayashree S

Page 76 of 127

Oracle SQL

The DELETE Command •

The DELETE Command is used to remove rows from a table or from a view's base table.

•

Syntax : DELETE FROM table [WHERE condition ];

Examples DELETE FROM temp_assign; DELETE FROM emp WHERE job = 'SALESMAN' AND comm < 100;

Exercises 1. Insert the values into the Salespeople, Customers, and Orders tables. 2. Insert the values into the ITEMMAST and ITTRAN tables. Make use of the itno_seq for inserting values for the ITNO column.

Jayashree S

Page 77 of 127

Oracle SQL

Querying Tables Topics • • •

• • • • • •

The SELECT Statement WHERE Clause SQL Functions • Group Functions • Row Functions ORDER BY Clause GROUP BY Clause Joins : Querying Multiple Tables Set Operators Nested Queries Using Special operators in sub-queries

Jayashree S

Page 78 of 127

Oracle SQL

The SELECT statement •

Syntax: SELECT * | [DISTINCT] column [, column]... FROM table [, table]... [WHERE condition] [GROUP BY column [, column]...] [HAVING condition] [ORDEY BY expr];

Examples • To display the information of all employees SELECT * FROM emp; •

To list employee names and their salaries SELECT ename, sal FROM emp;

WHERE Clause WHERE clause is used for conditional retrieval of rows Operators used to specify the conditions in WHERE clause Relational operators: = > < >= <= <> Logical operators: AND OR NOT Special operators: IS [NOT] NULL IN BETWEEN LIKE Examples: WHERE clause • To list the employees belonging to the department 30 SELECT * FROM emp WHERE deptno = 30; • •

•

To list the name and salary of the employees whose salary is more than 2000 SELECT ename, sal FROM emp WHERE sal > 2000;

•

To list empno and name of managers SELECT empno, ename FROM emp WHERE job = ‘MANAGER’;

•

To list names of clerks working in department 10 SELECT ename FROM emp WHERE job = ‘CLERK’ AND deptno = 10; To list employee details whose salary is between 1000 and 2000

•

Jayashree S

Page 79 of 127

Oracle SQL

SELECT * FROM emp WHERE sal >= 1000 AND sal <=2000; •

To list employee details whose salary is between 1000 and 2000 SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000;

•

To list the names of analysts and salesmen SELECT ename FROM emp WHERE job = ‘SALESMAN’ OR job = ‘ANALYST’;

•

To list name and salary of the employees belonging to dept 10 or 20 SELECT ename, sal FROM emp WHERE deptno IN (10, 20);

•

To list details of employees who have joined before the end of August 81 SELECT * FROM emp WHERE hiredate <= TO_DATE(‘31-AUG-81’, ‘DD-MONYY’);

•

To list names of employees who are not managers SELECT ename FROM emp WHERE job <> ‘MANAGER’;

•

List different job titles available in emp table SELECT DISTINCT job FROM emp;

•

To list employees who are not getting any commission SELECT ename FROM emp WHERE comm IS NULL;

•

To list employees belonging to some dept SELECT ename FROM emp WHERE deptno IS NOT NULL;

•

To list the employees whose names start with ‘SM’ SELECT ename FROM emp WHERE ename LIKE ‘SM%’;

•

To list employee names having ‘I’ as the second character SELECT ename FROM emp WHERE ename LIKE ‘_I%’;

•

To list any employees with the pattern ‘A_B’ in their name SELECT ename FROM emp WHERE ename LIKE ‘A%\_B%’ ESCAPE ‘\’;

Jayashree S

Page 80 of 127

Oracle SQL

Expressions and Aliases in columns • Arithmetic computations can be done on numeric columns. • The aggregate functions can also be used with an arithmetic expression. • Alias names can be given to columns and/or expressions on query outputs. • Alias names are displayed in place of column names. • Alias names are given to the right of a column name, enclosed within quotes. Example : •

To list name, salary and PF amount which is calculated as 10%, of all employees SELECT ename, sal, sal * .1 “PF” FROM emp;

SQL Functions A SQL function is similar to an operator in that it manipulates data items and returns a result. SQL functions differ from operators in the format in which they appear with their arguments. This format allows them to operate on zero, one, two, or more arguments : Function(argument, argument, …) SQL functions are of these general types : Single row (or scalar) functions Group (or aggregate) functions The two types of SQL functions differ in the number of rows upon which they act. A single row function returns a single result row for every row of a queried table or view, while a group function returns a single result row for a group of queried rows. GROUP (or Aggregate ) functions to summarize data • The Group functions produce a single value for an entire GROUP or table • In all group functions NULLs are ignored • Different Group functions : - COUNT - SUM - MAX - MIN - STDDEV - VARIANCE - AVG Examples: Group functions to summarize data • To list the number of employees working for the company SELECT COUNT(*) FROM emp; • To list the number of jobs available in the emp table SELECT COUNT(DISTINCT job) FROM emp;

Jayashree S

Page 81 of 127

Oracle SQL

•

To list the total salaries paid to employees in dept 10 SELECT SUM(sal) FROM emp WHERE deptno = 10;

•

To list the maximum salary of employee working as a clerk SELECT MAX(sal) FROM emp WHERE job = ‘CLERK’;

•

To list the average salary and no. of employees working in dept 30 SELECT AVG(sal), COUNT(*) FROM emp WHERE deptno = 20;

Single Row ( or Scalar) Functions Number Functions ABS(value) CEIL(value) COS(value) COSH(value) EXP(value) FLOOR(value) LN(value) LOG(value) MOD(value) NVL(value, substitute)

POWER(value, exponent) ROUND(value, precision) SIGN(value) SIN(value) SINH(value) SQRT(value) TAN(value) TANH(value) TRUNC(value, precision)

Examples : Number Functions SELECT ABS(-15) “Absolute” FROM DUAL; Absolute ---------15 SELECT CEIL(15.7) “Ceiling” FROM DUAL; Ceiling ---------16 SELECT FLOOR(15.7) “Floor” FROM DUAL; Floor ---------15 SELECT MOD(11,4) “Modulus” FROM DUAL; Modulus ---------3 SELECT POWER(3,2) “Raised” FROM DUAL;

Jayashree S

Page 82 of 127

Oracle SQL

Raised ---------9 SELECT ROUND(15.193,1) “Round” FROM DUAL; Round ---------15.2 SELECT ROUND(15.193,-1) “Round” FROM DUAL; Round ---------20 SELECT SIGN(-20) “Sign” FROM DUAL; Sign ----------1 SELECT TRUNC(15.79,1) “Truncate” FROM DUAL; Truncate ---------15.7 SELECT TRUNC(15.79,-1) “Truncate” FROM DUAL; Truncate ---------10 String Functions String || String ASCII(string) CHR(integer) CONCAT(string1, string2) INITCAP(string) INSTR(string, set[,start[,occurrence]]) LENGTH(string) LOWER(string)

LPAD(string, length[,’set’]) LTRIM(string[,’set’]) RPAD(string, length[,’set’]) RTRIM(string[,’set’]) SOUNDEX(string) SUBSTR(string, start[, count]) UPPER(string)

Examples : String Functions SELECT ename || ‘, ‘ || job FROM emp; SELECT ASCII(‘A’), ASCII(‘B’) FROM DUAL; SELECT CHR(77), CHR(46), CHR(56) FROM DUAL;

Jayashree S

Page 83 of 127

Oracle SQL

SELECT LOWER(ename), UPPER(ename), INITCAP(LOWER(ename)) FROM emp; SELECT ename, LENGTH(ename) FROM emp; SELECT RPAD(‘HELLO’, 24, ‘WORLD’) FROM DUAL; SELECT RPAD(ename, 35, ‘.’), deptno FROM emp; SELECT LPAD(ename, 15), sal FROM emp; SELECT RTRIM(ename, ‘ ‘) FROM emp; SELECT SUBSTR(hiredate, 4) FROM emp; SELECT INSTR(ename, ‘I’) FROM emp; SELECT loc FROM dept WHERE SOUNDEX(loc)=SOUNDEX(‘Sidney’); Date Functions ADD_MONTHS(date,count) GREATEST(date1, date2, date3, …) LEAST(date1, date2, date3, …) LAST_DAY(date) MONTHS_BETWEEN(date2, date1) NEXT_DAY(date, ‘day’) NEW_TIME(date, ‘this’, ‘other’) ROUND(date, ‘format’) TRUNC(date, ‘format’) TO_CHAR(date, ‘format’) TO_DATE(string, ‘format’)

Jayashree S

Page 84 of 127

Oracle SQL

Date Formats (With both TO_CHAR and TO_DATE) Format Example/Description MM 12 RM XII MON DEC MONTH DECEMBER DDD 354 DD 23 D 6 DY FRI DAY FRIDAY YYYY 1982 SYYYY -1000 YYY 982 YY 82 Y 2 YEAR NINETEEN-FORTY-SIX Q 3 (Number of quarter) WW 46 W 3 J Julian days since Dec 31, 4713 BC HH 11 HH12 Same as HH HH24 17 MI 58 SS 43 SSSS 43000 (0-83699) /,-:. Punctuation for TO_CHAR A.M P.M AM or PM B.C A.D BC or AD

Jayashree S

Page 85 of 127

Oracle SQL

Date Formats (Only with TO_CHAR) Format Example/Description “string” “is” fm Suppresses padding of Month or Day in format TH DdTH or DDTH gives: 24th or 24TH SP DDSP, DdSP, or ddSP gives: THREE, Three, or three SPTH DdSPTH gives: Third THSP Same as SPTH Time Zones Time Zone AST/ADT BST/BDT CST/CDT EST/EDT GMT HST/HDT MST/MDT NST PST/PDT YST/YDT

Description Atlantic Standard / Daylight Time Bering Standard / Daylight Time Central Standard / Daylight Time Eastern Standard / Daylight Time Greenwich Mean Time Alaska-Hawaii Standard / Daylight Time Mountain Standard / Daylight Time Newfoundland Standard Time Pacific Standard / Daylight Time Yukon Standard / Daylight Time

Examples : Date Functions SELECT SYSDATE FROM DUAL; SELECT ADD_MONTHS(SYSDATE, 6) FROM DUAL; SELECT ADD_MONTHS(SYSDATE, -6) FROM DUAL; SELECT LEAST(TO_DATE(‘20-JAN-98’),TO_DATE(‘20-DEC-98’)) FROM DUAL; SELECT NEXT_DAY(SYSDATE, ‘FRIDAY’) FROM DUAL; SELECT LAST_DAY(hiredate) “END MONTH” FROM emp; SELECT SYSDATE TODAY, LAST_DAY(ADD_MONTHS(SYSDATE, 6))+1 REVIEW FROM DUAL; SELECT ename, MONTHS_BETWEEN(SYSDATE, hiredate)/12 “EXPERIENCE” FROM emp; SELECT hiredate, TO_CHAR(hiredate,’DD/MM/YYYY’) FROM emp; SELECT hiredate, TO_CHAR(hiredate, ‘fmMonth, ddTH, YYYY)

Jayashree S

Page 86 of 127

Oracle SQL

“FORMATTED” FROM emp; SELECT ename, TO_CHAR(hiredate, ‘”JOINED ON” fmMonth ddth, YYYY, “AT” HH:MI’) “FORMATTED” FROM emp; SELECT hiredate, NEW_TIME(hiredate, ‘EST’, ‘HST’) FROM emp; SELECT TO_CHAR(TO_DATE(‘20-JAN-98’), ‘DAY’) FROM DUAL

Other Functions Function GREATEST (expr [,expr]…) LEAST (expr [,expr]…) NVL (expr1, expr2) VSIZE (expr) DECODE (value, if1, then1 then2]… else)

[, if2,

Description Returns the greatest of the list of expressions Returns the least of the list of expressions If expr1 is null, returns expr2; if expr1 is not null, returns expr1 Returns the number of bytes in the internal representation of expr If the value is if1 then the result of the DECODE is then1; if it is if2 then the result is then2, and so on; if the value equals none of the ifs, then the result is else

Examples : Other Functions SELECT GREATEST (‘HARRY’, ‘HARRIOT’, ‘HAROLD’) “GREATEST” FROM DUAL; SELECT ename, NVL(TO_CHAR(comm), ‘NOT APPLICABLE’) “COMMISSION” FROM emp WHERE deptno = 30; SELECT ename, sal + comm, sal + NVL(comm, 0) FROM emp; SELECT ename, VSIZE(ename) “BYTES” FROM emp; SELECT ename, DECODE(deptno, 10, ‘ACCOUNTING’, 20, ‘RESEARCH’, 30, ‘SALES’, 40, ‘OPERATION’, ‘NONE’) FROM emp;

Jayashree S

Page 87 of 127

Oracle SQL

ORDER BY Clause •

It is used to order the results of a query.

•

You can specify the column, and/or expression position also in the ORDER BY clause.

Examples : •

To list the employee details in descending order of salary SELECT ename, sal FROM emp ORDER BY sal DESC;

•

To list the employee name, salary, PF, HRA, DA and gross, ordering the result in ascending order of gross. HRA is 50% of salary and DA is 30% of salary. SELECT ename, sal, sal * .1 “PF”, sal * .5 “HRA”, sal *.3 “DA”, sal + (sal * .5) + (sal * .3) + (sal * .1) “GROSS” FROM emp ORDER BY 6;

GROUP BY Clause •

It is used with SELECT to combine a group of rows based on the values of a particular column or expression.

•

The aggregate functions are applied to the individual groups.

•

Conditional retrieval of rows from a grouped result is possible with the HAVING clause.

Examples : • To list the number of employees working in each department SELECT deptno, COUNT(*) FROM emp; •

To list the jobs and the total salaries payable to each job title SELECT job, SUM(sal) FROM emp GROUP by job ORDER BY 2 DESC;

•

To list the the jobs and the total salaries payable to each job title only if the total salary is > 5000 SELECT job, SUM(sal) FROM emp GROUP by job HAVING SUM ( sal ) > 5000;

Jayashree S

Page 88 of 127

Oracle SQL

Querying Multiple Tables : Joins •

Joins are used to combine columns from different tables.

• -

Types of joins : Equi Join Cartesian Join Outer Join Self Join

Examples : •

Equi Join : To list the employee numbers, names, department numbers and the department names SELECT empno, ename, emp.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno;

•

Cartesian Join : Consider the tables faculty(name) course(course_name) canhandle(faculty, course) To find the possible combinations of faculty and course SELECT * FROM faculty, course;

•

Outer Join : Display the list of employees working in each department. Display the department information even if no employee belongs to that department. SELECT empno,ename,emp.deptno, dname, loc FROM emp, dept WHERE emp.deptno ( + ) = dept.deptno; Note : The + cannot be placed on both the sides of the equation.

•

Self Join : To display the employee names and the manager names in the emp table SELECT WORKER.ename, MANAGER.ename FROM emp WORKER, emp MANAGER WHERE WORKER.mgr = MANAGER.empno;

Jayashree S

Page 89 of 127

Oracle SQL

SET Operators •

SET operators combine two or more queries into one result.

•

SET operators : - UNION : Rows of first query plus rows of second query, less duplicate rows - INTERSECT : Common rows from all queries - MINUS : Rows unique to the first query

Examples : •

UNION : To display the different designations in department 20 and 30 SELECT job FROM emp WHERE deptno = 20 UNION SELECT job FROM emp WHERE deptno = 30;

•

INTERSECT : To list the jobs common to department 20 and 30 SELECT job FROM emp WHERE deptno = 20 INTERSECT SELECT job FROM emp WHERE deptno = 30;

•

MINUS : To list the jobs unique to department 20 SELECT job FROM emp WHERE deptno = 20 MINUS SELECT job FROM emp WHERE deptno = 10 MINUS SELECT job FROM emp WHERE deptno = 30;

Jayashree S

Page 90 of 127

Oracle SQL

Nested Queries •

The result of one query is substituted dynamically in the condition of another.

•

SQL first evaluates the sub query within the WHERE clause. The return value is then substituted in the condition of the outer query.

•

When using relational operators, ensure that the sub query returns a single row output.

Examples: •

To list the names of employees drawing the highest salary SELECT ename FROM emp WHERE sal = ( SELECT MAX ( sal ) FROM emp );

•

To list the names of employees who have got an increment SELECT ename FROM emp WHERE empno IN ( SELECT empno FROM incr );

Nested Queries : Correlated subquery •

In a correlated sub query, the used in the outer query refers to the table used in the inner sub query. The sub query is executed repeatedly, once for each row of the outer query table.

Example : To list the employee names who have got more than one increment SELECT empno, ename FROM emp WHERE 1 < ( SELECT COUNT( * ) FROM incr WHERE empno = emp.empno ); Using Special Operators in Sub queries •

Special operators : - EXISTS : TRUE if a subquery returns at least one row. - ANY/SOME : Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to FALSE if the query returns no rows. - ALL : Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to TRUE if the query returns no rows.

Jayashree S

Page 91 of 127

Oracle SQL

Examples : •

To list the employee details only if more than 10 employees are present in dept 10 SELECT * FROM emp WHERE deptno = 10 AND EXISTS ( SELECT deptno, COUNT( * ) FROM emp WHERE deptno = 10 GROUP BY deptno HAVING COUNT ( * ) > 10 );

•

To list the employee names whose salary is greater than the lowest salary of an employee in dept 20 SELECT ename FROM emp WHERE sal > ANY ( SELECT sal FROM emp WHERE deptno = 20 );

•

To list the employee names whose salary is greater than the highest salary of an employee in dept 20 SELECT ename FROM emp WHERE sal > ALL ( SELECT sal FROM emp WHERE deptno = 20 );

More Examples: •

SELECT * FROM emp WHERE sal = ANY (SELECT sal FROM emp WHERE deptno = 30);

•

SELECT * FROM emp WHERE sal >= ALL (1400, 3000)

•

SELECT dname, deptno FROM dept WHERE EXISTS (SELECT * from emp WHERE dept.deptno = emp.deptno);

Jayashree S

Page 92 of 127

Oracle SQL

Exercises Refer to the Salespeople, Customers, and Orders Tables. Write queries for the following : 1. List the Salespeople table. 2. List the name and commission of all salespeople in London. 3. List all customers who were either located in San Jose or had a rating above 200. 4. Find all salespeople that were located in either Barcelona or London. 5. Extract from the Salespeople table, all salespeople with commission ranging from 0.10 to 0.12. 6. Display all salespeople and customers located in London. 7. Write a query that produces all rows from the Customers table for which the salesperson’s number is 1001. 8. Find all customers with NULL values in their city column. 9. Write a query that produces the rating followed by the name of each customer in San Jose. 10. Write a query that will give all orders for more than $1000. 11. Write a query that will give the names and cities of all salespeople in London with a rating <=100, unless the customer are located in Rome. 12. Write a query that will give names and cities of all salespeople in London with a commission above 0.10. 13. Write a query in two ways, that will produce all orders taken on October 3rd or 4th, 1990. 14. Write a query that selects all of the customers serviced by Steve or Robert. 15. Write a query that will produce all of the customers whose names begin with a letter from A to G. 16. Write a query that selects all orders except those with zeroes or NULLs in the Amount field. 17. Write a query that counts all orders for October 3rd. 18. Write a query that counts the number of different non-NULL city values in the Customers table. 19. Write a query that selects the highest rating in each city.

Jayashree S

Page 93 of 127

Oracle SQL

20. Write a query that selects the first customer, in alphabetical order whose name begins with the letter H. 21. Count the number of salespeople currently listing orders in the Orders table. 22. Find the largest order taken by each salesperson. 23. List the largest order taken by each salesperson on each date. 24. Show the names of all customers matched with the salespeople serving them. 25. Find all pairs of customers having the same rating. 26. Display all orders of the salesperson Robert. 27. Find all orders credited to the same salesperson that services Periera. 28. Display all orders that are greater than the average for October 4th. 29. Find all orders attributed to salespeople in London. 30. Display the commissions of all salespeople servicing customers in London. 31. Find salespeople with customers located in their cities. 32. Output only those customers whose ratings are higher than every customer in Rome. 33. Write a query that selects each customer’s smallest order. 34. Write a query that counts the number of salespeople registering orders for each day (if a salesperson has more than one order on a given day, he should be counted only once). 35. Write a query on the orders table that will produce the order number, the salesperson number and the amount of commission, for that order. 36. Write a query on the Customers table that will find the highest rating in each city. The output should be of the form : For the city city, the highest rating is rating 37. Write a query that lists customers in descending order of rating. Output the rating field first, followed by the customer’s name and number. 38. Write a query that totals the orders for each day and places the results in descending order. 39. Write a query that lists each order number followed by the name of the customer who made the order. 40. Write a query that gives the names of both the salesperson and the customer for each order after the order number.

Jayashree S

Page 94 of 127

Oracle SQL

41. Write a query that produces all customers serviced by salespeople with a commission above 12%. Output the customer’s name, the salesperson name, and the salesperson’s rate of commission. 42. Write a query that calculates the amount of the salespersons’ commission on each order by a customer with a rating above 100. 43. Write a query that uses a sub-query to obtain all orders for the customer named Harold. 44. Write a query that produces the names and ratings of all customers who have above average orders. 45. Write a query that selects the total amount in orders for each salesperson for who this total is greater than the amount of the largest order in the table. 46. Write a query that uses the EXISTS operator to extract all salespeople who have customers with a rating of 300. Solve this problem using a JOIN also. 47. Write a query that will find all salespeople who have no customers located in their city.

Jayashree S

Page 95 of 127

Oracle SQL

Views Topics • • • • • • • • • •

What is a View? How Views are Used Creating Views Creating Views with Errors Replacing Views Using Views Modifying a Join View Partition Views The ALTER VIEW Command Dropping a View

Jayashree S

Page 96 of 127

Oracle SQL

What is a View? •

A view is a logical table that allows you to access data from other tables and views. A view contains no data itself. The tables upon which a view is based are called base tables.

•

A view gives a tailored presentation of the data contained in one or more tables (or other views).

•

A view takes the output of a query and treats it as a table; therefore a view can be thought of as a “stored query” or a “virtual table”. You can use views in most places where a table can be used.

•

You can query views, and with some restrictions, users can update, insert into, and delete from the views. All operations performed on a view actually affect data in the base tables of the view and are subject to the integrity constraints and triggers of the base tables.

How Views are Used Views are used for the following purposes: • to provide an additional level of table security by restricting access to a predetermined set of rows and/or columns of a table For example, following figure shows how the STAFF view does not show the SAL or COMM columns of the base table EMP. •

to hide data complexity For example, a single view might be defined with a join, which is a collection of related columns or rows in multiple tables. However, the view hides the fact that this information actually originates from several tables.

•

to simplify commands for the user For example, views allow users to select information from multiple tables without actually knowing how to perform a join.

•

to present the data in a different perspective from that of the base table For example, the columns of a view can be renamed without affecting the tables on which the view is based.

•

to isolate applications from changes in definitions of base tables For example, if a view's defining query references three columns of a four column table and a fifth column is added to the table, the view's definition is not affected and all applications using the view are not affected.

Jayashree S

Page 97 of 127

Oracle SQL

•

to express a query that cannot be expressed without using a view For example, a view can be defined that joins a GROUP BY view with a table, or a view can be defined that joins a UNION view with a table.

•

to save complex queries For example, a query could perform extensive calculations with table information. By saving this query as a view, the calculations can be performed each time the view is queried.

•

to achieve improvements in availability and performance For example, a database administrator can divide a large table into smaller tables (partitions) for many reasons, including partition level load, purge, backup, restore, reorganization, and index building. Once partition views are defined, users can query partitions, rather than very large tables. This ability to prune unneeded partitions from queries increases performance and availability.

Figure : An Example of a View Table EMP EMPN ENAM O E 7329 SMITH 7499 ALLE N 7521 WARD

MGR HIREDAT SAL E CLERK 7902 17-DEC-87 300.00 SALESMA 7698 20-FEB-88 300.00 N SALESMA 7698 22-FEB-88 5.00 N MANAGE 7839 02-APR-88 R JOB

7566

JONES

EMPN O 7329 7499

ENAM JOB E SMITH CLERK ALLEN SALESM AN WARD SALESM AN JONES MANAGE R

7521 7566

MGR DEPTN O 7902 20 7698 30 7698

30

7839

20

COM DEPTN M O 800.00 20 1600.0 30 0 1250.0 30 0 2975.0 20 0

View STAFF

Creating Views • Use the SQL command CREATE VIEW to create a view. You can define views with any query that references tables, snapshots, or other views; however, the query that defines a view cannot contain the ORDER BY or FOR UPDATE clauses. Jayashree S

Page 98 of 127

Oracle SQL

•

Syntax : CREATE [ OR REPLACE ] [ FORCE | NOFORCE ] VIEW view [ ( alias [, alias ]… ) ] AS query [ WITH CHECK OPTION [ CONSTRAINT constraint ] ] [ WITH READ ONLY ]

•

The OR REPLACE option re-creates the view if it already exists.

•

The FORCE option creates the view regardless of whether the tables to which the view refers exist or whether the user has privileges on them. The user still can’t execute the view, but he/she can create it.

•

The NOFORCE option creates the view only if the base tables exist and the user has the privileges on them.

•

alias specifies names for the expressions selected by the view's query. The number of aliases must match the number of expressions selected by the view. If you omit the aliases, Oracle derives them from the columns or column aliases in the view's query. Hence, you must use aliases if the view's query contains expressions rather than only column names.

•

AS subquery identifies columns and rows of the table(s) that the view is based on. A view's query can be any SELECT statement without the ORDER BY or FOR UPDATE clauses.

•

WITH READ ONLY specifies that no deletes, inserts, or updates can be performed through the view.

•

WITH CHECK OPTION specifies that inserts and updates performed through the view must result in rows that the view query can select. The CHECK OPTION cannot make this guarantee if there is a subquery in the query of this view or any view on which this view is based.

•

CONSTRAINT is the name assigned to the CHECK OPTION constraint. If you omit this identifier, Oracle automatically assigns the constraint a name of this form: SYS_Cn, where n is an integer that makes the constraint name unique within the database.

•

You can define a view with a query that uses an asterisk (*) to select all the columns of a table: CREATE VIEW emp_vu AS SELECT * FROM emp ;

Examples : Views CREATE VIEW dept20

Jayashree S

Page 99 of 127

Oracle SQL

AS SELECT ename, sal*12 annual_salary FROM emp WHERE deptno = 20

Jayashree S

Page 100 of 127

Oracle SQL

CREATE VIEW clerk (id_number, person, department, position) AS SELECT empno, ename, deptno, job FROM emp WHERE job = 'CLERK' WITH CHECK OPTION CREATE VIEW clerk (id_number, person, department, position) AS SELECT empno, ename, deptno, job FROM emp WHERE job = 'CLERK' WITH READ ONLY Creating Views with Errors •

•

•

Assuming no syntax errors, a view can be created (with errors) even if the defining query of the view cannot be executed. For example, if a view is created that refers to a non-existent table or an invalid column of an existing table, or if the owner of the view does not have the required privileges, the view can still be created and entered into the data dictionary. You can only create a view with errors by using the FORCE option of the CREATE VIEW command: CREATE FORCE VIEW AS ...; When a view is created with errors, Oracle returns a message that indicates the view was created with errors. The status of such a view is left as INVALID. If conditions later change so that the query of an invalid view can be executed, the view can be recompiled and become valid. Oracle dynamically compiles the invalid view if you attempt to use it.

Replacing Views To alter the definition of a view, you must replace the view using one of the following methods: •

A view can be dropped and then re-created.

•

A view can be replaced by redefining it with a CREATE VIEW statement that contains the OR REPLACE option.

Jayashree S

Page 101 of 127

Oracle SQL

•

•

Example: CREATE OR REPLACE VIEW sales_staff AS SELECT empno, ename, deptno FROM emp WHERE deptno = 30 WITH CHECK OPTION CONSTRAINT sales_staff_cnst; Replacing a view has the following effects: - Replacing a view replaces the view's definition in the data dictionary. All underlying objects referenced by the view are not affected. - If previously defined but not included in the new view definition, the constraint associated with the WITH CHECK OPTION for a view's definition is dropped. - All views and PL/SQL program units dependent on a replaced view become invalid.

Using Views •

Views can be queried in the same manner as tables. For example, to query the SALES_STAFF view, enter a valid SELECT statement that references the view: SELECT * FROM sales_staff;

•

With some restrictions, rows can be inserted into, updated in, or deleted from a base table using a view. The following statement inserts a new row into the EMP table using the SALES_STAFF view: INSERT INTO sales_staff VALUES (7954, 'OSTER', 30);

• 1.

2.

3. 4.

Restrictions on DML operations for views use the following criteria in the order listed: If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, rows cannot be inserted into, updated in, or deleted from the base tables using the view. If a view is defined with the WITH CHECK OPTION, a row cannot be inserted into, or updated in, the base table (using the view) if the view cannot select the row from the base table. If a NOT NULL column that does not have a DEFAULT clause is omitted from the view, a row cannot be inserted into the base table using the view. If the view was created by using an expression, such as DECODE(deptno, 10, 'SALES', ...), rows cannot be inserted into or updated in the base table using the view.

Jayashree S

Page 102 of 127

Oracle SQL

Modifying a Join View • •

The Oracle Server allows you, with some restrictions, to modify views that involve joins. If you create a view that involves a join operation, such as: CREATE VIEW emp_dept AS SELECT e.empno, e.ename, e.deptno, d.dname, d.loc FROM emp e, dept d /* JOIN operation */ WHERE e.deptno = d.deptno AND d.loc IN ('DALLAS', 'NEW YORK', 'BOSTON'); then there are restrictions on modifying either the EMP or the DEPT base table through this view, for example, using a statement such as: UPDATE emp_dept SET ename = 'JOHNSON' WHERE ename = 'SMITH';

•

A modifiable join view is a view that contains more than one table in the top-level FROM clause of the SELECT statement, and that does not contain any of the following: - DISTINCT operator - aggregate functions: AVG, COUNT, MAX, MIN, STDDEV, SUM, or VARIANCE - set operations: UNION, UNION ALL, INTERSECT, MINUS - GROUP BY or HAVING clauses - START WITH or CONNECT BY clauses - ROWNUM pseudocolumn

Key-Preserved Tables: The concept of a key-preserved table is fundamental to understanding the restrictions on modifying join views. A table is key preserved if every key of the table can also be a key of the result of the join. So, a key-preserved table has its keys preserved through a join. Note: It is not necessary that the key or keys of a table be selected for it to be key preserved. It is sufficient that if the key or keys were selected, then they would also be key(s) of the result of the join. In the EMP_DEPT view, EMP is a key-preserved table, because EMPNO is a key of the EMP table, and also a key of the result of the join. DEPT is not a key-preserved table, because although DEPTNO is a key of the DEPT table, it is not a key of the join.

Jayashree S

Page 103 of 127

Oracle SQL

Rule for DML Statements on Join Views: Any UPDATE, INSERT, or DELETE statement on a join view can modify only one underlying base table. UPDATE Statements •

•

•

The following example shows an UPDATE statement that successfully modifies the EMP_DEPT view: UPDATE emp_dept SET ename = 'JOHNSON' WHERE ename = 'SMITH'; The following UPDATE statement would be disallowed on the EMP_DEPT view: UPDATE emp_dept SET loc = 'BOSTON' WHERE ename = 'SMITH'; because it attempts to modify the underlying DEPT table, and the DEPT table is not key preserved in the EMP_DEPT view. If the view is defined using the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are not modifiable. So, for example, if the EMP_DEPT view were defined using WITH CHECK OPTION, the following UPDATE statement would fail: UPDATE emp_dept SET deptno = 10 WHERE ename = 'SMITH';

DELETE Statements •

You can delete from a join view provided there is one and only one key-preserved table in the join. The following DELETE statement works on the EMP_DEPT view: DELETE FROM emp_dept WHERE ename = 'SMITH';

•

In the following view, a DELETE operation cannot be performed on the view because both E1 and E2 are key-preserved tables: CREATE VIEW emp_emp AS SELECT e1.ename, e2.empno, deptno FROM emp e1, emp e2 WHERE e1.empno = e2.empno;

Jayashree S

Page 104 of 127

Oracle SQL

•

If a view is defined using the WITH CHECK OPTION clause and the key-preserved table is repeated, then rows cannot be deleted from such a view. For example: CREATE VIEW emp_mgr AS SELECT e1.ename, e2.ename mname FROM emp e1, emp e2 WHERE e1.mgr = e2.empno WITH CHECK OPTION; No deletion can be performed on this view because the view involves a self-join of the table that is key preserved.

INSERT Statements •

The following INSERT statement on the EMP_DEPT view succeeds: INSERT INTO emp_dept (ename, empno, deptno) VALUES ('KURODA', 9010, 40); because only one key-preserved base table is being modified (EMP), and 40 is a valid DEPTNO in the DEPT table (thus satisfying the FOREIGN KEY integrity constraint on the EMP table).

•

An INSERT statement such as INSERT INTO emp_dept (ename, empno, deptno) VALUES ('KURODA', 9010, 77); would fail for the same reason that such an UPDATE on the base EMP table would fail: the FOREIGN KEY integrity constraint on the EMP table is violated.

•

An INSERT statement such as INSERT INTO emp_dept (empno, ename, loc) VALUES (9010, 'KURODA', 'BOSTON'); would fail with an ORA-01776 error ("cannot modify more than one base table through a view").

•

An INSERT cannot, implicitly or explicitly, refer to columns of a non-key-preserved table. If the join view is defined using the WITH CHECK OPTION clause, then you cannot perform an INSERT to it.

Using the UPDATABLE_COLUMNS Views: Three views are available to assist you in using the capability of modifying join views. These views are defined in the following table: View Name USER_UPDATABLE_COLUMNS

Description Shows all columns in all tables and views in the user's schema that are modifiable.

DBA_UPDATABLE_COLUMNS

Shows all columns in all tables and views in the DBA schema that are modifiable.

Jayashree S

Page 105 of 127

Oracle SQL

ALL_UPDATABLE_VIEWS

Shows all columns in all tables and views that are modifiable.

Partition Views •

The database administrator can use partition views to divide a very large table into multiple smaller pieces (or partitions) to achieve significant improvements in availability, administration, and performance.

•

The basic idea behind partition views is simple: divide the large table into multiple physical tables using a partitioning criteria; glue the partitions together into a whole for query purposes. A partition view can assign key ranges to partitions. Queries that use a key range to select from a partitions view will access only the partitions that lie within the key range.

Example: Sales data for a calendar year may be broken up into four separate tables, one per quarter: Q1_SALES, Q2_SALES, Q3_SALES and Q4_SALES. A partition view may then be defined by using check constraints or by using WHERE clauses. Partition Views Using Check Constraints ALTER TABLE Q1_SALES ADD CONSTRAINT C0 check (sale_date between'jan-1-1995' and 'mar-31-1995'); ALTER TABLE Q2_SALES ADD CONSTRAINT C1 check (sale_date between 'apr-1-1995' and 'jun-30-1995'); ALTER TABLE Q3_SALES ADD CONSTRAINT C2 check (sale_date between 'jul-1-1995' and 'sep-30-1995'); ALTER TABLE Q4_SALES ADD CONSTRAINT C3 check (sale_date between 'oct-1-1995' and 'dec-31-1995'); CREATE VIEW sales AS SELECT * FROM Q1_SALES UNION ALL SELECT * FROM Q2_SALES UNION ALL SELECT * FROM Q3_SALES UNION ALL SELECT * FROM Q4_SALES; This method has several advantages: • The check constraint predicates are not evaluated per row for queries. • The predicates guard against inserting rows in the wrong partitions. • It is easier to query the dictionary and find the partitioning criteria.

Jayashree S

Page 106 of 127

Oracle SQL

Partition Views Using WHERE Clauses CREATE VIEW sales AS SELECT * FROM Q1_SALES WHERE sale_date between 'jan-1-1995' and 'mar-31-1995' UNION ALL SELECT * FROM Q2_SALES WHERE sale_date between 'apr-1-1995' and 'jun-30-1995' UNION ALL SELECT * FROM Q3_SALES WHERE sale_date between 'jul-1-1995' and 'sep-30-1995' UNION ALL SELECT * FROM Q4_SALES WHERE sale_date between 'oct-1-1995' and 'dec-31-1995'; The drawbacks of this method: • The partitioning predicate is applied at runtime for all rows in all partitions that are not skipped. • If the user mistakenly inserts a row with sale_date = 'apr-4-1995' in Q1_SALES, the row will "disappear" from the partition view. • The partitioning criteria are difficult to retrieve from the data dictionary because they are all embedded in one long view definition. However, using WHERE clauses to define partition views has one advantage over using check constraints: the partition can be on a remote database with WHERE clauses. For example: SELECT * FROM [email protected] WHERE LOC = 'EAST' UNION ALL SELECT * FROM [email protected] WHERE LOC = 'WEST';

Because queries against eastern sales data do not need to fetch any western data, users will get increased performance. This cannot be done with constraints because the distributed query facility does not retrieve check constrains from remote databases. The ALTER VIEW Command •

Syntax : ALTER VIEW view COMPILE

•

ALTER VIEW recompiles a view. You can use ALTER VIEW to explicitly recompile a view that is invalid. Explicit recompilation allows you to locate recompilation errors before runtime. You may want to explicitly recompile a view after altering one of its base tables to ensure that the alteration does not affect the view or other objects that depend on it.

•

Jayashree S

Page 107 of 127

Oracle SQL

•

When you issue the ALTER VIEW command Oracle recompiles the view regardless of whether it is valid or invalid. Oracle also invalidates all dependent objects (procedures, functions, package bodies, and views that reference this view). If you subsequently reference one of these objects without first explicitly recompiling it, Oracle recompiles it implicitly at runtime.

Dropping a View Use the SQL command DROP VIEW to drop a view, as in DROP VIEW sales_staff; Review Questions 1. If modifications are made on a base table on which a view is based, after the view is created, the view does not show the changes. (T / F) 2. You can create a read only view by using the ________________________________ option in the CREATE VIEW command. 3. If you want that any inserts or updates performed through the view result in rows that the view query can select, you use the ______________________________________ clause in the CREATE VIEW command. 4. Only a ________________________________________ table could be modified through a join view, by issuing a DML statement.

Exercises Consider the EMP and DEPT tables, and 1. Create a view that contains the name and department number of all employees belonging to department 30 and 40, who are either clerk or salesman. 2. Create a view that stores the info regarding the total employees working in each department under each job title. 3. Create a view that stores the employee numbers and total salaries (sum of sal and comm) for all the employees. 4. Create a view that holds info of each department number, department name, the name of the last employee hired in that department, and his hire date.

Jayashree S

Page 108 of 127

Oracle SQL

Controlling Transactions Topics • • • • •

What are Transaction Control Commands? The COMMIT Command The ROLLBACK Command The SET TRANSACTION Command The AUTOCOMMIT Feature

Jayashree S

Page 109 of 127

Oracle SQL

What are Transaction Control Commands? •

The Transaction Control commands manage changes made by insert, update or delete commands.

•

A transaction (or a logical unit of work) is a sequence of SQL statements that Oracle treats as a single unit. A transaction begins with the first executable SQL statement after a COMMIT, ROLLBACK or connection to the database. A transaction ends with a COMMIT, ROLLBACK or disconnection (intentional or unintentional) from the database.

•

Transaction control commands : - COMMIT - ROLLBACK - SAVEPOINT - SET TRANSACTION

The COMMIT Command •

COMMIT is used to end your current transaction and make permanent all changes performed in the transaction.

This command also erases all savepoints in the transaction and releases the transaction's locks. Example : INSERT INTO dept VALUES (50, 'MARKETING', 'TAMPA'); COMMIT; •

The ROLLBACK Command •

ROLLBACK is used to undo all changes since the beginning of a transaction or since a savepoint.

Using the ROLLBACK command without the TO SAVEPOINT clause performs the following operations: - ends the transaction - undoes all changes in the current transaction - erases all savepoints in the transaction - releases the transaction's locks Example : UPDATE emp SET sal = sal + sal * .10; ROLLBACK; •

Jayashree S

Page 110 of 127

Oracle SQL

•

Using the ROLLBACK command with the TO SAVEPOINT clause performs the following operations: - rolls back just the portion of the transaction after the savepoint. - loses all savepoints created after that savepoint. - releases all table and row locks acquired since the savepoint.

Example : UPDATE emp SET comm = comm + 100 WHERE deptno = 10; SAVEPOINT s1; DELETE FROM emp WHERE comm < 300; ROLLBACK TO SAVEPOINT s1; The SET TRANSACTION Command

-

SET TRANSACTION command is used to perform one of the following operations on your current transaction: establish your current transaction as either a read-only or a read-write transaction assign your current transaction to a specified rollback segment

•

A SET TRANSACTION statement must be the first statement in your transaction.

•

Syntax : SET TRANSACTION { READ ONLY | READ WRITE | USE ROLLBACK SEGMENT segment }

• -

READ ONLY option establishes the current transaction as a read-only transaction after a transaction has been established as read-only, all subsequent queries in that transaction only see changes committed before the transaction began Read-only transactions are very useful for reports that run multiple queries against one or more tables while other users update these same tables. INSERT, UPDATE, and DELETE statements and SELECT statements with the FOR UPDATE clause are not permitted in READ ONLY transactions any Data Definition Language statement implicitly ends the read-only transaction.

•

-

Jayashree S

Page 111 of 127

Oracle SQL

Example : COMMIT; SET TRANSACTION READ ONLY; SELECT COUNT(*) FROM emp; SELECT COUNT(*) FROM dept; COMMIT ; The last COMMIT statement does not actually make permanent any changes to the database. It ends the read-only transaction. • -

READ WRITE option establishes the current transaction as a read-write transaction.

•

USE ROLLBACK SEGMENT option - Assigns the current transaction to the specified rollback segment. - This option also establishes the transaction as a read-write transaction. - If you issue a Data Manipulation Language statement in a transaction, Oracle assigns the transaction to a rollback segment. - The rollback segment holds the information necessary to undo the changes made by the transaction. - You can issue a SET TRANSACTION statement with the USE ROLLBACK SEGMENT clause to choose a specific rollback segment for your transaction. - If you do not choose a rollback segment, Oracle chooses one randomly and assigns your transaction to it.

The Auto Commit Feature •

SQL *PLUS has the facility to automatically commit your work without your explicitly telling to do so.

•

SET AUTOCOMMIT ON : Sets the auto commit feature ON; OFF is the default.

•

SET AUTOCOMMIT OFF : Turns off the autocommit feature.

Jayashree S

Page 112 of 127

Oracle SQL

Review Questions 1. A transaction can be defined as _________________________________________ __________________________________________________________________ 2. Transaction control commands available in Oracle are _________________________ _____________________________________________________________________ 3. The command used to make all the changes made in the current transaction permanent is __________________________________________ 4. You can undo only a portion of the transaction by _______________________________________________________ command. 5. You issue the command ________________________________________________ to make a transaction read only. 6. If you want the commit to happen automatically after each DML statement execution, the SQL*PLUS command used is _________________________________________

Jayashree S

Page 113 of 127

Oracle SQL

SQL Tuning Approaches to SQL Statement Tuning Restructure the Statements: Consider Alternative SQL Syntax • • •

Because SQL is a flexible language, more than one SQL statement may meet the needs of your application. Although two SQL statements may produce the same result, Oracle may process one faster than the other. You can use the results of the EXPLAIN PLAN statement to compare the execution plans and costs of the two statements and determine which is more efficient.

Example: Consider the following two statements to return all the departments in the DEPT table that have no employees in the EMP table. Each statement searches the EMP table with a subquery. Assume there is an index, DEPTNO_INDEX, on the DEPTNO column of the EMP table. Statement1: SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp); Oracle executes this statement by performing a full table scan of the EMP table despite the index on the DEPTNO column. This full table scan can be a time-consuming operation. Oracle does not use the index because the subquery that searches the EMP table does not have a WHERE clause that makes the index available. Statement2: SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno); The WHERE clause of the subquery refers to the DEPTNO column of the EMP table, so the index DEPTNO_INDEX is used. The index range scan of DEPTNO_INDEX takes less time than the full scan of the EMP table in the first statement. Furthermore, the first query performs one full scan of the EMP table for every DEPTNO in the DEPT table. For these reasons, the second SQL statement is faster than the first. If you have statements in your applications that use the NOT IN operator, as the first query in this example does, you should consider rewriting them so that they use the NOT EXISTS operator. This would allow such statements to use an index, if one exists.

Jayashree S

Page 114 of 127

Oracle SQL

Use Untransformed Column Values Use untransformed column values. For example, use WHERE a.order_no = b.order_no rather than WHERE TO_NUMBER(substr(a.order_no,instr(b.order_no,'.')-1) = TO_NUMBER(substr(a.order_no, instr(b.order_no, '.') - 1) Use Hints to Control Access Paths Use optimizer hints, such as /*+ORDERED */ to control access paths. This is a better approach than using traditional techniques or "tricks of the trade" such as CUST_NO + 0. For example, use SELECT /*+ FULL(EMP) */ E.ENAME FROM EMP E WHERE E.JOB = 'CLERK'; rather than SELECT E.ENAME FROM EMP E WHERE E.JOB || '' = 'CLERK'; Avoid Mixed-Mode Expressions Avoid mixed-mode expressions, and beware of implicit type conversions. When you want to use an index on the VARCHAR2 column charcol, but the WHERE clause looks like this: AND charcol = Oracle will translate that expression into AND to_number(charcol) = numexpr This has the following consequences: • Any expression using a column, such as a function having the column as its argument, will cause the optimizer to ignore the possibility of using an index on that column, even a unique index. • If the system processes even a single row having charcol as a string of characters that does not translate to a number, an error will be returned.

Jayashree S

Page 115 of 127

Oracle SQL

You can avoid this problem by replacing the top expression with the explicit conversion AND charcol = to_char() Use Care When Using IN and NOT IN with a Subquery Remember that WHERE (NOT) EXISTS is a useful alternative. Minimize the use of DISTINCT DISTINCT always creates a SORT; all the data must be instantiated before your results can be returned. Reduce the Number of Calls to the Database When appropriate, use INSERT, UPDATE, or DELETE RETURNING to select and modify data with a single call. This technique improves performance by reducing the number of calls to the database. Use Care When Joining Views The shared SQL area in Oracle reduces the cost of parsing queries that reference views. In addition, optimizer improvements make the processing of predicates against views very efficient. Together these factors make possible the use of views for ad hoc queries. Despite this, joins to views are not recommended, particularly joins from one complex view to another. Do Not Recycle Views Beware of writing a view for one purpose and then using it for other purposes, to which it may be ill-suited. Consider this example: SELECT dname from DX WHERE deptno=10; You can obtain dname and deptno directly from the DEPT table. It would be inefficient to obtain this information by querying the DX view (which was declared earlier in the present example). To answer the query, the view would perform a join of the DEPT and EMP tables, even though you do not need any data from the EMP table.

Jayashree S

Page 116 of 127

Oracle SQL

Using Indexes • • • •

Indexes improve the performance of queries that select a small percentage of rows from a table. As a general guideline, create indexes on tables that are queried for less than 2% or 4% of the table's rows. Building indexes "just in case" is not a good practice; indexes should not be built until required. To maintain optimal performance with indexes, drop indexes that your application is not using. You can find indexes that are not referenced in execution plans by processing all of your application SQL through EXPLAIN PLAN and capturing the resulting plans.

Guidelines for choosing index keys to index • • •

•

•

•

•

•

Consider indexing keys that are frequently used in WHERE clauses. Consider indexing keys that are frequently used to join tables in SQL statements. Index keys that have high selectivity. The selectivity of an index is the percentage of rows in a table having the same value for the indexed key. An index's selectivity is optimal if few rows have the same value. Do not use standard B*-tree indexes on keys or expressions with few distinct values. You can use bitmap indexes effectively in such cases, unless a high concurrency OLTP application is involved. Do not index columns that are frequently modified. UPDATE statements that modify indexed columns and INSERT and DELETE statements that modify indexed tables take longer than if there were no index. Such SQL statements must modify data in indexes as well as data in tables. They also generate additional undo and redo information. Do not index keys that appear only in WHERE clauses with functions or operators. A WHERE clause that uses a function (other than MIN or MAX) or an operator with an indexed key does not make available the access path that uses the index. Consider indexing foreign keys of referential integrity constraints in cases in which a large number of concurrent INSERT, UPDATE, and DELETE statements access the parent and child tables. Such an index allows UPDATEs and DELETEs on the parent table without share locking the child table. When choosing to index a key, consider whether the performance gain for queries is worth the performance loss for INSERTs, UPDATEs, and DELETEs and the use of the space required to store the index. You may want to experiment by comparing the processing times of your SQL statements with and without indexes. You can measure processing time with the SQL trace facility.

Jayashree S

Page 117 of 127

Oracle SQL

Composite Indexes A composite index contains more than one key column. Composite indexes can provide additional advantages over single-column indexes:

Improved selectivity

Sometimes two or more columns or expressions, each with poor selectivity, can be combined to form a composite index with more accurate selectivity.

Reduced I/O

If all columns selected by a query are in a composite index, then Oracle can return these values from the index without accessing the table.

A SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index. A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Consider this CREATE INDEX statement: CREATE INDEX comp_ind ON tab1(x, y, z); These combinations of columns are leading portions of the index: x, xy, and xyz. These combinations of columns are not leading portions of the index: yz, y, and z. Follow these guidelines for choosing keys for composite indexes: • Consider creating a composite index on keys that are frequently used together in WHERE clause conditions combined with AND operators, especially if their combined selectivity is better than the selectivity of either key individually. • If several queries select the same set of keys based on one or more key values, then consider creating a composite index containing all of these keys. Indexing the SELECT and WHERE Consider this query that does not have indexes: SELECT ename FROM emp WHERE deptno = 10; This would use a full table scan. Now, place an index on deptno column and try to improve performance: CREATE INDEX dept_idx1 ON emp(deptno);

Jayashree S

Page 118 of 127

Oracle SQL

SELECT ename FROM emp WHERE deptno = 10; The situation will be even worse! Instead, consider composite (concatenated) index on the columns contained in both the SELECT and the WHERE clauses: DROP INDEX dept_idx1; CREATE INDEX emp_idx1 ON emp(deptno, ename); SELECT ename FROM emp WHERE deptno = 10; The query now will be tremendously faster. The table itself did not have to be accessed. Indexing both the column contained in the SELECT clause and the column in the WHERE clause allows the query to only access the index. Fast Full Index Scans The fast full index scan is an alternative to a full table scan when there is an index that contains all the keys that are needed for the query. A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan. The following query and plan illustrate this feature: SELECT COUNT(*) FROM t1, t2 WHERE t1.c1 > 50 AND t1.c2 = t2.c1; The plan is as follows: SELECT STATEMENT SORT AGGREGATE HASH JOIN TABLE ACCESS t1 FULL INDEX t2_c1_idx FAST FULL SCAN Because index t2_c1_idx contains all columns needed from table t2, the optimizer uses a fast full index scan on that index. Fast full scan has a special index hint, INDEX_FFS, which has the same format and arguments as the regular INDEX hint.

Jayashree S

Page 119 of 127

Oracle SQL

Writing Statements that Avoid Using Indexes In some cases, you may want to prevent a SQL statement from using an access path that uses an existing index. You may want to do this if you know that the index is not very selective and that a full table scan would be more efficient. If the statement contains a construct that makes such an index access path available, then you can force the optimizer to use a full table scan through one of these methods: • You can use the NO_INDEX hint to give the CBO maximum flexibility while disallowing the use of a certain index. • You can use the FULL hint to force the optimizer to choose a full table scan instead of an index scan. • You can use the INDEX, INDEX_COMBINE, or AND_EQUAL hints to force the optimizer to use one index or a set of listed indexes instead of another. Function-based Indexes • •

•

• • • •

A function-based index is an index on an expression. Oracle strongly recommends using function-based indexes whenever possible. Define function-based indexes anywhere that you use an index on a column, except for columns with LOBs or REFs. Nested table columns and object types cannot contain these columns. You must set the QUERY_REWRITE_ENABLED session parameter to true to enable function-based indexes for queries. If QUERY_REWRITE_ENABLED is false, then function-based indexes are not used for obtaining the values of an expression in the function-based index. Function-based indexes are an efficient mechanism for evaluating statements that contain functions in WHERE clauses. You can create a function-based index to materialize computational-intensive expressions in the index. This permits Oracle to bypass computing the value of the expression when processing SELECT and DELETE statements. When processing INSERT and UPDATE statements, however, Oracle evaluates the function to process the statement.

Examples If you create the following index: CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b);

Jayashree S

Page 120 of 127

Oracle SQL

Then, Oracle can use it when processing queries such as: SELECT a FROM table_1 WHERE a + b * (c - 1) < 100; Function-based indexes defined with the UPPER(column_name) or LOWER(column_name) keywords allow case-insensitive searches. For example, the following index: CREATE INDEX uppercase_idx ON emp (UPPER(empname)); Facilitates processing queries such as: SELECT * FROM emp WHERE UPPER(empname) = 'MARK'; Keeping Shared SQL and PL/SQL in the Shared Pool •

• •

Oracle compares SQL statements and PL/SQL blocks issued directly by users and applications, as well as recursive SQL statements issued internally by a DDL statement. If two exact statements are issued, then the SQL or PL/SQL area used to process the first instance of the statement is shared. Shared SQL and PL/SQL areas are shared memory areas. Shared SQL and PL/SQL areas age out of the shared pool according to a "least recently used" (LRU) algorithm, similar to database buffers. To improve performance and prevent reparsing, you may want to prevent large SQL or PL/SQL areas from aging out of the shared pool.

Reserving Space for Large Allocations •

•

A problem can occur if users fill the shared pool, and then a large package ages out. If someone calls the large package back in, then a significant amount of maintenance is required to create space for it in the shared pool. You can avoid this problem by reserving space for large allocations with the SHARED_POOL_RESERVED_SIZE initialization parameter. This parameter sets aside room in the shared pool for allocations larger than the value specified by the SHARED_POOL_RESERVED_SIZE_MIN_ALLOC parameter.

Preventing Objects from Aging Out • •

The DBMS_SHARED_POOL package lets you keep objects in shared memory, so that they do not age out with the normal LRU mechanism. By using the DBMS_SHARED_POOL package, and by loading the SQL and PL/SQL areas before memory fragmentation occurs, the objects can be kept in memory. This ensures that memory is available, and it prevents the sudden,

Jayashree S

Page 121 of 127

Oracle SQL

inexplicable slowdowns in user response time that occur when SQL and PL/SQL areas are accessed after aging out. When to Use DBMS_SHARED_POOL •

• •

The procedures provided with the DBMS_SHARED_POOL package may be useful when loading large PL/SQL objects, such as the STANDARD and DIUTIL packages. When large PL/SQL objects are loaded, user response time is affected. This is because of the large number of smaller objects that need to age out of the shared pool to make room (due to memory fragmentation). In some cases, there may be insufficient memory to load the large objects. DBMS_SHARED_POOL is useful for frequently executed triggers. You may want to keep compiled triggers on frequently used tables in the shared pool. DBMS_SHARED_POOL also supports sequences. Sequence numbers are lost when a sequence ages out of the shared pool. DBMS_SHARED_POOL keeps sequences in the shared pool, thus preventing the loss of sequence numbers.

How to Use DBMS_SHARED_POOL To use the DBMS_SHARED_POOL package to pin a SQL or PL/SQL area, complete the following steps. 1. Decide which packages or cursors to pin in memory. 2. Start up the database. 3. Make the call to DBMS_SHARED_POOL.KEEP to pin your objects. This procedure ensures that your system does not run out of shared memory before the objects are loaded. By pinning the objects early in the life of the instance, you prevent memory fragmentation that could result from pinning a large portion of memory in the middle of the shared pool. Example: DBMS_SHARED_POOL.KEEP('scott.hispackage');

Jayashree S

Page 122 of 127

Oracle SQL

Using EXPLAIN PLAN What is EXPLAIN PLAN? The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. • A statement's execution plan is the sequence of operations Oracle performs to execute the statement. • The components of execution plans include: − An ordering of the tables referenced by the statement. − An access method for each table mentioned in the statement. − A join method for tables affected by join operations in the statement. • EXPLAIN PLAN output shows how Oracle executes SQL statements. EXPLAIN PLAN results alone, however, cannot differentiate between well-tuned statements and those that perform poorly. • For example, if EXPLAIN PLAN output shows that a statement uses an index, then this does not mean the statement runs efficiently. Sometimes using indexes can be extremely inefficient. • It is best to use EXPLAIN PLAN to determine an access plan, and later prove that it is the optimal plan through testing. • When evaluating a plan, always examine the statement's actual resource consumption. For best results, use the Oracle Trace or SQL trace facility and TKPROF to examine individual SQL statement performance. •

Creating the Output Table Before you can issue an EXPLAIN PLAN statement, you must create a table to hold its output. Use one of the following approaches: • Run the SQL script UTLXPLAN.SQL to create a sample output table called PLAN_TABLE in your schema. PLAN_TABLE is the default table into which the EXPLAIN PLAN statement inserts rows describing execution plans. • Issue a CREATE TABLE statement to create an output table with any name you choose. When you issue an EXPLAIN PLAN statement you can direct its output to this table. Any table used to store the output of the EXPLAIN PLAN command must have the same column names and datatypes as the PLAN_TABLE: CREATE TABLE plan_table (statement_id VARCHAR2(30), timestamp DATE, remarks VARCHAR2(80), operation VARCHAR2(30), options VARCHAR2(30), object_node VARCHAR2(128),

Jayashree S

Page 123 of 127

Oracle SQL

object_owner object_name object_instance object_type optimizer search_columns id parent_id position cost cardinality bytes other_tag other

VARCHAR2(30), VARCHAR2(30), NUMERIC, VARCHAR2(30), VARCHAR2(255), NUMERIC, NUMERIC, NUMERIC, NUMERIC, NUMERIC, NUMERIC, NUMERIC, VARCHAR2(255), LONG);

How to Run EXPLAIN PLAN The following example shows a SQL statement and its corresponding execution plan generated by EXPLAIN PLAN. The sample query retrieves names and related information for employees whose salary is not within any range of the SALGRADE table: SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND NOT EXISTS (SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal); This EXPLAIN PLAN statement generates an execution plan and places the output in PLAN_TABLE: EXPLAIN PLAN SET STATEMENT_ID = 'Emp_Sal' FOR SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND NOT EXISTS (SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal);

Jayashree S

Page 124 of 127

Oracle SQL

Selecting PLAN_TABLE Output in Table Format This SELECT statement generates the following output: SELECT operation, options, object_name, id, parent_id, position FROM plan_table WHERE statement_id = 'Emp_Sal' ORDER BY id; OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION COST CARDINALITY BYTES OTHER_TAG OPTIMIZER ----------------------------------------------------------------------------------------------SELECT STATEMENT 0 2 2 1 62 CHOOSE FILTER 1 0 1 NESTED LOOPS 2 1 1 2 1 62 TABLE ACCESS FULL EMP 3 2 1 1 1 40 ANALYZED TABLE ACCESS FULL DEPT 4 2 2 4 88 ANALYZED TABLE ACCESS FULL SALGRADE 5 1 2 1 1 13 ANALYZED Selecting PLAN_TABLE Output in Nested Format This type of SELECT statement generates a nested representation of the output that more closely depicts the processing order used for the SQL statement. SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options ||' '||object_name ||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan" FROM plan_table START WITH id = 0 AND statement_id = 'Emp_Sal' CONNECT BY PRIOR id = parent_id AND statement_id ='Emp_Sal'; Query Plan -----------------------------SELECT STATEMENT Cost = 5 FILTER NESTED LOOPS TABLE ACCESS FULL EMP TABLE ACCESS FULL DEPT

Jayashree S

Page 125 of 127

Oracle SQL

TABLE ACCESS FULL SALGRADE The order resembles a tree structure, illustrated in the following figure.

Jayashree S

Page 126 of 127

Oracle SQL

Bibliography Oracle documentation Oracle Database Concepts Oracle SQL Reference Oracle Database Performance Guide and Reference

***End of Document***

Jayashree S

Page 127 of 127