You can use the CREATE TABLE statement to enforce several different kinds of constraints on a table: candidate keys, primary keys, foreign keys, and check conditions. A CONSTRAINT clause can constrain a single column or group of columns in a table. The point of these constraints is to get Oracle to do most of the work […]
Read More
With Flashback Database, you can rewind the database to a previous point in time without media recovery (that is, without restoring backup copies of the datafiles). When the database is running, Flashback Database buffers and writes before-images of data blocks to flashback logs, which are generated by default for all permanent tablespaces and which reside […]
Read More
As of Oracle Database 11g, you can use the PIVOT and UNPIVOT operators to work with “crosstab” data. In a crosstab report, rows of data are displayed in separate columns. In this query, the second and third columns are the Fred Fuller columns, the fourth and fifth are the Dorah Talbot columns, and so on. […]
Read More
An index is a simple concept. It is typically a listing of keywords accompanied by the location of information on a subject. To find information on indexes, for instance, you look up the word “indexes” in the index at the back of this book. It will give the number of the page you are reading […]
Read More
You can use the NATURAL keyword to indicate that a join should be performed based on all columns that have the same name in the two tables being joined. For example, what titles in BOOK_ORDER match those already in BOOKSHELF? The natural join returned the results as if you had typed in the following: The […]
Read More
An index-organized table keeps its data sorted according to the primary key column values for the table. An index-organized table stores its data as if the entire table was stored in an index. Indexes serve two main purposes: To enforce uniqueness When a PRIMARY KEY or UNIQUE constraint is created, Oracle creates an index to enforce […]
Read More
When you create a partitioned table, you should create an index on the table. The index may be partitioned according to the same range values used to partition the table. In the following listing, the CREATE INDEX command for the BOOKSHELF_LIST_PART (list-partitioned) table is shown. The index partitions are placed in the PART1_NDX_TS and PART2_NDX_TS […]
Read More
Clustering is a method of storing tables that are intimately related and often joined together into the same area on disk. For example, instead of the BOOKSHELF table being in one section of the disk and the BOOKSHELF_AUTHOR table being somewhere else, their rows could be interleaved together in a single area, called a cluster. […]
Read More
Tuning, to a degree, is similar to maintenance. In order to be effective, the effort needs to be continuous. Occasionally you’ll encounter the silver bullet, but these occasions are few and far between. Effective tuning requires routine care involving collecting baselines, removing superfluous tasks, monitoring metrics, modifying parameters or objects, and testing the results. In […]
Read More
To help tune queries that use non-selective columns in their limiting conditions, you can use bitmap indexes. Bitmap indexes should only be used if the data is infrequently updated because they add to the cost of all data-manipulation transactions against the tables they index. NOTE Bitmap indexes should not be used for tables involved in […]
Read More
Oracle uses an optimizer to determine the most efficient way to perform each SQL command. For INSERT statements, Oracle tries to insert each new record into an existing block of data already allocated to the table. This execution plan optimizes the use of space required to store the data. However, it may not provide adequate […]
Read More
The USER pseudo-column is quite useful when you are using remote data access methods. For example, you may not want all remote users to see all rows in a table. To solve this problem, you must think of remote users as special users within your database. To enforce the data restriction, you need to create […]
Read More