Invisible Indexes in Oracle Database

DESCRIPTION
Oracle 11g introduces a new feature for indexes, invisible indexes. That can be useful in several different situations. An invisible index is an index that is maintained by the database but ignored by the optimizer unless explicitly specified. The invisible index is an alternative to dropping or making an index unusable. This feature is also functional when certain modules of an application require a specific index without affecting the rest of the application.

SYNTAX
CREATE INDEX index_name ON table_name(column_name) INVISIBLE;
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;


EXAMPLES
The following script creates and populates a table, then creates an invisible index on it.

CREATE TABLE test_tab (id  NUMBER);
Table created.

BEGIN
  FOR i IN 1 .. 10000 LOOP
    INSERT INTO test_tab VALUES (i);
  END LOOP;
  COMMIT;
END;
/
PL/SQL procedure successfully completed.

CREATE INDEX test_idx ON test_tab(id) INVISIBLE;
Index created.

EXEC DBMS_STATS.gather_table_stats(USER, 'test_tab', cascade=> TRUE);
PL/SQL procedure successfully completed.

A query using the indexed column in the WHERE clause ignores the index and does a full table scan.

SET AUTOTRACE ON
SELECT * FROM test_tab WHERE id = 9999;

----------------------------------------------------------------------------------------
| Id  | Operation         | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |      1 |        |      1 |00:00:00.01 |      24 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB |      1 |      1 |      1 |00:00:00.01 |      24 |
----------------------------------------------------------------------------------------

Setting the OPTIMIZER_USE_INVISIBLE_INDEXES parameter makes the index available to the optimizer.

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
Session altered.

SELECT id FROM test_tab WHERE id = 9999;

---------------------------------------------------------------------------------------
| Id  | Operation        | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |      1 |        |      1 |00:00:00.01 |       3 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |      1 |      1 |      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------

Making the index visible means it is still available to the optimizer when the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is reset.

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;
Session altered.

ALTER INDEX test_idx VISIBLE;
Index altered.

---------------------------------------------------------------------------------------
| Id  | Operation        | Name     | Starts | E-Rows | A-Rsows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |      1 |        |      1 |00:00:00.01 |       3 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |      1 |      1 |      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------

The current visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.

SELECT index_name, visibility FROM user_indexes WHERE index_name='TEST_IDX';

INDEX_NAME VISIBILITY
---------- --------------
TEST_IDX   VISIBLE

Resizing initial extent of the table

DESCRIPTION

There is no way to change the initial extent size of the table directly. There is a way to do this without dropping and recreating the table. It just needs to move the table with storage clause. It’s possible to move the table in other tablespace and back it again to the current one but this is not necessary. It’s just an option.

SYNTAX

ALTER TABLE [schema_name].[table_name] MOVE TABLESPACE [tablespace_name] STORAGE (INITIAL [number1] NEXT [number2] PCTINCREASE 0);

PARAMETERS or ARGUMENTS

schema_name - Name of the schema.

table_name - Name of the table which you want to change initial extent

number1 - Initial extent size in bytes. 

number2 - Size of the next extent which will be allocated to the object

NOTE

[number1] and [number2] can be represented with size_clause.

The size_clause lets you specify the amount of disk memory space. It can be a number of bytes, kilobytes (K), megabytes (M), gigabytes (G), terabytes (T). If you don’t specify any abbreviation the integer is considered as bytes.

Example: 1024; 64K; 10M ; 2G; 1T;

EXAMPLES

Tablespace name: def_tbs

Schema name      : def_schema

Table name         : test_table_1

Example 1:

ALTER TABLE def_schema.test_table_1

  MOVE TABLESPACE def_tbs

  STORAGE (INITIAL 64K NEXT 1M PCTINCREASE 0);

Example 2 :    Make the same like Example 1

ALTER TABLE def_schema.test_table_1

  MOVE TABLESPACE def_tbs

  STORAGE (INITIAL 65536 NEXT 1048576 PCTINCREASE 0);

MS Excel: ABS function

DESCRIPTION

The Microsoft Excel ABS function returns the absolute value of a number

SYNTAX

The syntax for the ABS function in Microsoft Excel is:

ABS( number )

PARAMETERS or ARGUMENTS

number - A numeric value used to calculate the absolute value.

EXAMPLES

Let's look at some Excel ABS function examples and explore how to use the ABS function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following ABS examples would return:

=ABS(A1)

 Result: 120

=ABS(A2)

 Result: 3.5

=ABS(A3)

 Result: 45

=ABS(-6.9)

 Result: 6.9

=ABS(5-15)

 Result: 10

MS Excel: IF function

DESCRIPTION

The Microsoft Excel IF function returns one value if a condition is true and another value if is not.

SYNTAX

The syntax for the IF function in Microsoft Excel is:

IF(logical_test, value_if_true, [value_if_false])

PARAMETERS or ARGUMENTS

logical_test     - The condition you want to test. You can use other logical functions within this argument, including AND, OR and XOR functions.

value_if_true - The value that you want to be returned if the result of logical_test is TRUE.

value_if_false - The value that you want to be returned if the result of logical_test is FALSE. This parameter is optional.

NOTE

You can put another IF function in IF function in the place of some of the arguments.

=IF(E2>=85,"A",IF(E2>=75,"B","C"))

EXAMPLES

Let's look at some Excel IF function examples and explore how to use it

The following IF examples would return:

=IF(A2>B2,"Over Budget","OK")

  Result: Over Budget

=IF(A4=500,B4-A4,"")

  Result: 425

=IF(A3>200, "Larger", "Smaller")

  Result: Larger

=IF(A2=1500, "Equal", "Not Equal")

  Result: Equal

Oracle DB: REPLACE Function

DESCRIPTION

The Oracle REPLACE function replaces a sequence of characters in a string with another set of characters.

SYNTAX

REPLACE( string1, string_to_replace, replacement_string )

PARAMETERS or ARGUMENTS

string1 - The string to replace a sequence of characters with another set of characters.

string_to_replace - The string that will be searched for in string1.

replacement_string – This parameter is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the REPLACE function simply removes all occurrences of string_to_replace, and returns the resulting string.

EXAMPLES

REPLACE('123123test', '12');    
     Result: '33test'

REPLACE('123work123', '123');   
     Result: 'work'

REPLACE('222oracle', '2', '3'); 
     Result: '333oracle'

REPLACE('0000102300', '0');     
     Result: '123'

REPLACE('0000888', '0', ' ');   
     Result: '    888'

Oracle DB: NVL Function

DESCRIPTION

NVL replaces NULL value with other value in the results of a query.

SYNTAX

NVL(expr1, expr2)

PARAMETERS or ARGUMENTS

If expr1 is NULL, then NVL returns expr2. If expr1 is not NULL, then NVL returns expr1.

The arguments expr1 and expr2 can have any datatype. If their datatypes are different, then Oracle Database implicitly converts one to the other. If they are cannot be converted implicitly, the database returns an error.

The implicit conversion is implemented as follows:

-          If expr1 is character data, then Oracle Database converts expr2 to the datatype of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.

-          If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

EXAMPLES

SQL> SELECT NVL(supplier_city, 'n/a')

       FROM suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a NULLvalue. Otherwise, it would return the supplier_city value.

MS Excel: SUM function

DESCRIPTION

The Microsoft Excel SUM function adds all numbers in a range of cells and returns the result.

SYNTAX
The syntax for the SUM function in Microsoft Excel is:
SUM( number1, [number2, ... number_n] )

OR

SUM ( cell1:cell2, [cell3:cell4], ... )

PARAMETERS or ARGUMENTS

number    -  A numeric value that you wish to sum.

cell           -  The range of cells that you wish to sum.

NOTE

You can sum combinations of both numbers and ranges of cells using the SUM function.

EXAMPLES

Let's look at some Excel SUM function examples and explore how to use the SUM function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following SUM examples would return:

=SUM(A2, A3)            
  Result: 17.7
=SUM(A3, A5, 45)        
  Result: 57.6
=SUM(A2:A6)             
  Result: 231.2
=SUM(A2:A3, A5:A6)      
  Result: 31.2
=SUM(A2:A3, A5:A6, 500) 
  Result: 531.2