Indexes as quick sorting (III)

Part III - How to manage?

The previous parts described the necessity and ways to create an index.
On using and supporting indexes in production DB you should monitor, analyze and manage objects. Developer applications have many useful abilities for your work.

Oracle SQL Developer	SQLDetective
Lists of actions for object (INDEX) managing. Note: screen shots in this article are from Oracle SQL Developer and SQLDetective applications for developers.

You see that different developer UIs allow some useful commands for index modifying. Looks like the action lists are dependent on application price and user necessities.

Oracle SQL Developer	SQLDetective

Oracle SQL Developer allows to drop not only the whole index but it's possible to drop a partition. Don't worry about data on deleting index or its part – you'll not lose the source data (not-indexed).

Oracle SQL Developer	SQLDetective

SQLDetective rebuilds the index by Index Wizard with more abilities than in an additional UI of Oracle SQL Developer.

Oracle SQL Developer	SQLDetective

You should rename an index manually in SQL Editor of SQLDeveloper typing "ALTER INDEX … RENAME TO …". Note: it's possible to rename an index partition (in the latest Oracle DB versions) but the renaming action is not available for some domain indexes.

Oracle SQL Developer	SQLDetective

Fast action for index enabling/disabling is available in both applications.

Oracle SQL Developer	SQLDetective

Use the COALESCE action to merge the contents of index blocks where possible to free blocks for reuse. Note: this command is equal to ALTER INDEX ... SHRINK SPACE.

Oracle SQL Developer	SQLDetective

Results of index analyzing allow to modify objects for best using.

Oracle SQL Developer	SQLDetective

Switch Activity action is available for function-based indexes. The UI for enabling/disabling exists in SQLDetective only.

Oracle SQL Developer	SQLDetective

The same clause INVISIBLE from ALTER INDEX command is available for any type of index but there is additional item in object menu named "Switch Visibility" in SQLDetective.

Oracle SQL Developer	SQLDetective

SQLDetective allows to move the index from one tablespace to another with modifying segment attributes. It's possible to relocate/move an index by popup menu in Storage Manager or Object Navigator. Also several indexes may be moved to other tablespace by Group Action of Object Navigator. Unfortunately, the window GUI has some problems in caption and elements align.
As an index is a copy of table/view/cluster then some properties of parent objects are applied automatically to the index. For example, it's unnecessary to set additional grants to columns used in index formula. But as an index is a standalone object then the storage parameters can be set separately from the parent object - for example, the source table is stored in one tablespace and indexes are stored in another tablespace.

For proper and helpful using of indexes, please, learn the following articles of Oracle Documentation: "Indexes and Index-Organized Tables", "Create Index", "Alter Index", "Drop Index", "Create Table (index organized)", "Create Cluster", "Create Indextype", "Create Operator", "Create Type".
Index your data and the application life becomes faster!

Indexes as quick sorting (II)

Part II – What index?

The previous part of index describing showed some examples of DML fast execution.
Now not only char columns are used in BigData. And it seems that Oracle can't work with all of them:
"
You cannot create an index on columns or attributes whose type is user-defined, LONG, LONG RAW, LOB, or REF, except that Oracle Database supports an index on REF type columns or attributes that have been defined with a SCOPE clause.
"
Don't worry. Oracle DB has a workaround. There are many types of indexes.
Note: The following screen shots are from Oracle SQL Developer and SQLDetective applications.

Oracle SQL Developer	SQLDetective
Don't forget to set Storage parameters on index creating. Not all of them are allowed for altering:
Create indexes only for frequent using statements because the index is stored in tablespace as a table copy.

Here are some useful words from Oracle Documentation about index organizations:
* B-tree (short for balanced trees) indexes
     * Index-organized tables
          Index-organized tables are useful when related pieces of data must be stored together or data must be physically stored in a specific order. A typical use of this type of table is for information retrieval, spatial data, and OLAP applications.
          A secondary index is an index on an index-organized table.

Oracle SQL Developer	SQLDetective
Table Wizards for Index-Organized table creating

     * Reverse key indexes
          The bytes of the index key are reversed.
     * Descending indexes
          Descending order of data is used.
     * B-tree cluster indexes
          Instead of pointing to a row, the key points to the block that contains rows related to the cluster key.

Oracle SQL Developer	SQLDetective
There is no Cluster Wizard in Oracle SQL Developer
An index on cluster may be created by object wizard in SQLDetective only

* Bitmap and bitmap join indexes
     In a bitmap index, an index entry uses a bitmap to point to multiple rows. In contrast, a B-tree index entry points to a single row

Oracle SQL Developer	SQLDetective
Only SQLDetective 4.4 allows to set joins

* Function-based indexes
     Function-based indexes are efficient for evaluating statements that contain functions in their WHERE clauses.

Oracle SQL Developer	SQLDetective
Type manually your formula into "Expression" editor.
SQLDetective has a tool for generating an expression, but it's used in Query Builder only.

* Application domain indexes
     You can encapsulate application-specific index management routines as an indextype schema object, and then define a domain index on table columns or attributes of an object type. Extensible indexing can efficiently process application-specific operators.

Oracle SQL Developer	SQLDetective
An indextype in Oracle SQL Developer you should create manually, it's not supported by object wizard.
SQLDetective supports the Indextype Wizard.

     Extensive indexing can:
          Accommodate indexes on customized, complex data types such as documents, spatial data, images, and video clips (see "Unstructured Data")
          Make use of specialized indexing techniques

* System indexes are created automatically for table constraints except foreign key.

On index creating and modifying, please, don't fully rely on wizard UI as they support not all logical issues and some rare or not-described in Oracle Documentation clauses are not supported by the developer application.
For using index abilities you should create objects of some other types ("Create Table (index organized)", "Create Cluster", "Create Indextype", "Create Operator", "Create Type"):

Oracle SQL Developer	SQLDetective
Oracle SQL Developer doesn't support Clusters and Indextypes wizards

Read about some other index options in the next topic.

Indexes as quick sorting (I)

Part I – Why indexes?

Oracle Database now grows and grows. It becomes BigData. As modern age wants to do everything fast then there is the best way to select necessary data in time.
Let's see an example.

1. Create a simple table with data:

CREATE TABLE FOR_SORT ( COL1 VARCHAR2(10 BYTE) NULL, COL2 NUMBER(5,0) NULL )
/
-- Insert script for table FOR_SORT
INSERT INTO FOR_SORT(COL1, COL2) VALUES('ddd', 1)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('ggg', 2)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('ccc', 3)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('fff', 4)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('hhh', 5)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('bbb', 6)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('iii', 7)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('aaa', 8)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('eee', 9)
/

2. Explain plan for simple select statement (SELECT A.* FROM FOR_SORT A;) is the following:

The red colored line in Explain Plan results means that the statement is not optimized.

3. Create an index on one column:

CREATE INDEX IND_COL1 ON FOR_SORT ( COL1 )

4. Explain plan for a select statement with indexed column in WHERE clause (SELECT A.* FROM FOR_SORT A WHERE A.COL1 > 'c';) is more cheaper. Pay attention to "Cost" and "CPU Cost" values:

Note: data is ordered automatically by indexed column.

5. Let's compare explain plan for the same statement but with non-indexed column in WHERE clause(SELECT A.* FROM FOR_SORT A WHERE A.COL2 > 2;):

Result of not-ordered select statement is the same but Explain Plan differs - see "Cost" and "CPU Cost" values: 1/2 (indexed column in WHERE clause) -> 3 (not-indexed) and 14613/7321 (indexed) -> 37587 (not-indexed).

6. Compare explain plan for statements with ordered data, with and without mentioning an indexed column:
SELECT A.* FROM FOR_SORT A ORDER BY A.COL1 DESC;

SELECT A.* FROM FOR_SORT A WHERE A.COL1 IS NOT NULL ORDER BY A.COL1 DESC;

"Cost" and "CPU Cost" values show the best selection: ordering takes much resources but the clause with indexed column decreases it (4 -> 2, 3 -> 1, 36022939 -> 17753, 37137 -> 8921). Results of both select statements are equal, a short clause (any mention of indexed column COL1 in WHERE clause but not in ORDER BY clause) was added for execution speed up.

You see, index helps to execute the DML statements faster. About more index using, please, read the next topic.

Data storing

There are several object types in Oracle DB for storing data. Some of them work with physical data, others operate with virtual data. Let's investigate TABLES, VIEWS, MATERIALIZED VIEWS, DIMENSIONS.

SQLDEVELOPER from Oracle and SQLDetective (SD) from Conquest Software Solutions allow to work partly with listed objects and their data.
SQLDEVELOPER works with Tables, Views, Materialized Views by object wizards:

and with some other data by Reports:

SD works with Tables, Views, Materialized Views, Dimensions by object wizards: 

TABLES
Tables are the main objects for work with data: adding, editing, deleting, storing. Table's data is stored physically in tablespaces (see SD wizard that allows to set storage parameters on table creating or modifying):

 Table Wizard in SQLDEVELOPER allows the limited list of column types (built-in: varchar2, number, date, clob, blob; ANSI: bfile, binary_double, etc.):

 Table Wizard in SD allows to generate columns as built-in types (number, varchar, date, raw, long, etc.), ANSI (char, float, etc.), user defined (object types of Oracle DB schemas):

 

 

 According to Oracle Documentation a table may be created on OBJECT TYPE, QUEUE, XML TYPE, that's possible by Table Wizard in SD:

 

 

 

 Constraints may be generated in SD on table creating:

 Table data is available for selecting and editing (Insert, Delete, Update, Merge) by Data Browser (Smart Dataset with Dataset Manager in SD), Query and Dataset Builders, Data tab in SQL Editor or Table Wizard.

 Data analyzing can be done by Data Dependency Analyzer, Explain Plan window, Table/View/Partition/Index/Cluster Analyzer (collect statistics, validate structures, list chained rows). On comparing objects in SD it's possible to compare tables by object properties and number of rows. Some developer applications allow to export and import data, duplicate rows, truncate or commit data, rename or drop object without typing and executing Oracle DB commands in SQL Editor. Data viewing in such applications is easy because of visualized sorts and filters.

 

 

 

VIEWS
Data in View is "virtual", it's aggregated from tables and views:

 

 As a View is created as selection from several tables so data editing becomes hard. Data in view is changed automatically on changing it in the source tables. If you need to edit the selected data then it's better to create a Materialized View. More complex view can be created on Object or XML Type, with check constraint. In different editions since Oracle 11g the same named view can be created by different select statements. View allow to rename columns, join, sort and filter data on selecting from source tables.

MATERIALIZED VIEWS
They were named Snapshots in Oracle 7. On MatView creating its data looks like imported from the select statement into MatView with physically created Table. In other words, the MatView becomes a standalone table after creating. Data of MatView may be modified and new version will be stored in the referenced table with the same name as MatView or refreshed from source tables of the select statement.
SQLDEVELOPER / Materialized View Wizard:

 

 

 

 

 SQLDetective / Materialized View Wizard:

 

DIMENSIONS
Dimension wizard in SD helps to visualize an OLAP cube structure:

 

 

 

Please, see the "Select statement" topic of Oracle Documentation about using MODEL, MEASURES, CUBE, DIMENSIONS.