10 Oracle Database Features That You May Not Know About

Last week, during a regular chat with my boss, we talked about a few Oracle Database features from the past.

That conversation sparked the idea for this blog.

While I usually focus on what’s new and upcoming in the Oracle Database, this blog highlights 10 features that might fly under your radar. I will update each of the features to include a link to a more in-depth blog with a full code demo..

1. Flashback Time Travel (11g): Full Blog Link

   Have you ever accidentally updated some data and didn't realize till days or weeks later? Flashback Time Travel lets you recover data changes without having to use incremental/full database backups or recovery processes. Unlike the traditional backup processes, this feature lets you view and recover data from any point in time without the overhead. Flashback Time Travel was renamed from Flashback Data Archive in version 19c.

   
   

   Flashback time travel is available in all editions of the database

-- See what your table looked like last Tuesday
SELECT * FROM employees 
  AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '7' DAY);

2. Online Table Move: Full Blog Link

   Table maintenance doesn't require downtime. With Online Table Move, you can:

   1. Reorganize tables

   2. Move to different tablespaces

   3. Enable compression

   4. and much more...

   All while your applications keep running.

ALTER TABLE sales MOVE ONLINE tablespace users; 

3. Invisible Columns: Full Blog Link

   Need to add new columns without breaking you existing applications? Invisible columns are an easy feature to use which do exactly that.

   
   

   Your old `SELECT *` statements keep working, and new code can explicitly reference these columns.

create table sales (
  id number,
  item_number number,
  discount_code number invisible
);

4. Private Temporary Tables: Full Blog Link

   Need to do something like run calculations without affecting others? Private Temporary Tables exist only for your session or transaction and when you're done, they're automatically cleaned up.

CREATE PRIVATE TEMPORARY TABLE ora$ptt_calculations
ON COMMIT DROP
AS
SELECT *
FROM sales
WHERE processed_flag = 'N';

5. Hybrid Partitioned Tables: Full Blog Link

   Hybrid Partitioned Tables let you do things like keep hot data in the database and cold data in external (potentially cheaper) storage while still being queryable as one table:

BEGIN
DBMS_CLOUD.CREATE_HYBRID_PART_TABLE(
    table_name => 'SALES_BY_YEAR',
    credential_name => 'OBJECT_STORE_CRED',
    format => json_object('type' value 'datapump'),
    column_list => 'PROD_ID NUMBER, CUST_ID NUMBER, TIME_ID DATE, 
                    AMOUNT_SOLD NUMBER(10,2)',
    partitioning_clause => 'PARTITION BY RANGE (TIME_ID)
        (PARTITION S_OLD VALUES LESS THAN (DATE ''2019-01-01'') EXTERNAL LOCATION (''abc''),
         PARTITION S_2019 VALUES LESS THAN (DATE ''2020-01-01''),
         PARTITION S_2020 VALUES LESS THAN (DATE ''2021-01-01''),
         PARTITION S_FUTURE VALUES LESS THAN (MAXVALUE))'
);
END;

6. SQL Macros: Full Blog Link

   Want function-like reusability without the performance hit of functions? SQL Macros are your answer:

CREATE OR REPLACE FUNCTION holiday_discount(
    discount number
)
return varchar2 SQL_MACRO(SCALAR)
IS
BEGIN
  RETURN q'{discount *.25}';
END;
/

7. DBMS_CLOUD: Full Blog Link

   DBMS_CLOUD provides a simple way for both cloud and on-premises databases to work with object storage

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OBJ_STORE_CRED',
    username => 'user',
    password => 'password'
  );
END;
/

8. Invisible Indexes: Full Blog Link

   Make indexes invisible to the optimizer while maintaining their structure and data. Useful for testing index impact or phasing out possibly unused indexes without dropping them

CREATE INDEX idx_emp_email ON employees(email) INVISIBLE;

9. Advanced Index Compression: Full Blog Link

   Gets the highest levels of index compression and has great cost-savings and performance improvements from to reduced I/O.

-- Create new compressed index
CREATE INDEX emp_idx ON employees(dept_id, emp_id)COMPRESS ADVANCED HIGH;

-- Rebuild existing index with compression
ALTER INDEX emp_idx REBUILD COMPRESS ADVANCED LOW;

10. APPROX_COUNT_DISTINCT function: Full Blog Link

    The APPROX_COUNT_DISTINCT function gives you approximate distinct counts using statistical methods. It trades perfect accuracy for better performance.

SELECT APPROX_COUNT_DISTINCT(visitor_id) as approx_visitors
FROM web_visits;