Archive for the ‘Oracle SQL’ Category

SQL optimization using Oracle Hints

ORACLE HINT REFERENCE

 

 

 

PARALLEL

Scanning full table with parallel degree of 8. Parallel always assumes full table scan.

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 8 ) */ last_name
  FROM employees hr_emp;
 

PARALLEL_INDEX

The PARALLEL_INDEX hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans for partitioned indexes.

SELECT /*+ PARALLEL_INDEX(table1, index1, 3) */

 

 

INDEX

When you specify an INDEX Hint, the optimizer knows that it has to use the INDEX specified in the hint. In this case, the optimizer does not go for a Full Table Scan nor does it use any other index. In addition, it does not calculate the cost of the Index to be used.

If no INDEX hint is specified the optimizer determines the cost of the each index that could be used to access the table and uses the one with the lower cost.

If there are multiple indexes specified with the Hint then the optimizer has to determine the cost of each index to be used with the specified table. Once that is determined, it uses the Index with the lower cost to access the table. In this case, the optimizer does not do a FULL Table Scan. Also note that, the optimizer may choose to use multiple indexes and then merge the result sets to access the table. This method is used if the cost is low.

select /*+ INDEX(emp_city idx_job_code) */ empname, 
job_code from emp where job_code = 'T';

INDEX_COMBINE

The INDEX_COMBINE hint instructs the optimizer to use a bitmap access path for the table. If indexspec is omitted from the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of indexes has the best cost estimate for the table. If you specify indexspec, then the optimizer tries to use some Boolean combination of the specified indexes. Each parameter serves the same purpose as in "INDEX Hint". For example:

SELECT /*+ INDEX_COMBINE(e emp_manager_ix emp_department_ix) */ *
  FROM employees e
  WHERE manager_id = 108
     OR department_id = 110;
 
FULL

You can use the FULL hint to bypass the use of the INDEX. For example if you have a table, which is indexed, and the value you are searching for has a large number of duplicates, then you can go in for a Full Table scan. If an index is used, in this case it will be inefficient. Using FULL hint will bypass the index(es).

select /*+ FULL(emp_status) */ empname, status from 
emp_status where status = 'P';
 
NO_INDEX

The NO_INDEX hint explicitly specifies which index cannot be used for the specified table.

select /*+ NO_INDEX(emp_status emp_status) */ empname,

status from emp_status where status = ‘P’;

 
  • If this hint specifies single or multiple available index(es), then the optimizer does not consider a scan on these indexes. Other indexes not specified are still considered.
  • If this hint specifies no indexes, then the optimizer does not consider a scan on any index on the table. This is the same as a NO_INDEX hint that specifies a list of all available indexes for the table.

 

*+ ALL_ROWS */

Explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption)

 

Hints for Join Operations

NO_USE_NL

The no_use_nl hint causes the CBO to exclude nested loops to join each specified table to another row source, using the specified table as the inner table. With this hint, only the hash join and the sort-merge joins will be considered for the specified tables.

          SELECT /*+ NO_USE_NL ( employees ) */

NO_USE_MERGE

This hint causes the CBO to exclude sort-merge to join each specified table to another row source, using the specified table as the inner table.

          SELECT /*+ NO_USE_MERGE ( employees dept ) */

NO_USE_HASH

This hint causes the CBO to exclude hash joins to join each specified table to another row source, using the specified table as the inner table.

           SELECT /*+ NO_USE_HASH ( employees dept ) */ …

NO_INDEX_FFS

This hint causes the CBO to exclude a fast full-index scan of the specified indexes on the specified table.

            SELECT  /*+ NO_INDEX_FFS ( tablespec indexspec ) */

CARDINALITY

This hint specifies the estimated cardinality returned by a query or portions of the query. Note that if no table is specified, the cardinality is the total number of rows returned by the entire query.

SELECT /*+ CARDINALITY ( [tablespec] card ) */

 

 

DATA WAREHOUSE HINTS

STAR_TRANSFORMATION

The STAR_TRANSFORMATION hint instructs the optimizer to use the best plan in which the transformation has been used. Without the hint, the optimizer could make a query optimization decision to use the best plan generated without the transformation, instead of the best plan for the transformed query. For example:

SELECT /*+ STAR_TRANSFORMATION */  *
  FROM sales s, times t, products p, channels c
  WHERE s.time_id = t.time_id
    AND s.prod_id = p.product_id
    AND s.channel_id = c.channel_id
    AND p.product_status = 'obsolete';

Even if the hint is specified, there is no guarantee that the transformation will take place. The optimizer generates the subqueries only if it seems reasonable to do so. If no subqueries are generated, then there is no transformed query, and the best plan for the untransformed query is used, regardless of the hint.

 

UNDOCUMENTED HINTS

CPU_COSTING

This hint turns CPU costing on for the SQL statement. This is the default cost model for the optimizer. The optimizer estimates the number and type of IO operations and the number of CPU cycles the database will perform during execution of the given query. It uses system statistics to convert the number of CPU cycles and number of IO(s) to the estimated query execution time. The CPU_COST column of the PLAN_TABLE stores the CPU cost.

               SELECT /*+ CPU_COSTING (tablespec indexspec) */

NO_CPU_COSTING

This hint turns off CPU costing for the SQL statement. CBO then uses the IO cost model, which measures everything in single-block reads and ignores CPU cost.

               SELECT /*+ NO_CPU_COSTING */

Finding time for Specific SCN

 

SELECT CURRENT_SCN FROM V$DATABASE;

To get the current SCN

SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL

1.1 ORA_ROWSCN

Is a pseudocolumn of any table that is not fixed or external. It represents
the SCN of the most recent change to a given row, that is, the latest COMMIT operation for the row. For example:

SELECT ora_rowscn, last_name, salary FROM employees
WHERE employee_id = 7788;
ORA_ROWSCN NAME SALARY
———- —- ——
202553 Fudd 3000

1.2 SCN_TO_TIMESTAMP( nSCN NUMBER)

Converts a SCN to TIMESTAMP
It has a precision of +/- 3 seconds

SQL> select scn_to_timestamp(884871) as timestamp from dual;

TIMESTAMP
—————————————————————————
09/03/2007 14:52:02,000000000

1.3 TIMESTAMP_TO_SCN(dTimestamp)

Converts TIMESTAMP to SCN
It has a precision of +/- 3 seconds

SQL> select timestamp_to_scn(to_timestamp(’08/03/2007 14:24:54′,’DD/MM/YYYY HH24:MI:SS’)) as scn from dual;

SCN
———-
845396

 

1.4 Table: SMON_SCN_TIME

This table translate time to SCN approximately.

When you use time based flashback you get the data in a period between +- 5 mn.
DBMS_FLASHBACK.ENABLE_AT_TIME and AS OF TIMESTAMP maps to an SCN value.  As the SCN-time is recorded every X minutes. The time you specify is rounded down by up to X minutes from database startup.
This situation could create the ORA-01466 unable to read data – table definition has changed. There is only track of times up to a maximum of 5 days (Database up time).

SELECT TO_CHAR(TIME_DP,’DDMONYYYY HH24:MI’) DATE_TIME,SCN
FROM SMON_SCN_TIME
Date_time               SCN
04MAY2006 16:20 576601
04MAY2006 16:26 576799
04MAY2006 16:30 577003
04MAY2006 16:36 577393
04MAY2006 16:41 577585
04MAY2006 16:46 577790

LINK WIKI ORACLE

Custom Script to Identify Invalid Packages

Identifying Invalid Objects

SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

Manual Approach

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

DBMS_DDL

EXEC DBMS_DDL.alter_compile(‘PACKAGE’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);

EXEC DBMS_DDL.alter_compile(‘PACKAGE BODY’,’MY_SCHEMA’,’MY_PACKAGE’);

EXEC DBMS_DDL.alter_compile(‘PROCEDURE’,’MY_SCHEMA’,’MY_PROCEDURE’);

EXEC DBMS_DDL.alter_compile(‘FUNCTION’, ‘MY_SCHEMA’, ‘MY_FUNCTION’);

EXEC DBMS_DDL.alter_compile(‘TRIGGER’, ‘MY_SCHEMA’, ‘MY_TRIGGER’);

DBMS_UTILITY

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects.

 

  • Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.
  • The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
  • The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and  DBMS_RANDOM to be present and valid.
  • — Schema level.

    EXEC UTL_RECOMP.recomp_serial(‘SCOTT’);

    EXEC UTL_RECOMP.recomp_parallel(4, ‘SCOTT’);

    — Database level.

    EXEC UTL_RECOMP.recomp_parallel(4);

    — Using job_queue_processes value.

    EXEC UTL_RECOMP.recomp_parallel();

    EXEC UTL_RECOMP.recomp_parallel(NULL, ‘SCOTT’);

     

    Custom Script for Compiling Invalid Packages in File

    CREATE OR REPLACE PROCEDURE COMPILE_PACKAGES

    IS

    outfile   UTL_FILE.file_type;

    stmt      VARCHAR2 (150);

    BEGIN

      outfile := UTL_FILE.fopen (‘PLSQL_FILE’, ‘INVALID_OBJECTS.txt’, ‘w’);

      FOR cur_rec IN (SELECT owner,

                             object_name,

                             object_type,

                             DECODE(object_type, ‘PACKAGE’, 1,

                                                 ‘PACKAGE BODY’, 2, 2) AS                    recompile_order

                      FROM   dba_objects

                      WHERE  object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)

                      AND    status != ‘VALID’

                      ORDER BY 4)

      LOOP

        BEGIN

          IF cur_rec.object_type = ‘PACKAGE’ THEN

            stmt := ‘ALTER ‘ || cur_rec.object_type || ‘ "’ || cur_rec.owner || ‘"."’ || cur_rec.object_name || ‘" COMPILE’;

            dbms_output.PUT_LINE ( ‘Package statement: ‘||stmt);

            –EXECUTE IMMEDIATE stmt;

          ElSE

            stmt:= ‘ALTER PACKAGE "’ || cur_rec.owner ||

                ‘"."’ || cur_rec.object_name || ‘" COMPILE BODY’;

            –EXECUTE IMMEDIATE stmt;

            dbms_output.PUT_LINE ( ‘Package statement: ‘||stmt);

          END IF;

          UTL_FILE.PUT_LINE(outfile, stmt);

        EXCEPTION

          WHEN OTHERS THEN

            DBMS_OUTPUT.put_line(cur_rec.object_type || ‘ : ‘ || cur_rec.owner ||

                                 ‘ : ‘ || cur_rec.object_name);

        END;

      END LOOP;

      UTL_FILE.FCLOSE(outfile);

    END COMPILE_PACKAGES;

    Main Source of this article: Oracle Base link on Recompiling Objects

    String Functions:INSTR,SUBSTR, REPLACE,TRANSLATE

    Built-in functions/operators available for manipulating Strings

    The most useful ones are LENGTH, SUBSTR, INSTR, and ||:

    • LENGTH(str) returns the length of str in characters.
    • SUBSTR(str,m,n) returns a portion of str, beginning at character m, n characters long. If n is omitted, all characters to the end of str will be returned.
    • INSTR(str1,str2,n,m) searches str1 beginning with its n-th character for the m-th occurrence of str2 and returns the position of the character in str1 that is the first character of this occurrence.
    • str1 || str2 returns the concatenation of str1 and str2.

    The example below shows how to convert a string name of the format 'last, first' into the format 'first last':

    SUBSTR(name,INSTR(name,’,’,1,1)+2) ||’ ‘|| SUBSTR(name, 1, INSTR(name,’,’,1,1)-1)

    For case-insensitive comparisons, first convert both strings to all upper case using Oracle’s built-in function upper() (or all lower case using lower()).

    SUBSTR

    The syntax for the substr function is:

    substr( string, start_position, [ length ] )

    string is the source string.
    start_position is the position for extraction. The first position in the string is always 1.
    length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.

    substr(‘This is a test’, 6, 2) would return ‘is’

    substr(‘This is a test’, 6) would return ‘is a test’

    substr(‘TechOnTheNet’, 1, 4) would return ‘Tech’

    substr(‘TechOnTheNet’, -3, 3) would return ‘Net’

    INSTR

    The syntax for the instr Oracle function is:

    instr( string1, string2 [, start_position [, nth_appearance ] ] )

    string1 is the string to search.

    string2 is the substring to search for in string1.

    start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.

    SUBSTR and INSTR

    Query returns string after the 6th occurence of ‘\’ in full path of data file name

    select substr(name,instr(name,’\’,1,6)+1) from v$datafile

    SYSTEM01.DBF
    USERS01.DBF
    SYSAUX.DBF

    REPLACE

    The syntax for the replace function is:

    replace( string1, string_to_replace, [ replacement_string ] )

    string1 is the string to replace a sequence of characters with another set of characters.

    string_to_replace is the string that will be searched for in string1.

    replacement_string 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.

    replace(‘123tech123’, ‘123’); would return ‘tech’

    replace(‘222tech’, ‘2’, ‘3’);    would return ‘333tech’

    TRANSLATE

    TRANSLATE(<string>, <‘list_to_match’>,<‘replacements_list’>)

    This demo replaces all commas with vertical bars.

    SELECT TRANSLATE(‘comma,delimited,list’  ,   ‘,‘    , ‘|‘)
    FROM dual;
    —————————-
    comma|delimited|list

    The following takes a DNA sequence and returns its complement.

    SELECT TRANSLATE(‘CAG-TTT-GAC-ACA-TGG-ATC’, ‘ACGT’, ‘GATC’) DNA
    FROM dual;
    ———————————————————-
    AGT-CCC-TGA-GAG-CTT-GCA

    In this demo the number of vowels in the string is counted. First With clause the whole line is stored in “data” pseudo table and then letter”x” will be translated to “x” (which won’t happen as there is no “x” in the string line) and all vowels in the line are translated to nothng as no substitute is given for them so they would simply be eliminated. All eliminating all vowels from “line” string, length of the remaining string is calculated which is 15. In the end, the number of vowels in the line string are calculated by taking the length of whole “line” string (23) minus the length of “line” string without vowels (15) which comes out to be 8.

    WITH data AS (SELECT ‘Whose line is it anyway’ line FROM dual)
    SELECT LENGTH(line)-LENGTH(TRANSLATE(line,’xaeiou’,’x’)) nbVowels
    FROM data;
    ————————————————-
    8

    TRANSLATE(line,’xaeiou’,’x’)                     = Whs ln s t nywy

    LENGTH(TRANSLATE(line,’xaeiou’,’x’) =  15

    LENGTH(line)  = 23

    LENGTH(line)-LENGTH(TRANSLATE(line,’xaeiou’,’x’))  = 23  –  15 = 8



    Constraints

    Several types of Oracle constraints can be applied to Oracle tables to enforce data integrity, including:

    • Oracle "Check" Constraint: This constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of region are North, South, East, or West, an Oracle check constraint can be added to the table definition to ensure the validity of the region column.
    • Not Null Constraint: This Oracle constraint is used to specify that a column may never contain a NULL value. This is enforced at SQL insert and update time.
    • Primary Key Constraint: This Oracle constraint is used to identify the primary key for a table. This operation requires that the primary columns are unique, and this Oracle constraint will create a unique index on the target primary key.
    • References Constraint: This is the foreign key constraint as implemented by Oracle. A references constraint is only applied at SQL insert and delete times.  At SQL delete time, the references Oracle constraint can be used to ensure that an employee is not deleted, if rows still exist in the DEPENDENT table.
    • Unique Constraint: This Oracle constraint is used to ensure that all column values within a table never contain a duplicate entry.

    Important Constraint Views

    dba_cons_columns
    dba_constraints

     

    CREATE TABLE Dept_tab (
        Deptno  NUMBER(3) CONSTRAINT Dept_pkey PRIMARY KEY, 
        Dname   VARCHAR2(15), 
        Loc     VARCHAR2(15))
    CREATE TABLE Emp_tab ( 
        Empno    NUMBER(5) CONSTRAINT Emp_pkey PRIMARY KEY, 
        Ename    VARCHAR2(15) NOT NULL, 
        Job      VARCHAR2(10), 
        Mgr      NUMBER(5) CONSTRAINT Mgr_fkey REFERENCES Emp_tab,
        Hiredate DATE, 
        Sal      NUMBER(7,2), 
        Comm     NUMBER(5,2), 
        Deptno   NUMBER(3) NOT NULL 
                 CONSTRAINT dept_fkey REFERENCES Dept_tab ON DELETE CASCADE);

    alter table  table_name add constraint constraint_name;

    Primary and Foreign Key Constraints
    ALTER TABLE Dept_tab 
        ADD CONSTRAINT PK_Dept PRIMARY KEY (deptno); 
    ALTER TABLE Emp_tab 
        ADD CONSTRAINT FKEY_Dept FOREIGN KEY (Deptno) REFERENCES Dept_tab;
    
    Unique Check and Not Null Constraints
    ALTER TABLE Dept_tab CONSTRAINT UQ_Dname UNIQUE (Dname, Loc);
    ALTER TABLE Dept_tab CONSTRAINT Loc_check1
    CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'));
    ALTER TABLE Emp_tab MODIFY (Ename VARCHAR2(15) NOT NULL);
    Enabling Disabling Dropping Constraints

    ALTER TABLE Dept  ENABLE CONSTRAINT UQ_Dname;

    ALTER TABLE Dept_tab  DISABLE CONSTRAINT UQ_Dname;

    ALTER TABLE Dept_tab  DROP CONSTRAINT  UQ_Dname;

     

    Constraint State

    DEFERRABLE Clause

    The DEFERRABLE and NOT DEFERRABLE parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET CONSTRAINT(S) statement. If you omit this clause, then the default is NOT DEFERRABLE.

    • Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The checking of a NOT DEFERRABLE constraint can never be deferred to the end of the transaction.

      If you declare a new constraint NOT DEFERRABLE, then it must be valid at the time the CREATE TABLE or ALTER TABLE statement is committed or the statement will fail.

    • Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. This setting in effect lets you disable the constraint temporarily while making changes to the database that might violate the constraint until all the changes are complete.

    The following statement creates table games with a NOT DEFERRABLE INITIALLY IMMEDIATE constraint check (by default) on the scores column:

    CREATE TABLE games (scores NUMBER CHECK (scores >= 0));

    CREATE TABLE games (scores NUMBER, CONSTRAINT unq_num UNIQUE (scores) INITIALLY DEFERRED DEFERRABLE);

    VALIDATE | NOVALIDATE

    The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default.

    ALTER TABLE sales ADD CONSTRAINT sales_time_fk
    FOREIGN KEY (time_id) REFERENCES times (time_id)
    ENABLE VALIDATE;

     

    RELY Constraints

    The ETL process commonly verifies that certain constraints are true. For example, it can validate all of the foreign keys in the data coming into the fact table. This means that you can trust it to provide clean data, instead of implementing constraints in the data warehouse. You create a RELY constraint as follows:

    ALTER TABLE sales ADD CONSTRAINT sales_time_fk
    FOREIGN KEY (time_id) REFERENCES times (time_id) 
    RELY DISABLE NOVALIDATE;

    This statement assumes that the primary key is in the RELY state. RELY constraints, even though they are not used for data validation, can:

    • Enable more sophisticated query rewrites for materialized views. See Chapter 18, " Query Rewrite" for further details.

    • Enable other data warehousing tools to retrieve information regarding constraints directly from the Oracle data dictionary.

    Creating a RELY constraint is inexpensive and does not impose any overhead during DML or load. Because the constraint is not being validated, no data processing is necessary to create it.

    DYNAMIC SQL FOR CONSTRAINTS

    To disable constraints in a schema for all tables

    SELECT ‘alter table ‘||owner||’.’||table_name||’ disable constraint ‘||constraint_name||’ ;’

    FROM dba_constraints where owner = ‘HR’

    To enable constraints, make sure that referential integrity constraints are enabled after primary key constraints.

    SELECT ‘alter table ‘||owner||’.’||table_name||’ enable constraint ‘||constraint_name||’ ;’

    FROM dba_constraints  where owner = ‘HR’

    order by constraint_type

    QUERIES FOR CHECKING CONSTRAINTS

      SELECT Constraint_name, Search_condition  
        FROM User_constraints  
        WHERE (Table_name = 'DEPT_TAB' OR Table_name = 'EMP_TAB') AND  
            Constraint_type = 'C';

    SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, LAST_CHANGE

    FROM DBA_CONSTRAINTS

    WHERE TABLE_NAME = ‘EMPLOYEES’

    SELECT Owner,Constraint_name, Table_name, Column_name 
    FROM dba_cons_columns

    where  table_name = ‘EMPLOYEES’

    and owner = ‘HR_DUP’

    SQL CASE Statement

    he syntax for the case statement is:

    CASE  [ expression ]
      WHEN condition_1 THEN result_1
      WHEN condition_2 THEN result_2
      …
      WHEN condition_n THEN result_n
      ELSE result
    END

    expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, … condition_n)

     

    select table_name,
    CASE owner
      WHEN ‘SYS’ THEN ‘The owner is SYS’
      WHEN ‘SYSTEM’ THEN ‘The owner is SYSTEM’
      ELSE ‘The owner is another value’
    END
    from all_tables;

    or you could rewrite it as

    select table_name,
    CASE
      WHEN owner=’SYS’ THEN ‘The owner is SYS’
      WHEN owner=’SYSTEM’ THEN ‘The owner is SYSTEM’
      ELSE ‘The owner is another value’
    END
    from all_tables;

     

    Evaluating Two Different Fields:

    select supplier_id,
    CASE
      WHEN supplier_name = ‘IBM’ and supplier_type = ‘Hardware’ THEN ‘North office’
      WHEN supplier_name = ‘IBM’ and supplier_type = ‘Software’ THEN ‘South office’
    END
    from suppliers;

    Managing Tables

    Creating a Table

    The CREATE TABLE command is used to create relational tables or object tables.

    Relational table: This is the basic structure to hold user data.

    Object table: Is a table that uses an object type for a column definition. An object table is a table that is explicitly defined to hold the object instance of a particular type.

     

    The example below creates a DEPARTMENTS table in a data dictionary managed tablespace.

    SQL> CREATE TABLE hr.departments( department_id NUMBER(4),

    department_name VARCHAR2(30), manager_id NUMBER(6), location_id NUMBER(4))

    STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 5)

    TABLESPACE data;

    STORAGE clause:

    The STORAGE clause specifies storage characteristics for the table. The storage allocated for the first extent is 200 KB. When a second extent is required it will be created at 200 KB also defined by the NEXT value. When a third extent is required, it will be created at 200 KB because the PCTINCREASE has been set to zero. The maximum amount of extents that can be used is set at five, with the minimum set to one.

    •MINEXTENTS: This is the minimum number of extents that is to be allocated.

    •MAXEXTENTS: This is the maximum number of extents to be allocated. If MINEXTENTS is specified with a value greater than one and the tablespace contains more than one datafile, the extents will be spread across the different datafiles.

    •PCTINCREASE: This is the percent of increase in extent size after NEXT extent and thereafter.

     

    PCTFREE must be a value from zero to ninety-nine. A value of zero means that the entire block can be filled by inserts of new rows. The default value is 10. This value reserves 10% of each block for updates to existing rows and allows inserts of new rows to fill a maximum of 90% of each block.

    •PCTUSED: Specifies the minimum percentage of used space that is maintained for each data block of the table. A block becomes a candidate for row insertion when its used space falls below PCTUSED. PCTUSED is specified as integer from zero to ninety-nine and defaults to 40.

    The combination of PCTFREE and PCTUSED determines whether new rows will be inserted into existing data blocks or into new bocks. The sum of these two must be equal to or less than 100. These parameters are used to utilize space within a table more efficiently.

    Note: PCTUSED, FREELISTS, and FREELIST GROUPS are deprecated with the Oracle9i feature Automatic Segment-Space Management. Refer to the “Storage Structures and Relationship” lesson for details regarding this feature.

    •INITRANS: Specifies the initial number of transaction entries allocated within each data block allocated to the table. This value can range from 1-255 and default to one INITRANS: Ensures that a minimum number of concurrent transactions can update the block. In general, this value should not be changed from its default.

    •MAXTRANS: Specifies the maximum number of concurrent transaction that can update a data block allocated to the table. This limit does not apply to queries. The value can range from 1-255 and the default is a function of the data block size.

     

    Creating Temporary Tables

    Temporary tables can be created to hold session-private data that exists only for the duration of a transaction or session.

    The CREATE GLOBAL TEMPORARY TABLE command creates a temporary table that can be transaction-specific or session-specific. For transaction-specific temporary tables, data exists for the duration of the transaction, while for session-specific temporary tables, data exists for the duration of the session. Data in a session is private to the session. Each session can only see and modify its own data. DML locks are not acquired on the data of the temporary tables. The clauses that control the duration of the rows are:

    • ON COMMIT DELETE ROWS: To specify that rows are only visible within the transaction
    • ON COMMIT PRESERVE ROWS: To specify that rows are visible for the entire session

    You can create indexes, views, and triggers on temporary tables and you can also use the Export and Import utilities to export and import the definition of a temporary table. However, no data is exported, even if you use the ROWS option. The definition of a temporary table is visible to all sessions.

    Setting PCTFREE

    A higher PCTFREE affords more room for updates within a database block. Set a higher value if the table contains:

    • Columns that are initially NULL and later updated with a value
    • Columns that are likely to increase in size as a result of an update

    A higher PCTFREE will result in lower block density—each block can accommodate fewer rows. The formula specified above ensures that there is enough free space in the block for row growth.

    Setting PCTUSED

    Set PCTUSED to ensure that the block is returned to the free list only when there is sufficient space to accommodate an average row. If a block on the free list does not contain sufficient space for inserting a row, the Oracle server looks up the next block on the free list. This linear scan continues until either a block with sufficient space is found or the end of the list is reached. Using the formula given reduces the time taken to scan the free list by increasing the probability of finding a block with the required free space.

    Changing Storage and Block Utilization Parameters

    Some of the storage parameters and any of the block utilization parameters can be modified by using the ALTER TABLE command.

    Syntax:

    ALTER TABLE hr.employees

    PCTFREE 20

    PCTUSED 60

    STORAGE NEXT (500k MINEXTENTS 2 MAXEXTENTS 100);

    ALLOCATING EXTENTS MANUALLY

    Extents may need to be allocated manually:

    • To control the distribution of extents of a table across files
    • Before loading data in bulk to avoid dynamic extension of tables

    ALTER TABLE hr.employess

    ALLOCATE EXTENT (SIZE 500K  DATAFILE ‘C:\ORACLE\DATA01.DBF’);

    Moving Table To Different Tablespace

    A nonpartitioned table can be moved without having to run the Export or Import utility. In addition, it allows the storage parameters to be changed. This is useful when:

    • Moving a table from one tablespace to another
    • Reorganizing the table to eliminate row migration

    ALTER TABLE hr.employees move tablespace data1;

     

    Adding a Table Column and Check Constraint: Example

    The following statement adds a column named duty_pct of datatype NUMBER and a column named visa_needed of datatype VARCHAR2 with a size of 3 and a CHECK integrity constraint:

     ALTER TABLE countries 
       ADD (duty_pct     NUMBER(2,2)  CHECK (duty_pct < 10.5),
            visa_needed  VARCHAR2(3)); 

     

    Renaming a Column: Example

    ALTER TABLE customers

       RENAME COLUMN credit_limit TO credit_amount;

    TRUNCATE TABLE

    TRUNCATE TABLE hr.employees;

    • The effects of using this command are as follows:
    • All rows in the table are deleted.
    • No undo data is generated and the command commits implicitly because TRUNCATE TABLE is a DDL command.
    • Corresponding indexes are also truncated.
    • A table that is being referenced by a foreign key cannot be truncated.
    • The delete triggers do not fire when this command is used.

    DROP TABLE

    DROP TABLE hr.employees CASCADE CONSTRAINT;

    When a table is dropped, the extents used by the table are released. If they are contiguous, they may be coalesced either automatically or manually at a later stage. The CASCADE CONSTRAINTS option is necessary if the table is the parent table in a foreign key relationship.

     

    Dropping a Column

    Dropping a column can take a significant amount of time because all the data for the column is deleted from the table.

    Dropping a column can be time consuming and requires a large amount of undo space. While dropping columns from large tables, checkpoints can be specified to minimize the use of undo space. In the example in the slide, a checkpoint occurs every 1,000 rows. The table is marked INVALID until the operation completes.

    ALTER TABLE hr.employees DROP COLUMN comments CASCADE constraints CHECKPOINT 1000;

    If the instance fails during the operation, the table remains INVALID on start up, and the operation will have to be completed.

    SQL> ALTER TABLE hr.employees DROP COLUMNS CONTINUE;

    Using the UNUSED Option

    Instead of removing a column from a table, the column can be marked as unused and then removed later. This has the advantage of being relatively quick, as it does not reclaim the disk space because the data is not removed. Columns that are marked as unused can be removed at a later time from the table when there is less activity on the system.

    Unused columns act as if they are not part of the table. Queries cannot see data from unused columns. In addition, the names and data types of those columns are not displayed when a DESCRIBE command is executed. A user can add a new column with the same name as an unused column. Marking column COMMENTS as unused:

    ALTER TABLE hr.employees SET UNUSED COLUMN comments CASCADE CONSTRAINTS;

    Dropping UNUSED column

    ALTER TABLE hr.employees DROP UNUSED COLUMNS CHECKPOINT 1000;

    The following query shows that the table EMPLOYEES owned by HR has one unused column:

    SQL > SELECT * FROM dba_unused_col_tabs;

    OWNER TABLE_NAME COUNT

    —– ————– ——

    HR EMPLOYEES 1

    To identify tables that have partially completed DROP COLUMN operations the DBA_PARTIAL_DROP_TABS view can be queried.

    SQL > SELECT * FROM dba_partial_drop_tabs;

    OWNER TABLE_NAME COUNT

    —– ————– ——

    no rows selected

    Obtaining Table Information

    Information about tables can be obtained from the data dictionary.

    • DBA_OBJECTS
    • DBA_TABLES

    To obtain the data object number and the location of the table header for all tables owned by HR, use the following query:

    SQL > SELECT table_name FROM dba_tables WHERE owner = ‘HR’;

     

     
     
    For details see Alter table link.