Oracle Dictionary

From Robs_Wiki
Revision as of 15:11, 24 January 2020 by Qadmin wiki (talk | contribs) (R)
Jump to: navigation, search



  • adaptive cursor sharing: Oracle 11g uses Adaptive Cursor Sharing to compare the effectiveness of execution plans between executions with different bind variable values. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement. more on adaptive cursor sharing...
  • bitmap join index: In a bitmap join index, the ROWIDs from one table are stored along with the indexed column from the joined table. The bitmap join index in Oracle is a lot like building a single index across two tables. You must build a primary key or unique constraint on one of the tables. When you are looking for information from just the columns in the index or a count, then you will be able to access the single join index.
  • ASSM (Automatic Segment Space Management): ASSM is a method used by Oracle to manage space inside data blocks. It eliminates the need to specify parameters like PCTUSED, Freelists and Freelist groups for objects created in the tablespace.


  • bind variable peeking: Bind Variable Peeking is a feature introduced in 9i that was added to allow the optimizer to peek at the value of bind variables and then use a histogram to pick an appropriate plan.
  • buffer gets: A buffer get represents the number of times Oracle had to access a block. The reads could have been satisfied either from memory (the buffers) or have resulted in a physical IO.


  • cardinality: Cardinality is the number of rows the optimizer guesses will be processed for a plan step. cardinality for a single column = total # rows / number of distinct values. For example, a table with 100 rows and 10 distinct value has a cardinality of 10. Because Oracle assumes an even distribution if no histograms are being used.
  • column density: Density is a measure of how often duplicate values occur in a column. It is as a measure of the uniqueness of the data in a column: high density --> less unique data. Density values range from 0 to 1.0. There are different (but equivalent) ways to think of density. Density = 1/[# of distinct values in a column] / Density = Avg. number of duplicates for a given value / Total row count


  • deprecated: The database feature is no longer being enhanced but is still fully supported" A well known example is Oracle Streams which is fully supported in Oracle Database 12c - but not in Oracle Multitenant - and is deprecated and therefore not enhanced or developed any further. Query for deprecated parameters: SELECT NAME,VALUE FROM V$PARAMETER WHERE ISDEFAULT='FALSE' AND ISDEPRECATED='TRUE';
  • domain index: The domain index is another feature, first introduced in Oracle 8i as an extensible index. The domain index is used for indexing non-traditional data, such as LOB data, video data and other non-textual columns.
  • driving table: In Oracle, the driving table is the table that is joined to other tables in an SQL query. The driving table is read once and for each row in the driving table, the inner table is processed once. more on driving table...
  • IOT: Index Organized Tables (IOT) have their primary key data and non-key column data stored within the same B*Tree structure. Effectively, the data is stored within the primary key index. There are several reasons to use this type of table.
  • PCTFREE: This parameter is part of a segment creation statement. The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block.
  • PCTUSED: This parameter is part of a segment creation statement. The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block. After a data block is filled to the limit determined by PCTFREE, Oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED.


  • Index clustering factor: The clustering factor records the number of blocks that will be read when scanning the index. If the index being used has a large clustering factor, then more table data blocks have to be visited to get the rows in each index block (because adjacent rows are in different blocks). If the clustering factor is close to the number of blocks in the table, then the index is well ordered, but if the clustering factor is close to the number of rows in the table, then the index is not well ordered.
  • Index Skip Scan: Prior to Oracle 9i, it was not possible to perform an Index Scan (Range or Unique) on a concatenated index unless the WHERE clause included a predicate on the leading column(s) of the index. Index Skip Scan allows Oracle to use an index when the first column is not supplied in the WHERE clause.
  • Inline View: An inline view is a SELECT statement in the FROM-clause of another SELECT statement. See examples_inline_view


  • proxy server: proxy server forward client requests or (reverse proxies) server responses to the server (forward proxy / users (reverse proxy). They can also provide: load balancing, ip masking, traffic scrubbing, firewall services and content caching.


  • real-time apply: Dataguard option that ships changes directly (rather than waiting for the archive to be filled first) from the Primary to the Standby database. Makes use of Standby Redo Log Files. Check the setting with this query: select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;
  • recursive sql: In order to execute a SQL statement issued by a user, Oracle must issue additional statements, for example to check if the queried table exist, if the user has permission on the table, etc. Such statements are called recursive statements or recursive sql.


  • selectivity: The number of rows returned / total number of rows
  • SQL Profile: A SQL profile is a set of auxiliary information specific to a SQL statement. Conceptually, a SQLprofile is to a SQL statement what statistics are to a table or index. The database can use the auxiliary information to improve execution plans. Once implemented, the database creates the profile and stores it persistently in the data dictionary. If a user issues a statement for which a profile has been built, then the query optimizer uses both the environment and the SQL profile to build a well-tuned plan.


  • varchar2 byte versus varchar2 char: If you define the field as VARCHAR2(11 BYTE), Oracle can use up to 11 bytes for storage, but you may not actually be able to store 11 characters in the field, because some of them take more than one byte to store, e.g. non-English characters. By defining the field as VARCHAR2(11 CHAR) you tell Oracle it can use enough space to store 11 characters, no matter how many bytes it takes to store each one. A single character may require up to 4 bytes.