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;
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 below. 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.
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;
Besides, ALL_COLL_TYPES dictionary view can be used in order to see all system defined VARRAYs and nested tables. (DBMS_DEBUG_VC2COLL, ORA_MINING_NUMBER_NT, ORA_MINING_VARCHAR2_NT are some of the useful collections.)
SELECT COLL_TYPE, ELEM_TYPE_NAME, TYPE_NAME, LENGTH, UPPER_BOUND FROM ALL_COLL_TYPES WHERE OWNER = 'SYS' AND ELEM_TYPE_NAME IN ('VARCHAR2', 'NUMBER', 'DATE', 'CLOB') ORDER BY COLL_TYPE, ELEM_TYPE_NAME, TYPE_NAME;
You can also use MULTISET operators with nested tables.
DECLARE ARR_NT1 sys.ORA_MINING_NUMBER_NT := sys.ORA_MINING_NUMBER_NT(1, 2, 3, 4, 5); ARR_NT2 sys.ORA_MINING_NUMBER_NT := sys.ORA_MINING_NUMBER_NT(1, 2, 3, 8, 9); ARR_NT3 sys.ORA_MINING_NUMBER_NT; BEGIN --Merge ARR_NT3 := ARR_NT1 MULTISET UNION ARR_NT2; --1, 2, 3, 4, 5, 1, 2, 3, 8, 9 --Merge distinct ARR_NT3 := ARR_NT1 MULTISET UNION DISTINCT ARR_NT2; --1 2 3 4 5 8 9 --Minus ARR_NT3 := ARR_NT1 MULTISET EXCEPT ARR_NT2; --4, 5 --Intersect ARR_NT3 := ARR_NT1 MULTISET INTERSECT ARR_NT2; --1, 2, 3 END;
For more information about system defined types check the following link.
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;
ORACLE_MAINTAINED field has been introduced in Oracle 12c. This is a useful field that species whether the user was created by Oracle-supplied scripts. For example, if you want to find only objects created by non-default users, instead of filter each user individually, you can use the following query.
SELECT * FROM ALL_OBJECTS WHERE OWNER IN (SELECT USERNAME FROM ALL_USERS WHERE ORACLE_MAINTAINED = 'N');
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;
In Oracle 11g and backwards, you cannot use upper queries predicate in inline views. It can be happened implicitly in query transformation (predicate pushdown). Oracle 12c introduce LATERAL inline view syntax. Hence, you can use this feature as shown in the following.
CREATE TABLE TEMP1 AS SELECT * FROM DBA_OBJECTS;
SELECT COUNT(*) FROM TEMP1 T1, (SELECT * FROM TEMP1 T2 WHERE OWNER = 'SYSTEM' AND T1.OBJECT_ID = T2.OBJECT_ID); Error at line 1 ORA-00904: "T1"."OBJECT_ID": geçersiz belirleyici
As it can be seen from the above example, you cannot reference T1.OBJECT_ID predicate in the inline view instead you can use LATERAL syntax as of Oracle 12c.
SELECT COUNT(*) FROM TEMP1 T1 INNER JOIN LATERAL (SELECT * FROM TEMP1 T2 WHERE OWNER = 'SYSTEM' AND T1.OBJECT_ID = T2.OBJECT_ID) ON 1 = 1;
SELECT COUNT(*) FROM TEMP1 T1, LATERAL (SELECT * FROM TEMP1 T2 WHERE OWNER = 'SYSTEM' AND T1.OBJECT_ID = T2.OBJECT_ID);
I hope you found this helpful. 🙂