TDE Part-2: Implement TDE Tablespace Encryption

In this part, we will implement TDE Tablespace Encryption. As TDE Tablespace Encryption have several major benefits over TDE Column Encryption ( Please read Part-1 for details ), we skip TDE column encryption and give our full focus on TDE Tablespace Encryption.

In order to implement TDE tablespace encryption, your Oracle Database version must be 11g release 1 (11.1) or higher. If you wish to test the enhanced tablespace encryption features, Oracle Database 11g Release 2 (11.2) is required.

TDE tablespace encryption will encrypting entire tablespaces. That means all objects stored in such tablespace will be 'By Default' encrypted. There is no restrictions to have encrypted and unencrypted tablespaces simultaneously in you database. You only encrypt those tablespaces which contain user/application data, system tablespaces must be remain as it is (i.e, unencrypted).

How to Encrypt a Tablespace Using TDE

Step 1 : Configure Oracle Wallet

Oracle Wallet is a secure password storage container which is placed outside of Oracle Database. To reduce the content of this post, we will give a brief configure of Oracle wallet here (if you have more interest please through this post for details). For TDE, oracle recommended to place the wallet outside of Oracle database though default wallet locations are inside database directories:

• $ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet
• $ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet.

When selecting which wallet to use, TDE first try to use the wallet specified by the parameter ENCRYPTION_WALLET_LOCATION. If the parameter is not set, then it looks the parameter WALLET_LOCATION. If this is not set as well, then TDE looks for a wallet at the default database location which i mention above. There are several usages of Oracle Wallet, TDE is one of them. Oracle strongly recommends that you use a separate wallet to store TDE master encryption keys. To set-up a separate wallet, set the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file to point to the wallet used exclusively by TDE.

We Like to put the wallet files outside of Oracle installation directories. Therefore we chose location '/u01/encryption_wallet/'. Now, add following lines to sqlnet.ora and save it.

ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/encryption_wallet)))

Step 2: Create an Oracle PKCS#12 standard wallet 

Command: orapki wallet create -wallet '/u01/encryption_wallet' -pwd "oracle123"
This command creates a wallet file 'ewallet.p12' in specified location. The wallet password must be consist of eight or more alphanumeric characters. If no password has been specified on the command line, it prompts you to enter and reenter the wallet password.

Step 3: Generate Master encryption Key into wallet.

SQL> CONN / AS SYSDBA
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "oracle123";
These will generate a random TDE master encryption key and save it to wallet. The password you given here is the wallet password not the master encryption key.

Step 4: Open the wallet if it is closed.

Before you proceed to create an encrypted tablespace, the Oracle wallet containing the tablespace master encryption key must be open. The wallet must also be open before you can access data in an encrypted tablespace. Wallets must be reopened after an instance restart and can be closed to prevent access to encrypted data. Once the wallet has been opened, it remains open until you shut down the database
instance, or close it explicitly by issuing the close command. When you restart the instance, you must issue the OPEN command again.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "oracle123";
SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "oracle123";

Step 5: Create Encrypted Tablespace

CREATE TABLESPACE enc_tbs DATAFILE '/oradata/enc_tbs01.dbf' SIZE 200M AUTOEXTEND ON NEXT 20M ENCRYPTION USING 'AES128' DEFAULT STORAGE(ENCRYPT);
You can chose other alternative encryption algorithms that are 3DES168, AES192, AES256. The key lengths are included in the names of the algorithms themselves. If no encryption algorithm is specified, the default encryption algorithm AES128 is used.

Step 6: Data Migration

You cannot encrypt an existing tablespace. So if you wish to encrypt existing data, you need to move them from unencrypted tablespaces to encrypted tablespaces. For doing this you use:

1. Oracle Data Pump utility.
2. Commands like CREATE TABLE...AS SELECT... 
3. Move tables like ALTER TABLE...MOVE..  or rebuild indexes.
4. Oracle Table Redefinition. 

To check whether the tablesspaces is encrypted
SQL> SELECT TABLESPACE_NAME,ENCRYPTED FROM DBA_TABLESPACES;                                                            
TABLESPACE_NAME                ENCRYPTED
------------------------------ ---------------------
SYSAUX                       NO
USER                             NO
ENC_TBS                   YES

Setting and Resetting the Master Encryption Key
ALTER SYSTEM SET ENCRYPTION KEY ["certificate_ID"] IDENTIFIED BY "password";

Opening and Closing the Encrypted Wallet

The database must load the master encryption key into memory before it can encrypt or decrypt columns/tablespaces. Opening the wallet allows the database to access the master encryption key. Use the following ALTER SYSTEM command to explicitly open the wallet:
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password";
Once the wallet has been opened, it remains open until you shut down the database instance, or close it explicitly by issuing the following command:
SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "password";
Each time you restart a database instance, you must explicitly open the wallet before open the database.
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password";
SQL> ALTER DATABASE OPEN;

Backup and Recovery of Master Encryption Keys

You cannot access any encrypted data without the master encryption key. As the master encryption key is stored in the Oracle wallet, the wallet should be periodically backed up in a secure location. You must back up a copy of the wallet whenever a new master encryption key is set. Recovery Manager (RMAN) does not back up the wallet as part of the database backup. If you lose the wallet that stores the master encryption key, you can restore access to encrypted data by copying the backed-up version of the wallet to the appropriate location. If the restored wallet was archived after the last time that the master encryption key was reset, then no additional action needs to be taken. If the restored wallet does not contain the most recent master encryption key, then you can recover old data up to the point when the master encryption key was reset by rolling back the state of the database to that point in time. All modifications to encrypted columns after the master encryption key was reset are lost.

TDE Part-1: Transparent Data Encryption

Transparent Data Encryption (TDE), part of Oracle Advanced Security,  encrypts critical data that are stored in data files in the form of tables, indexes, materialized views etc. Oracle provides several security mechanism for authentication and authorization to validate database users who access data. All these measures are there for secure access control but not any for database files such as datafiles, redo log and archive log files. If you have the datafiles, you can see the data though you have no access in the database. Therefore file level security is required to protect the data. On other side, you want minimal configurations or changes in middle and top layer of the application to achieve database file level security.

In 10g Release 2, Oracle first introduce TDE, a transparent data encryption-decryption mechanism to secure data stored in datafiles. Here transparent means database users or application need not to do any changes to achieve TDE. Database itself will encrypt data while writing into datafiles and decrypt then while reading. database users and applications not even bother for data encryption-decryption, Oracle database will do these transparently for them.    To prevent unauthorized decryption, transparent data encryption stores the encryption keys in a
security module (oracle wallet) external to the database. Data is transparently encrypt/decrypted by the database, user does not require any action on their part.

There are two types of TDE:

1. TDE Column Encryption - data encryption-decryption for columns of a table. It encrypts/decrypts data at the SQL layer. This feature introduce in Oracle 10g R2.
2. TDE tablespace Encryption - data encryption-decryption for all objects that stored into the encrypted tablespace. It encrypts/decrypts data during read/write operations This feature introduced in Oracle 11g R1.

Restrictions on Using TDE Column Encryption

Do not use TDE column encryption with the following database features:

1. Index types other than B-tree
2. Range scan search through an index
3. External large objects (BFILE)
4. Synchronous Change Data Capture
5. Transportable Tablespaces
6. Original import/export utilities does not support. You need to use Oracle Data Pump.
7. TDE encrypted columns can't be use as foreign key constraints.

Restrictions on Using TDE Tablespace Encryption

Majority of the restrictions that apply to TDE column encryption, such as data type restrictions and index type restrictions are not applicable to TDE tablespace encryption. Though, the are some other restrictions
that apply to TDE tablespace encryption:

1. External Large Objects (BFILEs) can not be encrypted using TDE tablespace encryption. This is because these files reside outside the database.
2. To perform import and export operations, you need to use Oracle Data Pump.

Things You Need To Consider Before Implementing TDE

1. 4-8% performance impact in end-user response time and an increase of 1-5% in CPU usage. Though it ensure below 10% performance impact.
2. Existing tablespace can not be converted to encrypted tablespace. You need to use table reorganize/redefinition or datapump Export-Import for Migration.
3. Increase disk consumption. Encrypting a single column would require between 32 and 48 bytes of additional storage for each row, on average. Though TDE tablespace encryption has no storage overheads.
4. Require Goldengate 11.1.1.1 for replication
5. Increase RMAN(backup/restore) & DataPump(export/import) duration.
6. Need Separate backup task for Wallet that contains encryption decryption master key.
7. Need Oracle Advanced Security Licence (11,500 USD/Core).
8. Using a PKI key pair (PKI certificate) as a master encryption key may result in greater performance degradation when accessing encrypted columns in the database.

Add Unique key in a table that contains duplicate row

Requirement : A table contains some duplicate data. Now we want to add a unique constraint that skip existing duplicate values but check newly inserted duplicate values.

SQL> create table t2 (id number(10), t varchar2(20));

Table created.


SQL> insert into t2 values (1,'A');

1 row created.

SQL> insert into t2 values (1,'A');

1 row created.

SQL> insert into t2 values (1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE;
alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE
*
ERROR at line 1:
ORA-02299: cannot validate (HASAN.UK_T2) - duplicate keys found


SQL> select * from t2;

ID T
---------- ------------------------------------------------------------
1 A
1 A
1 A

So normal method does not work !



Case 1: New Table That means initially the table does not have any data

SQL> create table t3 (id number(10), t varchar2(20));

Table created.

SQL> alter table t3 add constraint gpu unique (id) deferrable initially deferred;

Table altered.

SQL> alter table t3 disable constraint gpu;

Table altered.


SQL> insert into t3 values(1,'A');

1 row created.

SQL> insert into t3 values(1,'A');

1 row created.

SQL> insert into t3 values(1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL>
SQL> select * from t3;

ID T
---------- ------------------------------------------------------------
1 A
1 A
1 A



SQL> alter table t3 enable novalidate constraint gpu;

Table altered.

SQL> insert into t3 values(2,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t3 values(2,'A');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HASAN.GPU) violated

SQL> alter table t3 modify constraint gpu INITIALLY IMMEDIATE;

Table altered.

SQL> insert into t3 values(1,'A');
insert into t3 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.GPU) violated



Case 2: Existing Table that contains duplicate data


SQL> create table t2 (id number(1),a varchar2(10));

Table created.


SQL> insert into t2 values(1,'A');

1 row created.

SQL> insert into t2 values(1,'A');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t2 add constraint uk_t2 unique(id) DEFERRABLE INITIALLY DEFERRED disable;

Table altered.

SQL> alter table t2 enable novalidate constraint uk_t2;

Table altered.

SQL> insert into t2 values(1,'A');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HASAN.UK_T2) violated


SQL> alter table t2 modify constraint uk_t2 INITIALLY IMMEDIATE;

Table altered.

SQL> insert into t2 values(1,'A');
insert into t2 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.UK_T2) violated


OR


SQL> alter table t2 add constraint uk_t2 unique(id) disable;

Table altered.


SQL> alter table t2 enable novalidate constraint uk_t2;

Table altered.

SQL> insert into t2 values(1,'A');
insert into t2 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.UK_T2) violated

DRIVING_SITE SQL Hint

We all are know about Oracle Data Base Link and I called it dblink in short throughout this article. Usisg dblink you can easily access remote Database Object. Now Please think the following situation:

Situation:

Suppose you have 2 tables: 1. table1 in local DB 2. table2 in remote DB.The table1 size is 10 MB and table2 size is 100 MB. We need to join those two tables and we access only in local DB.

If you now think about performance, it is important to where the sql query executed and the join perform. For above situation, it is better to bring table1 from local DB to Remote DB and execute the sql in remote server and finally bring back the result in local DB.

Lets do the job

The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization. The syntax of this hint is:

/*+DRIVING_SITE(table)*/

where table is the name or alias for the table at which site the execution should take place.

Example:

SELECT /*+DRIVING_SITE(table2)*/ * FROM tabl1, table2@remote_db
WHERE table1.DEPTNO = table2.DEPTNO;

If this query is executed without the hint, rows from table2 will be sent to the local site and the join will be executed there. With the hint, the rows from table1 will be sent to the remote site and the query will be executed there, returning the result to the local DB.

create Foreign key On two diffrent schema table

Some times you may face easy questions which can put into difficulties. Today I got that kind of a question and i am happy so that I get the answer though it take some time find it.

Here is the question which i got from a forum member :

" how to link a table from scott user to the hr user using a foreign key. For an example hr.emp to scott.dept using a foreign key "


In my answer, I assume hr.emp table has a colunm name scott_dept_id . So I create a foreign key on hr.emp (scott_dept_id) by referencing scott.dept (id).

sql> connect scott / pass;
sql> GRANT select,update, delete, references on dept to hr;

Though only references privilege required to create the foreign key

sql> connect hr/password;
sql> alter table emp add constraint fk_test foreign key (scott_dept_id) references scott.dept (id);


So simple ! but hard enough to bother you, if you not know exactly . Thanks to that forum user who lead me to learn this thing .

Reverse Key Index

I think we are familiar with Oracle Index, specially Bitmap Index. Indexes are used to help oracle retrieve data faster. But there is a drawback of Index, if an index create on a large table which under go massive insert/ update, the index may raise contention issue. To get ride from this Oracle provides couple of solutions and Reverse key Index is one of them.

Real world case scenario

Case 1: Suppose table 'track_user' contains login related information of a system. The primary key column 'ID' of the table populated by an increasing sequence. So every new entries (for the index) come to the same blocks when a row inserted into the table. This is the way contention may increase!

As all we know that the primary key constraint is impose on column by creating an unique index on that column. So if we use reverse key indexes in this case then the index entries will go to different blocks and contention will be reduced.

Case 2: If you have a table with column which is populated by an increasing sequence and some times it go under some delete operation for old records. A index was created on That column and this index is on face range scan when you issue a select on that table. But this index has contention issues on index blocks.

To avoide the contention issue you can use Reverse key index as a soluation.

What is Reverse key index ?

Reverse key index was first introduce in Oracle 8. A reverse key index reverses the bytes of each column indexed (except for the ROWID) while keeping the column order same as normal index.

Uses

1. Reverse key index can help avoid performance degradation in indexes in an Oracle Parallel server environment where modifications to the index are concentrated on a small set of leaf blocks. For example, if you insert rows with keys 101, 102 and 103 into a table with a regular index, the rows are likely to be inserted into the same leaf block. When users connected to different instances of the same database insert into the same block a pingoccurs.(When a block is written to disk by one instance so that another instance can read it, it is called a ping.) Excessive pinging will severely degrade performance, so you want to reduce it. In a Reverse Key Index the keys in our example become 101, 201 and 301, and the rows are inserted into disparate parts of the index segment. This type of index is therefore useful in a parallel server environment because it reduces pinging.
2. Reverse Key Indexes can also be useful in situations where users insert ascending values and delete lower values from a table. A regular index would become skewed, but a Reverse Key Index would not.
3. 
   

Disadvantages

With a Reverse Key Index you cannot run an index range scanning query. This is because lexically adjacent keys are not stored next to each other in a Reverse Key Index. You can only perform fetch-by-key value or full-index scans . Of course, you can avoid the index and perform full table scans or use the parallel query option.

Create And Manage Reverse Key Index

You create a Reverse Key Index with the key word REVERSE:

Create Index index_name on table_name (a,b,c) Reverse;

You can rebuild a Reverse Key Index into a regular index with the keyword NOREVERSE

Alter Index index_name Rebuild Noreverse;

If you rebuild a Reverse Key Index without the keyword NOREVERSE, it will rebuilt the Reverse Key Index.

Alter Index index_name Rebuild;

You cannot rebuild a normal index into a reverse key index. You must drop the normal index and create the reverse index.

Rebuilding a Partitioned Index

Rebuilding a partitioned index is slightly different then rebuilding a normal (non-partitioned) index.

Let see what happen when we try to rebuild a partitioned index in usual way

SQL> ALTER INDEX TEST_ID_IDX REBUILD ONLINE COMPUTE STATISTICS;
ORA-14086: a partitioned index may not be rebuilt as a whole

Cause: User attempted to rebuild a partitioned index using ALTER INDEX REBUILD statement, which is illegal.

Action: Rebuild the index, a partition at a time or drop and recreate the entire index


If you choose to rebuild that partition then run the following SQL

SQL> ALTER INDEX TEST_ID_IDX REBUILD PARTITION TEST_2005 ONLINE COMPUTE STATISTICS;

Here is a SQL for finding/ rechecking partitioned index

SELECT uip.index_name
, utp.table_name
, uip.partition_name
, ui.index_type
, ui.global_stats
, uip.last_analyzed
FROM USER_IND_PARTITIONS uip
, USER_TAB_PARTITIONS utp
, USER_INDEXES ui
WHERE uip.partition_name= utp.partition_name
AND ui.index_name=uip.index_name
AND ui.table_name = utp.table_name
AND uip.index_name= 'TEST_ID_IDX'
AND utp.table_name= 'TEST';

ORACLE TEXT

Oracle Text is a technology that enables you to build text query applications and document classification applications. Oracle Text provides indexing, word and theme searching, and viewing capabilities for text.

To design your Oracle Text application, you must determine the type of queries you expect to execute. We can divide application queries into four different categories:

1. Text Queries on Document Collections: A text query application enables users to search document collections such as Web sites, digital libraries, or document warehouses. Searching is enabled by first indexing the document collection. The collection is typically static with no significant change in content after the initial indexing run. Documents can be of any size and of different formats such as HTML, PDF, or Microsoft Word. These documents are stored in a document table. Queries usually consist of words or phrases. Other query operations such as stemming, proximity searching, and wild carding can be used to improve the search results. The queries for this type of application are best served with a CONTEXT index on your document table. To query this index, your application uses the SQL CONTAINS operator in the WHERE clause of a SELECT statement.
2. Queries on Catalog Information: Catalog information consists of inventory type information such as online book store or auction site. The stored information consists of text information such as book titles and related structured information such as price. The information is usually updated regularly to keep the online catalog up to date with the inventory. Queries are usually a combination of a text component and a structured component, such as price or author. Results are almost always sorted by a structured component such as date or price. Catalog applications are best served by a CTXCAT index. You query this index with the CATSEARCH operator in the WHERE clause of a SELECT statement.
3. Document Classification: In a document classification application, an incoming stream or a set of documents is compared to a predefined set of rules. When a document matches one or more rules, the application performs some action. For example, assume we have an incoming stream of news articles. We can define a rule to represent the category of Finance. The rule is essentially one or more queries that select document about the subject of Finance. The rule might have the form 'stocks or bonds or earnings'. When a document arrives about a Wall Street earnings forecast and satisfies the rules for this category, the application takes an action such as tagging the document as Finance or emailing one or more users. To create a document classification application, you create a table of rules and then create a CTXRULE index. To classify an incoming stream of text, use the MATCHES operator in the WHERE clause of a SELECT statement.
4. XML Searching: An XML search application performs searches over XML documents. In a regular document search, you usually search across a set of documents to return documents that satisfy a text predicate; in an XML search, you often use the structure of the XML document to restrict the search. Typically, only that part of the document that satisfies the search is returned. For example, instead of finding all purchase orders that contain the word electric , the user might need only purchase orders in which the comment field contains electric . Oracle Text enables you to perform XML searching using the following approaches: A. Using Oracle Text B. Using the Oracle XML DB Framework and C. Combining Oracle Text features with Oracle XML DB.
   

Related Topics:

1. ORACLE TEXT Installation.
2. Example of Text Queries on Document Collections.
3. Example of Queries on Catalog Information.
   
4. Example of Document Classification.

ORACLE TEXT: Document Classification

The function of a classification application is to perform some action based on document content. These actions can include assigning a category id to a document or sending the document to a user. The result is classification of a document. Documents are classified according to pre-defined rules. These rules select for a category. For instance, a query rule of 'presidential elections' might select documents for a category about politics. Oracle Text provides several types of classification. One type is simple , or rule-based classification, discussed here, in which you create both document categories and the rules for categorizing documents. With supervised classification , Oracle Text derives the rules from a set of training documents you provide. With clustering , Oracle Text does all the work for you, deriving both rules and categories.

Create the Rule Table


CREATE TABLE queries (
query_id NUMBER,
query_string VARCHAR2(80)
);

INSERT INTO queries VALUES (1, 'oracle'); INSERT INTO queries VALUES (2, 'larry or ellison');
INSERT INTO queries VALUES (3, 'oracle and text'); INSERT INTO queries VALUES (4, 'market share');

Create Your CTXRULE Index


CREATE INDEX queryx ON queries(query_string) INDEXTYPE IS CTXRULE;

Classify with MATCHES


COLUMN query_string FORMAT a35;
SELECT query_id,query_string FROM queries WHERE MATCHES (query_string, 'Oracle announced that its market share in databases increased over the last year.')>0;

Related Topics:

1. Example of Text Queries on Document Collections.
2. Example of Queries on Catalog Information.
   

ORACLE TEXT: Queries on Catalog Information

This example creates a catalog index for an auction site that sells electronic equipment such as cameras and CD players. New inventory is added everyday and item descriptions, bid dates, and prices must be stored together. The application requires good response time for mixed queries. The key is to determine what columns users frequently search so that we can create a suitable CTXCAT index. Queries on this type of index are issued with the CATSEARCH operator.

Create Your Table

Set up an auction table to store your inventory:

CREATE TABLE auction(
item_id NUMBER,
title VARCHAR2(100),
category_id NUMBER,
price NUMBER,
bid_close DATE);


Populate Your Table

Now populate the table with various items, each with an id, title, price and bid_date:

INSERT INTO AUCTION VALUES(1, 'NIKON CAMERA', 1, 400, '24-OCT-2002'); INSERT INTO AUCTION VALUES(2, 'OLYMPUS CAMERA', 1, 300, '25-OCT-2002'); INSERT INTO AUCTION VALUES(3, 'PENTAX CAMERA', 1, 200, '26-OCT-2002'); INSERT INTO AUCTION VALUES(4, 'CANON CAMERA', 1, 250, '27-OCT-2002');


Determine your Queries

You need to determine what criteria are likely to be retrieved. In this example, you determine that all queries search the title column for item descriptions, and most queries order by price. When using the CATSEARCH operator later, we'll specify the terms for the text column and the criteria for the structured clause. Step 2 Create the Sub-Index to Order by Price For Oracle Text to serve these queries efficiently, we need a sub-index for the price column, since our queries will order by price. Therefore, create an index set called auction_set and add a sub-index for the price column:

EXEC CTX_DDL.CREATE_INDEXT_SET('auction_iset'); EXEC CTX_DDL.ADD_INDEX('auction_iset','price'); /* sub-index A*/


Create the CTXCAT Index

Create the combined catalog index on the AUCTION table with CREATE INDEX as follows:

CREATE INDEX auction_titlex ON AUCTION(title) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS ('index set auction_iset');

Querying Your Table with CATSEARCH : When you have created the CTXCAT index on the AUCTION table, you can query this index with the CATSEARCH operator. First set the output format to make the output readable:

COLUMN title FORMAT a40;
SELECT title, price FROM auction WHERE CATSEARCH(title, 'CAMERA', 'order by price')> 0;
SELECT title, price FROM auction WHERE CATSEARCH(title, 'CAMERA', 'price <= 300')>0;

Update Your Table

You can update your catalog table by adding new rows. When you do so, the CTXCAT index is automatically synchronized to reflect the change. For example, add the following new rows to our table and then reexecute the query:

INSERT INTO AUCTION VALUES(5, 'FUJI CAMERA', 1, 350, '28-OCT-2002');
INSERT INTO AUCTION VALUES(6, 'SONY CAMERA', 1, 310, '28-OCT-2002');

SELECT title, price FROM auction WHERE CATSEARCH(title, 'CAMERA', 'order by price')> 0;

Related Topics:

1. Example of Text Queries on Document Collections.
2. Example of Document Classification.

ORACLE TEXT: Text Queries on Document Collections

In a basic text query application, users enter query words or phrases and expect the application to return a list of documents that best match the query. Such an application involves creating a CONTEXT index and querying it with CONTAINS.


Create your Text Table

The following example creates a table called docs with two columns, id and text, by using the CREATE TABLE statement. This example makes the id column the primary key. The text column is VARCHAR2.

CREATE TABLE docs (id NUMBER PRIMARY KEY, text VARCHAR2(200));

Load Documents into Table: You can use the SQL INSERT statement to load text to a table. To populate the docs table, use the INSERT statement as follows:

INSERT INTO docs VALUES(1, 'California is a state in the US.');
INSERT INTO docs VALUES(2, 'Paris is a city in France.');
INSERT INTO docs VALUES(3, 'France is in Europe.');


Create the CONTEXT index

Index the HTML files by creating a CONTEXT index on the text column as follows. Since you are indexing HTML, this example uses the NULL_FILTER preference type for no filtering and uses the HTML_SECTION_GROUP type:

CREATE INDEX idx_docs ON docs(text) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');

Use the NULL_FILTER because you do not need to filter HTML documents during indexing. However, if you index PDF, Microsoft Word, or other formatted documents, use the CTXSYS.INSO_FILTER (the default) as your FILTER preference. This example also uses the HTML_SECTION_GROUP section group which is recommended for indexing HTML documents. Using HTML_SECTION_GROUP enables you to search within specific HTML tags, and eliminates from the index unwanted markup such as font information.


Querying Your Table with CONTAINS

You query the table with the SELECT statement with CONTAINS to retrieve the document ids that satisfy the query. Before doing so, set the format of the SELECT statement's output so that it is easily readable. To do so, set the width of the text column to 40 characters:

COLUMN text FORMAT a40;
SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'France', 1) > 0;


Present the Document

In a real application, you might want to present the selected document to the user with query terms highlighted. Oracle Text enables you to mark up documents with the CTX_DOC package. We can demonstrate HTML document markup with an anonymous PL/SQL block in SQL*Plus. However, in a real application you might present the document in a browser. This PL/SQL example uses the in-memory version of CTX_DOC.MARKUP to highlight the word France in document 3. It allocates a temporary CLOB (Character Large Object datatype) to store the markup text and reads it back to the standard output. The CLOB is then de-allocated before exiting:

SET SERVEROUTPUT ON;

DECLARE
mklob CLOB;
amt NUMBER := 40;
line VARCHAR2(80);
BEGIN

CTX_DOC.MARKUP('idx_docs','3','France', mklob);
DBMS_LOB.READ(mklob, amt, 1, line);
DBMS_OUTPUT.PUT_LINE('FIRST 40 CHARS ARE:'||line); DBMS_LOB.FREETEMPORARY(mklob);

END;
/

Synchronize the Index After Data Manipulation

When you create a CONTEXT index, you need to explicitly synchronize your index to keep it up to date with any inserts, updates, or deletes to the text table. Oracle Text enables you to do so with the CTX_DDL.SYNC_INDEX procedure. Add some rows to the docs table:

INSERT INTO docs VALUES(4, 'Los Angeles is a city in California.');
INSERT INTO docs VALUES(5, 'Mexico City is big.');

Since the index is not synchronized, these new rows are not returned with a query on city :

SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'city', 1) > 0;

Therefore, synchronize the index with 2Mb of memory, and reexecute the query:

EXEC CTX_DDL.SYNC_INDEX('idx_docs', '2M');

COLUMN text FORMAT a50;
SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'city', 1) > 0;


Related Topics:

1. Example of Queries on Catalog Information.
   
2. Example of Document Classification.

Parent Table locket while inserting in child table !

The parent table lock with LMODE=3 when an insert command is issued on its child table. An index is also present on the foreign key , but still a table level lock apply on the parent table. Because there is a constraint that requires that the parent exist before the child can be inserted, Oracle locks the parent table to keep the parent record from being deleted while the child record is being inserted. This is a well known issue on Oracle.

Table Compression

Now a days Most systems usually involve large amounts of data stored in a few very large tables. As these systems evolve, the demand on disk space will grow quickly. In today's environment, data warehouses of hundreds of terabytes have become increasingly common. To manage disk capacity, the table compression feature introduced in Oracle9i Release 2 can significantly reduce the amount of disk space used by database tables and improve query performance in some cases.

Working Mechanism

The Oracle9i Release 2 table compression feature works by eliminating duplicate data values found in database tables. Compression works at the database block level. When a table is defined as compressed, the database reserves space in each database block to store single copies of data that appear in multiple places within that block. This reserved space is called the symbol table. Data tagged for compression is stored only in the symbol table and not in the database rows themselves. As data tagged for compression appears in a database row, the row stores a pointer to the relevant data in the symbol table, instead of the data itself. The space savings come from eliminating redundant copies of data values in the table. The effects of table compression are transparent to a user or an application developer. Developers access a table the same way regardless of whether a table is compressed or not, so SQL queries don't have to change once you decide to compress a table. Table compression settings are usually configured and managed by database administrators or architects, with little involvement from developers or users.

Create a Compressed Table

To create a compressed table, use the COMPRESS keyword in the CREATE TABLE statement. The COMPRESS keyword directs Oracle Database to store rows in the table in compressed format wherever possible. The following is an example of the CREATE TABLE COMPRESS statement:

CREATE TABLE EMP (
ID NUMBER NOT NULL,
NAME VARCHAR2(50) NOT NULL,
SAL NUMBER NOT NULL,
JOIN_DATE DATE NOT NULL,
ADDRESS VARCHAR2(100)
) COMPRESS;

Alternatively, you can use the ALTER TABLE statement to change the compression attribute of an existing table, as in the following:

ALTER TABLE EMP COMPRESS;

To determine whether a table has been defined using COMPRESS, query the USER_TABLES data dictionary view and look at the COMPRESSION column, as in the example below:

SELECT TABLE_NAME, COMPRESSION FROM USER_TABLES WHERE TABLE_NAME='EMP';

TABLE_NAME COMPRESSION
------------------ -----------
EMP ENABLED


The COMPRESS attribute can also be defined at the tablespace level, either at the time of creation (by using CREATE TABLESPACE) or later (by using ALTER TABLESPACE). The COMPRESS attribute has inheritance properties similar to those of storage parameters. When a table is created in a tablespace, it inherits the COMPRESS attribute from the tablespace. To determine whether a tablespace is defined using COMPRESS, query the DBA_ TABLESPACES data dictionary view and look at the DEF_TAB_COMPRESSION column, as in the following example:

SELECT TABLESPACE_NAME,DEF_TAB_COMPRESSION FROM DBA_TABLESPACES;

TABLESPACE_NAME DEF_TAB_COMPRESSION
--------------- -------------------
DATA_TS_01 DISABLED
INDEX_TS_01 DISABLED

As you might expect, you can still explicitly compress or uncompress a table in a tablespace, regardless of the COMPRESS value at the tablespace level.

Loading Data into a Compressed Table

Note that when you specify COMPRESS as shown above, you aren't actually compressing any data. The commands above only modify a data dictionary setting. Data isn't actually compressed until you load or insert data into a table. Furthermore, to ensure that data is actually compressed, you need to use a proper method to load or insert data into the table. Data compression takes place only during a bulk load or bulk insert process, using one of the following four methods:

1. Direct path SQL*Loader
2. Serial INSERT with an APPEND hint
3. Parallel INSERT
4. CREATE TABLE ... AS SELECT


Note: If you don't use the correct loading or INSERT method, the data in the table will remain uncompressed, even if the table is defined using COMPRESS. For example, if you use conventional path SQL*Loader or regular INSERT statements, data will not be compressed.

When to Use Table Compression

The way that Oracle Database chooses to compress or not compress table data has implications for the kind of applications best suited for table compression. As described above, data in a table defined using COMPRESS gets compressed only if it is loaded using direct path mode or inserted using append or parallel mode. Data inserted through regular insert statements will remain uncompressed. In online transaction processing (OLTP) systems, data is usually inserted using regular inserts. As a result, these tables generally do not get much benefit from using table compression. Table compression works best on read-only tables that are loaded once but read many times. Furthermore, updating data in a compressed table may require rows to be uncompressed, which defeats the purpose of compression. As a result, tables that require frequent update operations are not suitable candidates for table compression.

Finally, consider the effects of row deletion on the use of table compression. When you delete a row in a compressed table, the database frees up the space occupied by the row in the database block. This free space can be reused by any future insert. However, since a row inserted in conventional mode isn't compressed, it is unlikely that it would fit in the space freed up by a compressed row. High volumes of successive DELETE and INSERT statements may cause fragmentation and waste even more space than would be saved using compression.


Compressing an Existing Uncompressed Table

ALTER TABLE EMP MOVE COMPRESS;

You can also use the ALTER TABLE ... MOVE statement to uncompress a table,

ALTER TABLE EMP MOVE NOCOMPRESS;

Note that the ALTER TABLE ... MOVE operation acquires an EXCLUSIVE lock on the table, which prevents any DML operation on the table while the statement executes. You can avoid this potential problem by using the online table redefinition feature of Oracle9i Database.


Compressing a Partitioned Table

ALTER TABLE EMP MOVE PARTITION EMP3_03 COMPRESS;

Benefits

The biggest reason to use table compression is to save storage space. A table in compressed form will usually occupy less space when compared to its uncompressed form. A compressed table can be stored in fewer blocks results in storage savings, but fewer blocks can mean performance improvements as well. Queries on a compressed table in an I/O bound environment will often complete more quickly, because they need to read fewer database blocks

Impotent SQL Queries

1. To see current Oracle Version :

SQL> SELECT version FROM v$instance;

VERSION
-----------------
10.2.0.1.0

2.

ALTER TABLE

Specifications of 'ALTER TABLE' are as follows

Adding Foreign key

ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (col1, col2)
REFERENCES table_2 (cola,colb);

Adding unique constraint

The following example adds a unique constraint on the column column_name in the table whose name is table_name. The name of the constraint is constraint_name.

ALTER TABLE table_name
add constraint constraint_name
unique (column_name)

Disabling constraints

Integrity constraints can be disabled with the ALTER TABLE command.

ALTER TABLE table-name disable constraint-specification;
ALTER TABLE table-name disable constraint constraint-name;

Adding new Column

ALTER TABLE foo_table add bar_column char(1);
ALTER TABLE foo_table add (bar_column1 char(1),bar_column2 char(1));

Modifying a column

Renaming a column name

ALTER TABLE some_table rename column column_name to new_column_name;

Changing a column's type

A column's type can be changed if the column is empty within the entire table, otherwise Oracle gives an ORA-01440: column to be modified must be empty to decrease precision or scale.

ALTER TABLE some_table modify (column_name number);

Changing null to not null or vice versa

ALTER TABLE some_table modify (column_name not null);

ALTER TABLE some_table modify col_not_null number null;

Specifying tablespace for index

ALTER TABLE tbl add constraint pk_tbl
primary key (col_1, col_2)
using index tablespace ts_idx

Removing a constraint

ALTER TABLE table_name drop constraint constraint_name;

Create Database Link

Purpose

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services. Once you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement

Prerequisites

To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database. Oracle Net must be installed on both the local and remote Oracle databases.


Keyword and Parameters

SHARED : Specify SHARED to use a single network connection to create a public database link that can be shared among multiple users.

PUBLIC : Specify PUBLIC to create a public database link available to all users. If you omit this clause, the database link is private and is available only to you.

DBLINK : Specify the complete or partial name of the database link. If you specify only the database name, then Oracle Database implicitly appends the database domain of the local database. If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects. If the value of GLOBAL_NAMES is FALSE, and if you have changed the global name of the database, then you can specify the global name. The maximum number of database links that can be open in one session or one instance of a Real Application Clusters configuration depends on the value of the OPEN_LINKS and OPEN_LINKS_PER_INSTANCE initialization parameters.

Restriction on Creating Database Links

You cannot create a database link in another user's schema, and you cannot qualify dblink with the name of a schema. Periods are permitted in names of database links, so Oracle Database interprets the entire name, such as ralph.linktosales, as the name of a database link in your schema rather than as a database link named linktosales in the schema ralph.

CONNECT TO Clause : The CONNECT TO clause lets you enable a connection to the remote database.

CURRENT_USER Clause : Specify CURRENT_USER to create a current user database link. The current user must be a global user with a valid account on the remote database. If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user. When executing a stored object (such as a procedure, view, or trigger) that initiates a database link, CURRENT_USER is the username that owns the stored object, and not the username that called the object. For example, if the database link appears inside procedure scott.p (created by scott), and user jane calls procedure scott.p, the current user is scott. However, if the stored object is an invoker-rights function, procedure, or package, the invoker's authorization ID is used to connect as a remote user. For example, if the privileged database link appears inside procedure scott.p (an invoker-rights procedure created by scott), and user Jane calls procedure scott.p, then CURRENT_USER is jane and the procedure executes with Jane's privileges.

user IDENTIFIED BY password : Specify the username and password used to connect to the remote database using a fixed user database link. If you omit this clause, the database link uses the username and password of each user who is connected to the database. This is called a connected user database link.

dblink_authentication : Specify the username and password on the target instance. This clause authenticates the user to the remote server and is required for security. The specified username and password must be a valid username and password on the remote instance. The username and password are used only for authentication. No other operations are performed on behalf of this user. You must specify this clause when using the SHARED clause. USING 'connect string' : Specify the service name of a remote database. If you specify only the database name, then Oracle Database implicitly appends the database domain to the connect string to create a complete service name. Therefore, if the database domain of the remote database is different from that of the current database, then you must specify the complete service name.


Examples

The examples that follow assume two databases, one with the database name local and the other with the database name remote. The examples use the Oracle Database domain. Your database domain will be different.

Defining a Public Database Link: Example

The following statement defines a shared public database link named remote that refers to the database specified by the service name remote:

CREATE PUBLIC DATABASE LINK remote USING 'remote';

This database link allows user hr on the local database to update a table on the remote database (assuming hr has appropriate privileges):

UPDATE employees@remote SET salary=salary*1.1 WHERE last_name = 'Baer';


Defining a Fixed-User Database Link: Example

In the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database:

CREATE DATABASE LINK local CONNECT TO hr IDENTIFIED BY hr USING 'local';

Once this database link is created, hr can query tables in the schema hr on the local database in this manner:

SELECT * FROM employees@local;

User hr can also use DML statements to modify data on the local database:

INSERT INTO employees@local (employee_id, last_name, email, hire_date, job_id) VALUES (999, 'Claus', 'sclaus@oracle.com', SYSDATE, 'SH_CLERK');

UPDATE jobs@local SET min_salary = 3000 WHERE job_id = 'SH_CLERK';

DELETE FROM employees@local WHERE employee_id = 999;

Using this fixed database link, user hr on the remote database can also access tables owned by other users on the same database. This statement assumes that user hr has SELECT privileges on the oe.customers table. The statement connects to the user hr on the local database and then queries the oe.customers table:

SELECT * FROM oe.customers@local;


Defining a CURRENT_USER Database Link: Example

The following statement defines a current-user database link to the remote database, using the entire service name as the link name:

CREATE DATABASE LINK remote.us.oracle.com CONNECT TO CURRENT_USER USING 'remote';

The user who issues this statement must be a global user registered with the LDAP directory service. You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hr on the remote database:

CREATE SYNONYM emp_table FOR oe.employees@remote.us.oracle.com;

CREATE DATABASE LINK ORA.ORACLE.NET
connect to hr identified by hr
using '(DESCRIPTION=(
ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=db2)
(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=ORA.ORACLE.NET)
(server=DEDICATED)))';

Useful Views:

1. DBA_DB_LINKS.
2. ALL_DB_LINKS.
3. USER_DB_LINKS

Common Problems:

1. ORA-02082: a loopback database link must have a connection qualifier

Create a control file

Syntax:

CREATE CONTROLFILE [REUSE] [SET] DATABASE database
LOGFILE [GROUP int] filespec
[RESETLOGS | NORESETLOGS]
DATAFILE filespec options [CHARACTER SET charset]

Options:
MAXDATAFILES int
MAXLOGFILES int
MAXLOGMEMBERS int
MAXLOGHISTORY int
MAXINSTANCES int
ARCHIVELOG | NOARCHIVELOG
FORCE LOGGING

Several LOGFILE and/or DATAFILEs can be specified at once if separated with commas. Do not include in the DATAFILE clause any datafiles in temporary or read-only tablespaces. You can add these to the database later.

Use this command to re-create a control file only if:

* All copies of your existing control files have been lost through media failure.
* You want to change the name of the database.
* You want to change the maximum number of redo log file groups, redo log file members, archived redo log files, datafiles, or instances that can concurrently have the database mounted and open.

Oracle recommend that you perform a full backup of all database files before using this statement. (see the Oracle User-Managed Backup and Recovery Guide)

An alternative to CREATE CONTROLFILE is
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
this generates a new SQL script that may be used to re-create the controlfile.

Specify REUSE to indicate that existing control files identified by the initialization parameter CONTROL_FILES can be reused and overwritten.

Use SET DATABASE to change the name of the database.

Use the logfile_clause to specify ALL the redo log files for the database.

Specify RESETLOGS if you want Oracle to ignore the contents of the files listed in the LOGFILE clause. The log files do not have to exist but each redo_log_file_spec in the LOGFILE clause must specify the SIZE parameter. Oracle will assign all online redo log file groups to thread 1 and will enable this thread for public use by any instance. You must then open the database using ALTER DATABASE RESETLOGS.

NORESETLOGS will use all files in the LOGFILE clause as they were when the database was last open. These files must exist and must be the current online redo log files rather than restored backups. Oracle will reassign the redo log file groups to reenabled threads as previously assigned.

The FORCE LOGGING clause will put the database into FORCE LOGGING mode after control file creation.

Related Commands:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE

Related Views:

GLOBAL_NAME
DBA_DATA_FILES

V$CONTROLFILE
V$DATABASE
V$DATAFILE
V$LOG
V$LOGFILE

user: SYS vs SYSTEM

SYS

When any database is created, the user SYS is automatically created and granted the DBA role.

All of the base tables and views for the database's data dictionary are stored in the schema SYS. These base tables and views are critical for the operation of Oracle. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by Oracle. They should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. (However, you can change the storage parameters of the data dictionary settings if necessary.)

Ensure that most database users are never able to connect using the SYS account.

SYSTEM

When a database is created, the user SYSTEM is also automatically created and granted the DBA role.

The SYSTEM username is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle options and tools. Never create in the SYSTEM schema tables of interest to individual users.

SYS has the SYSDBA privilege, SYSTEM doesn't.

Managing Indexes Part #3

After Managing Indexes Part #2

Creating an Index Associated with a Constraint

Oracle Database enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created by the database when the constraint is enabled. No action is required by you when you issue the CREATE TABLE or ALTER TABLE statement to create the index, but you can optionally specify a USING INDEX clause to exercise control over its creation. This includes both when a constraint is defined and enabled, and when a defined but disabled constraint is enabled.

To enable a UNIQUE or PRIMARY KEY constraint, thus creating an associated index, the owner of the table must have a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege. The index associated with a constraint always takes the name of the constraint, unless you optionally specify otherwise.


Specifying Storage Options for an Index Associated with a Constraint


You can set the storage options for the indexes associated with UNIQUE and PRIMARY KEY constraints using the USING INDEX clause. The following CREATE TABLE statement enables a PRIMARY KEY constraint and specifies the storage options of the associated index:

CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER)
ENABLE PRIMARY KEY USING INDEX TABLESPACE users PCTFREE 0;

Specifying the Index Associated with a Constraint

If you require more explicit control over the indexes associated with UNIQUE and PRIMARY KEY constraints, the database lets you:

1. Specify an existing index that the database is to use to enforce the constraint
2. Specify a CREATE INDEX statement that the database is to use to create the index and enforce the constraint
   

These options are specified using the USING INDEX clause. The following statements present some examples.

Example 1:

CREATE TABLE a (a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));


Example 2:

CREATE TABLE b( b1 INT, b2 INT,CONSTRAINT bu1 UNIQUE (b1, b2)
USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);

Example 3:

CREATE TABLE c(c1 INT, c2 INT);
CREATE INDEX ci ON c (c1, c2);
ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;


If a single statement creates an index with one constraint and also uses that index for another constraint, the system will attempt to rearrange the clauses to create the index before reusing it.


Collecting Incidental Statistics when Creating an Index

Oracle Database provides you with the opportunity to collect statistics at very little resource cost during the creation or rebuilding of an index. These statistics are stored in the data dictionary for ongoing use by the optimizer in choosing a plan for the execution of SQL statements. The following statement computes index, table, and column statistics while building index emp_ename on column ename of table emp:

CREATE INDEX emp_ename ON emp(ename) COMPUTE STATISTICS;

Creating an Index Online

You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index build is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online.

The following statements illustrate online index build operations:

CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;


Creating a Function-Based Index

Function-based indexes facilitate queries that qualify a value returned by a function or expression. The value of the function or expression is precomputed and stored in the index.

To create a function-based index, you must have the COMPATIBLE parameter set to 8.1.0.0.0 or higher. In addition to the prerequisites for creating a conventional index, if the index is based on user-defined functions, then those functions must be marked DETERMINISTIC. Also, you just have the EXECUTE object privilege on any user-defined function(s) used in the function-based index if those functions are owned by another user.

Additionally, to use a function-based index:

1. The table must be analyzed after the index is created.
2. The query must be guaranteed not to need any NULL values from the indexed expression, since NULL values are not stored in indexes.
   

To illustrate a function-based index, consider the following statement that defines a function-based index (area_index) defined on the function area(geo):

CREATE INDEX area_index ON rivers (area(geo));


In the following SQL statement, when area(geo) is referenced in the WHERE clause, the optimizer considers using the index area_index.

SELECT id, geo, area(geo), desc FROM rivers WHERE Area(geo) >5000;

Table owners should have EXECUTE privileges on the functions used in function-based indexes.

Because a function-based index depends upon any function it is using, it can be invalidated when a function changes. If the function is valid, you can use an ALTER INDEX ... ENABLE statement to enable a function-based index that has been disabled. The ALTER INDEX ... DISABLE statement lets you disable the use of a function-based index. Consider doing this if you are working on the body of the function.

Altering Indexes

To alter an index, your schema must contain the index or you must have the ALTER ANY INDEX system privilege. Among the actions allowed by the ALTER INDEX statement are:

1. Rebuild or coalesce an existing index
2. Deallocate unused space or allocate a new extent
3. Specify parallel execution (or not) and alter the degree of parallelism
4. Alter storage parameters or physical attributes
5. Specify LOGGING or NOLOGGING
6. Enable or disable key compression
7. Mark the index unusable
8. Start or stop the monitoring of index usage
   

You cannot alter index column structure.

END

Managing Indexes Part #2

Continued after Managing Indexes Part #1

Order Index Columns for Performance

The order of columns in the CREATE INDEX statement can affect query performance. In general, specify the most frequently used columns first. If you create a single index across columns to speed up queries that access, for example, col1, col2, and col3; then queries that access just col1, or that access just col1 and col2, are also seeded up. But a query that accessed just col2, just col3, or just col2 and col3 does not use the index.

Limit the Number of Indexes for Each Table

A table can have any number of indexes. However, the more indexes there are, the more overhead is incurred as the table is modified. Specifically, when rows are inserted or deleted, all indexes on the table must be updated as well. Also, when a column is updated, all indexes that contain the column must be updated.

Thus, there is a trade-off between the speed of retrieving data from a table and the speed of updating the table. For example, if a table is primarily read-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes could be preferable.

Drop Indexes That Are No Longer Required

Consider dropping an index if:

1. It does not speed up queries. The table could be very small, or there could be many rows in the table but very few index entries.
2. The queries in your applications do not use the index.
3. The index must be dropped before being rebuilt.

Consider Parallelizing Index Creation


You can parallelize index creation, much the same as you can parallelize table creation. Because multiple processes work together to create the index, the database can create the index more quickly than if a single server process created the index sequentially.

When creating an index in parallel, storage parameters are used separately by each query server process. Therefore, an index created with an INITIAL value of 5M and a parallel degree of 12 consumes at least 60M of storage during index creation.

Consider Creating Indexes with NOLOGGING

You can create an index and generate minimal redo log records by specifying NOLOGGING in the CREATE INDEX statement. Creating an index with NOLOGGING has the following benefits:

1. Space is saved in the redo log files.
2. The time it takes to create the index is decreased.
3. Performance improves for parallel creation of large indexes.
   

In general, the relative performance improvement is greater for larger indexes created without LOGGING than for smaller ones. Creating small indexes without LOGGING has little effect on the time it takes to create an index. However, for larger indexes the performance improvement can be significant, especially when you are also parallelizing the index creation.

Creating an Index Explicitly

You can create indexes explicitly (outside of integrity constraints) using the SQL statement CREATE INDEX. The following statement creates an index named emp_ename for the ename column of the emp table:

CREATE INDEX emp_ename ON emp(ename) TABLESPACE users
STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) PCTFREE 0;

Notice that several storage settings and a tablespace are explicitly specified for the index. If you do not specify storage options (such as INITIAL and NEXT) for an index, the default storage options of the default or specified tablespace are automatically used.

Creating a Unique Index Explicitly

Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Nonunique indexes do not impose this restriction on the column values. Use the CREATE UNIQUE INDEX statement to create a unique index. The following example creates a unique index:

CREATE UNIQUE INDEX dept_unique_index ON dept (dname)
TABLESPACE indx;

Alternatively, you can define UNIQUE integrity constraints on the desired columns. The database enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key. This is discussed in the following section. However, it is advisable that any index that exists for query performance, including unique indexes, be created explicitly.

Discussion continued Managing Indexes Part #3