Index on a virtual column: would it help others?

Is it possible to create an index on a column a and use it for column b? In certain situations yes.

Let’s first build the model and explore those situations (in 11.2.0.3)

  create table t1
        as   select
             rownum n1
            ,to_date(to_char(to_date('01/01/1985','dd/mm/yyyy'),'J') + trunc(dbms_random.value(1,11280)),'J') ord_date
            ,dbms_random.string('L',dbms_random.value(1,30))||rownum text_comment
        from dual
        connect by level <= 10000;

  alter table t1 add virt_date date generated always as (trunc(ord_date)) virtual;

  begin
        dbms_stats.gather_table_stats
           (ownname          => user,
            tabname          =>'t1',
            method_opt       => 'for all columns size 1'
          );
      end;
     /
 

I have created a simple table t1 to which I have attached a virtual column using trunc function (here below with n°4):

  describe t1
 Name                 Type
 -------------------- --------------------
 1      N1            NUMBER
 2      ORD_DATE      DATE
 3      TEXT_COMMENT  VARCHAR2(4000 CHAR)
 4      VIRT_DATE     DATE
 

I want to explore the following select:

 select * from t1 where ord_date =to_date('02/08/2011','dd/mm/yyyy');
 

I have a predicate on ord_date. I have a virtual column based on ord_date. What do you think if I create an index on the virtual column?  Would it help my above query?

 create index ind_virt_date on t1(virt_date);
 select * from t1 where ord_date =to_date('02/08/2011','dd/mm/yyyy');

 ---------------------------------------------------------------------------------------------
 | Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT            |               |       |       |     3 (100)|          |
 |*  1 |  TABLE ACCESS BY INDEX ROWID| T1            |     1 |    40 |     3   (0)| 00:00:01 |
 |*  2 |   INDEX RANGE SCAN          | IND_VIRT_DATE |     1 |       |     1   (0)| 00:00:01 |
 ---------------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 1 - filter("ORD_DATE"=TO_DATE(' 2011-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
 2 - access("T1"."VIRT_DATE"=TRUNC(TO_DATE(' 2011-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
 

Yes it did. The CBO uses the index on the virtual column (ind_virt_date) which is based on a different column (ord_date) to cover the predicate where ord_date = to_date(’02/08/2011′,’dd/mm/yyyy’);

What I have shown up to now is: If you create an index on a virtual column, and you use a where clause on the column on which the virtual column is based on, the index is selected by the CBO.

But the question is: would this be extensible to all types of virtual columns?

And the answer is: I don’t think so

Ok, let’s test with another virtual column using again the trunc function

 alter table t1 add virt1_n1 number generated always as(trunc(n1)) virtual;
 create index ind_virt_n1 on t1 (virt1_n1);

 select * from t1 where n1 = 1;

 -------------------------------------------------------------------------------------------
 | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT            |             |       |       |     2 (100)|          |
 |*  1 |  TABLE ACCESS BY INDEX ROWID| T1          |     1 |    41 |     2   (0)| 00:00:01 |
 |*  2 |   INDEX RANGE SCAN          | IND_VIRT_N1 |    40 |       |     1   (0)| 00:00:01 |
 -------------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 1 - filter("N1"=1)
 2 - access("T1"."VIRT_N1"=TRUNC(1))
 

The index on the virtual column has been again used to cover a predicate on its underlying column.

Let’s test another case with ceil function this time.

 drop index ind_virt_n1;
 drop index ind_virt_date;
 alter table t1 drop column virt1_n1;
 alter table t1 add virt1_n1 generated always as (ceil(n1)) virtual;

 create index ind_virt_n1 on t1(virt1_n1);

 select * from t1 where n1 = 1;

 --------------------------------------------------------------------------
 | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 --------------------------------------------------------------------------
 |   0 | SELECT STATEMENT  |      |       |       |    17 (100)|          |
 |*  1 |  TABLE ACCESS FULL| T1   |     1 |    41 |    17   (0)| 00:00:01 |
 --------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 1 - filter("N1"=1)
 

The index on the virtual column has, however, not been used by the CBO in this case

Another case with abs function this time.

 alter table t1 drop column virt1_n1;
 alter table t1 add virt1_n1 generated always as (abs(n1)) virtual;
 create index ind_virt_n1 on t1(virt1_n1);

 select * from t1 where n1 = 1;

 --------------------------------------------------------------------------
 | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 --------------------------------------------------------------------------
 |   0 | SELECT STATEMENT  |      |       |       |    17 (100)|          |
 |*  1 |  TABLE ACCESS FULL| T1   |     1 |    41 |    17   (0)| 00:00:01 |
 --------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 1 - filter("N1"=1)
 

Another case with nvl function this time:

alter table t1 drop column virt1_n1;
alter table t1 add virt1_n1 generated always as (nvl(n1,0)) virtual;

create index ind_virt_n1 on t1(virt1_n1);

select * from t1 where n1 = 1;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    17 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    40 |    17   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1"=1)

The index on these virtual columns has not been used by the CBO.

Summary: In all cases of functions I have used to generate the virtual column, the only function that has permitted to an index created on its generated virtual column to be selected by the CBO (when this virtual column is not in the predicate part) is the trunc function.

My first thought was that when the internal definition of the virtual column matches the expression I’ve used to create the virtual column then the index will be used. But I was wrong because both trunc and ceil (or abs or nvl) have the same matching between their internal definition and the expression I have used to create them respectively:

 select column_name col_name, data_default def
 from dba_tab_columns
 where table_name = 'T1'
 and column_name in ('VIRT_DATE','VIRT_N1', 'VIRT1_N1', 'VIRT2_N1','VIRT3_N1');

 COL_NAME       DEF
 ------------  -----------------
 VIRT_DATE     TRUNC("ORD_DATE")
 VIRT_N1       TRUNC("N1",2)
 VIRT2_N1      ABS("N1")
 VIRT3_N1      NVL("N1",0)
 VIRT1_N1      CEIL("N1")
 

Do you know why the trunc function works differently here?

Ah! by the way I have also tested the trunc function by setting(in 12c) the hidden parameter _truncate_optimization_enabled from its default value TRUE to FALSE and this has not changed anything

CBO and unusable unique index

Very recently a question came up on oracle-list where the original poster was wondering about two things (a) how could a refresh on materialized view allows duplicate key to be possible in the presence of a unique index and (b) he was struggling about a particular select which is giving a wrong results.

The answer to the first question is easy and I have already blogged about it. He was refreshing the materialized view using a FALSE value for the parameter atomic refresh. With this particular refresh parameter, the materialized view is refreshed  using a rapid truncate table followed by a direct path insert. Direct path load as shown in my blog, will silently disable the unique index allowing duplicate keys to be accepted. Thought that this seems to be true (in this context of materialized view refresh) only in 11gR2. The preceding release (10gR2) is not allowing duplicate keys during this kind of refresh as I’ve shown in my answer in the oracle-list forum. Does this mean that 10gR2 is not direct path loading when atomic refresh is set to FALSE? I have to check.

But what motivated the current blog article is the second question. See with me

SQL> create table a(id int,val number);
Table created.

SQL> insert into a select 1, 1 from dual;
1 row created.

SQL> create table b(id int);
Table created.

SQL> create unique index uq_b on b(id);
Index created.

Then I will use a sqlloader to load data into table b using a direct path load in order to silently disable the unique index. The control file(c.ctl) I will be using resembles to:

LOAD DATA
INFILE *
REPLACE
INTO TABLE B
(id position(1:1) TERMINATED BY ",")
BEGINDATA
1;
1;

And now I will launch the sqlloader

C:\>sqlldr user/paswd@database control=c.ctl direct=true

SQL*Loader: Release 10.2.0.3.0 - Production on Tue Oct 22 16:46:06 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Load completed - logical record count 3.

What do you think it happens to the unique index and to the table b after this direct path load?

SQL> select index_name, status from user_indexes where index_name ='UQ_B';

INDEX_NAME                     STATUS
------------------------------ --------
UQ_B                           UNUSABLE

SQL> select count(1) from b;

COUNT(1)
----------
2

The unique index has been disabled and there are duplicate keys in table b.

So far so good.

Let’s now start exploring the Original Poster queries problem

SQL> select a.*
from a, b
where a.id = b.id(+);

ID        VAL
---------- ----------
1          1
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    15 (100)|          |
|   1 |  TABLE ACCESS FULL| A    |     1 |    26 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2) 

The CBO knows that there is a unique index on b(id). And, as far as there is one id in table a, the CBO, obviously, assumes that there will be only one record for the corresponding id in table b. This is why table b is not present in the execution plan by the way. Unfortunately the unique index has been disabled by the direct path load and has permitted the presence of duplicate record in table b. This is the reason why the query is producing a wrong result.

If we force the CBO to access the table b the result is however correct

SQL>  select a.*,b.id
from a, b
where a.id = b.id(+);

ID        VAL         ID
---------- ---------- ----------
1          1          1
1          1          1

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    36 (100)|          |
|*  1 |  HASH JOIN OUTER   |      |     2 |    78 |    36   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     1 |    26 |    15   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     2 |    26 |    20   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")

Note
-----
- dynamic sampling used for this statement (level=2)

It is clear that the CBO is not looking the unique index status during the optimization (plan generation) phase.

But, what if instead of the unusable unique index, we have a disabled unique constraint? Will the CBO consider the status of the unique constraint in this case?

SQL> alter table b add constraint b_uk unique (id) disable;
Table altered.

SQL> select a.*
from a, b
where a.id = b.id(+);

ID        VAL
---------- ----------
1          1

Oups. The CBO is still wrong. What if we drop the culprit index?

SQL> drop index uq_b;
Index dropped.

SQL> select a.*
from a, b
where a.id = b.id(+);

ID        VAL
---------- ----------
1          1
1          1

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    36 (100)|          |
|*  1 |  HASH JOIN OUTER   |      |     2 |    18 |    36   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     1 |     6 |    15   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     2 |     6 |    20   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")

Which finally gives a correct result.

Bottom line: always make sure your unique indexes are usable.

UPDATE 27-12-2013 :  following a discussion here,Ted from the Oracle Global Customer Support, pointed me to the bug number 17533502 which seems very close the problem exposed here. Ted also pointedme to a work arround solution as shown below:

SQL> select * from v$version where rownum=1;

BANNER                                                                       CON_ID
---------------------------------------------------------------------------- -------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0

SQL> select a.*
 2 from a, b
 3 where a.id = b.id(+);

ID   VAL
--- ----
 1   1

SQL> ALTER SESSION SET "_optimizer_join_elimination_enabled" = false;

Session altered.

SQL> select a.*
 2 from a, b
 3 where a.id = b.id(+);

ID  VAL
--- -----
 1  1
 1  1

You want a bridge from single instance to RAC? this book is for you

When Syed Jaafar Oracle ACE Director and one of the world RAC experts asked me to review the new book Oracle Expert Oracle RAC 12c he has coauthored with 3 other Oracle ACE Directors, my first thought was: how a non experienced  RAC developer could seriously review this book?

In my last decade of Oracle single Instance DBA-Developer carrier, I have got the feeling that Jonathan Lewis books (plus Tom Kyte and Christian Antognini ones) have let no place for other books ; at least in what those books deal about. In addition, Jonathan has a style of writing and narrating  technical features that makes you feeling as if you are reading an agreeable story. I am still waiting to read as attractive and as elegant books as those written by Jonathan Lewis.

Although I might not have any RAC experience, I finally decided to start reviewing this book more like a typical reader who knew the single instance fundamentals and wanted to learn basics of RAC. I know that I am very severe in judging the writing style of books (even in English which isn’t my mother tongue) and this is on what I was going to strengthen my attention.

The first thing I did is to go through the Table of content and isolate Chapters that might be close to my background. The Chapters I have selected for review was then 6, 10, 11 and 12. Unintentionally, those Chapters belong to Riyaj Shamsudeen. Then I started reading Chapter 8 Backup and Recovery in RAC written by Syed. I couldn’t resist the temptation to write the current review before finishing reading the complete book.

Below you can find my review of what I have already read. The review I sent to the authors contains several pages where I asked several clarifications and questions that revealed to be crucial and very important by the author themselves:

Chapter 6:   Application Design Issues: I am very pleased to say that I have been agreeably surprised by Riyaj style of writing. He goes to the essential using concise words touching the heart of his desired goal (what he wants to explain).  Although the design issues he presented can be found in several other trusted documents and blog articles, it is nevertheless worth having them listed all over in a dedicated chapter and in an elegant writing style; and among all of that, Riyaj has presented these issues always with a comparison between the effect these issues have on single instance and the magnified effect they can have in RAC. We have had a discussion about Localized Inserts and globally hash partitioning them to reduce the contention on the index. About excessive Commits, Sequence Cache, Index Design, Excessive Parallel Scans and Full Table Scans which you have already guessed are the main application design you should absolutely be aware of before embarking into a RAC project. Definitely this Chapter is a must to have Chapter for RAC (and even single Instance) designer.

Chapter 10: RAC Database Optimization: A very interesting Chapter where several important RAC specific feature have been clearly identified and explained such as Cache Fusion, Global Resource Directory (GRD), Several specific RAC wait events, etc…

When you read this (red is mine): “In a single instance, a database block can reside only in the local buffer cache. Therefore, if the block is not in the local buffer cache, it is read from the disk. In RAC, the block can be resident in any buffer cache. So, a special type of processing takes place that deviates sharply from single-instance processing” and you know the fundamental work Oracle has to do to guaranty the ACID in single instance then you may realize how the task could be complicated in RAC. Riyaj  ”simplifies” this for you. He has clearly explained this concept and showed how a consistent read is fabricated and how a Current Block read is requested and got in RAC.

If you read this Chapter and Chapter 2 (Chapter 2 Redo and Undo) of the last Jonathan Lewis book  then you will be sure that you’ve made your Redo and Undo Internal “Giro” or “tour de France”.

Chapter 11: Locks and Deadlocks: In the process of reviewing this book I was very impatient to start reading this chapter. Because I am a big fan of Locks and Deadlocks and I have read everything about these two subjects. Although I can read and interpret easily a single instance deadlock graph I am unable to correctly interpret a deadlock graph from a RAC machine. Riyaj, as far as I am aware, is the first author who has published something about deadlock in RAC. I told him that I would have highly appreciate if he went into one or two real life RAC deadlock graphs and explained them in details in this chapter. Because practical cases are what readers want to see and  what they most appreciate.

Chapter 12: Parallel Query in RAC: Having no real experience in this particular subject, I read it as someone who wanted to learn how parallel Query is handled in a RAC machine. I will certainly keep this Chapter very close to me and will couple it to the work (blog articles and webinars) done by Randolph Geist and recent articles written by Jonathan Lewis to  definitely master the parallel query in both single instance and RAC. As always, my experience let me know that sometimes I read a chapter (for example Chapter 2 Redo and Undo of Jonathan Lewis) that I found very difficult to understand. But when I worked hard to understand it (and it took me several months), I magically discovered how much this Chapter is well written and strictly speaking is wonderful. I have a feeling that it will be the same story with Riyaj Chapter 12.

Chapter 8: Backup and Recovery in RAC: this chapter explains step by step how a backup and recovery of instance are done in RAC. It includes very interesting pictures which confirm the mantra “a picture is worth a thousand words”. The concepts are well presented and explained with details. I have no real experience in this field to make objective judgments. However, if I am to start a new RAC job where Backup and Recovery are in my tasks then this chapter will be close to me and I will be basing my comprehension effort on it. Nice to have 

Conclusion: The book contains 14 Chapters and more than 431 pages. I have reviewed 5 Chapters. If you come from single instance as I am, you will find this book an excellent bridge. If you are experienced RAC developers and you want to learn how to avoid main application design in RAC (you shouldn’t doing them as you are already an experienced RAC person) then Chapter 6 is for you and should absolutely be read and understood. If you want to learn internal of Redo, Undo, Consistent and Current Read, the Chapter 10 is for you. You will also learn and understand in this Chapter the main RAC wait events. If you want to learn how Locks and Deadlocks are handled (do you know that the internal process, that kills the first session which started waiting in case of deadlock, takes place every 10 seconds and not every 3 seconds?) Chapter 11 is for you; and if, as I have the intention to do, you want to definitely understand and master Parallel Query in general and in RAC particularly then print out Chapter 12 and start exploring it. I am sure that after a hard work (it depends on your knowledge) you will certainly finish by savoring it

On how to enforce uniqueness when unique constraint is not possible

The following select gives a list of existing table that do not possess a unique constraint

select table_name
from user_tables u1
where not exists (select null
from  user_constraints u2
where u2.table_name      = u1.table_name
and   u2.constraint_type = 'U');

However, the above select doesn’t mean that there isn’t any other uniqueness enforcement  implemented without a dedicated unique constraint.

Recently, I have been confronted to a business rule to be implemented in a Data Vault model which specifies that each Satellite or Satellite of Link table should have a unique key enforcement satisfying the following rule

For each couple of (col1, col2) I could have at most a unique record for a not null dv_load_end_date column.

A picture being worth a thousand words let’s see this through a real example

SQL> create table t1
(t1_sk            number
,dv_load_date     date
,seq_nbr          number
,n1               number
,n2               number
,status           varchar2(10)
,identification   varchar2(30)
,dv_load_end_date date);

SQL> alter table t1 add constraint t1_pk primary key (t1_sk,dv_load_date, seq_nbr);

The unique business rule would be described as follows: for each couple (t1_sk, n2) I could have at most one record for a null dv_load_end_date column

Is this doable via a unique constraint? No it is not.

And here where function based index comes to the rescue

SQL> create unique index ind_t1_uk on t1
(case when dv_load_end_date is null then t1_sk else null end
,case when dv_load_end_date is null then n2 else null end);

And here how the inserts go

SQL> insert into t1 (t1_sk ,dv_load_date ,seq_nbr,n1,n2,dv_load_end_date)
              values(1, sysdate, 1, 1, 100, null);

1 row created.

SQL> insert into t1 (t1_sk ,dv_load_date ,seq_nbr,n1,n2,dv_load_end_date)
              values(1, sysdate, 1, 1, 100, sysdate);

1 row created.

SQL> insert into t1 (t1_sk ,dv_load_date ,seq_nbr,n1,n2,dv_load_end_date)
              values(1, sysdate, 1, 1, 100, null);

insert into t1 (t1_sk ,dv_load_date ,seq_nbr,n1,n2,dv_load_end_date)
*
ERROR at line 1:
ORA-00001: unique constraint (XXX.IND_T1_UK) violated

SQL> insert into t1 (t1_sk ,dv_load_date ,seq_nbr,n1,n2,dv_load_end_date)
              values(1, sysdate, 1, 1, 200, null);
1 row created.

This post is the second one in a set of small scripts (blog articles) I decided to collect in my blog for my documentation and reuse purposes

Intelligent CBO

Just a small note to show you a situation I’ve recently encountered which shows how the CBO is intelligent. I have the following two pieces of SQL

UPDATE t1 a
SET
a.padding = 'yyyyyyyy'
WHERE
a.id1 in
(SELECT
     b.id2
FROM t2  b
WHERE a.id1 = a.n1   ---> spot this
);

And the second one

UPDATE t1 a
SET
a.padding = 'yyyyyyyy'
WHERE
a.id1 in
(SELECT
 b.id2
 FROM t2  b
)
AND a.id1 = a.n1;       ---> spot this

I would not have written the first SQL in order to restrict the updates only to records in t1 having identical id1 and n1. I would have logically issued the second one instead.

But to my surprise the CBO recognized that the where clause in the subquery (WHERE a.id1 = a.n1 ) should be applied to the main update by replacing it with the AND clause outside the brackets. Here below are the corresponding execution plans

First query

Plan hash value: 1788758844
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |     1 |    91 |   899   (2)| 00:00:03 |
|   1 |  UPDATE             | T1   |       |       |            |          |
|*  2 |   HASH JOIN SEMI    |      |     1 |    91 |   899   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| T1   |     1 |    78 |   447   (2)| 00:00:02 |
|   4 |    TABLE ACCESS FULL| T2   |   104K|  1320K|   449   (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID1"="B"."ID2")
3 - filter("A"."ID1"="A"."N1")

Note
-----
- dynamic sampling used for this statement (level=2)

Second query

Plan hash value: 1788758844
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |     1 |    91 |   899   (2)| 00:00:03 |
|   1 |  UPDATE             | T1   |       |       |            |          |
|*  2 |   HASH JOIN SEMI    |      |     1 |    91 |   899   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| T1   |     1 |    78 |   447   (2)| 00:00:02 |
|   4 |    TABLE ACCESS FULL| T2   |   104K|  1320K|   449   (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID1"="B"."ID2")
3 - filter("A"."ID1"="A"."N1")

Note
-----
- dynamic sampling used for this statement (level=2)

The same plan hash value and the same predicate part. It’s funny enough.

If you want to play with the test here is the model (borrowed from Jonathan Lewis)

create table t1
as
with generator as (
select  --+ materialize
rownum id
from dual
connect by
level <= 10000)
select
   rownum id1,
   trunc(dbms_random.value(1,1000))    n1,
   lpad(rownum,10,'0') small_vc,
   rpad('x',100)       padding
from
 generator   v1,
 generator   v2
where
rownum <= 100000;

create table t2
 as
with generator as (
select  --+ materialize
rownum id
from dual
connect by
level <= 10000)

select
 rownum                  id2,
 trunc(dbms_random.value(10001,20001))   x1,
 lpad(rownum,10,'0') small_vc,
rpad('x',100)       padding
from
 generator   v1,
 generator   v2
where
rownum <= 100000;