Senarios for Queries

 

1. Top 5 rows (employees)  from each department in a single query

use Row_Number()over(partition by dept_id ) Then Filter

2.  ALL / ANY > Query

https://oracle-base.com/articles/misc/all-any-some-comparison-conditions-in-sql

3. Update a partition key value.

It is not possible to update partition key column value. We need to first enable table movement.

Alter table table name enable movement;

Now we can update the key value column.

External Tables

 

External tables are created using the SQL CREATE TABLE...ORGANIZATION EXTERNAL statement.

TYPE — specifies the type of external table. Each type of external table is supported by its own access driver.

ORACLE_LOADER — this is the default access driver. It loads data from external tables to internal tables. The data must come from text data files.

ORACLE_DATAPUMP — this access driver can perform both loads and unloads. The data must come from binary dump files.

• ORACLE_HIVE — extracts data stored in Apache HIVE.

SQL> CREATE TABLE emp_load

  2    (employee_number      CHAR(5),

  3     employee_dob         CHAR(20),

  4     employee_last_name   CHAR(20),

  5     employee_first_name  CHAR(15),

  6     employee_middle_name CHAR(15),

  7     employee_hire_date   DATE)

  8  ORGANIZATION EXTERNAL

  9    (TYPE ORACLE_LOADER

 10     DEFAULT DIRECTORY def_dir1

 11     ACCESS PARAMETERS

 12       (RECORDS DELIMITED BY NEWLINE

 13        FIELDS (employee_number      CHAR(2),

 14                employee_dob         CHAR(20),

 15                employee_last_name   CHAR(18),

 16                employee_first_name  CHAR(11),

 17                employee_middle_name CHAR(11),

 18                employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"

 19               )

 20       )

 21     LOCATION ('info.dat')

 22    );

 

Table created.

SQL> CREATE TABLE inventories_EXT

2 ORGANIZATION EXTERNAL

3 (

4 TYPE ORACLE_DATAPUMP

5 DEFAULT DIRECTORY def_dir1

6 LOCATION ('inv_xt.dmp')

7 )

8 AS SELECT * FROM inventories;

Table created.

LISTAGG Function

 https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/LISTAGG.html

As an Aggregate Function

SELECT department_id "Dept.", LISTAGG(last_name, '; ' ON OVERFLOW TRUNCATE '...') WITHIN GROUP (ORDER BY hire_date) "Employees" FROM employees GROUP BY department_id ORDER BY department_id;

Dept. Employees ------ ------------------------------------------------------------ 10 Whalen 20 Hartstein; Fay 30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares 40 Mavris 50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot ; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; ... (23) 70 Baer . . .

As an Analytic Function

SELECT department_id "Dept", hire_date "Date", last_name "Name", LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name) OVER (PARTITION BY department_id) as "Emp_list" FROM employees WHERE hire_date < '01-SEP-2003' ORDER BY "Dept", "Date", "Name"; Dept Date Name Emp_list ----- --------- --------------- --------------------------------------------- 30 07-DEC-02 Raphaely Raphaely; Khoo 30 18-MAY-03 Khoo Raphaely; Khoo

Bulk Insertion - INSERT ALL

 

ALTER SESSION SET CURSOR_SHARING = FORCE;

1. INSERT ALL
2. INTO my_table (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
3. INTO my_table (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
4. INTO my_table2 (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
5. INTO my_table3 (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
6. WHEN CONDITION THEN
7. INTO my_table4 (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
8. SELECT * FROM dual;

DBMS_APPLICATION_INFO

 DBMS_APPLICATION_INFO.set_module
(
module_name => g_module_name||'.'||g_block_name,
action_name => 'Initializing ...'
);

DBMS_APPLICATION_INFO.read_module(l_prev_module_block,l_prev_action);

DBMS_APPLICATION_INFO.set_client_info
        (
        client_info =>g_module_name||'.'||g_block_name
        );

DBMS_APPLICATION_INFO.read_client_info (l_curr_module_block);

DBMS_REDEFINITION- DeFragamentation

 Defragamentation of Tables  in database

https://oracle-base.com/articles/11g/online-table-redefinition-enhancements-11gr1

https://medium.com/@dmitry.romanoff/how-to-check-fragmentation-in-an-oracle-database-a575b8a6e2ea

Calculate Fragmented Space in Tables

Check Db_Block_Size  parameter , here it is 8 KB.

SELECT      
  table_name,     
  avg_row_len, num_rows,    
  ROUND(((blocks*8/1024)),2) || 'MB' AS "TOTAL_SIZE",     
  ROUND((num_rows*avg_row_len/1024/1024),2) || 'MB' AS "ACTUAL_SIZE",     
  ROUND(((blocks*8/1024) - (num_rows*avg_row_len/1024/1024)),2) || 'MB' AS "FRAGMENTED_SPACE",     
  (ROUND(((blocks*8/1024) - (num_rows*avg_row_len/1024/1024)),2) / ROUND(((blocks*8/1024)),2)) * 100 AS "PERCENTAGE" 
FROM user_tables  
WHERE ROUND(((blocks*8/1024)),2) > 0 
ORDER BY 6 DESC;

Once you’ve identified fragmentation, consider the following methods to address it:

ALTER TABLE YOUR_TABLE_NAME MOVE;

ALTER INDEX YOUR_INDEX_NAME REBUILD;

For large tables, consider using online redefinition to minimize downtime:

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE (
      uname => 'COMMON', orig_table => 'CITY', int_table => 'CITY_TMP');
END;
EXEC DBMS_REDEFINITION.sync_interim_table('COMMON', 'CITY', 'CITY_TMP'); 
ALTER TABLE COMMON.CITY_TMP ADD (CONSTRAINT CITY_pk2 PRIMARY KEY (CITY_ID));
EXEC DBMS_REDEFINITION.finish_redef_table('COMMON', 'CITY', 'CITY_TMP'); 

Online Table Redefinition Including Dependents (COPY_TABLE_DEPENDENTS)

The above process drops the dependent Trigger , indexes on the table.

So we use dbms_redefinition.copy_table_dependents   to copy dependent objects like triggers, indexes, constraints, grants and optionally its statistics and materialized view logs.

-- Copy dependents.

SET SERVEROUTPUT ON

DECLARE
  l_num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname               => 'TEST',
    orig_table          => 'REDEF_TAB',
    int_table           => 'REDEF_TAB2',
    copy_indexes        => 1,             -- Default
    copy_triggers       => TRUE,          -- Default
    copy_constraints    => TRUE,          -- Default
    copy_privileges     => TRUE,          -- Default
    ignore_errors       => FALSE,         -- Default
    num_errors          => l_num_errors,
    copy_statistics     => FALSE,         -- Default
    copy_mvlog          => FALSE);        -- Default
    
  DBMS_OUTPUT.put_line('num_errors=' || l_num_errors); 
END;
/

In-Memory Column Store in Oracle Database 12c

 

https://oracle-base.com/articles/12c/in-memory-column-store-12cr1

The In-Memory Column Store (IM column store) was the headline feature of the 12.1.0.2 patchset. This features allows you to store columns, tables, partitions and materialized views in memory in a columnar format, rather than the typical row format. The advantage of having data in memory is obvious, but the columnar storage lends itself extremely well to analytic queries found in business intelligence products.

ALTER SYSTEM SET INMEMORY_SIZE=2G SCOPE=SPFILE;

ALTER SYSTEM SET INMEMORY_QUERY=ENABLE;

CREATE TABLE im_tab (
  id  NUMBER
) INMEMORY;

CREATE TABLE noim_tab (
  id  NUMBER
) NO INMEMORY;
SELECT table_name,
       inmemory,
       inmemory_priority,
       inmemory_distribute,
       inmemory_compression,
       inmemory_duplicate  
FROM   user_tables
ORDER BY table_name;