Origin

The origin of creating this blog is I need to use the account of WordPress to comment an excellent article of Jonathan Lewis.

Next, I’ll tell you something fantastic.

Occasionally, I look at the remarkable article about Defaults that has been published by Jonathan on his personal blog. When I have finished reading it curiously and seriously, I also do some test according to the script file of “defaults_cost.sql” he had supplied and the full content of that SQL script is as follows,

rem
rem     Script:         defaults_cost.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2017
rem

create table t1 (
        column1  varchar2(10),
        column2  varchar2(10),
        column3  varchar2(10),
        column4  varchar2(10),
        column32 varchar2(32)   default 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
        column33 varchar2(33)   default 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
        virtual1      generated always as (
                column4 || column3 || column2 || column1
        ) virtual
)
segment creation immediate
;

execute dbms_output.put_line(dbms_stats.create_extended_stats(user,'t1','(column1 || column2 || column3 || column4)'))
execute dbms_output.put_line(dbms_stats.create_extended_stats(user,'t1','(column1,column2,column3)'))

create or replace function plsqlfunction_with_30char_name(
        i_in varchar2
)
return varchar
deterministic
is
begin
        return initcap(i_in);
end;
/

create index t1_i1 on t1(substr(plsqlfunction_with_30char_name(column1),1,10));

Unfortunately, at first I don’t understand some details which he has mentioned. Next, I do the test and then post my doubt about the formatted trace file (using TKPROF) on his comment area, such as,

********************************************************************************

SQL ID: 47r1y8yn34jmj Plan Hash: 2191121161

select default$
from
col$ where rowid=:1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     50      0.01       0.01          0          0          0           0
Fetch       50      0.00       0.00          0        100          0          50
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      101      0.01       0.01          0        100          0          50

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY USER ROWID COL$ (cr=1 pr=0 pw=0 time=25 us starts=1 cost=1 size=15 card=1)

Afterwards, Jonathan replies to me for my comment. Due to his encouragement, I have eventually comprehended some details he has explained.

There are total 4 screenshots that I have posted in his comment area and he replies to me as follows,

From then on, I’ve created my personal blog of WordPress and regularly publish a series of related oracle articles on this blog.

 

---

 

Update

May 08, 2018 : For beautifying code snippet I quoted, hence, I have adopted this tag of code on WordPress;

May 20, 2018 : Adding a horizontal line for separating update part from body paragraph;