LOCKS
Locks are the mechanisms used to prevent destructive
interaction between users accessing same resource simultaneously. Locks
provides high degree of data concurrency.
TYPES
1 Row level locks
2 Table level locks
ROW
LEVEL LOCKS
In the row level lock a row is locked exclusively so that
other cannot modify the row until the transaction holding the lock is committed
or rolled back. This can be done by using select..for update clause.
Ex:
SQL> select * from emp where sal > 3000 for update of comm.;
TABLE
LEVEL LOCKS
A table level lock will protect table data thereby
guaranteeing data integrity when data is being accessed concurrently by
multiple users. A table lock can be held in several modes.
1 Share lock
2 Share update lock
3 Exclusive lock
SHARE
LOCK
A share lock locks the table allowing other users to only
query but not insert, update or delete rows in a table. Multiple users can
place share locks on the same resource at the same time.
Ex:
SQL> lock table emp in share mode;
SHARE
UPDATE LOCK
It locks rows that are to be updated in a table. It permits
other users to concurrently query, insert , update or even lock other rows in
the same table. It prevents the other users from updating the row that has been
locked.
Ex:
SQL> lock table emp in share update
mode;
EXCLUSIVE
LOCK
Exclusive lock is the most restrictive of tables locks.
When issued by any user, it allows the other user to only query. It is similar
to share lock but only one user can place exclusive lock on a table at a time.
Ex:
SQL> lock table emp in share exclusive mode;
NOWAIT
If one user locked the table without nowait then another
user trying to lock the same table then he has to wait until the user who has
initially locked the table issues a commit or rollback statement. This delay
could be avoided by appending a nowait clause in the lock table command.
Ex:
SQL> lock table emp in exclusive mode
nowait.
DEADLOCK
A deadlock occurs when tow users have a lock each on
separate object, and they want to acquire a lock on the each other’s object.
When this happens, the first user has to wait for the second user to release
the lock, but the second user will not release it until the lock on the first
user’s object is freed. In such a case, oracle detects the deadlock
automatically and solves the problem by aborting one of the two transactions.
INDEXES
Index is typically a listing of keywords accompanied by the
location of information on a subject. We can create indexes explicitly to speed
up SQL statement execution on a table. The index points directly to the
location of the rows containing the value.
WHY INDEXES?
Indexes are most useful on larger tables, on columns that
are likely to appear in where clauses as simple equality.
TYPES
1
Unique index
2
Non-unique index
3
Btree index
4
Bitmap index
5
Composite index
6
Reverse key index
7
Function-based index
8
Descending index
9
Domain index
10 Object index
11 Cluster index
12 Text index
13 Index organized table
14 Partition index
v Local index
ü Local prefixed
ü Local non-prefixed
15
Global index
ü Global prefixed
ü Global non-prefixed
UNIQUE INDEX
Unique indexes guarantee that no two rows of a table have
duplicate values in the columns that define the index. Unique index is
automatically created when primary key or unique constraint is created.
Ex:
SQL> create unique index stud_ind on
student(sno);
NON-UNIQUE INDEX
Non-Unique indexes do not impose the above restriction on
the column values.
Ex:
SQL> create index stud_ind on
student(sno);
BTREE INDEX or ASCENDING INDEX
The default type of index used in an oracle database is the
btree index. A btree index is designed to provide both rapid access to
individual rows and quick access to groups of rows within a range. The btree
index does this by performing a succession of value comparisons. Each comparison
eliminates many of the rows.
Ex:
SQL> create index stud_ind on
student(sno);
BITMAP INDEX
This can be used for low cardinality columns: that is
columns in which the number of distinct values is snall when compared to the
number of the rows in the table.
Ex:
SQL> create bitmap index stud_ind on
student(sex);
COMPOSITE INDEX
A composite index also called a concatenated index is an
index created on multiple columns of a table. Columns in a composite index can
appear in any order and need not be adjacent columns of the table.
Ex:
SQL> create bitmap index stud_ind on
student(sno, sname);
REVERSE KEY INDEX
A reverse key index when compared to standard index,
reverses each byte of the column being indexed while keeping the column order.
When the column is indexed in reverse mode then the column values will be
stored in an index in different blocks as the starting value differs. Such an
arrangement can help avoid performance degradations in indexes where
modifications to the index are concentrated on a small set of blocks.
Ex:
SQL> create index stud_ind on
student(sno, reverse);
We can rebuild a reverse key index into normal index using
the noreverse keyword.
Ex:
SQL> alter index stud_ind rebuild
noreverse;
FUNCTION BASED INDEX
This will use result of the function as key instead of
using column as the value for the key.
Ex:
SQL> create index stud_ind on
student(upper(sname));
DESCENDING INDEX
The order used by B-tree indexes has been ascending order.
You can categorize data in B-tree index in descending order as well. This
feature can be useful in applications where sorting operations are required.
Ex:
SQL> create index stud_ind on
student(sno desc);
TEXT INDEX
Querying text is different from querying data because words
have shades of meaning, relationships to other words, and opposites. You may
want to search for words that are near each other, or words that are related to
thers. These queries would be extremely difficult if all you had available was
the standard relational operators. By extending SQL to include text indexes, oracle text
permits you to ask very complex questions about the text.
To use oracle text, you need to create a text index
on the column in which the text is stored. Text index is a collection of tables
and indexes that store information about the text stored in the column.
TYPES
There are several different types of indexes available in
oracle 9i. The first, CONTEXT is supported in oracle 8i as well as oracle 9i. As of oracle 9i,
you can use the CTXCAT text index fo further enhance your
text index management and query capabilities.
1
CONTEXT
2
CTXCAT
3
CTXRULE
The CTXCAT
index type supports the transactional synchronization of data between the base
table and its text index. With CONTEXT indexes, you need to manually tell oracle to update the values in
the text index after data changes in base table. CTXCAT index types do not generate score
values during the text queries.
HOW TO CREATE TEXT INDEX?
You can create a text index via a special version of the
create index comman. For context index, specify the ctxsys.context index type
and for ctxcat index, specify the ctxsys.ctxcat index type.
Ex:
Suppose you have a table called BOOKS with the following
columns
Title, Author, Info.
SQL> create index book_index on books(info) indextype is
ctxsys.context;
SQL> create index book_index on books(info) indextype is
ctxsys.ctxcat;
TEXT QUERIES
Once a text index is created on the info column of BOOKS table, text-searching capabilities
increase dynamically.
CONTAINS & CATSEARCH
CONTAINS function takes two parameters – the column name and the search
string.
Syntax:
Contains(indexed_column, search_str);
If you create a CTXCAT
index, use the CATSEARCH function in place of CONTAINS. CATSEARCH takes three parameters – the column
name, the search string and the index set.
Syntax:
Contains(indexed_column,
search_str, index_set);
HOW A TEXT QEURY WORKS?
When a function such as CONTAINS or CATSEARCH is used in query, the text portion of
the query is processed by oracle text. The remainder of the query is processed
just like a regular query within the database. The result of the text query
processing and the regular query processing are merged to return a single set
of records to the user.
SEARCHING FOR AN EXACT MATCH OF A WORD
The following queries will search for a word called
‘prperty’ whose score is greater than zero.
SQL> select * from books where contains(info, ‘property’) > 0;
SQL> select * from books where catsearch(info, ‘property’, null)
> 0;
Suppose if you want to know the score of the ‘property’ in
each book, if score values for individual searches range from 0 to 10 for each
occurrence of the string within the text then use the score function.
SQL> select title, score(10) from books where contains(info,
‘property’, 10) > 0;
SEARCHING FOR AN EXACT MATCH OF MULTIPLE WORDS
The following queries will search for two words.
SQL> select * from books where contains(info, ‘property AND harvests’) > 0;
SQL> select * from books where catsearch(info, ‘property AND harvests’, null) > 0;
Instead of using AND
you could hae used an ampersand(&). Before using this method, set define
off so the & character will not be seen as part of a variable name.
SQL> set define off
SQL> select * from books where contains(info, ‘property &
harvests’) > 0;
SQL> select * from books where catsearch(info, ‘property harvests’, null) > 0;
The following queries will search for more than two words.
SQL> select * from books where contains(info, ‘property AND harvests AND workers’) > 0;
SQL> select * from books where catsearch(info, ‘property harvests
workers’, null) > 0;
The following queries will search for either of the two
words.
SQL> select * from books where contains(info, ‘property OR harvests’) > 0;
Instead of OR
you can use a vertical line (|).
SQL> select * from books where contains(info, ‘property | harvests’) > 0;
SQL> select * from books where catsearch(info, ‘property | harvests’, null) > 0;
In the following queries the ACCUM(accumulate) operator adds together
the scores of the individual searches and compares the accumulated score to the
threshold value.
SQL> select * from books where contains(info, ‘property ACCUM harvests’) > 0;
SQL> select * from books where catsearch(info, ‘property ACCUM harvests’, null) > 0;
Instead of OR
you can use a comma(,).
SQL> select * from books where contains(info, ‘property , harvests’)
> 0;
SQL> select * from books where catsearch(info, ‘property , harvests’,
null) > 0;
In the following queries the MINUS operator subtracts the score of the
second term’s search from the score of the first term’s search.
SQL> select * from books where contains(info, ‘property MINUS harvests’) > 0;
SQL> select * from books where catsearch(info, ‘property NOT harvests’, null) > 0;
Instead of MINUS
you can use – and instead of NOT
you can use ~.
SQL> select * from books where contains(info, ‘property - harvests’) > 0;
SQL> select * from books where catsearch(info, ‘property ~ harvests’, null) > 0;
SEARCHING FOR AN EXACT MATCH OF A PHRASE
The following queries will search for the phrase. If the
search phrase includes a reserved word within oracle text, the you must use
curly braces ({}) to enclose text.
SQL> select * from books where contains(info, ‘transactions {and}
finances’) > 0;
SQL> select * from books where catsearch(info, ‘transactions {and}
finances’, null) > 0;
You can enclose the entire phrase within curly braces, in
which case any reserved words within the phrase will be treated as part of the
search criteria.
SQL> select * from books where contains(info, ‘{transactions and
finances}’) > 0;
SQL> select * from books where catsearch(info, ‘{transactions and
finances}’, null) > 0;
SEARCHING FOR WORDS THAT ARE NEAR EACH OTHER
The following queries will search for the words that are in
between the search terms.
SQL> select * from books where contains(info, ‘workers NEAR harvests’) > 0;
Instead of NEAR
you can use ;.
SQL> select * from books where contains(info, ‘workers ; harvests’)
> 0;
No comments:
Post a Comment