Here are the rules Oracle uses to execute each query, and the order in which execution takes place: Choose rows based on the WHERE clause. Group those rows together based on the GROUP BY clause. Calculate the results of the group functions for each group. Choose and eliminate groups based on the HAVING clause. Order […]
Read More
The issue of whether allowing SQL to do automatic conversion of datatypes is a good practice or not has arguments on either side. On one hand, this practice considerably simplifies and reduces the functions necessary to make a SELECT statement work. On the other hand, if your assumption about what will be in the column […]
Read More
If your applications use only two-digit values for years, you may encounter problems related to years around 2000. If you only specify two digits of a year (such as “98” for “1998”), you are relying on the database to specify the century value (the “19”) when the record is inserted. If you are putting in […]
Read More
Group-value functions in Oracle treat NULL values differently than single-value functions do. Group functions (other than COUNT(*)) ignore NULL values and calculate a result without considering them. Take AVG as an example. Suppose you have a list of 100 friends and their ages. If you picked 20 of them at random and averaged their ages, […]
Read More
It is worthwhile to sum up in a single place all the major issues in designing Oracle database applications—thus “The Commandments” (or perhaps “The Suggestions”). Their presentation does not assume that you need to be told what to do, but rather that you are capable of making rational judgments and can benefit from the experience […]
Read More
One of the often-neglected steps in building software is understanding the end user’s job—the tasks that computer automation is intended to support. Occasionally, this is because the application itself is quite specialized; more often, it is because the approach to design tends to be data oriented. Frequently, these are the major questions asked in the […]
Read More
In Oracle, intelligent keys are so named because they contain nontrivial combinations of information. The term is misleading in the extreme because it implies something positive or worthwhile. A more meaningful term might be overloaded keys. General ledger and product codes often fall into this category and contain all the difficulties associated with other codes, […]
Read More
The basic approach to naming [objects in Oracle application design] is to choose meaningful, memorable, and descriptive readable names, avoiding abbreviations and codes, and using underscores either consistently or not at all. In a large application, table, column, and data names will often be multiword, as in the case of REVERSED_EXPENSE_ACCOUNT or LAST_GL_CLOSE_DATE. The goal […]
Read More
Many of us look forward to the day when we can simply type a “natural” language query in English, and have the answer back, on our screen, in seconds. We are closer to this goal than most of us realize. The limiting factor is no longer technology, but rather the rigor of thought in our […]
Read More
The basic approach to naming is to choose meaningful, memorable, and descriptive readable names, avoiding abbreviations and codes, and using underscores either consistently or not at all. In a large application, table, column, and data names will often be multi-word, as in the case of REVERSED_EXPENSE_ACCOUNT or LAST_GL_CLOSE_DATE. The goal of thoughtful naming methods is […]
Read More
Hybrid columnar compression (HCC) changes the way row data is stored to obtain dramatically better compression ratios, often 10x or more. Rather than storing a series of rows inside a data block, HCC operates on an aggregate group of data blocks called a compression unit. Within the compression unit, HCC-compressed data contains the data for […]
Read More
The Exadata flash cache makes use of flash memory to speed up access to commonly used data. Flash-based solid-state storage has latency times and I/O operation rates much higher than rotating storage, improving performance particularly in transaction-processing applications. The flash cache is stored on flash-memory cards on storage servers. The storage server software manages the […]
Read More