Appendix B. Glossary

 

This glossary contains only terms that specifically are used for SQL optimization. For terms that apply to the subject of SQL in general, consult the 1,000- term glossary on our Web site, ourworld. compuserve .com/homepages/OCELOTSQL/glossary.htm.

Before the definition there may be a "Used by" note. For example, "Used by: Microsoft, Sybase" indicates that Microsoft and Sybase authorities prefer the term and/or definition. The words "Used by: this book only" indicate a temporary and non-standard term that exists only for this book's purposes.

When a word has multiple meanings, the first definition is marked "[1]" and subsequent definitions are marked with incremented numbers . " See " and " see also " refer to other terms in this glossary.

Numbers/Symbols
_rowid

Used by: MySQL

See also [ row identifier ]


1NF

First normal form table, a table that contains only scalar values.



2NF

Second normal form table, a 1NF table that contains only columns that are dependent upon the entire primary key.



3NF

Third normal form table, a 2NF table whose non-key columns are also mutually independent; that is, each column can be updated independently of all the rest.



A
access plan

The plan used by the optimizer to resolve an SQL statement.



ADO

ActiveX Data Objects, an API that enables Windows applications to access a database.



aggregate function
See [ set function ]
API

Application Programming Interface, the method by which a programmer writing an application program can make requests of the operating system or another application.



applet

A Java program that can be downloaded and executed by a browser.



B
B-tree

A structure for storing index keys; an ordered, hierarchical, paged assortment of index keys. Some people say the "B" stands for "Balanced."



back compression

Making index keys shorter by throwing bytes away from the back.

See also [ compression ]
See also [ front compression ]


balanced tree
See [ B-tree ]
BDB

Berkeley DB, an embedded database system that is bundled with MySQL.



big-endian

A binary data transmission/storage format in which the most significant bit (or byte) comes first.



binary sort

A sort of the codes that are used to store the characters .



bitmap

Multiple lines of bits.



bitmap index

An index containing one or more bitmaps.

See also [ bitmap ]


bit vector

A line of bits.



block

Used by: Oracle

[1] Oracle-speak for the smallest I/O unit in a database. All other DBMSs use the word page.

[2] When Job #1 has an exclusive lock on Table1 and Job #2 tries to access Table1 , Job #2 must waitit is blocked.

See also [ page ]


block fetch

A fetch that gets multiple rows at once.



blocking unit
See [ page ]
BNF

Backus-Naur Form, a notation used to describe the syntax of programming languages.



bookmark

Used by: Microsoft

A pointer in an index key. If the data is in a clustered index, the bookmark is a clustered index key. If the data is not in a clustered index, the bookmark is an RID.

See also [ row locator ]


buffer
See [ buffer pool ]
buffer pool

A fixed- size allocation of memory, used to store an in-memory copy of a bunch of pages.



bulk INSERT

A multiple-row INSERT submitted to the DBMS as a single unit for processing all at once.



bytecode

An intermediate form of code in which executable Java programs are represented. Bytecode is higher level than machine code, but lower level than source code.



C
cartesian explosion

The effect on the size of a cartesian join's product as the joined tables grow in size. During a join, the DBMS creates a temporary table to hold the join result. The temporary table's size is the product of the sizes of the two original tables, which means the processing time goes up geometrically if Table1 and Table2 get bigger.



cartesian join

The set of all ordered pairs {a , b} where a is a member of set A and b is a member of set B . In database terms, a cartesian product joins all rows in Table1 with all rows in Table2 . Thus if Table1 has the values {T_a1 , T_b1} and Table2 has the values {T_a2 , T_b2} then the cartesian product is {(T_a1 , T_a2) (T_a1 , T_b2) (T_b1 , T_a2) (T_b1 , T_b2)} .Cartesian products are useful for explanation, but when we see an operation which "goes cartesian," we usually criticize the optimizer. Also known as a cross join.



cartesian product
See [ cartesian join ]
case insensitive

A sort order that considers 'SMITH' and 'Smith' to be equal.



case sensitive

A sort order that considers 'SMITH' and 'Smith' to be two different strings.



CBO

Cost-based optimizer, an optimizer that uses volatile data (e.g., the row and column values that have been inserted) and an override (e.g., that the database contents are more important than the fixed optimizing assumptions) to determine the optimal query resolution plan. A cost-based optimizer is a type of rule-based optimizer that has additional volatile information available to it so that it can override a fixed assumption.



CGI

Common Gateway Interface, a standard way for a Web server to pass a Web user's request to an application program and to receive data back to forward to the user .



cluster

Used by: Microsoft, Sybase

A structure for storing data in a specific order; that is, an index that has data pages at the leaf layer. The main idea behind clusters is that all rows should be kept in order permanently according to some column value, such as the primary key.



clustered index

An index that the DBMS uses to determine the order of data rows, according to values in one or more columns, called the cluster key. With a strong-clustered index, the data pages are the index's leaves and are thus always in order. With a weak-clustered index, data pages are separate from index leaf pages and the rows need not be 100% in order. The terms weak clustered index and strong-clustered index are not common usage; they appear only in this book.



clustered key or clustered index key

The column chosen to be the index key for a clustered index.



collating sequence
See [ collation ]
collation

A set of rules that determines the result when character strings are compared.



composite index
See [ compound index ]
composite table

A table that contains column values derived from two or more other tables.



compound index

An index whose keys contain values derived from more than one data column.



compression

Making index keys shorter by throwing bytes away from the front or from the back.

See also [ back compression ]
See also [ front compression ]


concurrency

The ability of multiple transactions to share the same database simultaneously .



concurrent transactions

Two transactions that have overlapping start or end times. To prevent concurrent transactions from interfering with each other, the DBMS may arrange a lock.



connection pooling

A facility that allows connections to a data source to be stored and reused. A Java term.



constant propagation

Substituting a constant value for an expression.



control interval
See [ page ]
cost

The total execution time required for an SQL statement.



cost-based optimizer
See [ CBO ]
covering index

An index that contains every column in the select list of a query.



cursor

A marker that indicates the current position within a result set.



D
data block
See also [ block ]
See also [ page ]


data change statement

An SQL statement that changes data; INSERT, UPDATE, or DELETE.



data source

A repository for storing data. An ODBC/JDBC term.



DBA

Database Administrator, the individual who directs or performs all activities related to maintaining a successful database environment.



dbc

Database connection, an ODBC resource.



DBMS

Database Management System, a program that lets one or more computer users create and access the data in a database.



dbspace

Used by: Informix

See also [ tablespace ]


DDL

Data Definition Language, SQL statements that define or destroy data objects, like CREATE TABLE, ALTER TABLE, and DROP TABLE.



deadlock

A condition that arises when two or more transactions are waiting for one another to release locks.



denormalize

Break the normalization rules deliberately, in an attempt to gain speed or save space.



denormalization
See [ denormalize ]
denormalizing
See [ denormalize ]
dense index
See [ unique index ]
density (of an index or table)

The reciprocal of the count of the number of distinct keys. For example, suppose you have these key values: {A,C,C,D,D} . The number of distinct keys, that is, the number that a SELECT DISTINCT ... statement would return, is three: {A,C,D} . Now recall from your arithmetic classes that reciprocal = 1/Nthat is, the reciprocal of a number N is the number that would yield one when multiplied by N . The reciprocal of 3 is 1/3; therefore, the density for our example is 1/3. It's possible to get a preliminary guess of the number of rows that column1 = <literal> will return by multiplying the table's cardinality by the density of the index.

WARNING: The following definitions of density from vendor manuals or other texts are imprecise or confusing: "the average number of rows which are duplicates" (Sybase); "density is inversely proportional to index sensitivity" (various).



dependence

A concept used in normalization. If the value of column1 uniquely determines the value of column2 , then column2 is functionally dependent on column1 . If the value of column1 limits the possible values in column2 to a specific set, then column2 is set dependent on column1 .



desc

Descriptor for SQL statement parameters, an ODBC resource.



deterministic (function)

A function that always generates the same outputs, given the same inputs.



dictionary sort

A sort that results in a list that is very close to what one expects to see in an English dictionary.



dictionary sort with tie-breaking

A dictionary sort with multiple passes to sort accented characters differently from unaccented characters and uppercase letters differently from lowercase letters .



Dirty Read

A problem arising with concurrent transactions. The Dirty Read problem occurs when a transaction reads a row that has been changed but not committed by another transaction. The result is that Transaction #2's work is based on a change that never really happened . You can avoid Dirty Read by using an isolation level of READ COMMITTED or higher.



DML

Data Manipulation Language, SQL statements that manipulate data, like INSERT, UPDATE, and DELETE.



driven table
See [ inner table ]
driver

The table that the DBMS examines first when evaluating a join or subquery expression.



driving table
See [ outer table ]
DSS

Decision Support System, a computer application that provides support for business decision making.



E
elevator seeking

Travelling through a disk's physical locations in the manner of an elevator, instead of jumping backward or forward for each request, an operating system term.



Enterprise Bean

A server component written in Java.



env

Environment, an ODBC resource.



equijoin

A join using the equals operator in the join expression.



escalation

The act of consolidating several real or potential small-grain locks into a single big-grain lock. Concurrency gets worse , but overhead gets better.



exclusive (lock mode)

A lock that may not coexist with any other lock on the same object.



expanding update

A data-change statement that increases the size of a row.



extent

A group of pages that are allocated together, as part of the initial creation of an object or when an existing extent becomes full.

See also [ read group ]


F
fetch loop

A loop that contains calls to a function that takes a row from a result set.



file

A group of contiguous extents.



FILLFACTOR

Used by: Informix, Ingres, Microsoft, Sybase

Percent (of a page) to fill.

See also [ PCTFREE ]


filter
See [ restrict ]
first normal form
See [ 1NF ]
flatten (a query)

A plan to process a subquery: make everything one level, that is, transform the query to a join, then process as a join.



flattened (query)
See [ flatten (a query) ]
FPU

Floating Point Unit, a microprocessor that manipulates numbers faster than the basic microprocessor used by a computer.



fragment

Used by: Informix

What Informix users call a partition.

See also [ partition ]


fragmentation

[1] Used by: everyone except Informix. The tendency for row[x] to be not immediately followed by row[x+1] because of blank spots or disordering.

[2] Used by: Informix. What Informix users call partitioning.



freelist

Used by: Oracle

A list of pages that have free space in them.



front compression

Making index keys shorter by throwing bytes away from the front.

See also [ compression ]
See also [ back compression ]


functionally dependent

A concept used in normalization. If the value of column1 uniquely determines the value of column2 , then column2 is functionally dependent on column1 .



G
granularity (of a lock)

The size of the locked areadatabase, file, table, page, row, or column.



GUID

Used by: Microsoft

Global Unique Identifier, the Microsoft unique-key generator.



H
hash

A number (often a 32-bit integer) that is derived from column values using a lossy compression algorithm. DBMSs occasionally use hashing to speed up access, but indexes are a more common mechanism.



hash join

A method for producing a joined table. Given two input tables Table1 and Table2 , processing is as follows :

(a) For each row in Table1 , produce a hash. Assign the hash to a hash bucket.

(b) For each row in Table2 , produce a hash. Check if the hash is already in the hash bucket. If it is: there's a join. If it is not: there's no join.



heap

A structure for storing data in an unstructured manner. When you add something to a heap, it goes wherever there is free space, which probably means at the end. Existing data is not moved to make free space available for new data.



heap-organized table
See [ heap ]
histogram

Detailed information on the distribution of values over a column; information stored for the sake of the optimizer.



hoisting

Taking an invariant assignment out of a loop.



host program

A computer program, written in a non-SQL language, containing calls to an SQL API or containing embedded SQL statements.



hot spot

A page in either the index or data file that every job wants to access at the same time.



I
impedance

A lack of correspondence between two data types, one of which is being assigned to the other.



index-organized table

Used by: Oracle

See also [ cluster ]


inner table

The table in the inner loop of a nested-loop join. When you write an SQL statement with an inner join, the inner table is determined by the DBMS based on its join strategy for that statement. When you write an outer join, though, the order of the join determines the inner table: for the join expression Table1 LEFT JOIN Table2 the inner table must be Table2 and for Table1 RIGHT JOIN Table2 the inner table must be Table1 .



in-place update

Used by: Microsoft, Sybase

A data change that does not cause a row to move.

See also [ out-of-place update ]


intent lock

A lock of a larger object related to a locked small object. Implicitly, any small-grain lock implies a shared big-grain lock. If there is one or more locks on pages belonging to a table, then there will also be a lock on the table itself, as a separate lock record.



in-to-out

A plan to process a subquery; for each row in the inner query, lookup in the outer query.



isolation level (of a transaction)

In standard SQL, a setting that determines the type of concurrency problem that will be tolerated for a transaction. In order from lowest level of isolation to highest, the options are: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.



ITL slot
See [ mark on the wall ]
J
JDBC

Java Database Connectivity, an API for SQL programs.



JNDI

Java Naming and Directory Interface, an API that provides naming and directory services for Java programs.



join

The relational operator that allows data from multiple tables to be combined. A join matches rows of multiple tables based on columns with common values.



join expression

The WHERE clause search condition that tells the DBMS over which columns it is to join two tables.



join index

An index that contains keys from more than one table, interspersed, to aid with join expressions.



JVM

Java Virtual Machine, a platform-independent programming language that converts Java bytecode into machine language and executes the result.



L
latch

A low-level on-off mechanism that ensures two processes or threads can't access the same object at the same time.

See also [ lock ]


leaf (page of an index)

A page at the bottom level of a B-tree (the page at the top level is the root). Typically a leaf page contains pointers to the data pages (if it's a non-clustered index) or to the data itself (if it's a clustered index).



little-endian

A binary data transmission/storage format in which the least significant bit (or byte) comes first.



load
See [ bulk INSERT ]
LOB

Large object, for example, a BLOB (binary large object) or a CLOB (character large object).



lock

A method the DBMS uses to prevent concurrent transactions from interfering with one another. Physically, a lock is one of three things: a latch, a mark on the wall, or a RAM record.



locking level
See [ granularity (of a lock) ]
lock mode

The type of lock a DBMS has arranged for a transaction. Options are exclusive, shared, or update.



lock record
See [ RAM record ]
lock type
See [ lock mode ]
Lost Update

A problem arising with concurrent transactions. The Lost Update problem occurs when two transactions read and make a change to the same row. When the first transaction COMMITs, its change is lost because the second transaction's change supersedes it. The result is as if Transaction #1's change never happened. You can avoid Lost Update by using an isolation level of READ UNCOMMITTED or higher.



LRU

Least-Recently-Used, an algorithm that replaces the page that hasn't been accessed for the longest time



M
mark on the wall

An ITL slot or mark put against a row by the DBMS. By putting a mark right beside the row accessed by a transaction, the DBMS ensures that no other transaction has to spend much time checking whether the row is locked.

See also [ lock ]


materialization

Temporarily writing rows; usually this means physically copying data from the DBMS server to the client. To evaluate some expressions, the DBMS will create a temporary table, put the rows from the original table(s) into the temporary table, and select from the temporary copy; this is known as materialization.



materialize
See [ materialization ]
materialized view

A view whose rows take up space. When you select from a view, the DBMS can elect to do one of two things: (a) it can get the rows from the original table, convert any derived columns, and pass the results to the application or (b) it can create a temporary table and put the rows from the original table(s) into the temporary table, then select from the temporary copy. The latter case results in a materialized view. Materialization is often necessary when there is no one-to-one correspondence between the original table's rows and the view's rows (because there is a grouping) or when many tables are affected and concurrency would be harmed (because there is a join).



merge join
See [ sort-merge join ]
merge scan
See [ sort-merge join ]
method

A procedure associated with a Java class or interface.



migrated row

A row at the end of the table due to an expanding update overflow.



migration

The process of finding a home for an expanding update. When a page overflows due to a data change that increases the length of a variable-length column, a row must be shifted to another page. In practice, a pointer to the overflow page is left in the place of the original row.

See also [ shift ]


multi-column index
See [ compound index ]
MVCC

Multi Version Concurrency Control, a mechanism that sometimes doesn't use shared locks for rows.

See also [ versioning ]


N
NaN

Not a Number. In the IEEE definition for floating-point numeric representation, a NaN is a number that cannot be represented (such as the result after a division by zero) and is thus assigned a special code.



nested-loop join

A method for producing a joined table. Given two input tables Table1 and Table2 , processing is as follows:

 for (each row of Table1) {   for (each row of Table2) {     compare and produce joined (Table1, Table2) row   } } 

Notice the for loop nested within a for loop.



niladic function

A function that has no arguments, for example, SQL's CURRENT_DATE and CURRENT_USER.



non-clustered index

An index with a structure completely separate from the data rows it indexes; normally a B-tree. The lowest rows of a non-clustered index contain the index key values, with each key value pointing to the data rows that contain that key. The data rows are not stored in an order based on the index key, but rather in a heap.



non-deterministic (function)

A function that may generate different outputs each time it is run, even if the inputs are always the same.



Non-repeatable Read

A problem arising with concurrent transactions. The Non-repeatable Read problem occurs when a transaction reads a row twice, once before and once after another transaction does a data change. The result is that Transaction #1 gets conflicting data from its reads. You can avoid Non-repeatable Read by using an isolation level of REPEATABLE READ or higher.



normalization

The process of designing a database so that its tables follow the rules specified by relational theory. In practice, this usually means that all database tables are in third normal form.

See also [ 1NF ]
See also [ 2NF ]
See also [ 3NF ]


normalize

To rearrange columns within tables or move columns from one table to another, as part of the database design process, according to rules based on relational theory. In a normalized table, one set of columns is the primary key (which uniquely identifies a row of the table) and all other columns are functionally dependent upon the entire primary key.

See also [ 1NF ]
See also [ 2NF ]
See also [ 3NF ]


normalizing
See also [ normalization ]
See also [ normalize ]


NTFS

NT File System, the file system that the Microsoft Windows NT operating system uses for storing and retrieving files.



O
ODBC

Open Database Connectivity, an API for SQL programs.



OLTP

Online Transaction Processing, a type of computer processing in which the system responds immediately to user requests.



optimistic locking

Locking that assumes conflict is unlikely . Generally , this means avoiding locks and checking for conflict between two transactions only after data changes have been made.



outer table

The table in the outer loop of a nested-loop join. When you write an SQL statement with an inner join, the outer table is determined by the DBMS based on its join strategy for that statement. When you write an outer join, though, the order of the join determines the outer table: for the join expression Table1 LEFT JOIN Table2 the outer table must be Table1 and for Table1 RIGHT JOIN Table2 the outer table must be Table2 .



out-of-place update

Used by: Microsoft, Sybase A data change that causes a row to move.



out-to-in

A plan to process a subquery; for each row in the outer query, lookup in the inner query.



P
packed decimal

A number representation where each number is expressed as a sequence of decimal digits and then each decimal digit is encoded as a four-bit binary number (or nibble). In some cases, the rightmost nibble contains the sign (positive or negative).



page

A fixed-size hopper that stores rows of data or index keys; a minimal unit for disk I/O. Depending on the DBMS, a page is also called a data block, a block, a blocking unit, a control interval, or a row group.



page read

A transfer from disk to memory.



partition

Used by: everyone except Informix

A group of contiguous extents. Often a partition is a file, but it doesn't have to be.

Informix calls this a fragment.



partitioning

Used by: everyone except Informix

The process of splitting a database object (usually a tablespace, table, or index) into two or more physical locations, or partitions, that is, a splitting of a logical group of pages (for example, the pages of a table) into chains or files which are physically removed from each other, perhaps on separate disks. Informix calls this fragmentation.



PCTFREE

Used by: IBM, Oracle

Percent (of a page) to leave free.

See also [ FILLFACTOR ]


pessimistic locking

Locking that assumes conflict is likely. Generally, this means locking an entire object at the beginning of a transaction and not releasing the lock until transaction end.



Phantom

A problem arising with concurrent transactions. The Phantom problem occurs when a transaction reads multiple rows twice; once before and once after another transaction does a data change that affects the search condition in the first transaction's reads. The result is that Transaction #1 gets a different (larger) result set back from its second read. You can avoid Phantoms by using an isolation level of SERIALIZABLE.



PL/SQL

Used by: Oracle

Procedure Language extensions to SQL, Oracle term for stored procedures.



positioned delete

A DELETE statement that allows you to delete the row at the current cursor position. Syntax: DELETE WHERE CURRENT OF <cursor>.



positioned update

An UPDATE statement that allows you to update the row at the current cursor position. Syntax: UPDATE WHERE CURRENT OF <cursor>.



precompiler

A utility you use to "compile" SQL code before you compile the host program, that is, a utility that converts SQL statements in a host program to statements that a compiler can understand. A remnant of embedded-SQL days; there is no such thing as an SQL compiler.



prepared statement

An SQL statement that has been parsed and planned, for example, with ODBC's SQLPrepare function.



project

A relational-theory term. Decide which columns will be operated on (using a select list). The project operation picks the columns that will be in the result set by evaluating an SQL statement's select list.



pseudocolumn

A virtual column, as opposed to a real, physical column. Pseudocolumns take up no space in the row.



PSM

Persistent Stored Modules, SQL Standard term for stored procedures.



PTQ

Used by: Microsoft

Pass Through Query, a Microsoft Access query option.



Q
query

A SELECT statement.



R
RAM record

Used by: this book only. The usual mechanism for locks of rows, pages, or tables, a RAM record is a permanent memory area containing data that describes which objects are locked as well as how the objects are locked (e.g., shared, exclusive, update, etc.).



RBO

Rule-based optimizer, an optimizer that arranges execution plans using fixed data and assumptions, such as statement syntax and existence of indexes.



R-C

Used by: this book only

Non-standard abbreviation for the READ COMMITTED transaction isolation level.



read
See [ page read ]
READ COMMITTED

A transaction isolation level that ensures mandatory shared locks but allows them to be released before the transaction ends. READ COMMITTED tells the DBMS you want it to allow reading of rows that have been written by other transactions only after they have been committed.



read group

A group of contiguous pages that are read together.



read-only transaction

A transaction that doesn't do a data change.



READ UNCOMMITTED

A transaction isolation level that ensures no locks are issued and no locks are checked during the transaction. READ UNCOMMITTED tells the DBMS you want it to allow reading of rows that have been written but not committed by other transactions.



REPEATABLE READ

A transaction isolation level that ensures mandatory shared locks, which will not be released until the transaction ends. REPEATABLE READ tells the DBMS it must not allow a situation where one transaction gets two sets of data from two reads of the same row because a second transaction changed that row between the two reads.



response time

The time a statement takes to execute.



restrict

Decide which rows will be operated on (using a predicate); a relational-theory term. The restrict operation picks the rows that will be in the result set by evaluating an SQL statement's search condition. Also known as filter.



restrictive expression

An expression which causes a restrict, that is, the part of an SQL statement's search condition that tells the DBMS which rows belong in the result set.



result set

What a SELECT statement (or an equivalent to a SELECT, such as a catalog function or a stored procedure that contains a SELECT) returns.



result set size

The number of rows in a result set, that is, the numbers of rows selected.



RID

Used by: IBM, Microsoft, Sybase

See also [ row identifier ]


RLE

Used by: InterBase

Run-Length Encoding, a compression method that converts consecutive identical characters into a code consisting of the character and the number marking the length of the run. For example, if a string is 'AAAAA' then the storage is (5)(A) .



root (node of an index)

A B-tree index is hierarchical, with a top page (where searching starts) containing keys that point to a mid page, which contains keys that point to a leaf page. The top page is known as the root, as if the tree is upside down with the leaves at the bottom and the root at the top.



row group
See [ page ]
ROWID

Used by: Informix, Oracle

See also [ row identifier ]


row identifier

An identifier that uniquely describes a row of a table or index to the DBMS; generally a physical address.

See also [ row locator ]


row locator

The pointer from an index key to a data row.

See also [ row identifier ]


rowset size

The number of rows of a result set that can be fetched at once.



RPC

Remote Procedure Call, a protocol that enables two computers on a network to request and send services to one another without having to understand network details.



R-R

Used by: this book only

Non-standard abbreviation for the REPEATABLE READ transaction isolation level.



R-U

Used by: this book only

Non-standard abbreviation for the READ UNCOMMITTED transaction isolation level.



rule-based optimizer
See [ RBO ]
S
S

Used by: this book only

Non-standard abbreviation for the SERIALIZABLE transaction isolation level.



SCN

Used by: Oracle

System Change Number, a number, internal to Oracle, that is incremented over time as data changes are written to the log.



search condition

A predicate, or combination of predicates, that returns true/false/unknown ; generally found in an SQL WHERE clause.



second normal form
See [ 2NF ]
selectivity (of an index)

The number of distinct values divided by the total number of values. For example, if the values are {A,A,B,B} then the number of distinct values, that is, the number that a SELECT DISTINCT . . . statement would return, is two, while the total number of values is four.

So selectivity is 2/4 in this case. Selectivity is usually expressed as a percentage ("selectivity is 50%"), but some express it as a ratio ("selectivity is 0.5") instead.

WARNING: The following definitions of selectivity from vendor manuals or other texts are imprecise or confusing: "the number of rows in the table divided by the number of distinct values" (Oracle); "the ratio of duplicate key values in an index" (Sybase).

WARNING: The phrase "high selectivity" means either "a large selectivity number" or "a small selectivity number" depending on who is saying it.



select list

Everything between the keyword SELECT and the keyword FROM in a SELECT statement.



sequencing

Forcing rows to be close together by using SERIAL data types or IDENTITY columns or auto-incrementing objects.



SERIALIZABLE

A transaction isolation level that ensures the DBMS will lock paths to objects, not just objects, during the course of a transaction. SERIALIZABLE tells the DBMS you want it to execute concurrent transactions in a manner that produces the same effect as a serial execution of those transactions.



serialized transaction

A transaction during which other users are prevented from making data changes.



set dependent

A concept used in normalization. If the value of column1 limits the possible values in column2 to a specific set, then column2 is set dependent on column1 .



set function

A function that takes a collection of values and returns a single value as a result. The SQL Standard set functions are AVG, COUNT, MAX, MIN, and SUM.



set operator

An operator that merges two or more sets. The SQL Standard set operators are EXCEPT, INTERSECT, and UNION.



shared (lock mode)

A lock that may coexist with any number of other shared locks, or with one update lock, on the same object.



shift

Movement of rows caused by a change in the row size. When the length of a row is changed due to an UPDATE or DELETE operation, that row and all subsequent rows on the page may have to be moved or shifted.

See also [ migration ]


shrinking update

A data-change statement that decreases the size of a row.



skew

An observation about the distribution of values in a set. If value1 , value2 , and value3 each occur five times, there is no skew: the values are evenly distributed. On the other hand, if value1 occurs five times and value2 occurs ten times and value3 occurs 100 times, there is skew: the values are unevenly distributed.



sort key

Used by: this book only

A string with a series of one-byte numbers that represents the relative ordering of characters.



sort-merge join

A method for producing a joined table. Given two input tables Table1 and Table2 , processing is as follows:

(a) Sort Table1 rows according to join-column values.

(b) Sort Table2 rows according to join-column values.

(c) Merge the two sorted lists, eliminating rows where no duplicates exist.



SPL

Used by: Informix

Stored Procedure Language, Informix term for stored procedures.



splitting (an index)

A process whereby the DBMS makes a newly inserted or updated key fit into the index when the key won't fit in the current page. To make the key fit, the DBMS splits the page: it takes some keys out of the current page and puts them in a new page.



statistics

Volatile data about the database, stored in the system catalog so that the optimizer has access to it.



stmt

Statement container, an ODBC resource.



strong-clustered index

Used by: this book only

See also [ clustered index ]


subquery

A SELECT within another SQL statement, usually within another SELECT.



subselect
See [ subquery ]
synchronized (method)

An attribute of a Java method that provides concurrency control among multiple threads sharing an object.



syntax

Used by: this book only

A choice of words and their arrangement in an SQL statement.



T
table scan

A search of an entire table, row by row.



tablespace

A file or group of files that contain data.



third normal form
See [ 3NF ]
throughput

The number of operations the DBMS can do in a time unit.



tid

Used by: Ingres

See also [ row identifier ]


transaction

A series of SQL statements that constitute an atomic unit of work: either all are committed as a unit or they are all rolled back as a unit. A transaction begins with the first statement since the last transaction end and finishes with a transaction end (either COMMIT or ROLLBACK) statement.



transform

The process of rewriting an SQL statement to produce the same result, but with different syntax. When two SQL statements have different syntax but will predictably and regularly produce the same outputs, they are known as transforms of one another.



transitively dependent

A concept used in normalization. If column2 is dependent on column1 and column3 is dependent on column2 , then it is also true that column3 is dependent on column1 ; the Law of Transitivity applies to dependence too.



U
UDT

User-defined data type, a data type defined by a user using the SQL CREATE TYPE statement.



Unicode

A character encoding. Used in Java and sometimes used by DBMSs to support the SQL NCHAR/NCHAR VARYING data type.



unique index

An index with perfect selectivity, that is, an index with no duplicate values allowed. Standard SQL allows multiple NULLs in a unique index, since a NULL is not considered to be equal to any other value, including another NULL. Many DBMSs, however, accept only one NULL in a unique index. Some DBMSs won't allow even a single NULL.



uniquifier

Used by: Microsoft

A 4-byte value added to a clustered index key to make it unique



update (lock mode)

A lock that may coexist with any number of shared locks, but not with another update lock nor with an exclusive lock on the same object.



URL

Uniform Resource Locator, the electronic address for an Internet site.



V
vector

A one-dimensional array.



versioning

A mechanism that sometimes doesn't use shared locks for rows. Versioning is also known as Multi Version Concurrency Control (MVCC).



W
weak-clustered index

Used by: this book only

See also [ clustered index ]


 


SQL Performance Tuning
SQL Performance Tuning
ISBN: 0201791692
EAN: 2147483647
Year: 2005
Pages: 125

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net