#Constraints: Are used to limit the type of data that can go into a table . This ensures the accuracy and reliability of the data in the database.
Constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table.
Syntax:(
Example: Create Table emp(emp_no int NOT NULL,Name Varchar2(20))
Using Alter : ALTER TABLE CUSTOMERS
MODIFY SALARY NUMBER (18) NOT NULL;
For Dropping:ALTER TABLE EMPLOYEES DROP CONSTRAINT;
Most used Constraints are Below.
NOT NULL Constraint: Ensures that a column cannot have NULL value.
DEFAULT Constraint: Provides a default value for a column when none is specified.
UNIQUE Constraint: Ensures that all values in a column are different.
PRIMARY Key: Uniquely identified each rows/records in a database table.
FOREIGN Key: Uniquely identified a rows/records in any another database table.
CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.
FAQS: What is the difference between primary key and unique key?
Primary should not allow null; where as unique key will allow nulls.
By default Primary key is created as clustered index; whereas unique key is created as non clustered index.
#Indexes:
An index can be created in a table to find data more quickly and efficiently.
An index is a pointer to a location of data and creates an entry for each value that appears in the indexed columns.
By default, Oracle creates B-tree indexes.
Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.
Categories of Indexes: Implicit or Explicit:
Indexes may also be classified as implicit or explicit. Explicit indexes are created using the create index command and implicit indexes are created by Oracle when you ,e.g. enable a unique constraint on a table.
Types of Indexes:
Total indexes are in oracle is 5.
1.B Tree- Indexes are used to avoid large sorting operations.Ex:a SQL query requiring 10,000 rows to be presented in sorted order will often use a b-tree index)
Example(Normal Index): CREATE INDEX supplier_idx
ON supplier (supplier_name);
FAQS:
Limitations of B-Tree Index(Normal Index): Low-cardinality columns:columns with less than 200 distinct values do not have the selectivity required in order to benefit from standard b-tree index
No support for SQL functions: B-tree indexes are not able to support SQL queries using Oracle's built-in functions.
2.Bitmap Index- Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index.
3.Function_Based-One of the most important advances in Oracle indexing is the introduction of function-based indexing. Function-based indexes allow creation of indexes on expressions, internal functions, and user-written functions in PL/SQL and Java.
Example: CREATE INDEX IX_NAME_LOWER
ON EMP (LOWER(ENAME));
4.Domain:A domain index is an index designed for a specialized domain, such as spatial or image processing. Users can build a domain index of a given type after the designer creates the index type.
A domain index can be created on a column of a table just like a B-tree index. However, an indextype must be explicitly specified.
Example:
CREATE INDEX ResumeTextIndex ON Employees(resume)
INDEXTYPE IS TextIndexType
PARAMETERS (':Language English :Ignore the a an');
The INDEXTYPE clause specifies the indextype to be used. The PARAMETERS clause identifies any parameters for the domain index, specified as a string. This string is passed uninterpreted to the ODCIIndexCreate routine for creating the domain index
5.Ascending or Descending Index:When defining indexes, you can specify whether the data for each column is stored in ascending or descending order. If neither direction is specified, ascending is the default.
Example:create index idx_col1 on EMP (ENO asc);
You can also create function-based indexes.
Example: CREATE INDEX IX_NAME_LOWER
ON PEOPLE (LOWER(NAME));
RENAME AN INDEX:ALTER INDEX index_name
RENAME TO new_index_name;
COLLECT STATISTICS ON AN INDEX
If you forgot to collect statistics on the index when you first created it or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics at a later date.
Syntax
The syntax for collecting statistics on an index in Oracle/PLSQL is:
ALTER INDEX index_name
REBUILD COMPUTE STATISTICS;
DROP AN INDEX
Syntax:DROP INDEX index_name;
#Sequences:In Oracle, you can create an auto number field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.
Example:CREATE SEQUENCE supplier_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.NEXTVAL, 'Kraft Foods');
We can Alter Sequence:ALTER SEQUENCE seq_name
INCREMENT BY 124;
Drop Sequence:DROP SEQUENCE sequence_name;
Constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table.
Syntax:
Example: Create Table emp(emp_no int NOT NULL,Name Varchar2(20))
Using Alter : ALTER TABLE CUSTOMERS
MODIFY SALARY NUMBER (18) NOT NULL;
For Dropping:ALTER TABLE EMPLOYEES DROP CONSTRAINT
Most used Constraints are Below.
NOT NULL Constraint: Ensures that a column cannot have NULL value.
DEFAULT Constraint: Provides a default value for a column when none is specified.
UNIQUE Constraint: Ensures that all values in a column are different.
PRIMARY Key: Uniquely identified each rows/records in a database table.
FOREIGN Key: Uniquely identified a rows/records in any another database table.
CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.
FAQS: What is the difference between primary key and unique key?
Primary should not allow null; where as unique key will allow nulls.
By default Primary key is created as clustered index; whereas unique key is created as non clustered index.
#Indexes:
An index can be created in a table to find data more quickly and efficiently.
An index is a pointer to a location of data and creates an entry for each value that appears in the indexed columns.
By default, Oracle creates B-tree indexes.
Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.
Categories of Indexes: Implicit or Explicit:
Indexes may also be classified as implicit or explicit. Explicit indexes are created using the create index command and implicit indexes are created by Oracle when you ,e.g. enable a unique constraint on a table.
Types of Indexes:
Total indexes are in oracle is 5.
1.B Tree- Indexes are used to avoid large sorting operations.Ex:a SQL query requiring 10,000 rows to be presented in sorted order will often use a b-tree index)
Example(Normal Index): CREATE INDEX supplier_idx
ON supplier (supplier_name);
FAQS:
Limitations of B-Tree Index(Normal Index): Low-cardinality columns:columns with less than 200 distinct values do not have the selectivity required in order to benefit from standard b-tree index
No support for SQL functions: B-tree indexes are not able to support SQL queries using Oracle's built-in functions.
2.Bitmap Index- Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index.
3.Function_Based-One of the most important advances in Oracle indexing is the introduction of function-based indexing. Function-based indexes allow creation of indexes on expressions, internal functions, and user-written functions in PL/SQL and Java.
Example: CREATE INDEX IX_NAME_LOWER
ON EMP (LOWER(ENAME));
4.Domain:A domain index is an index designed for a specialized domain, such as spatial or image processing. Users can build a domain index of a given type after the designer creates the index type.
A domain index can be created on a column of a table just like a B-tree index. However, an indextype must be explicitly specified.
Example:
CREATE INDEX ResumeTextIndex ON Employees(resume)
INDEXTYPE IS TextIndexType
PARAMETERS (':Language English :Ignore the a an');
The INDEXTYPE clause specifies the indextype to be used. The PARAMETERS clause identifies any parameters for the domain index, specified as a string. This string is passed uninterpreted to the ODCIIndexCreate routine for creating the domain index
5.Ascending or Descending Index:When defining indexes, you can specify whether the data for each column is stored in ascending or descending order. If neither direction is specified, ascending is the default.
Example:create index idx_col1 on EMP (ENO asc);
You can also create function-based indexes.
Example: CREATE INDEX IX_NAME_LOWER
ON PEOPLE (LOWER(NAME));
RENAME AN INDEX:ALTER INDEX index_name
RENAME TO new_index_name;
COLLECT STATISTICS ON AN INDEX
If you forgot to collect statistics on the index when you first created it or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics at a later date.
Syntax
The syntax for collecting statistics on an index in Oracle/PLSQL is:
ALTER INDEX index_name
REBUILD COMPUTE STATISTICS;
DROP AN INDEX
Syntax:DROP INDEX index_name;
#Sequences:In Oracle, you can create an auto number field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.
Example:CREATE SEQUENCE supplier_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.NEXTVAL, 'Kraft Foods');
We can Alter Sequence:ALTER SEQUENCE seq_name
INCREMENT BY 124;
Drop Sequence:DROP SEQUENCE sequence_name;
No comments:
Post a Comment