Create index

CREATE INDEX
--------------------------------
Que What do you mean by index ????
Ans :

1.   In a  General we all that  index is  that  in which we have list of important topics having page no on which it is written or  found. so  through the  index the work of searching particular topic is reduced and we got the fast and  optimum result .
2. Same in the database we  create an Index on particular field so that we can easy to find the data and got the result as fast as possible.if we create index that will also  help to retrieve the result that will matter in performance tuning.

For DESCRIBE the  USER_INDEXES TABLE  we have Command  DESC user_indexes
f

DESC USER_INDEXES  command  will  display above  information of  user_indexes table

which has different columns and their data type of that table.

1. Above image  list  the list of  index for the  particular table .  index start from SYS are the  implicit  index which are create when  we create primary key  column.
2. implicit index is create when primary key constraint is add and removed when Primary key constraint is removed.

Sequence table Description and list of sequence

How to get List of  List of  SEQUENCES Created by the User  ????
--------------------------------------------------------------------------------------------
QUERY :: select * from user_sequences

Result::

DESCRIPTION::

user_Seuences is table which stores all the information all about the created sequences. like MINVAL,MAXVAL,INCREMENT_BY,CYCLE,CACHE_SIZE

MinValue :: this attribute  will define what is the minimum value of Sequence.

MaxValue:: this  attribute  will define what is the maximum value of Sequence.

INCREMENT_BY::this  attribute  will define what is the Increment value of Sequences.

Cycle_FLAG:: This attribute will define that  you want to  restart the cycle after define maximum value.

CACHE_SIZE:: this attribute will define size of cache. this will allow to define  how many value are there in Cache or  buffer ??

Create sequence with by Different ways and parameters

Following  post will  Help  you  to create  sequence  in oracle


Create SEQUENCE  Command
-----------------------------------------
Syntax:

CREATE SEQUENCE   SEQUENCE_NAME
MINVALUE  [-100 ]   //optional   [Can be  positive or Negative]
MAXVALUE [100]    //optional    [Can be  positive or Negative]
START WITH 0        //optional     [Can be  positive or Negative]
INCREMT BY 1      //optional      [Can be  positive or Negative]

-----------------------------------------
create sequence SqEmp
minvalue 1
maxvalue 500
start with 1
increment by 2

Above Image has  Create Command for Creating Sequence like [ 1,3,5,7,9....]   because   we  Increment Sequence by  "2"  so.. Here We define minimum value  is   1.
Maximum  value is 500 [Here maximum value will be  499].
sequence will no  display index value  more  than  500.

From Above  Image  you can  see that  we can create sequence  with  different style  and  different  parameters.
In a  Image[1]  we have parameter's start with in which we define

1.  start with (1) 
2.  increment by (1) 

  if  we Don't  assign such parameter it by default  take (1) for that parameters.

Cascade delete

In Following Image you can see 2  different result set 

1. result set 1 is before Delete. [Before delete Query]
2. result set 2 is after Delete. [After delete Query]

When you perform Delete operation on dept [parent] table it delete its own table and also delete child table (EMP) table's record which are connected to it.

If we Don't Give  on DELETE CASCADE then it will not allow you to delete this parent table records.

Add new Column with Foreign key Constraint

ADDING NEW COLUMN AFTER TABLE CREATION  with FK Constraint

 alter table emp
 add deptno number(3,0)
 add constraint fk_deptno
 foreign key (deptno) references dept(deptno)
on delete cascade

after above option  table  EMP will be  look like

• Here DEPTNO is Foreign key of DEPT table.

1. ON DELETE CASCADE option is  optionally.  this  option will  delete all the record when user delete any deptno from its parent table DEPT.
2. dept is Parent table.
3. all Child record  will be deleted when its releted Parent table is deleted.

Create table with Column level constraint for Dept table.

table name ::  DEPT
--------------------------------------------
Create table Command for DEPT table
create table dept
(deptno number(3,0) primary key,
dname varchar2(15) not null,
location varchar2(15)
)

table description after above Command is as under.

Here we Give column level Constraint for DEPTNO.

Here DEPT NO is  Primary key for  Dept table.

create Table in oracle with simple primary key and check constraint.

Table name :Emp
-------------------

Create table CODE:
create table emp
(
empid numeric(3,0),
ename varchar2(15) not null,
egender char(1),
esal number(5,0),
constraint chk_Pk_empid primary key(empid),
constraint chk_gender check(egender in('m','f','M','F')),
constraint chk_sal check(esal between 0 and 9999)
)

After table Create code table definition will be look like.

1. Here Empid is Primary key of Emp table.
2. Ename should be not null entry.
3. gender data should be 'M','F','m','f'.
4. salary should be in range of  0-9999 range. 

Here we  give constraint after column created. this constraint are know as Table level constraint.
here the benefit of column level constraint we can refer multiple column at the same time.
in Column level constraint we can't refer multiple column we only can refer current column.

we see that Constraint Topic in coming posts.