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
If you are a user in the LOCAL database shown in Figure 1, you can access objects in the REMOTE database via a database link. To do this, simply append the database link name to the name of any table or view that is accessible to the remote account. When appending the database link name […]
Read More
Traditional (B*-tree) indexes are most useful on columns with a significant amount of variety in their data. For instance, a column that indicates whether a company is a current client with a Y or N value would be a poor choice for a traditional index and could actually slow down a query; a bitmap index […]
Read More