Find SQL Execution Plan

SQL> EXPLAIN PLAN FOR SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=50;

Explained.

SQL> SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 18113 |  2352K|   126   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES | 18113 |  2352K|   126   (1)| 00:00:02 |
-------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("DEPARTMENT_ID"=50)

Note
-----
   - dynamic sampling used for this statement (level=2)

17 rows selected.

SQL>

NULLs Value in Index Column.

If Indexed columns (Single Column that contain null values) contain null values then the associated row is not indexed, for that indexed resulting is smaller index structure.

We can analyze schema object such as table, index and cluster to collect and manage statistics, validity of storage format, identify migrated and chained rows.

Oracle always recomand that collect statistics using DBMS_STATS package, so it is better to collect optimizer statistics using DBMS_STATS package and not by analyzing.


SQL> CREATE TABLE ACCOUNT_INFORMATION
  2  (ACC_NUMBER NUMBER,
  3   ACC_TITLE VARCHAR2(100),
  4   ACC_NARATION VARCHAR2(100)
  5   );

Table created.

Elapsed: 00:00:00.02
SQL> CREATE UNIQUE INDEX IND_ACC_INFO_AC_NUMBER ON ACCOUNT_INFORMATION(ACC_NUMBER);

Index created.

Elapsed: 00:00:00.03
SQL> CREATE INDEX IND_ACC_INFO_NARATION  ON ACCOUNT_INFORMATION(ACC_NARATION);

Index created.

Elapsed: 00:00:00.02
SQL> INSERT INTO ACCOUNT_INFORMATION (ACC_NUMBER, ACC_TITLE, ACC_NARATION)
  2  SELECT LEVEL, LEVEL||' ACC NAME TEST '||LEVEL, NULL FROM DUAL CONNECT BY LEVEL <= 100000;

100000 rows created.

Elapsed: 00:00:00.34
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.01
SQL> ANALYZE INDEX IND_ACC_INFO_AC_NUMBER VALIDATE STRUCTURE;

Index analyzed.

Elapsed: 00:00:00.04
SQL> SELECT NAME, LF_ROWS, LF_BLKS, BR_ROWS, BR_BLKS, DEL_LF_ROWS, DISTINCT_KEYS FROM INDEX_STATS;

NAME                              LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS DEL_LF_ROWS DISTINCT_KEYS
------------------------------ ---------- ---------- ---------- ---------- ----------- -------------
IND_ACC_INFO_AC_NUMBER             100000        187        186          1           0        100000

Elapsed: 00:00:00.02
SQL> ANALYZE INDEX IND_ACC_INFO_NARATION VALIDATE STRUCTURE;

Index analyzed.

Elapsed: 00:00:00.02
SQL> SELECT NAME, LF_ROWS, LF_BLKS, BR_ROWS, BR_BLKS, DEL_LF_ROWS, DISTINCT_KEYS FROM INDEX_STATS;

NAME                              LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS DEL_LF_ROWS DISTINCT_KEYS
------------------------------ ---------- ---------- ---------- ---------- ----------- -------------
IND_ACC_INFO_NARATION                   0          1          0          0           0             0

Elapsed: 00:00:00.00
SQL>

Faster Access Rows inside Table Using ROWID pseudocolumn.

ROWID : ROWID is pseudo column that returns the address of the row. It is faster to access data from table. This column are unique for an particular table. Rows in different tables that are stored together in the same cluster can have the same ROWID .

This is combination of :

The data object number of the object.
The data block in the datafile.
The position of the row in the data block (first row is 0).
The datafile number of tablespace.

SQL> SET TIMING ON;
SQL> CREATE TABLE BIG_TABLE
  2  (ID NUMBER(10),
  3  NAME VARCHAR2(200)
  4  );

Table created.

Elapsed: 00:00:00.05
SQL> INSERT INTO BIG_TABLE (ID , NAME)
  2  SELECT LEVEL, NULL FROM DUAL CONNECT BY LEVEL <= 100000;

100000 rows created.

Elapsed: 00:00:00.11
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.01
SQL> DECLARE
  2  TYPE REC_DATA IS RECORD
  3  (
  4  ID BIG_TABLE.ID%TYPE
  5  );
  6  TYPE TT_DATA IS TABLE OF REC_DATA INDEX BY PLS_INTEGER;
  7  T_DATA TT_DATA;
  8  BEGIN
  9     SELECT ID BULK COLLECT INTO T_DATA FROM BIG_TABLE ;
 10
 11     FORALL IND IN T_DATA.FIRST .. T_DATA.LAST
 12     UPDATE BIG_TABLE
 13     SET NAME ='NAME '||T_DATA(IND).ID
 14     WHERE ID=T_DATA(IND).ID;
 15  END;
 16  /

PL/SQL procedure successfully completed.

Elapsed: 00:04:59.99
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> DECLARE
  2  TYPE REC_DATA IS RECORD
  3  (
  4  ID BIG_TABLE.ID%TYPE,
  5  ROW_ID VARCHAR2(1000)
  6  );
  7  TYPE TT_DATA IS TABLE OF REC_DATA INDEX BY PLS_INTEGER;
  8  T_DATA TT_DATA;
  9  BEGIN
 10     SELECT ID,ROWID BULK COLLECT INTO T_DATA FROM BIG_TABLE ;
 11
 12     FORALL IND IN T_DATA.FIRST .. T_DATA.LAST
 13     UPDATE BIG_TABLE
 14     SET NAME ='NAME '||T_DATA(IND).ID
 15     WHERE ROWID=T_DATA(IND).ROW_ID;
 16  END;
 17  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.55
SQL>