Function-based index
A function-based index indexes a funciton’s return value.
It can be a:
- Built-in SQL function
- PL/SQL function
- A user-written function
It relieves the server from having to invoke the function for every key value as it performs a search on the indxed expression.
For example:
CREATE INDEX table1_ix ON TABLE1(my_function(column1, column2));
Now any query that contains the expression my_function(column1, column2) in the WHERE clause may be able to take advantage of this index (otherwise, it would force a full table scan)
Reversed-key indexes
From: Oracle reverse key index tips
It has been suggested that using reverse-key indexes will speed-up Oracle INSERT statements, especially with an increasing key, like an index on an Oracle sequence (which is used for the primary key of the target table). For large batch inserts, Oracle reverse key indexes will greatly speed-up data loads because the high-order index key has been reversed.
Note: An Oracle reverse key index DOES NOT change the functionality of the index, and it’s not the same as reversing the index key values.
In general, an Oracle reverse key index relieve data block contention (buffer busy waits) when inserting into any index where the index key is a monotonically increasing value which must be duplicated in the higher-level index nodes.
With the index key reversal, only the lowest-level index node is changed, and not all of the high-order index nodes, resulting in far faster insert speed. For updates, Oracle updates the index nodes with each update statement.
Depending on the size of your update batch, it’s often faster to drop, update and then re-build the index. If you have more than one CPU, you might consider doing a parallel index rebuild for faster speed. It splits-up the full-table scan.
ROWID
More explanation at: Overview of ROWID and UROWID Datatypes
Oracle uses rowids internally for the construction of indexes. Each key in an index is associated with a rowid that points to the associated row’s address for fast access. End users and application developers can also use rowids for several important functions:
- Rowids are the fastest means of accessing particular rows.
- Rowids can be used to see how a table is organized.
- Rowids are unique identifiers for rows in a given table.
There are Restricted ROWIDs and Extended ROWIDs.
Structure of a Restricted ROWID
ROWID ENAME
—————— ———-
00000DD5.0000.0001 KRISHNAN
As shown, a restricted rowid’s VARCHAR2/hexadecimal representation is in a three-piece format, block.row.file:
- The data block that contains the row (block DD5 in the example). Block numbers are relative to their datafile, not tablespace. Two rows with identical block numbers could reside in two different datafiles of the same tablespace.
- The row in the block that contains the row (rows 0, 1, 2 in the example). Row numbers of a given block always start with 0.
- The datafile that contains the row (file 1 in the example). The first datafile of every database is always 1, and file numbers are unique within a database.
Structure of an Extended ROWID
ROWID LAST_NAME
—————— ———-
AAAAaoAATAAABrXAAA BORTINS
AAAAaoAATAAABrXAAE RUGGLES
AAAAaoAATAAABrXAAG CHEN
AAAAaoAATAAABrXAAN BLUMBERG
An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:
- OOOOOO: The data object number that identifies the database segment (AAAAao in the example). Schema objects in the same segment, such as a cluster of tables, have the same data object number.
- FFF: The tablespace-relative datafile number of the datafile that contains the row (file AAT in the example).
- BBBBBB: The data block that contains the row (block AAABrX in the example). Block numbers are relative to their datafile, not tablespace. Therefore, two rows with identical block numbers could reside in two different datafiles of the same tablespace.
- RRR: The row in the block.
7-44. Data Dictionary
The data dictionary can be used to query about users, objects, constraints and storage.
By querying the Oracle with SELECT * FROM dictionary WHERE table_name like ‘USER_%’, we get the list of views that the user owns.
For example:
To find out the name of the view used to retrieve the indexes that INVENTORY user owns, we query the SELECT * FROM dictionary WHERE table_name like ‘USER_IND%’. There should be a row where the table_name is USER_INDEXES.
To list all constraints that the INVENTORY user can see, we should use the view ALL_CONSTRAINTS. We use ALL because we want to include other user’s constraints and its own constraints.
To list the names of tables created in your schema: SELECT table_name, tablespace_name FROM user_tables;
To see significant information about any sequences in the database that you have access to: SELECT sequence_name, min_value, max_value, increment_by FROM all_sequences WHERE sequence_owner IN (‘MDSYS’, ‘XDB’);
To see which users in this database are currently able to log in: SELECT username, account_status FROM dba_users WHERE account_status = ‘OPEN’;
To see what information you can view about all the indexes in the database: DESCRIBE dba_indexes;
7-29. B-Tree index leaf entry characteristics
In a B-tree index on a nonpartitioned table:
- Key value are repeated if there are multiple rows that have the same key value unless the index is compressed
- There is no index entry corresponding to a row that has all key columns that are
NULL. Therefore, aWHEREclause specifyingNULLwill always result in a full table scan. - Restricted
ROWIDis used to point to the rows of the table because all rows belong to the same segment.
7-24. Dropping a table
Dropping a table removes:
- Data
- Table structure
- Database triggers
- Corresponding indexes
- Associated object privileges
Optional clauses for the DROP TABLE statement:
- CASCADE CONSTRAINTS: Dependent referential integrity constraints are removed as well.
- PURGE: No flashback possible
Some considerations:
If you do not use the PURGE option, the table definition, associated indexes, and triggers are placed in a recycle bin. That means that the space taken up by the table and its indexes still counts against user’s allowed quota for the tablespaces involved. Use PURGE RECYCLEBIN command to empty the recycle bin or FLASHBACK TABLE command to recover schema objects if PURGE clause was not used in DROP TABLE statement
7-19. Deferred and nondeferred constraints
Constraints are checked at the time of:
- Statement execution, for nondeferred constraints
- COMMIT, for deferred constraints
So the order is (case: DML statement, followed by COMMIT):
- Nondeferred constraints checked
- COMMIT issued
- Deferred constraints checked
- COMMIT complete
Non-deferred constraints are enforced at the end of every DML statement and a violation causes the statement to roll back.
Deferred constraints are enforced when a transaction is committed and a violation causes the transaction to roll back.
A constraint that is defined as deferrable can be specified as one of the following:
- Initially immediable: specifies that by default it must function as an immediate constraint unless explicitly set otherwise using SET CONSTRAINT {constraint name, … ALL} {IMMEDIATE DEFERRED}
- Initially deferred: specifies that by default the constraint must be enforced only at the end of the transaction.
To appreciate the value of initially deferred let us consider the chicken-egg problem.
7-17. Constraint States
To better deal with situations where data must be temporarily in violation of a constraint, you can designate a constraint to be in various states.
An integrity constraint can be ENABLED or DISABLED. This applies to newly entered data.
An integrity constraint can VALIDATE or NOVALIDATE existing data.
To sumarize, an integrity constraint can be in one of the four states:
- DISABLE NOVALIDATE (often used when the data is from an already validated source and the table is read-only, so no new data is being entered into the table).
- DISABLE VALIDATE (used when the existing data must be validated but the data is not going to be modified and the index is not otherwise needed for performance).
- ENABLE NOVALIDATE (used so that existing constraint violations can be corrected, and at the same time, new violations are not allowed to enter the system).
- ENABLE VALIDATE (both new and existing data must conform to the constraint)
7-6. Naming database objects
Non-quoted names are stored in uppercase and not case sensitive
6-28. Oracle Password Verification Function: VERIFY_FUNCTION
The Oracle server provides a password complexity verification function named VERIFY_FUNCTION. This function is created with the /rdbms/admin/utlpwdmg.sql script. The password complexity verification function must be created in the SYS schema. It can be used as a template for you customized password verification.
The supplied password verification function enforces these password restrictions:
- The minimum length is four characers.
- The password cannot be the same as the username.
- The password must have at least one alphabetic, one numeric, and one special character.
- The password must differ from the prvious password by at least three letters.
In adittion to creating VERIFY_FUNCTION, the utlpwdmg script also changes the DEFAULT profile with the following ALTER PROFILE command:
ALTER PROFILE default LIMIT
PASSWORD_LIFE_TIME 60
PASSOWRD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFYH_FUNCTION verify_function;
