5 Life Saving Features of Oracle

1) Partitioned Outer Join

I would like to start with PARTITIONED OUTER JOIN. Because it is one of the least used feature. Suppose that you have two basic tables that represent the course hours of instructors, these courses starts at between 9:00 and 15:00 o’clock and every course takes an hour. You can see the contents of the tables as shown below.

create table t_tutor_schedule
(
instructor varchar2(100),
course varchar2(100),
start_time interval day(0) to second(0),
end_time interval day(0) to second(0)
);

insert into t_tutor_schedule values('Connor McDonald', 'Advanced PL/SQL programming', to_dsinterval('0 10:00:00'), to_dsinterval('0 11:00:00'));

insert into t_tutor_schedule values('Connor McDonald', 'SQL tunning', to_dsinterval('0 13:00:00'), to_dsinterval('0 14:00:00'));

insert into t_tutor_schedule values('Connor McDonald', 'Oracle Architecture', to_dsinterval('0 14:00:00'), to_dsinterval('0 15:00:00'));

insert into t_tutor_schedule values('Maria Colgan', 'Optimizer statistics', to_dsinterval('0 09:00:00'), to_dsinterval('0 10:00:00'));

insert into t_tutor_schedule values('Maria Colgan', 'SQL fundamentals', to_dsinterval('0 10:00:00'), to_dsinterval('0 11:00:00'));

insert into t_tutor_schedule values('Maria Colgan', 'Database In-Memory and Indexes', to_dsinterval('0 13:00:00'), to_dsinterval('0 14:00:00'));

insert into t_tutor_schedule values('Maria Colgan', 'JSON in Oracle database', to_dsinterval('0 14:00:00'), to_dsinterval('0 15:00:00'));

commit;
create table t_course_hours
as
select 
numtodsinterval( to_date(to_char(sysdate, 'dd/mm/yyyy') || '08:00:00', 'dd/mm/yyyy hh24:mi:ss') + level/24 - trunc(sysdate), 'day' ) hours 
from dual connect by level <= 7;
SELECT * FROM t_tutor_schedule;
SELECT * FROM t_course_hours;

You want to show the full schedule of each instructor whether he or she has lesson or not. In other words, you want to see the whole day schedule even if there is not any course for a specific hour. You need to include gaps too. So, you want to generate the following diagram.

You might think that you can achieve this with FULL OUTER JOIN. However, it cannot meet exactly what you want.

SELECT
    t.instructor, t.course, EXTRACT(HOUR FROM (h.hours)) course_time
FROM t_tutor_schedule t FULL OUTER JOIN t_course_hours h on t.START_TIME = h.HOURS
ORDER BY instructor, course_time;

Let’s use PARTITION BY clause in order to get the intended result.

SELECT 
    t.instructor, t.course, EXTRACT(HOUR FROM (h.hours)) course_time
FROM t_tutor_schedule t 
PARTITION BY (instructor) 
RIGHT JOIN t_course_hours h ON t.START_TIME = h.HOURS 
ORDER BY instructor, course_time;

2) REGEXP_LIKE

The first example that comes to my mind for REGEXP_LIKE is that it helps to reduce the usage of LIKE condition in the WHERE clause. Especially, If you need to use multiple values with LIKE condition. Than, REGEXP_LIKE can be a savior for you. Let’s say you have a table with user comments and a word list table. You want to filter the comments which contain undesired words. In that word list table what if it has thousands of rows. Surely, you don’t want to write thousands of LIKE conditions for all of these words separately as in the example below.

SELECT * FROM T_COMMENTS 
WHERE 
TXT NOT LIKE '%unwanted_word_1%' AND
TXT NOT LIKE '%unwanted_word_2%' AND
TXT NOT LIKE '%unwanted_word_3%' AND
TXT NOT LIKE '%unwanted_word_4%' AND
TXT NOT LIKE '%unwanted_word_5%' AND
TXT NOT LIKE '%unwanted_word_6%' AND
TXT NOT LIKE '%unwanted_word_7%' AND
TXT NOT LIKE '%unwanted_word_8%' AND
TXT NOT LIKE '%unwanted_word_9%' AND
TXT NOT LIKE '%unwanted_word_10%' AND
.....
TXT NOT LIKE '%unwanted_word_1000%';

Bear in mind that regular expressions are CPU intensive. It is really powerful processes. However computationally expensive. So, use it only when you absolutely have to.

You can use the following query instead.

SELECT * FROM 
T_COMMENTS UC, 
(
    SELECT 
        LISTAGG(UNWANTED_WORD, '|') WITHIN GROUP(ORDER BY NULL) WORDS --Arranging the right pattern
    FROM T_WORD_LIST
) WL
WHERE NOT REGEXP_LIKE (UC.TXT, WL.WORDS);

If the number of words you want to filter is not many, you can also use it as follows.

SELECT * FROM T_COMMENTS
WHERE NOT REGEXP_LIKE (TXT, 'unwanted_word_1|unwanted_word_2|unwanted_word_3');

3) Generating desired amount of rows

You can generate rows on the fly. There are multiple ways for this. In the following examples, number sequences are generated from 1 to 10.

--Hierarchical query
SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 10;


--Subquery factoring
WITH
Q1(N) AS 
(
    SELECT 1 FROM DUAL
    UNION ALL
    SELECT N+1 FROM Q1 WHERE N < 10
)
SELECT * FROM Q1;


--XAMTABLE
SELECT ROWNUM FROM XMLTABLE('1 to 10');


--Model clause
SELECT V
FROM DUAL
MODEL DIMENSION BY (0 R)
      MEASURES (0 V)
      RULES ITERATE (10) (
        V[ITERATION_NUMBER] = ITERATION_NUMBER + 1
      )
ORDER BY 1;  

Let’s compare the runtimes between them for 500k rows. The comparison has been performed in Oracle 19C.

DECLARE
    V_START NUMBER;
     
    PROCEDURE SHOW_ELAPSED_TIME(P_NAME VARCHAR2)
    IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(P_NAME||' elapsed time: '||TO_CHAR((DBMS_UTILITY.GET_TIME - V_START)/100, '00.00'));
        V_START := DBMS_UTILITY.GET_TIME;
    END;
     
BEGIN
     
    V_START := DBMS_UTILITY.GET_TIME;
     
    FOR I IN (SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 500000)
    LOOP
        NULL;
    END LOOP;
     
    SHOW_ELAPSED_TIME('Hierarchical query');
     
    FOR I IN (
                WITH
                Q1(N) AS
                (
                    SELECT 1 FROM DUAL
                    UNION ALL
                    SELECT N+1 FROM Q1 WHERE N < 500000
                )
                SELECT * FROM Q1
             )
    LOOP
        NULL;
    END LOOP;
     
    SHOW_ELAPSED_TIME('Subquery factoring');
     
     
    FOR I IN (SELECT ROWNUM FROM XMLTABLE('1 to 500000'))
    LOOP
        NULL;
    END LOOP;
     
    SHOW_ELAPSED_TIME('XAMTABLE');
 
    FOR I IN (
                SELECT V
                FROM DUAL
                MODEL DIMENSION BY (0 R)
                      MEASURES (0 V)
                      RULES ITERATE (500000) (
                        V[ITERATION_NUMBER] = ITERATION_NUMBER + 1
                      )
                ORDER BY 1
             )
    LOOP
        NULL;
    END LOOP;
     
    SHOW_ELAPSED_TIME('Model clause');
     
END;

Hierarchical query elapsed time:  00.28
Subquery factoring elapsed time:  01.99
XAMTABLE elapsed time:  00.31
Model clause elapsed time:  41.40

As can be seen from the results, CONNECT BY clause is more efficient than the others. In addition, you can also generate random numbers.

SELECT 
    ROUND(DBMS_RANDOM.VALUE(1, 10)) 
FROM DUAL CONNECT BY LEVEL <= 10;

4) Oracle defined collections

You can use Oracle’s built-in collection types. Although it is not used much, it can make your job easier in some places. Some of the system-defined types are listed in the following. If you look at the script of ODCI built-in collections, you will see that they are VARRAY types. In addition, if you want to use associative arrays you can use DBMS_SQL package.

  • sys.ODCIDateList
  • sys.ODCINumberList
  • sys.ODCIRawList
  • sys.ODCIVarchar2List
SELECT * FROM TABLE(SYS.ODCINUMBERLIST(1, 2, 3, 4, 5, 6, 7, 8, 9, 10));
SELECT * FROM TABLE(SYS.ODCIVARCHAR2LIST('a', 'b', 'c', 'd', 'e'));
DECLARE

    ARR1 SYS.ODCINUMBERLIST;
    ARR2 DBMS_SQL.NUMBER_TABLE;
    
BEGIN

    ARR1 := SYS.ODCINUMBERLIST(1, 3, 5, 7, 9);
    
    FOR IND IN ARR1.FIRST..ARR1.LAST
    LOOP
        DBMS_OUTPUT.PUT_LINE(ARR1(IND));
    END LOOP;
    
    ARR2(1) := 1;
    ARR2(2) := 3;
    ARR2(3) := 5;
    ARR2(4) := 7;
    ARR2(5) := 9; 

    FOR IND IN ARR2.FIRST..ARR2.LAST
    LOOP
        DBMS_OUTPUT.PUT_LINE(ARR2(IND));
    END LOOP;

END;

For more information you can check the following link.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/addci/extensibility-constants-types-and-mappings.html#GUID-0C7F23BA-A9CA-472E-8830-FDF4BFA5556F

5) Data Dictionary

Using data dictionary wisely can facilitate your job easier in many places. Basically, it stores critical information about database objects, user information and dynamic performance metrics. There are two general categories of data dictionary views. One is static views that begins with USER, ALL, DBA, CDB and the other is dynamic performance views that begins with V$ and GV$. Dynamic performance views provide real time statistics such as how many sessions are open, which SQL is executing, memory usage etc. You can use DICTIONARY and DICT_COLUMNS views to get information about all dictionary views.

SELECT * FROM DICTIONARY;

SELECT * FROM DICT_COLUMNS;

For example, if you want to find all data dictionary views related to JSON. You can run a query such as this.

--Oracle 19C 
SELECT * FROM dictionary where table_name like '%JSON%';

There are four different prefixes and the description of them show below.

USER_Only shows the user’s own objects that selects the view.
ALL_Shows objects that the querying user has been granted privileges and own objects.
DBA_Shows all objects
CDB_CDB views involve all DBA views.

Data dictionary is stored in the SYSTEM tablespace and it is always online when the database is open.

Below you can find some useful queries using the data dictionary.

--To find table name from the lowest or highest value of any column.
SELECT * FROM DBA_TAB_COL_STATISTICS 
WHERE OWNER = 'USER' AND (UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE) LIKE '%DATA%' OR UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE) LIKE '%DATA%');

--To find object name which contains a spesific code
SELECT * FROM DBA_SOURCE WHERE LOWER(TEXT) LIKE '%your code%';

--To list all columns of your table
SELECT LISTAGG(COLUMN_NAME, ',') WITHIN GROUP(ORDER BY COLUMN_ID) FROM USER_TAB_COLS WHERE TABLE_NAME = 'TABLE_NAME';

--Disk usage
select 
    df.tablespace_name "Tablespace", 
    totalusedspace "Used GB", 
    (df.totalspace - tu.totalusedspace) "Free GB", 
    df.totalspace "Total GB", 
    round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
from
    (select tablespace_name, round(sum(bytes) / (1024*1024*1024), 2) TotalSpace from dba_data_files group by tablespace_name) df,
    (select round(sum(bytes)/(1024*1024*1024), 2) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
order by 4 desc;

Moreover, you can use SYS_CONTEXT built-in SQL function to display your session information.

SELECT 
    sys_context('USERENV', 'CURRENT_USER') usr,
    sys_context('USERENV', 'AUTHENTICATION_METHOD') auth_method,
    sys_context('USERENV', 'HOST') host,
    sys_context('USERENV', 'DB_NAME') db_name,
    sys_context('USERENV', 'INSTANCE_NAME') inst,
    sys_context('USERENV', 'IP_ADDRESS') ip_addr,
    sys_context('USERENV', 'SERVER_HOST') server_host,
    sys_context('USERENV', 'SERVICE_NAME') service_name,
    sys_context('USERENV', 'SID') sid,
    sys_context('USERENV', 'TERMINAL') terminal,
    sys_context('USERENV', 'OS_USER') os_user
FROM dual;

I hope you found this helpful. 🙂

One thought on “5 Life Saving Features of Oracle

Leave a Reply

Your email address will not be published.