Data Model Concepts

Conceptual Data Model :

Logical Data Model  :

Physical Data Model :

What is COST ?

It is the actual resource consumption, or the estimate of resource consumption.

Translated, this says the following:
The cost is the time spent on single-block reads, plus the time spent on multiblock reads,
plus the CPU time required, all divided by the time it takes to do a single-block read.
Which means the cost is the total predicted execution time for the statement, expressed
in units of the single-block read time.

Cardinality

Expected number of rows retrieved by a particular operation in the execution plan. Vital figure to determine join, filters, and sort costs.

Join Order

Join Types

Join method

Access Methods

 FTS        

 Rowid Scan

 Index Unique Scan       -- When we have primary/unique key

 Index Range Scan        -- When there is no unique index

 Index Full Scan            -- When all data in index. Allways it is seqintial.

 Index Fast Full scan     -- Always it will make you db_file_multiblock_count. If this configured, then this will use instead of FS.

 Index Skip Scan            -- Even leading column not exists, still it will use index.

 Index Join Scan           -- When data is there in multiple indexes, then oracle will join the indexs to get the data.

 Index Bitmap Access     -- A bitmap index is organized as a B*-tree index but, with bitmap indexes, a single index entry uses a bitmap to point to many rows simultaneously. Each bitmap header stores start and end ROWIDs.

All about Execution Plan

The Execution Plan

An execution plan shows the detailed steps necessary to execute a SQL statement. These steps are expressed as a set of database operators that consume and produce rows. The order of the operators and their implementations is decided by the query optimizer using a combination of query transformations and physical optimization techniques

How To Quickly Add/Remove Column Statistics (Histograms) For A Column

In some situations it is necessary to quickly add or remove histograms from a particular column (also known as column statistics.)

This article shows how to accomplish this with one command.

The commands given in this article will not affect column statistics currently gathered for other columns, they will only touch the specified column.

Column statistics can be:
- so called minimal consisting of just 1 bucket (2 endpoints) with min/max value information required by the Optimizer
- histograms with between 2 and 254 buckets (3 and 255 endpoints respectively)
- absent with no column information recorded in the data dictionary (not normally recommended)

Assuming you are connected as the owner of the table.

To add column statistics use one of the following varieties:

• using up to the default number of buckets 75:
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name>');
• using the appropriate number of buckets for the data distribution (will be minimal when not skewed) :
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name> size skewonly');
• using maximum number of buckets (up to 254) :
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name> size 254');
• using up to specified number of buckets N (N is between 2 and 254) :
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name> size N');

• gathering statistics for multiple columns with different bucket sizes :
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt => 'for columns size N <column_name1>,<column_name2> for columns size M <column_name3>,<column_name4>');
• gathering statistics for multiple columns with different bucket sizes plus minimal information for the remaining columns:
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt => 'for all columns size 1 for columns size N <column_name1>,<column_name2> for columns size M <column_name3>,<column_name4>');

To delete histograms from a column leaving behind base column statistics i.e. minimal information required for the Optimizer (this is for pre-Oracle11g versions:)

SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name> size 1');

To completely erase all types of column statistics/histograms for a column including minimal statistics:

SQL> exec dbms_stats.delete_column_stats(null, '<table_name>', '<column_name>');

This is generally not recommended as the Optimizer needs at least the minimal (size 1) information.

In Oracle11g there is new syntax to delete the histogram for a column while still leaving behind the base column statistics:

SQL> exec dbms_stats.delete_column_stats(null, '<table_name>', '<column_name>', col_stat_type=>'HISTOGRAM');

Additionally there is functionality to delete histograms for a partition:

exec dbms_stats.delete_column_stats(ownname=>'<owner>',tabname=>'<table_name>',
colname=>'<column_name>',partname=>'<partition_name>', col_stat_type=>'HISTOGRAM')

and to delete column histograms for the table and all its partitions:

exec dbms_stats.delete_column_stats(ownname=>'<owner>',tabname=>'<table_name>',
colname=>'<column_name>',cascade_parts=>TRUE,col_stat_type=>'HISTOGRAM')