In CONTEXT index queries, you can specify the maximum number of words between
the search terms.
SQL> select * from books where contains(info, ‘NEAR((workers, harvests),10)’ > 0;
USING WILDCARDS DURING SEARCHES
You can use wildcards to expand the list of valid search
terms used during your query. Just as in regular text-string wildcard
processing, two wildcards are available.
% - percent sign; multiple-character
wildcard
_ - underscore; single-character wildcard
SQL> select * from books where contains(info, ‘worker%’) > 0;
SQL> select * from books where contains(info, ‘work___’) > 0;
SEARCHING FOR WORDS THAT SHARE THE SAME STEM
Rather than using wildcards, you can use stem-expansion
capabilities to expand the list of text strings. Given the ‘stem’ of a word,
oracle will expand the list of words to search for to include all words having
the same stem. Sample expansions are show here.
Play - plays playing played playful
SQL> select * from books where contains(info, ‘$manage’) > 0;
SEARCHING FOR FUZZY MATCHES
A fuzzy match expands the specified search term to include
words that are spelled similarly but that do not necessarily have the same word
stem. Fuzzy matches are most helpful when the text contains misspellings. The
misspellings can be either in the searched text or in the search string
specified by the user during the query.
The following queries will not return anything because its
search does not contain the word ‘hardest’.
SQL> select * from books where contains(info, ‘hardest’) > 0;
It does, however, contains the word ‘harvest’. A fuzzy
match will return the books containing the word ‘harvest’ even though ‘harvest’
has a different word stem thant the word used as the search term.
To use a fuzzy match, precede the search term with a
question mark, with no space between the question mark and the beginning of the
search term.
SQL> select * from books where contains(info, ‘?hardest’) > 0;
SEARCHING FOR WORDS THAT SOUND LIKE OTHER WORDS
SOUNDEX, expands search terms based on how the word sounds.
The SOUNDEX expansion method uses the same text-matching logic available via
the SOUNDEX function in SQL.
To use the SOUNDEX option, you must precede the search term
with an exclamation mark(!).
SQL> select * from books where contains(info, ‘!grate’) > 0;
INDEX SYNCHRONIZATION
When using CONTEXT indexes, you need to manage the text index contents; the text indexes
are not updated when the base table is updated. When the table was updated, its
text index is out of sync with the base table. To sync of the index, execute
the SYNC_INDEX procedure of the CTX_DDL package.
SQL> exec CTX_DDL.SYNC_INDEX(‘book_index’);
INDEX SETS
Historically, problems with queries of text indexes have
occurred when other criteria are used alongside text searches as part of the
where clause. To improve the mixed query capability, oracle features index
sets. The indexes within the index set may be structured relational columns or
on text columns.
To create an index set, use the CTX_DDL package to create the index set and
add indexes to it. When you create a text index, you can then specify the index
set it belongs to.
SQL> exec
CTX_DDL.CREATE_INDEX_SET(‘books_index_set’);
The add non-text indexes.
SQL> exec CTX_DDL.ADD_INDEX(‘books_index_set’, ‘title_index’);
Now create a CTXCAT
text index. Specify ctxsys.ctxcat as the index type, and list the index set in
the parameters clause.
SQL> create index book_index on books(info) indextype is
ctxsys.ctxcat
parameters(‘index set books_index_set’);
INDEX-ORGANIZED TABLE
An index-organized table keeps its data sorted according to
the primary key column values for the table. Index-organized tables store their
data as if the entire table was stored in an index.
An index-organized table allows you to store the entire
table’s data in an index.
Ex:
SQL> create table student (sno
number(2),sname varchar(10),smarks number(3)
constraint pk primary key(sno) organization index;
PARTITION INDEX
Similar to partitioning tables, oracle allows you to
partition indexes too. Like table partitions,
index partitions could be in different tablespaces.
LOCAL INDEXES
1
Local keyword tells oracle to create a separte index for each
partition.
2
In the local prefixed index the partition key is specified on the
left prefix. When the underlying table is partitioned baes on, say two columns
then the index can be prefixed on the first column specified.
3
Local prefixed indexes can be unique or non unique.
4
Local indexes may be easier to manage than global indexes.
Ex:
SQL> create index stud_index on
student(sno) local;
GLOBAL INDEXES
1
A global index may contain values from multiple partitions.
2
An index is global prefixed if it is partitioned on the left prefix
of the index columns.
3
The global clause allows you to create a non-partitioned index.
4
Global indexes may perform uniqueness checks faster than local
(partitioned) indexes.
5
You cannot create global indexes for hash partitions or
subpartitions.
Ex:
SQL> create index stud_index on
student(sno) global;
Similar to table partitions, it is possible to move them
from one device to another. But unlike table partitions, movement of index
partitions requires individual reconstruction of the index or each partition
(only in the case of global index).
Ex:
SQL> alter index stud_ind rebuild
partition p2
1
Index partitions cannot be dropped manually.
2
They are dropped implicitly when the data they refer to is dropped
from the partitioned table.
MONITORING USE OF INDEXES
Once you turned on the monitoring the use of indexes, then
we can check whether the table is hitting the index or not.
To monitor the use of index use the follwing syntax.
Syntax:
alter index index_name
monitoring usage;
then check for the details in V$OBJECT_USAGE view.
If you want to stop monitoring use the following.
Syntax:
alter index index_name
nomonitoring usage;
DATA MODEL
1 ALL_INDEXES
2 DBA_INDEXES
3 USER_INDEXES
4 ALL_IND-COLUMNS
5 DBA-IND_COLUMNS
6 USER_IND_COLUMNS
7 ALL_PART_INDEXES
8 DBA_PART_INDEXES
9 USER_PART_INDEXES
10 V$OBJECT_USAGE
SQL*PLUS COMMNANDS
These
commands does not require statement terminator and applicable to the sessions ,
those will be automatically cleared when session was closed.
BREAK
This
will be used to breakup the data depending on the grouping.
Syntax:
Break or bre [on <column_name>
on report]
COMPUTE
This
will be used to perform group functions on the data.
Syntax:
Compute or comp [group_function of column_name
on breaking_column_name or
report]
TTITLE
This
will give the top title for your report. You can on or off the ttitle.
Syntax:
Ttitle or ttit [left | center | right]
title_name skip n other_characters
Ttitle or ttit [on or off]
BTITLE
This
will give the bottom title for your report. You can on or off the btitle.
Syntax:
Btitle or btit [left | center | right]
title_name skip n other_characters
Btitle or btit [on or off]
Ex:
SQL> bre on deptno skip 1 on report
SQL> comp sum of sal on deptno
SQL> comp sum of sal on report
SQL> ttitle center 'EMPLOYEE DETAILS' skip1 center
'----------------'
SQL> btitle center '** THANKQ **'
SQL> select * from emp order by deptno;
Output:
EMPLOYEE
DETAILS
-----------------------
EMPNO
ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- -------
-------------- --------
---------- ----------
7782 CLARK
MANAGER 7839 09-JUN-81
2450 10
7839 KING
PRESIDENT
17-NOV-81 5000
7934 MILLER CLERK 7782 23-JAN-82
1300
---------- **********
8750 sum
7369 SMITH
CLERK 7902 17-DEC-80 800 20
7876 ADAMS
CLERK 7788 23-MAY-87 1100
7902 FORD
ANALYST 7566 03-DEC-81 3000
7788 SCOTT
ANALYST 7566 19-APR-87 3000
7566 JONES
MANAGER 7839 02-APR-81 2975
---------- **********
10875 sum
7499
ALLEN SALESMAN 7698
20-FEB-81 1600 300 30
7698
BLAKE MANAGER 7839
01-MAY-81 2850
7654
MARTIN SALESMAN 7698 28-SEP-81 1250 1400
7900
JAMES CLERK 7698 03-DEC-81 950
7844
TURNER SALESMAN 7698 08-SEP-81 1500 0
7521
WARD SALESMAN 7698
22-FEB-81 1250 500
---------- **********
9400 sum
----------
sum
29025
** THANKQ **
CLEAR
This
will clear the existing buffers or break or computations or columns formatting.
Syntax:
Clear or cle buffer | bre | comp |
col;
Ex:
SQL> clear buffer
Buffer cleared
SQL> clear bre
Breaks cleared
SQL> clear comp
Computes cleared
SQL> clear col
Columns cleared
CHANGE
This
will be used to replace any strings in SQL statements.
Syntax:
Change or c/old_string/new_string
If
the old_string repeats many times then new_string replaces the
first string only.
Ex:
SQL> select * from det;
select * from det
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> c/det/dept
1* select * from
dept
SQL> /
DEPTNO DNAME LOC
----------
---------------- -----------
10 ACCOUNTING NEW YORK
20 RESEARCH ALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
COLUMN
This
will be used to increase or decrease the width of the table columns.
Syntax:
Column or col <column_name>
format <num_format|text_format>
Ex:
SQL> col deptno format 999
SQL> col dname format a10
SAVE
This
will be used to save your current SQL statement as SQL Script file.
Syntax:
Save or sav <file_name>.[extension] replace or rep
If
you want to save the filename with existing filename the you have to use
replace option.
By
default it will take sql as the extension.
Ex:
SQL> save ss
Created file ss.sql
SQL> save ss replace
Wrote file ss.sql
EXECUTE
This
will be used to execute stored subprograms or packaged subprograms.
Syntax:
Execute or exec <subprogram_name>
Ex:
SQL> exec sample_proc
SPOOL
This
will record the data when you spool on, upto when you say spool off. By default
it will give lst as extension.
Syntax:
Spool on | off | out | <file_name>.[Extension]
Ex:
SQL> spool on
SQL> select * from dept;
DEPTNO DNAME
LOC
--------- --------------
----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> spool off
SQL> ed on.lst
SQL> select * from dept;
DEPTNO DNAME
LOC
--------- --------------
----------
No comments:
Post a Comment