SQL Quiz for Intersect

I noticed that there is a sql quiz of oracle database on the place where I seem like to not remember it a few days ago. But the stuff of this SQL Quiz should look like as below.

Which statement is true regarding the INTERSECT operator in the oracle database?

(1) By reversing the order of the intersected tables alter the result.

(2) It ignores the NULL values.

(3) The number of columns and the data types must be identical for all of the
    SELECT statements in the query.

(4) The names of all columns in the SELECT statements must be identical.

(5) None.

When ended up reading the prior SQL topic and each choice I know the topic has certainly only a correct answer. For being able to pick up the correct answer I built some demos to verify the description of each choice of this SQL Quiz is whether right or not. By the way my all of demos is separately tested on oracle 18.3.0.0 and 19.8.0.0 (LiveSQL).

Firstly, let me take a look at the choice 1 and my demo is as follows:

PROMPT  ==================================================================
PROMPT             Building a demo for verifying Choice 1:
PROMPT  ==================================================================
PROMPT  By reversing the order of the intersected tables alter the result.
PROMPT  ==================================================================

DROP TABLE t1 PURGE;

CREATE table t1(a VARCHAR2(1), b VARCHAR2(1));

INSERT INTO t1 VALUES ('a', 'b');
INSERT INTO t1 VALUES ('a', 'c');
INSERT INTO t1 VALUES ('b', 'c');

COMMIT;

DROP TABLE t2 PURGE;

CREATE TABLE t2(a VARCHAR2(1), b VARCHAR2(1));

INSERT INTO t2 VALUES ('a', 'b');
INSERT INTO t2 VALUES ('c', 'd');
INSERT INTO t2 VALUES ('b', 'c');

COMMIT;

SELECT * FROM t1
INTERSECT
SELECT * FROM t2
;

SELECT * FROM t2
INTERSECT
SELECT * FROM t1
;

When performing the previous SQL demo, you’ll get the same query result whatever the first SELECT table t1 OR the first SELECT table t2.

-- on 18.3 and LiveSQL:

A B
- -
a b
b c

As you can see the stuff of choice 1 is incorrect – “By reversing the order of the intersected tables DON’T alter the result” (note: only showing the first SELECT table’s column names).

Next, it’s my second demo for the choice 2.

PROMPT  =======================================
PROMPT  Building a demo for verifying Choice 2:
PROMPT  =======================================
PROMPT  It ignores the NULL values.
PROMPT  =======================================

DROP TABLE t1 PURGE;

CREATE table t1(a CHAR(1), b CHAR(1), c CHAR(1));

INSERT INTO t1 VALUES ('a', 'b', null);
INSERT INTO t1 VALUES ('a', null, 'c');
INSERT INTO t1 VALUES ('a', 'b', 'c');

COMMIT;

DROP TABLE t2 PURGE;

CREATE TABLE t2(a CHAR(1), b CHAR(1), c CHAR(1));

INSERT INTO t2 VALUES ('a', 'b', null);
INSERT INTO t2 VALUES ('a', null, 'c');
INSERT INTO t2 VALUES ('d', 'e', 'f');

COMMIT;

SELECT * FROM t1
INTERSECT
SELECT * FROM t2
;

Then the preceding last SQL QUERY result is as below respectively on 18.3 and LiveSQL:

-- on 18.3:

A B C
- - -
a b
a   c

-- on LiveSQL:

A B C
- - -
a b -
a - c

As you can see from the above outcome we found that the INTERSECT operation of the SELECT of two tables hasn’t ignored the NULL values (the choice 2 is absolutely wrong). For better showing the NULL values we can also use the SQL expression CASE WHEN … THEN … ELSE … END or the SQL function NVL() to return a pretty friendly result. Let me show you about them in the following two number of SQL snippets.

Case when … then … else … end

SELECT
           CASE WHEN a IS NULL THEN 'null'
           ELSE a
           END "A",
           CASE WHEN b IS NULL THEN 'null'
           else b
           end "B",
           CASE WHEN c IS NULL THEN 'null'
           else c
           end "C"
FROM
           t1
INTERSECT
SELECT
           CASE WHEN a IS NULL THEN 'null'
           ELSE a
           END "A",
           CASE WHEN b IS NULL THEN 'null'
           else b
           end "B",
           CASE WHEN c IS NULL THEN 'null'
           else c
           end "C"
FROM
           t2
;

Of course the prevous SQL statement shows the same query result on 18.3 and LiveSQL.

-- on 18.3:

A    B    C
---- ---- ----
a    b    null
a    null c

-- on LiveSQL:

A B    C
- ---- ----
a b    null
a null c

NVL()

SELECT
           NVL(a, 'null') a,
           NVL(b, 'null') b,
           NVL(c, 'null') c
FROM
           t1
INTERSECT
SELECT
           NVL(a, 'null') a,
           NVL(b, 'null') b,
           NVL(c, 'null') c
FROM
           t2
;

Due to using NVL() still shows the identical outcome with the CASE WHEN for the intersect operation hence I deliberately omit it.

Now it’s time to verify the choice 3 is whether correct or not. We should carefully read the stuff of this choice.

The number of columns and the data types must be identical for all of the
SELECT statements in the query.

As you can see from the previous info there are two number of key points in that choice – the number of columns AND the data types. So I’ll illustrate it from those two aspects.

The different number of columns

PROMPT  =========================================================================
PROMPT                  Building a demo for verifying Choice 3:
PROMPT  =========================================================================
PROMPT  The number of columns and the data types must be identical for all of the
PROMPT  SELECT statements in the query.
PROMPT  =========================================================================

-- The number of columns of the SQL query (for the INTERSECTION in two tables) is different. 

DROP TABLE t1 PURGE;

CREATE table t1(a NUMBER(1), b NUMBER(1));

INSERT INTO t1 VALUES (1, 2);
INSERT INTO t1 VALUES (2, 3);
INSERT INTO t1 VALUES (1, 3);

COMMIT;

DROP TABLE t2 PURGE;

CREATE TABLE t2(a NUMBER(1), b NUMBER(1));

INSERT INTO t2 VALUES (1, 2);
INSERT INTO t2 VALUES (3, 4);
INSERT INTO t2 VALUES (2, 3);

COMMIT;

SELECT
           a, b
FROM       t1
INTERSECT
SELECT     a
FROM       t2
;

We can get the same error ORA-01789 both on oracle 18.3 and LiveSQL.

-- on 18.3 and LiveSQL:

ORA-01789: query block has incorrect number of result columns

The different data types

-- The data type of columns of the SQL query (for the INTERSECTION in two tables) is different.

DROP TABLE t1 PURGE;

CREATE table t1(a NUMBER(1), b NUMBER(1));

INSERT INTO t1 VALUES (1, 2);
INSERT INTO t1 VALUES (2, 3);
INSERT INTO t1 VALUES (1, 3);

COMMIT;

DROP TABLE t2 PURGE;

CREATE TABLE t2(a CHAR(1), b CHAR(1));

INSERT INTO t2 VALUES ('1', '2');
INSERT INTO t2 VALUES ('3', '4');
INSERT INTO t2 VALUES ('2', '3');

COMMIT;

SELECT * FROM t1
INTERSECT
SELECT * FROM t2
;

Now we return the same error of ORA-01790 separately on 18.3 and LiveSQL.

-- on 18.3 and LiveSQL:

ORA-01790: expression must have same datatype as corresponding expression

By contrary the description of the choice 3 is correct – must have the identical number of columns and the data types in the two number of SELECT statements for the intersect operation.

P.S. – We can also deem that the NUMBER and FLOAT are the same data type because the FLOAT data type is a subtype of NUMBER as you can see from this link of oracle official docs on 19c and the following screenshot.

Next let me verify the choice 4, here’s my fourth demo.

PROMPT  ====================================================================
PROMPT                Building a demo for verifying Choice 4:
PROMPT  ====================================================================
PROMPT  The names of all columns in the SELECT statements must be identical.
PROMPT  ====================================================================

DROP TABLE t1 PURGE;

CREATE table t1(a INTEGER);

INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);

COMMIT;

DROP TABLE t2 PURGE;

CREATE TABLE t2(b INTEGER);

INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (3);
INSERT INTO t2 VALUES (4);

COMMIT;

SELECT a FROM t1
INTERSECT
SELECT b FROM t2
;

Certainly the returned result is all okay on 18.3 and LiveSQL even if those two tables’ column name is different.

-- on 18.3:

         A
----------
         1
         3

-- on LiveSQL:

A
-
1
3

So, the choice 4 is wrong as well.

As s whole the SQL Quiz’s correct answer is the choice 3 (Of course it makes no sense that we read the choice 5). You can also see the entire demo codes from my Github repository.

Two skills for Changing Password of A User

Have you known how many methods for changing password of a user in the oracle database? Might the common method is using the “ALTER USER username IDENTIFIED BY password” in the SYS schema or your own schema to change it. Recently I know another approach when learning the course Oracle Database 19c: SQL Workshop in the Lesson 18 – Controlling User Access in the section of Changing Your Password (on my annual oracle database learning subscription from Oracle University), as you can see from the following screenshot.

So this post I will use the previous two skills to illustrate it.

  • ALTER USER … IDENTIFIED BY …;
  • PASSW[ORD] command in the SQL*Plus

Alter User … Identified By …

In this section I will use the following SQL code snippets to implement it.

Firstly, I create a user of qwz and then grant it with the role of CONNECT and RESOURCE in the SYS schema.

14:41:50 SYS@ora18c> create user qwz identified by qwz;

User created.

14:42:21 SYS@ora18c> grant connect, resource to qwz;

Grant succeeded.

Next connect to the user qwz and use “alter user …” to change its password.

14:42:30 SYS@ora18c> conn qwz/qwz

Connected.

14:42:33 QWZ@ora18c> alter user qwz identified by qwz123;

User altered.

or, I directly change the password of user qwz in the SYS schema.

14:44:41 SYS@ora18c> alter user qwz identified by qwz;

User altered.

Note: as you can see from the preceding demo I am able to quickly change the password of a user to becocme a new one instead of inputtng an OLD one. Of course, after you certainly input your password you are able to go into your own schema.

Passw[ord] in SQL*Plus

I need to take a look at the usage of PASSWORD in the SQL*Plus.

14:45:29 SYS@ora18c> help password

 PASSWORD
 --------

 Allows you to change a password without displaying it on an input device.

 PASSW[ORD] [username]


14:45:32 SYS@ora18c> 

Next I will change the password of user qwz using the command PASSW of the SQL*Plus in the SYS schema (with the username after the keyword password).

14:45:32 SYS@ora18c> passw qwz
Changing password for qwz
New password:         (qwz123 no echo)
Retype new password:  (qwz123 no echo)
Password changed

or, connect to the user qwz, and then change its password by inputting the keyword passw.

14:46:12 SYS@ora18c> 
14:47:20 SYS@ora18c> conn qwz/qwz123

Connected.
14:47:24 QWZ@ora18c> passw
Changing password for QWZ
Old password:          (qwz123 no echo)
New password:          (qwz no echo)
Retype new password:   (qwz no echo)
Password changed

As you can see I must firstly input the OLD password so that allowing me to change a new one because I certainly know the old password.

Addendum

There is one point to notice when you change the password of SYS schema in the schema qwz. Although oracle prompt that you can input a new password for SYS, actually you get an error of ORA-01031.

15:29:38 QWZ@ora18c> passw sys
Changing password for sys
New password:         (sys no echo)
Retype new password:  (sys no echo)
ERROR:
ORA-01031: insufficient privileges


Password unchanged

Trigger bug

There has a section “CALL Statements in Triggers” in the “Lesson 10: Creating Compound, DDL, and Event Database Triggers” in the Student Guide “Program with PL/SQL – Student Guide II” in this course “Program with PL/SQL” of ODDLS (Oracle Database Deveoper Learning Subscription) subscribed by me.

As you can see from the first screenshot about procedure and trigger code, actually there exists an invoking procedure “log_execution” clause when creating the trigger “log_employee”. And you should also notice that adding an interesting comment “– no semicolon needed” after that invoking statement in order to remind reader not to add an extra semicolon (there is also a conspicuous Note in the second screenshot).

For practicing that trigger code, I separately tested it on oracle 11.2, 12.1, 12.2, 18.3, 19.3 and LiveSQL (current 19.5). Unfortunately PL/SQL compiler alwayws reports the very weird error of PLS-00103 on all the oracle versions I mentioned previously. So yesterday morning I emailed to Steven Feuerstein (Oracle PL/SQL guru all over the world) to ask for him to help me, as a result, he replied to me it looks like a bug if removing the comment “– no semicolon needed” and then it works. He was going to report the issue. The following are three pictures about our emails back and forth.

After his help, today I wrote the following test SQL code snippet on Github.

REM
REM     Script:        invoke_procedure_in_trigger.sql
REM     Author:        Quanwen Zhao
REM     Dated:         Jul 31, 2020
REM
REM     Last tested:
REM             11.2.0.4
REM             12.1.0.2
REM             12.2.0.1
REM             18.3.0.0
REM             19.3.0.0
REM             19.5.0.0 -- LiveSQL
REM
REM     Purpose:
REM       If adding a comment after the call procedure clause in a trigger, PL/SQL compiler
REM       will report the very weird error of PLS-00103. So I emailed to Steven Feuerstein,
REM       he suggests me remove that comment and then it will normally work, which seems to
REM       be a bug.
REM

CREATE TABLE emp (empno NUMBER, ename VARCHAR2(10));

INSERT INTO emp VALUES(7839, 'King');
INSERT INTO emp VALUES(7698, 'Blake');
INSERT INTO emp VALUES(7782, 'Clark');

COMMIT;

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE log_execution IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('log_execution: Emp table has inserted 1 row.');
END;
/

CREATE OR REPLACE TRIGGER log_emp
BEFORE INSERT ON emp
FOR EACH ROW
CALL log_execution -- no semicolon needed
/

Warning: Trigger created with compilation errors.

show errors
Errors for TRIGGER LOG_EMP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/37     PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         := . ( @ % ;
         The symbol ";" was substituted for "end-of-file" to continue.

-- if eliminating the comment '-- no semicolon needed' after the 'CALL log_execution' clause

CREATE OR REPLACE TRIGGER log_emp
BEFORE INSERT ON emp
FOR EACH ROW
CALL log_execution
/

Trigger created.

INSERT INTO emp VALUES(7566, 'Jones');
log_execution: Emp table has inserted 1 row.

1 row created.

COMMIT;

And I have to say LiveSQL is a pretty nice tool runing Oracle SQL and PL/SQL code, hence I also wrote a SQL script to test my code (in other words, report a bug). Here‘s my LiveSQL script.

Finally I replied to Steven Feuerstein to let me know the bug number that when MoS has been going to finish reporting.

Addendum

Update Aug 01, 2020: Yesterday evening Steven Feuerstein also emailed to me the bug filed number #31698004 and he reported the issue to oracle and was still able to reproduce it on 20c (outside of LiveSQL).

Displaying and setting PL/SQL Initialization Parameters

There are the following four types of initialization parameters for PL/SQL compilation.

  • PLSQL_CODE_TYPE
  • PLSQL_OPTIMIZE_LEVEL
  • PLSQL_CCFLAGS
  • PLSQL_WARNINGS

In releases before Oracle Database 10g, the PL/SQL compiler translated your code to machine code without applying many changes for performance. Oracle Database 11g onwards, PL/SQL uses an optimizing compiler that can rearrange code for better performance. You do not need to do anything to get the benefits of this new optimizer; it is enabled by default.

The following table is specially describing those four number of initialization parameter of PL/SQL compilation.

+============================+=============================================================================+
|Compiler Option             |Description                                                                  |
+============================+=============================================================================+
|PLSQL_CODE_TYPE             |Specifies the compilation mode for PL/SQL library units.                     |
+----------------------------+-----------------------------------------------------------------------------+
|PLSQL_OPTIMIZE_LEVEL        |Specifies the optimization level to be used to compile PL/SQL library units. |
+----------------------------+-----------------------------------------------------------------------------+
|PLSQL_WARNINGS              |Enables or disables the reporting of warning messages by the PL/SQL compiler.|
+----------------------------+-----------------------------------------------------------------------------+
|PLSQL_CCFLAGS               |Controls conditional compilation of each PL/SQL library unit independently.  |
+----------------------------+-----------------------------------------------------------------------------+

The new compiler improves the performance of PL/SQL code and allows it to execute approximately two times faster than an Oracle 8i database and 1.5 times to 1.75 times as fast as Oracle 9i Database Release 2.

To get the fastest performance, the compiler setting must be:

PLSQL_CODE_TYPE = NATIVE
PLSQL_OPTIMIZE_LEVEL = 2

Hence this post I focus on displaying and setting the previous two initialization parameters for PL/SQL compilation.

Taking the schema “OE” for example in Oracle Database 11.2, firstly, we use the following SQL code snippet to check the current value of both PLSQL_CODE_TYPE and PLSQL_OPTIMIZE_LEVEL based on some oracle objects.

-- running on the schema of 'OE'.

SET LINESIZE 200
SET PAGESIZE 200
COLUMN code_type FORMAT a15

SELECT name
     , type
     , plsql_code_type AS CODE_TYPE
     , plsql_optimize_level AS OPT_LVL
FROM   user_plsql_object_settings;

NAME                           TYPE         CODE_TYPE          OPT_LVL
------------------------------ ------------ --------------- ----------
ACTIONS_T                      TYPE         INTERPRETED              2
ACTION_T                       TYPE         INTERPRETED              2
ACTION_V                       TYPE         INTERPRETED              2
ADD_JOB_HISTORY                PROCEDURE    INTERPRETED              2
CATALOG_TYP                    TYPE         INTERPRETED              2
CATALOG_TYP                    TYPE BODY    INTERPRETED              2
CATEGORY_TYP                   TYPE         INTERPRETED              2
COMPOSITE_CATEGORY_TYP         TYPE         INTERPRETED              2
COMPOSITE_CATEGORY_TYP         TYPE BODY    INTERPRETED              2
CORPORATE_CUSTOMER_TYP         TYPE         INTERPRETED              2
CUSTOMER_TYP                   TYPE         INTERPRETED              2
CUST_ADDRESS_TYP               TYPE         INTERPRETED              2
CUST_DATA                      PACKAGE      INTERPRETED              2
CUST_DATA                      PACKAGE BODY INTERPRETED              2
CUST_LIST                      PROCEDURE    INTERPRETED              2
......

51 rows selected.

As you can see from the above vaule “INTERPRETED” based on PLSQL_CODE_TYPE, but we can set the compiler initialization parameter’s value using the ALTER SYSTEM or ALTER SESSION statements.

Secondly, we’ll use the ALTER SESSION statement to set PLSQL_CODE_TYPE to become “NATIVE” in order to get the fastest performance.

ALTER SESSION SET PLSQL_CODE_TYPE = 'NATIVE';

Thirdly, I create a procedure “add_job_history” on the schema “OE”.

CREATE OR REPLACE PROCEDURE add_job_history
  (p_emp_id             job_history.employee_id%TYPE,
   p_start_date         job_history.start_date%TYPE,
   p_end_date           job_history.end_date%TYPE,
   p_job_id             job_history.job_id%TYPE,
   p_department_id      job_history.department_id%TYPE
  )
  IS
  BEGIN
    INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id)
    VALUES (p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
  END add_job_history;
/

Abruptly oracle gives me the following warning message.

Warning: Procedure created with compilation errors.

Using a simple statement “SHOW ERRORS” to quickly find out which lines the compilation errors are located in.

oe@ORA11GR2> SHOW ERRORS
Errors for PROCEDURE ADD_JOB_HISTORY:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/5     PL/SQL: SQL Statement ignored
10/17    PL/SQL: ORA-01031: insufficient privileges

As you can see from the previous error ORA-01031 that has a bit of confusion. Next, I check the object_type of “job_history”.

COLUMN object_name FORMAT a12
COLUMN object_type FORMAT a12

SELECT object_name, object_type FROM user_objects WHERE object_name = 'JOB_HISTORY';

OBJECT_NAME  OBJECT_TYPE
------------ ------------
JOB_HISTORY  SYNONYM

Oh, my god! As you can see from the above object_type, it’s a synonym (not a table). A real table “JOB_HISTORY” should be from the schema “HR”. By the following SQL, you can prove my description whether it is right.

COLUMN synonym_name FORMAT a12
COLUMN table_owner  FORMAT a12
COLUMN table_name   FORMAT a12

SELECT synonym_name, table_owner, table_name FROM user_synonyms WHERE synonym_name = 'JOB_HISTORY';

SYNONYM_NAME TABLE_OWNER  TABLE_NAME
------------ ------------ ------------
JOB_HISTORY  HR           JOB_HISTORY

So, switching to the schema “HR”, rerunning all the previous code step by step.

SET LINESIZE 200
SET PAGESIZE 200
COLUMN code_type FORMAT a15

SELECT name
     , type
     , plsql_code_type AS CODE_TYPE
     , plsql_optimize_level AS OPT_LVL
FROM   user_plsql_object_settings;

NAME                           TYPE         CODE_TYPE          OPT_LVL
------------------------------ ------------ --------------- ----------
ADD_COL                        PROCEDURE    INTERPRETED              2
ADD_DEPARTMENT                 PROCEDURE    INTERPRETED              2
ADD_DEPARTMENT_NOEX            PROCEDURE    INTERPRETED              2
ADD_DEPT                       PROCEDURE    INTERPRETED              2
ADD_EMPLOYEE                   PROCEDURE    INTERPRETED              2
ADD_ROW                        PROCEDURE    INTERPRETED              2
......

81 rows selected.
ALTER SESSION SET PLSQL_CODE_TYPE = 'NATIVE';
CREATE OR REPLACE PROCEDURE add_job_history
  (p_emp_id             job_history.employee_id%TYPE,
   p_start_date         job_history.start_date%TYPE,
   p_end_date           job_history.end_date%TYPE,
   p_job_id             job_history.job_id%TYPE,
   p_department_id      job_history.department_id%TYPE
  )
  IS
  BEGIN
    INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id)
    VALUES (p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
  END add_job_history;
/

Procedure created.
SELECT name
     , type
     , plsql_code_type AS CODE_TYPE
     , plsql_optimize_level AS OPT_LVL
FROM   user_plsql_object_settings;

NAME                           TYPE         CODE_TYPE          OPT_LVL
------------------------------ ------------ --------------- ----------
ADD_COL                        PROCEDURE    INTERPRETED              2
ADD_DEPARTMENT                 PROCEDURE    INTERPRETED              2
ADD_DEPARTMENT_NOEX            PROCEDURE    INTERPRETED              2
ADD_DEPT                       PROCEDURE    INTERPRETED              2
ADD_EMPLOYEE                   PROCEDURE    INTERPRETED              2
ADD_JOB_HISTORY                PROCEDURE    NATIVE                   2
ADD_ROW                        PROCEDURE    INTERPRETED              2
......

82 rows selected.

As you can see the last SQL query result, the PLSQL_CODE_TYPE of the procedure “ADD_JOB_HISTORY” being created just now has become NATIVE.

Potential Bug on Muti-tenant DB

Middle last month I found a very strange phenomenon on oracle 19.3 of Windows platform – querying “SELECT COUNT(*) FROM all_objects” is very rapid on SYS but pretty slow on a COMMON user.

So I decided to use a 10046 event to trace the SQL statement previously mentioned so that expected to find out some valuable clues. Take a look at my operation steps as follows.

  • running on SYS schema
  • running on C##QWZ schema
  • tracking SQL via 10046 event on C##QWZ schema

SYS

16:18:35 SYS@ora19c> select count(*) from all_objects;

  COUNT(*)
----------
     72352

16:18:46 SYS@ora19c> 

C##QWZ

16:28:29 C##QWZ@ora19c> select count(*) from all_objects;

  COUNT(*)
----------
     55672

16:40:01 C##QWZ@ora19c> 

10046 event

SET LINESIZE 150
SET PAGESIZE 150
SET SERVEROUTPUT OFF

ALTER SESSION SET max_dump_file_size = 'UNLIMITED';
ALTER SESSION SET tracefile_identifier = '10046';
ALTER SESSION SET events '10046 trace name context forever,level 12';

select count(*) from all_objects;

ALTER SESSION SET events '10046 trace name context off';
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

Next generating a trc file named “…10046.trc” such as, “C:\APP\ADMINISTRATOR\VIRTUAL\diag\rdbms\ora19c\ora19c\trace\ora19c_ora_20224_10046.trc”.

Continue reading

Wget

A few of days ago Jonathan Lewis sent the following message (quoting his original stuff) on Twitter.

EARLY WARNING: at the end of May my old (unchanged since September 2012) website jlcomp.demon.co.uk will be taken off the internet. I may update and post a very small number of the articles on my blog eventually, but if there’s anything you want to keep from it, copy it now.

04:40PM 2020-04-04 Twitter Web App

and,

WARNING: by end May the DEMON domain (with my old website jlcomp.demon.co.uk) will disappear. If there’s anything you want from the website copy it now.

wget -e robots=off -r -np http://www.jlcomp.demon.co.uk -o log.txt

05:53 PM 2020-04-06 Twitter Web App

I heard of this bad news and asked him what reason it is afterwards he replied to me – the license of sub-domain DEMON was about to expire. So it’s time to copy those stuff from his old website if you keep somthing.

Continue reading

Optimizing SQL with LIKE expression – 3

So far I’ve written two number of blog notes about how to optimize sql with LIKE expression (here and here). A couple of days ago I built another funny case including two different columns in the WHERE clause of SQL statement, the former is a like expression and the latter is a constant expression. Hence this post will focus on talking about how to optimize this kind of SQL query.

Here is a demo (same as my previous two posts) creating a test table and quickly inserting some random data with 3e5 lines.

CREATE TABLE person
( id   NUMBER GENERATED ALWAYS AS IDENTITY
, name VARCHAR2(6) NOT NULL
, sex  VARCHAR2(1) NOT NULL
, flag VARCHAR2(1)
, pwd  VARCHAR2(6)
, CONSTRAINT person_pk PRIMARY KEY(id)
);

EXEC DBMS_RANDOM.seed(0);

INSERT /*+APPEND*/ INTO person (name, sex, flag, pwd)
SELECT DBMS_RANDOM.string('A', 6) AS name
     , CASE MOD(ROWNUM, 2) WHEN 0 THEN 'F'
                           WHEN 1 THEN 'M'
       END sex
     , CASE MOD(ROWNUM, 3) WHEN 0 THEN 'T'
                           WHEN 1 THEN NULL
                           WHEN 2 THEN 'F'
       END flag
     , DBMS_RANDOM.string ('p', 6) AS pwd
FROM dual
CONNECT BY level <= 3e5;

COMMIT;

EXEC DBMS_STATS.gather_table_stats(ownname => NULL, tabname => 'PERSON');

As known as wondering a running SQL query is whether more efficient or not, it’s best to check its row-source execution plan that acquired from memory. The simple SQL query is as below.

SELECT *
  FROM person
 WHERE name LIKE 'qw%'
   AND sex = 'F'
;

Now I will use the following three type of cases to optimize the preceding SQL query.

  • creating two seprarate normal indexes for two columns
  • creating a normal index and a bitmap index for two columns
  • creating a composite index for two columns
Continue reading

Optimizing SQL with LIKE expression – 2

In my previous post I introduced 4 number of cases about how to optimize SQL with LIKE expression on oracle database. Even if I thought that I didn’t completely extend some more important details to describe/discuss those optimization skills particularly in previous case 2 and 4 as follows.

  • %qw
  • q%w
Continue reading

Optimizing SQL with LIKE expression

This post is focused on talking about how to optimize several SQL statements with LIKE expression in oracle database. Now let me quote some important knowledge point about the pattern of LIKE condition on 20c.

The LIKE conditions specify a test involving pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE conditions match a portion of one character value to another by searching the first value for the pattern specified by the second.

The pattern can contain special pattern-matching characters:

An underscore (_) in the pattern matches exactly one character (as opposed to one byte in a multibyte character set) in the value.

A percent sign (%) in the pattern can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. The pattern ‘%’ cannot match a null.

In general a LIKE expression has four number of situation in a SQL query statement. Take, for example, some random combinations of between ‘%’ and character strings ‘qw’.

  • qw%
  • %qw
  • %qw%
  • q%w
Continue reading

Middle-N, Pagination Query and Optimization

So far I’ve written 3 number of notes in the series of “top-n, pagination query and optimization”, they’re as follows.

A few days ago in particular Nigel Bayliss gave me some good ideas that was never in my mind (by communicating with email back and forth) so it is a reason why today I am going to write this blog note. Here’s my test demo (running on 19.3) creating table “staff”.

Continue reading