It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

SPOOL command



Syntax

SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

CRE[ATE]
Creates a new file with the name specified.

REP[LACE]
Replaces the contents of an existing file. If the file does not exist, REPLACE creates the file. This is the default behavior.

APP[END]
Adds the contents of the buffer to the end of the file you specify.

OFF
Stops spooling.

OUT
Stops spooling and sends the file to your computer's standard (default) printer. This option is not available on some operating systems.

Enter SPOOL with no clauses to list the current spooling status.

To record your output in the new file DIARY using the default file extension, enter
SPOOL DIARY CREATE

To append your output to the existing file DIARY, enter
SPOOL DIARY APPEND

To record your output to the file DIARY, overwriting the existing content, enter
SPOOL DIARY REPLACE

To stop spooling and print the file on your default printer, enter
SPOOL OUT

Btree Vs Bitmap indexes - When to choose whom


  • B-Trees are the typical index type used when you do CREATE INDEX ... in a database:
    1. They are very fast when you are selecting just a small very subset of the index data (5%-10% max typically)
    2. They work better when you have a lot of distinct indexed values.
    3. Combining several B-Tree indexes can be done, but simpler approaches are often more efficient.
    4. 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.
    5. 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.

    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 indexes are a more specialized index variant:
    1. They encode indexed values as bitmaps and so are very space efficient.
    2. They tend to work better when there are few distinct indexed values
    3. DB optimizers can combine several bitmap indexed very easily, this allows for efficient execution of complex filters in queries.
    4. They are very inefficient when inserting/updating 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.

Creating EXPLAIN PLAN for Select Queries

1. Create PLAN_TABLE and a synonym so that all user can access this table.

The explain plan process stores data in the PLAN_TABLE. This table can be located in the current schema or a shared schema and is created using in SQL*Plus as follows.


-- Creating a shared PLAN_TABLE prior to 11g
SQL> CONN sys/password AS SYSDBA
Connected
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql  
OR SQL> @C:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql  -- on windows
SQL> GRANT ALL ON sys.plan_table TO public;
SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;

2. After this AUTOTRACE - The Easy Option?

Switching on the AUTOTRACE parameter in SQL*Plus causes an explain to be performed on every query. In SQL*Plus you can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after a successful SQL DML statement, such as SELECT, DELETE, UPDATE or INSERT. It is useful for monitoring and tuning the performance of these DML statements.


You can control the report by setting the AUTOTRACE system variable. See Table 11-1.


Table 11-1 Autotrace Settings

Autotrace Setting Result
------------------ -------
SET AUTOTRACE OFF No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY Similar to SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.

3. But when we try to enable we get following errors:
SQL> SET AUTOTRACE ON
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report


Setups Required for the Autotrace Report
-----------------------------------------
To use this feature, the PLUSTRACE role must be granted to the user, such as HR. DBA privileges are required to grant the PLUSTRACE role.

4. When as a SYS user we tried to assign this role:

SQL> grant plustrace to hr;
grant plustrace to hr
      *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

5. To fix this, we have to manually create this role by executing below sql:

In Unix: $ORACLE_HOME/sqlplus/admin/plustrce.sql
OR
In Windows: C:\app\ehimkar\oracle\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql

6. Now Grant this role to user HR:

SQL> grant plustrace to hr;

Grant succeeded.

7. Now we are able to connect as HR and enable the trace:

SQL> conn hr/hr
Connected.
SQL> set autotrace on;
SQL>

8. Now when we execute any SQL a Explain plan will be generated  quickly for this:

SQL> select * from employees
  2  where employee_id=201;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
        201 Michael              Hartstein
MHARTSTE                  515.123.5555         17-FEB-04 MK_MAN          13000
                      100            20



Execution Plan Plan hash value: 1833546154
-------------- --------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU

)| Time     |

--------------------------------------------------------------------------------

-------------

|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0

)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0

)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0

)| 00:00:01 |

--------------------------------------------------------------------------------

-------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=201)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1034  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>


If you do not want to generate Explain plan like above. If you want only Explain plan to be generated for specific queries then:

Create PLAN_TABLE in HR schema by Executing following script from HR schema:

----------------------------------------------------------------------------

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql  
OR SQL> @C:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql  -- on windows

A).

EXPLAIN PLAN SET STATEMENT_ID='t1'
FOR SELECT Employee_id FROM EMPloyees
where employee_id = 184;

SELECT PLAN_ID, STATEMENT_ID, OPERATION, OBJECT_NAME
    FROM PLAN_TABLE
    WHERE STATEMENT_ID='t1';

   PLAN_ID STATEMENT_ID         OPERATION            OBJECT_NAME
---------- -------------------- -------------------- --------------------
        19 t1                   SELECT STATEMENT
        19 t1                   INDEX                EMP_EMP_ID_PK

B).
EXPLAIN PLAN SET STATEMENT_ID='em1'
FOR SELECT FIRST_NAME  FROM EMPloyees
where employee_id = 184;

SQL> SELECT PLAN_ID, STATEMENT_ID, OPERATION, OBJECT_NAME
  2  FROM PLAN_TABLE
  3  WHERE STATEMENT_ID='em1';
  
   PLAN_ID STATEMENT_ID         OPERATION            OBJECT_NAME
---------- -------------------- -------------------- --------------------
         2 em1                  SELECT STATEMENT
         2 em1                  TABLE ACCESS         EMPLOYEES
         2 em1                  INDEX                EMP_EMP_ID_PK

C).
EXPLAIN PLAN SET STATEMENT_ID='e3'
FOR SELECT * FROM EMPloyees
where employee_id = 184;

SELECT PLAN_ID, STATEMENT_ID, OPERATION, OBJECT_NAME
   FROM PLAN_TABLE
   WHERE STATEMENT_ID='e3';
  
   PLAN_ID STATEMENT_ID         OPERATION            OBJECT_NAME
---------- -------------------- -------------------- --------------------
         4 e3                   SELECT STATEMENT
         4 e3                   TABLE ACCESS         EMPLOYEES
         4 e3                   INDEX                EMP_EMP_ID_PK
 
D).
EXPLAIN PLAN SET STATEMENT_ID='e4'
FOR SELECT * FROM EMPloyees
WHERE FIRST_NAME = 'Jonathon';

SELECT PLAN_ID, STATEMENT_ID, OPERATION, OBJECT_NAME
   FROM PLAN_TABLE
   WHERE STATEMENT_ID='e4';
   
   PLAN_ID STATEMENT_ID         OPERATION            OBJECT_NAME
---------- -------------------- -------------------- --------------------
         5 e4                   SELECT STATEMENT
         5 e4                   TABLE ACCESS         EMPLOYEES
 
E). 
EXPLAIN PLAN SET STATEMENT_ID='e5'
FOR SELECT * FROM EMPloyees
WHERE FIRST_NAME = 'Jonathon'
AND EMPLOYEE_ID = 184;

SELECT PLAN_ID, STATEMENT_ID, OPERATION, OBJECT_NAME
   FROM PLAN_TABLE
   WHERE STATEMENT_ID='e5';

   PLAN_ID STATEMENT_ID         OPERATION            OBJECT_NAME
---------- -------------------- -------------------- --------------------
         6 e5                   SELECT STATEMENT
         6 e5                   TABLE ACCESS         EMPLOYEES
         6 e5                   INDEX                EMP_EMP_ID_PK
 
F).  
EXPLAIN PLAN SET STATEMENT_ID='e6'
FOR SELECT * FROM EMPloyees
WHERE EMPLOYEE_ID = 184
AND UPPER(FIRST_NAME) = 'JONATHON';


SELECT PLAN_ID, STATEMENT_ID, OPERATION, OBJECT_NAME
   FROM PLAN_TABLE
   WHERE STATEMENT_ID='e6';

   PLAN_ID STATEMENT_ID         OPERATION            OBJECT_NAME
---------- -------------------- -------------------- --------------------
         6 e5                   SELECT STATEMENT
         6 e5                   TABLE ACCESS         EMPLOYEES
         6 e5                   INDEX                EMP_EMP_ID_PK
 

Oracle Function Based Indexes



A function-based index is an index built on an expression. It extends your indexing capabilities beyond indexing on a column. A function-based index increases the variety of ways in which you can access data.

Notes:
  • You must set the QUERY_REWRITE_ENABLED initialization parameter to TRUE.
  • The index is more effective if you gather statistics for the table or schema, using the procedures in the DBMS_STATS package.
  • The index cannot contain any null values. Either make sure the appropriate columns contain no null values, or use the NVL function in the index expression to substitute some other value for nulls.
The expression indexed by a function-based index can be an arithmetic expression or an expression that contains a PL/SQL function, package function, C callout, or SQL function. Function-based indexes also support linguistic sorts based on collation keys, efficient linguistic collation of SQL statements, and case-insensitive sorts.

Like other indexes, function-based indexes improve query performance. For example, if you need to access a computationally complex expression often, then you can store it in an index. Then when you need to access the expression, it is already computed. 

Function-based indexes have all of the same properties as indexes on columns. However, unlike indexes on columns which can be used by both cost-based and rule-based optimization, function-based indexes can be used by only by cost-based optimization. 

Advantages of Function-Based Indexes

Function-based indexes:
  • Increase the number of situations where the optimizer can perform a range scan instead of a full table scan. For example, consider the expression in this WHERE clause:
    CREATE INDEX Idx ON Example_tab(Column_a + Column_b);
    SELECT * FROM Example_tab WHERE Column_a + Column_b < 10;
    The optimizer can use a range scan for this query because the index is built on (column_a + column_b). Range scans typically produce fast response times if the predicate selects less than 15% of the rows of a large table. The optimizer can estimate how many rows are selected by expressions more accurately if the expressions are materialized in a function-based index. (Expressions of function-based indexes are represented as virtual columns andANALYZE can build histograms on such columns.)
  • Precompute the value of a computationally intensive function and store it in the index. An index can store computationally intensive expression that you access often. When you need to access a value, it is already computed, greatly improving query execution performance.
  • Create indexes on object columns and REF columns. Methods that describe objects can be used as functions on which to build indexes. For example, you can use the MAP method to build indexes on an object type column.
  • Create more powerful sorts. You can perform case-insensitive sorts with the UPPER and LOWER functions, descending order sorts with the DESCkeyword, and linguistic-based sorts with the NLSSORT function.

Another function-based index calls the object method distance_from_equator for each city in the table. The method is applied to the object column Reg_Obj. A query could use this index to quickly find cities that are more than 1000 miles from the equator:

CREATE INDEX Distance_index 
ON Weatherdata_tab (Distance_from_equator (Reg_obj));

SELECT * FROM Weatherdata_tab 
WHERE (Distance_from_equator (Reg_Obj)) > '1000';


Another index stores the temperature delta and the maximum temperature. The result of the delta is sorted in descending order. A query could use this index to quickly find table rows where the temperature delta is less than 20 and the maximum temperature is greater than 75.
CREATE INDEX compare_index 
ON Weatherdata_tab ((Maxtemp - Mintemp) DESC, Maxtemp);

SELECT * FROM Weatherdata_tab
WHERE ((Maxtemp - Mintemp) < '20' AND Maxtemp > '75');

Examples of Function-Based Indexes

Example: Function-Based Index for Case-Insensitive Searches
The following command allows faster case-insensitive searches in table EMP_TAB.
CREATE INDEX Idx ON Emp_tab (UPPER(Ename));

The SELECT command uses the function-based index on UPPER(e_name) to return all of the employees with name like :KEYCOL.
SELECT * FROM Emp_tab WHERE UPPER(Ename) like :KEYCOL;

Example: Precomputing Arithmetic Expressions with a Function-Based Index

The following command computes a value for each row using columns A, B, and C, and stores the results in the index.
CREATE INDEX Idx ON Fbi_tab (A + B * (C - 1), A, B);

The SELECT statement can either use index range scan (since the expression is a prefix of index IDX) or index fast full scan (which may be preferable if the index has specified a high parallel degree).
SELECT a FROM Fbi_tab WHERE A + B * (C - 1) < 100;

Example: Function-Based Index for Language-Dependent Sorting

This example demonstrates how a function-based index can be used to sort based on the collation order for a national language. The NLSSORT function returns a sort key for each name, using the collation sequence GERMAN.
CREATE INDEX Nls_index 
    ON Nls_tab (NLSSORT(Name, 'NLS_SORT = German'));

The SELECT statement selects all of the contents of the table and orders it by NAME. The rows are ordered using the German collation sequence. The Globalization Support parameters are not needed in theSELECT statement, because in a German session, NLS_SORT is set to German and NLS_COMP is set to ANSI.
SELECT * FROM Nls_tab WHERE Name IS NOT NULL
    ORDER BY Name;

Restrictions for Function-Based Indexes

Note the following restrictions for function-based indexes:

  • Only cost-based optimization can use function-based indexes. Remember to call DBMS_STATS.GATHER_TABLE_STATISTICS orDBMS_STATS.GATHER_SCHEMA_STATISTICS, for the function-based index to be effective.
  • Any top-level or package-level PL/SQL functions that are used in the index expression must be declared as DETERMINISTIC. That is, they always return the same result given the same input, for example, the UPPER function. You must ensure that the subprogram really is deterministic, because Oracle Database does not check that the assertion is true.
    The following semantic rules demonstrate how to use the keyword DETERMINISTIC:
    • You can declare a top level subprogram as DETERMINISTIC.
    • You can declare a PACKAGE level subprogram as DETERMINISTIC in the PACKAGE specification but not in the PACKAGE BODY. Errors are raised ifDETERMINISTIC is used inside a PACKAGE BODY.
    • You can declare a private subprogram (declared inside another subprogram or a PACKAGE BODY) as DETERMINISTIC.
    • DETERMINISTIC subprogram can call another subprogram whether the called program is declared as DETERMINISTIC or not.
  • If you change the semantics of a DETERMINISTIC function and recompile it, then existing function-based indexes and materialized views report results for the prior version of the function. Thus, if you change the semantics of a function, you must manually rebuild any dependent function-based indexes and materialized views.
  • Expressions in a function-based index cannot contain any aggregate functions. The expressions should reference only columns in a row in the table.
  • You must analyze the table or index before the index is used.
  • Bitmap optimizations cannot use descending indexes.
  • Function-based indexes are not used when OR-expansion is done.
  • The index function cannot be marked NOT NULL. To avoid a full table scan, you must ensure that the query cannot fetch null values.
  • Function-based indexes cannot use expressions that return VARCHAR2 or RAW data types of unknown length from PL/SQL functions. A workaround is to limit the size of the function's output by indexing a substring of known length:

    -- INITIALS() might return 1 letter, 2 letters, 3 letters, and so on.
    -- We limit the return value to 10 characters for purposes of the index.
    CREATE INDEX func_substr_index ON
      emp_tab(substr(initials(ename),1,10);
    
    -- Call SUBSTR both when creating the index and when referencing
    -- the function in queries.
    SELECT SUBSTR(initials(ename),1,10) FROM emp_tab;



You Might Also Like

Related Posts with Thumbnails

Pages