Scripts for Indexes - Part 4 Posted by MOHAMMED ABDUL HAMEED on 7:21 AM

CREATE INDEX : Creates an index on a table where Duplicate values are allowed.

CREATE INDEX Syntax : CREATE INDEX index_name ON table_name (column1, column2, ...);

SQL> CREATE INDEX emp_ename ON emp(ename);
 
         Index created.

CREATE UNIQUE INDEX : Creates a unique index on a table where Duplicate values are not allowed:

CREATE UNIQUE INDEX Syntax :CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

SQL> CREATE UNIQUE INDEX dept_unique_index ON dept                  (dname);

                Index created.

DROP INDEX : To Drop the index.

DROP INDEX Syntax : DROP INDEX index_name;

SQL> DROP INDEX emp_ename; 

        Index dropped.

Indexes Data Dictionary Views - Part 3 Posted by MOHAMMED ABDUL HAMEED on 4:54 AM

DBA_INDEXES : DBA view describes indexes on all tables in the database.

ALL_INDEXES : ALL view describes indexes on all tables accessible to the user.

USER_INDEXES : USER view is restricted to indexes owned by the user. 

INDEX_STATS : Stores information from the last ANALYZE INDEX...VALIDATE STRUCTURE statement.

INDEX_HISTOGRAM : Stores information from the last ANALYZE INDEX...VALIDATE STRUCTURE statement.

Type of Indexes in Oracle - Part 2 Posted by MOHAMMED ABDUL HAMEED on 2:48 PM

B-Tree index :- B-tree is a self-balancing tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time.

This index is default index in oracle, Good for high cardinality (not identical or unique values like usernames, user-ids)

Bitmap index:- Bitmap index is a special kind of database index that uses bitmaps.

Excellent in Data warehouse environments Good for low cardinality values ( Less repeated values ex: Gender)

Function Based index:- Function-based indexes allow you to create an index based on a function or expression. 

Good for columns that have SQL functions applied to them. This can be used with either a B-tree or bitmap index.
           
Concatenate or Composite index: - A database composite index or multi-column index is an index that is based on several columns.

Reverse index:-  A form of B-tree index; useful to balance I/O in an index that has many sequential inserts.

Descending Index:- A form of B-tree index; used with indexes where corresponding column values are sorted in a descending order (the default order is ascending).

Global Partitioned Index:- Global index across all partitions in a partitioned table or regular table. This can be a B-tree index type and can’t be a bitmap index type.

Local Partitioned Index:- Local index based on individual partitions in a partitioned table. This can be either a B-tree or bitmap index type.

Indexes in Oracle - Part 1 Posted by MOHAMMED ABDUL HAMEED on 1:20 PM

An indexes are schema objects that contains an entry for each value that appears in the indexed columns of the table and provides fast access to rows.Index is a database object that makes data retrieval faster.

 We can say Indexes are the easiest way to improve the performance of long running queries with full tables scan. By properly indexing large tables query completion time can go from hours to minutes.

Indexes created on column and that column is called index key.

Indexes not only improve Select queries but also it improves Update & Delete.

Oracle 12.2 New Feature: SQL*Plus History Posted by MOHAMMED ABDUL HAMEED on 1:17 PM

SQL*Plus History is not enabled by default we need to enable it

Sql > set hist on

We can check the history is enable or not by using the below command :

Sql > show history
history is ON and set to "100"

Note :  The default length of the history is 100.

To change the length of the history we can use :

Sql > set hist 50

Check the History :

Sql > history
2 show history
3 select tablespace_name from dba_tablspaces;
4 select host_name from v$instance;


To run a command from the history use the history run command

Sql > hist 4 run

Host_Name
-----------------
mahameed

Help History :

SQL> help history
HISTORY
-------
Stores, lists, executes, edits of the commands
entered during the current SQL*Plus session.
 HIST[ORY] [N {RUN | EDIT | DEL[ETE]}] | [CLEAR]
N is the entry number listed in the history list.
Use this number to recall, edit or delete the command.
 Example:
HIST 3 RUN - will run the 3rd entry from the list.
HIST[ORY] without any option will list all entries in the list.