Oracle Database Private Link

What is a DBLink

A database link is a one-way connection between two physical database servers that allows a client to access them as one logical database. 
For example a client connected to local database A can use a link stored in database A to access information in remote database B, but users connected to database B cannot use the same link to access data in database A. 
If local users on database B want to access data on database A, then they must define a link that is stored in the data dictionary of database B.

Privileges Necessary for Creating Database Links

To see which privileges you currently have available:

SELECT DISTINCT PRIVILEGE AS "Database Link Privileges" FROM ROLE_SYS_PRIVS
WHERE PRIVILEGE
IN ( 'CREATE SESSION','CREATE DATABASE LINK','CREATE PUBLIC DATABASE LINK')

Private Database Links Example: 

Create:

CREATE DATABASE LINK linkName
  CONNECT TO destinationDatabaseUser IDENTIFIED BY pass USING
  '(DESCRIPTION=    
   (ADDRESS=     
   (PROTOCOL=TCP)     
   (HOST=172.20.000.001)     
   (PORT=1521))    
   (CONNECT_DATA=     
   (SERVICE_NAME=Sevi_Ce)))'  ;

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. 

Drop:

DROP DATABASE LINK linkName;

You can drop a database link just as you can drop a table or view. If the link is private, then it must be in your schema. If the link is public, then you must have the DROP PUBLIC DATABASE LINK system privilege.

Close:

ALTER SESSION CLOSE DATABASE LINK linkName;

If you access a database link in a session, then the link remains open until you close the session. A link is open in the sense that a process is active on each of the remote databases accessed through the link.

Insert:

INSERT INTO RemoteDBTable@linkName SELECT * FROM LocalDBTable;

After you have created a database link

You can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

Oracle bitmap index vs b-tree index

Difference

The b-tree indexes are used  usuall when we have too many distinct columns and for high cardinaties, and bitmap indexex are used for low cardinaties,  usually when we have repeated columns.

B-Trees 

B-Trees are the typical index type used when you do CREATE INDEX ... in a database:

• They are very fast when you are selecting just a small very subset of the index data (5%-10% max typically).
• They work better when you have a lot of distinct indexed values.
• Combining several B-Tree indexes can be done, but simpler approaches are often more efficient.
• They are not useful when there are few distinct values for the indexed data, or when you want to get a large (>10% typically) subset of the data.
• Each B-Tree index impose a small penalty when inserting/updating values on the indexed table. This can be a problem if you have a lot of indexes in a very busy table.
• Internal structure: A b-tree index has index nodes (based on data block size), it a tree.

This characteristics make B-Tree indexes very useful for speeding searches in OLTP applications, when you are working with very small data sets at a time, most queries filter by ID, and you want good concurrent performance.

Bitmap

Bitmap indexes are a more specialized index variant:

• They encode indexed values as bitmaps and so are very space efficient.
• They tend to work better when there are few distinct indexed values
• DB optimizers can combine several bitmap indexed very easily, this allows for efficient execution of complex filters in queries.
• They are very inefficient when inserting/updating values.
• Internal structure: A bitmap index looks like this, a two-dimensional array with zero and one (bit) values.

Bitmap indexes are mostly used in data warehouse applications, where the database is read only except for the ETL processes, and you usually need to execute complex queries against a star schema, where bitmap indexes can speed up filtering based on conditions in your dimension tables, which do not usually have too many distinct values.

As a very short summary: 

Use B-Tree indexes (the "default" index in most databases) unless you are a data warehouse developer and know you will benefit for a bitmap index.

Oracle Bitmap Index

Every Index Prupose

Is to provide pointers to a table rows that have a value.

Bitmap Index

In a bitmap index, instead of a list of rowids, Oracle creates a bitmap for each key value of the index.

Each bit of the map corresponds to a possible rowid. If the bit is set to 1, it means that the rowid contains that key value. 

An internal Oracle function converts the position of the bit into the corresponding rowid.

If the number of different values of the index is small, then the index bitmap will be very efficient in the use of physical space.

Example 

Suppose we have the following customers table:

CLIENTE        APELLIDO       REGION
101            PEREZ          NORTE
102            GARCIA         CENTRO
103            LOPEZ          SUR
104            SAN MARTIN     SUR
105            BROWN          CENTRO
106            CANEPA         CENTRO

The region column has low cardinality, since the possible values are very few (NORTH, CENTER, SOUTH). There are only three possible values for the region so a bitmap index would be appropriate for this column. However, a bitmap index for the CLIENT or LAST column is not recommended, given its high cardinality. 

The following would be the representation of the bitmap index for the REGION column. The index has three bitmaps, one for each region.

NORTE    CENTRO    SUR         
1        0         0
0        1         0
0        0         1
0        0         1
0        1         0
0        1         0

Each entry or bit in the bitmap index corresponds to a single row in the customer table. The value of the bit will depend on the corresponding value of the row in the table. For example, for the NORTH region the bitmap has a 1 in the first position. This is because the first row of the customer table has the NORTH value in the REGION column. The bitmap then has all zeros, indicating that the rest of the rows in the table have no clients in the NORTH region.

An SQL statement on this table and with the index bitmap, would be solved in the following way.

select count(*) from CLIENTES

    where REGION in ('NORTE','SUR');

A bitmap index can resolve this statement with great efficiency by counting the amount of ones existing in the resulting bitmap as shown in the following figure:

NORTE   CENTRO   SUR   (NORTE O SUR)
1       0        0      1
0       1        0      0
0       0        1      1
0       0        1      1
0       1        0      0
0       1        0      0

The "NORTH OR SOUTH" column is the resulting bitmap used to access the table.

Additionally, unlike B-tree indices, bitmap indices may include rows with NULL values ​​within the index structure. As for partitioned tables, bitmap indices can be used only if they are local to the partition. Global bitmap indices are not supported for partitioned tables.