Indexes
·
Indexes are the database objects, defined on table columns,
which makes the optimizer work easy
·
Defining an index won’t give any guarantee for its usage it all
depends on how optimizer chooses
·
Defining excess of indexes is not appropriate or preferable
·
If you define an index on a column index stores that column data
along with rowid’s
·
If you provide a condition on index column now optimizer will
search the index column data with the help of rowed it identifies the table
record vary directly instead of scanning entire table data
·
Define indexes on column which are frequently used in where and
order by clauses
·
Optimizer search the indexes based on column ‘selectivity’s’.
- Selectivity:
More
selectivity=less duplicity
Less selectivity=more
duplicity
·
Columns which are having more selectivity are good choice for
indexes
·
We can define n no of indexes on table columns
·
More no of indexes are useful for select statement but for DML
operations it is not useful since it hinders the performance.
·
Equal operator readily invokes the column indexes whenever you
use the index column in where clause with equal operator
·
Not equal to(!=) will not invoke the indexes(performance
degrades)
·
Like operator will not invoke the indexes when ‘%’ is at leading
position (or) starting character.
Drawback:
·
Function
won’t allow the indexes to make use of it unless it is a functional index
Here all sql statements are make use of optimizer and
optimizer is nothing but DBMS set of programs it choosing optimizer.
·
LOBRAW
and column won’t allow indexes
·
Indexes
will get automatically defined on a columns which are having unique and FK
constraint. An index can hold maximum of 32 columns but in each bit map index
it is 30
·
Oracle
by default make use of B+ tree index
·
Indexes
won’t store null values except bitmap index
·
When
you drop the table with it, indexes also get dropped
·
Bitmap
indexes are useful for flag columns(less selectivity, high duplicity)
SQL> select /*+hint */
Here it providing hint to query for
multiline comment
SQL> select count(*) from emp;
based on rowed concept data will
pick up directly and get displayed instead of total table scan
Types of indexes:
·
B tree index (less duplicate)
·
Bitmap index (more duplicate )
·
Unique index
·
Simple
·
Complex/composite index
·
Functional index
·
Cluster index
Simple index:
·
If you define an index on single column
those indexes are called simple indexes
o
SQL>create index indsql on emp(sal);
·
To find out whether the optimizer has
chosen the index (or) not we have a DML command called ‘explain plan’
·
With the help of explain plan you can find
out the path choosen by the optimizer
·
Explain plan populates the plan table
with optimizer information (or) explain plan will make use of plan table for
optimizer information
·
So, to find out whether the optimizer
preferred index or not we use ‘explain plan’
SQL> explain plan for select *
from emp where sal>1000;
Output: explained
SQL> desc plan_table;
select
operation, options, object_name, search_columns from plan_table where
object_name=’emp’;
Here
optimizer choosen the index by making use of rowed, if index is dropped, then
full table scan occurs
Opration
|
Options
|
Object_nme
|
Table access
|
By index rowed
|
Emp
|
Once
we drop the index optimizer go for complete table scan as shown in the
following
EG:
SQL> drop index indsal;
SQL> delete from plan_table;
SQL> explain plan for
select * from emp where sal>1000;
Operation
|
Options
|
Object_name
|
Table access
|
Full(complete table
scan)
|
Emp
|
Complex index:
In this we define indexes on more
than one column
Note: Defining an index on
indexed column is not possible
SQL> create index indsql on
emp(sal); //invalid
Eg:
SQL> create index comind on
emp(sal,deptno);
SQL> select * from emp
where sal>2000 and deptno>10;
SQL> select * from emp
where deptno>10;
Note: Here optimizer will make use of index even through you won’t
mention
all the index column in where clause this is due to because of ‘skip
column’
mechanism
unique index:
To
define an unique index on a column that column should not contain
duplicate
values.
Note: Without using PK and unique constraints we can restrict the user
not to provide duplicate values on a column by providing unique index
immediately after defining a table
Attempting on define unique index on a
column which is having duplicate
data ends in error.
SQL> create unique index uniindex on emp(ename);
Non-unique index: If
index is creating on duplicate values i.e
SQL> create unique index
unidex2 on emp(deptno);
It is
invalid statement because deptno is having duplicate data
Bitmap index:
Cluster index:
·
It
is logical memory area in which related
and frequently tables are placed together binding with a cluster common column
·
The
concept of cluster and cluster indexes is useful to increase the performance.
SQL> create cluster cl(sno
number(5));
SQL> create table cltab(sno
number(5), loc varchar2(10)) cluster cl(sno);
SQL> create table
cltab1(sno number(5),name varchar2(10)) cluster cl(sno);
SQL> create index clindex
on cluster cl;
SQL> desc user_ind_columns;
Regular
expressions(10g):
It is
simply called as pattern. They are the sequence of characters which
specifies
the pattern in a given string. To make the complex search easy
we
have regular expressions.
1. Regexp_instr
·
It
displays the strating character (or) end character position based on the
occurance (or) it is an extension to ‘instring’
2. Regexp substr
3. Regexp replace
4. Regexp like
5. Regexp count(11g)
·
Displays the set of characters from a given string when
characters are matched with pattern (or) extension to ‘substr’.
·
Replace the text with another text when pattern is matched in
given string
·
It simply returns Boolean value after performing a match if
match is found return true else null.
Meta
characters:
· ---------------------
Any single character
? --------- Zero
or one character
* -------------------- Zero
or more characters
[…] ------- To specify optional characters
() --------- Grouping
an expression
^ ---------- Anchors
the starting character of line
+?
*?
\S --------- No space
\A
\z
\Z
\x
Match parameter:
C ----------- Case sensitive (default)
M ----------- Multiline
N ----------- Newline
X ----------- Ignore space
Syntax:
Regexp-instr(str,patt[,pas[,oc[,ropt[,mp[,subexp]]]]])
Regexp-substr(str,patt[,pos[,oc[,mp[,subexp]]]])
Regexp-replace(str,patt[,reppatt[,pos[,oc[,mp]]]])
Regexp-like(str,patt[,mp]);
Regexp-count(str,patt[,pos]);
Str-source string
Patt-pattern
Pos-position
Oc-occurance
Ropt-return
option(0,1) default 0
Mp-match
parameter(I,c,m,n,x)
Subexp-subexpression(11g)(1-9)
Reppatt-replace
pattern
Regexp-substr:
SQL> select regexp-substr(name,’(\(\d{3}\)[-/.])(\/’) from regtab;
SQL> select regexp-substr(name,’\(\d{3}\)[-1.]\(\d{3}\)[-/.]\(\d{3}\)’)
Output: (123)-(456)-(789)
(123)-(456)-(789)
(123)-(456)-(789)
SQL> select rgexp-replace(‘x@gmail.comy@gmail.com’,’@’,’#’,1,1) from
dual;
SQL> select rgexp-replace(‘x@gmail.comy@gmail.com’,’@’,’#’,1,2) from
dual;
SQL> select rgexp-replace(‘x@gmail.comy@gmail.com’,’@’,’#’) from
dual;
Note: If
you don’t mention position and occurance it replaces with #
wherever @ is there.
Output:
3. X#gmail.com y#gmail.com
SQL> select * from regtab where regexp-like(name,’a/x’);
SQL> select * from regtab where regexp-like(name,’^a/^b’);
Note: We’ve to use regular expressions in
where clause
Regular
expressions are useful in following things:
PL/SQL
|
Views
|
Indexes
|
Constraints
|
·
Regular
expression always looks for true condition, else return null.
SQL> select ename from emp where ename like ‘A%’ or ename like ‘s%’;
SQL> select ename from emp where regexp-like(ename,’^A/^s’);
SQL> select * from regtab where regexp-like(name,’\s’);
SQL> select * from regtab where regexp-like(name,’\s’);
SQL> select * from regtab where regexp-like(name,’\d’);
SQL> select regexp-count(‘welcome’,’E’,1) from dual;
·
It
counts the records in a given string and if there is no match it returns ‘0’
SQL> select
regexp-substr(‘abc’,’a b c’,1,1,’y’) from dual;
No comments:
Post a Comment