SQL - What is an Index?

What is an Index?

• An index is a performance-tuning method of allowing faster retrieval of records. 
• An index creates an entry for each value that appears in the indexed columns.By default, Oracle creates B-tree indexes.
• Some indexes are created implicitly through constraints that are placed on a table. For example, a column with the constraint that its values be unique causes Oracle to create a unique key index.
• Indexes don’t fix all performance problems and can actually degrade performance if used improperly.
  Always test all applications with any new index you create.

Example

select Employees.FIRST_NAME from employees where EMPLOYEES.SALARY >1000  and Employees.FIRST_NAME like 'M%';

Oracle indexing Types

1.             B-tree index,
2.             Bitmap indexes,
3.             Function-based indexes,
4.             index-only tables (IOTs);

How these indexes may dramatically increase the speed of Oracle SQL queries ?

B-tree

•       A tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time.
•       The B-tree is a generalization of a binary search tree in that a node can have more than two children.
•       Indexes contain pointers (in the form of ROWIDs) to table data, This reduces I/O by eliminating the need to sequentially scan a table’s blocks when searching for a row (sequential scan of table blocks is called a "full table scan"). 

SQL – Union vs. Union All – Which is better for performance?

UNION

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL

• The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

Which is better for performance?

A UNION statement effectively does a SELECT DISTINCT on the results set.

If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

Example

select Employees.FIRST_NAME from employees where EMPLOYEES.SALARY>1000 union

select Employees.FIRST_NAME from employees where  Employees.FIRST_NAME like 'M%';

SQL Tuning Advisor

Grant advisor to hr;
grant SELECT_CATALOG_ROLE to HR;
grant SELECT ANY DICTIONARY to HR;
ALTER system SET CONTROL_MANAGEMENT_PACK_ACCESS='DIAGNOSTIC+TUNING';

Validate creation of one master record with one detail record at least

Case
Any new Department must provide at least one Employee.

Implementation Method

1. Create "Business Component from Tables" for Departments and Employees.
2. Check the property "Composition Association" at the association.

There are many method to apply the validation of existing one Employee for each Department:

• Using "Entity Validators" of type "Script Expression"
  Create new "Validator" on the Department of type script Expression and write:

 if(DETAIL_ACCESSOR_NAME.count('ANY_DETAIL_ATTRIBUTE_NAME') >=1)
return true;
return false;

• Using "Entity Validators" of type"Collection":
  Cretae new entity validators on Department of type "Collection",
  Choose the :
  Operation =  "Count"
  Accessor  = Accessor Name of Employee.
  Atribute   = any attribute
  Operator  = "GreaterOrEqualTo"
  Enter Literal Value = 1
• Create managed been class implements "BindingContainerValidator" interface and overrides "validateBindingContainer" method:

@Override
public void validateBindingContainer(BindingContainer bindingContainer) {
if(  ( (DCIteratorBinding) bindingContainer.get("DETAIL_ITERATOR_NAME")).getCurrentRow()==null){
throw new ValidatorException(new FacesMessage("Create a new Contact info before commit"));
        }
    }
       then go to page definition properties and set two properties
       CustomValidator="#{YOUR_CUSTOM_VALIDATE_MANAGED_BEAN}"
      SkipValidation="custom"

EntityState ( STATUS_NEW , STATUS_INITIALIZED )

EntityState

1. STATUS_INITIALIZED ( -1 )This status indicates a new record that is not yet a candidate to insert into the database as no changes have yet occurred to its attributes, it's basically just empty.
2. STATUS_NEW ( 0 )Says that at least one of the attributes of the EO has been updated ( attribute is AutoSubmit or a  button is  clicked then the validation will start) and thus the record is a candidate to be inserted to the database.

When you click "CreateInsert" button, the new record status is STATUS_INITIALIZED

When you set any attribute value, the entity's state transitions into NEW and will be added to the transaction.

When an entity row is created, modified, or removed, it is automatically enrolled in the transaction's list of pending changes.

        The entity instance is added to the entity cache once the primary key is populated.

When you call commit() on the Transaction object, it processes its pending changes list, validating new or modified entity rows that might still be invalid.

When the entity rows in the pending list are all valid, the Transaction issues a database SAVEPOINT and coordinates saving the entity rows to the database. 

If all goes successfully, it issues the final database COMMIT statement.
If anything fails, the Transaction performs a ROLLBACK TO SAVEPOINT to allow the user to fix the error and try again.

The Transaction object used by an application module represents the working set of entity rows for a single end-user transaction.By design, it is not a shared, global cache.

The database engine itself is an extremely efficient shared, global cache for multiple, simultaneous users.

Setting row with status STATUS_INITIALIZED delay validation.

https://docs.oracle.com/cd/E57014_01/adf/api-reference-model/oracle/jbo/Row.html