Sokrates on Oracle

Archive for the ‘sql’ Category

Find first n gaps in integer primary key

Posted by Matthias Rogel on 13. September 2016

Setup

sokrates@12.1 > create table t( pk int primary key check(pk > 0));

Table created.

sokrates@12.1 > insert /*+ignore_row_on_dupkey_index(t(pk)) */ into t
sokrates@12.1 > select trunc(dbms_random.value(1, 1e5)) from dual
sokrates@12.1 > connect by level <= 1e5 
sokrates@12.1 > /

63187 rows created.

Finding the first n gaps

sokrates@12.1 > variable n number
sokrates@12.1 > exec :n := 1000

PL/SQL procedure successfully completed.

sokrates@12.1 > set autotr traceonly timi on
sokrates@12.1 > with
sokrates@12.1 > gaps(g, counter, isgap) as
sokrates@12.1 > (
sokrates@12.1 >    select 0, 1, cast(null as varchar2(1)) from dual
sokrates@12.1 >    union all
sokrates@12.1 >    select
sokrates@12.1 >       gaps.g + 1,
sokrates@12.1 >       gaps.counter + case when t.pk is null then 1 else 0 end,
sokrates@12.1 >       case when t.pk is null then 'x' end
sokrates@12.1 >    from gaps, t
sokrates@12.1 >    where gaps.counter <= :n
sokrates@12.1 >    and t.pk(+) = gaps.g + 1
sokrates@12.1 > )
sokrates@12.1 > search breadth first by g asc set o
sokrates@12.1 > cycle g set is_cycle to 1 default 0
sokrates@12.1 > select
sokrates@12.1 >    gaps.g
sokrates@12.1 > from
sokrates@12.1 >    gaps
sokrates@12.1 > where
sokrates@12.1 >    gaps.isgap = 'x'
sokrates@12.1 > /

1000 rows selected.

Elapsed: 00:00:00.12

Execution Plan
----------------------------------------------------------
Plan hash value: 3013247790

----------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name         | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |              |     2 |    30 |     5  (20)| 00:00:01 |
|*  1 |  VIEW                                     |              |     2 |    30 |     5  (20)| 00:00:01 |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|              |       |       |            |       |
|   3 |    FAST DUAL                              |              |     1 |       |     2   (0)| 00:00:01 |
|   4 |    NESTED LOOPS OUTER                     |              |     1 |    39 |     2   (0)| 00:00:01 |
|*  5 |     RECURSIVE WITH PUMP                   |              |       |       |            |       |
|*  6 |     INDEX UNIQUE SCAN                     | SYS_C0087690 |     1 |    13 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("GAPS"."ISGAP"='x')
   5 - filter("GAPS"."COUNTER"<=TO_NUMBER(:N))
   6 - access("T"."PK"(+)="GAPS"."G"+1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2656  consistent gets
          0  physical reads
          0  redo size
       9313  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
       2635  sorts (memory)
          0  sorts (disk)
       1000  rows processed
Advertisements

Posted in Allgemein, sql | 3 Comments »

The Fundamental theorem of arithmetic – SQL version

Posted by Matthias Rogel on 26. May 2015

Every positive integer (except the number 1) can be represented in exactly one way apart from rearrangement as a product of one or more primes, see for example Wolfram MathWorld or Wikipedia.

Here is the SQL-Version, we compute this for all integers up to 100

with bound as
(
select 100 as bound from dual
),
n_until_bound as (
select level+1 n
from dual
connect by level <= (select bound.bound from bound)
),
primes_under_bound as
(
select n_until_bound.n as prime
from n_until_bound
minus
select n1.n * n2.n
from n_until_bound n1, n_until_bound n2
where n1.n <= n2.n
and n1.n <= (select sqrt(bound.bound) from bound)
),
primepowers_until_bound as
(
select p.prime, l.exponent
from primes_under_bound p, lateral(select level as exponent from dual connect by level <= log(p.prime, (select bound.bound from bound))) l
),
factors as
(
select n.n, pb.prime, pb.exponent
from n_until_bound n, primepowers_until_bound pb
where mod(n.n, power(pb.prime, pb.exponent)) = 0
),
largestfactors as
(
select
 f.n, f.prime, min(f.exponent) keep(dense_rank first order by f.exponent desc) as exponent
from factors f
group by f.n, f.prime
)
select /*+pallel */ lf.n || ' = ' || listagg(lf.prime || case when lf.exponent > 1 then ' ^ ' || lf.exponent end, ' * ') within group(order by lf.prime asc) as factorization
from largestfactors lf
group by lf.n
order by lf.n
FACTORIZATION
----------------------------------------------------------------------------------------------------
2 = 2
3 = 3
4 = 2 ^ 2
5 = 5
6 = 2 * 3
7 = 7
8 = 2 ^ 3
9 = 3 ^ 2
10 = 2 * 5
11 = 11
12 = 2 ^ 2 * 3
13 = 13
14 = 2 * 7
15 = 3 * 5
16 = 2 ^ 4
17 = 17
18 = 2 * 3 ^ 2
19 = 19
20 = 2 ^ 2 * 5
21 = 3 * 7
22 = 2 * 11
23 = 23
24 = 2 ^ 3 * 3
25 = 5 ^ 2
26 = 2 * 13
27 = 3 ^ 3
28 = 2 ^ 2 * 7
29 = 29
30 = 2 * 3 * 5
31 = 31
32 = 2 ^ 5
33 = 3 * 11
34 = 2 * 17
35 = 5 * 7
36 = 2 ^ 2 * 3 ^ 2
37 = 37
38 = 2 * 19
39 = 3 * 13
40 = 2 ^ 3 * 5
41 = 41
42 = 2 * 3 * 7
43 = 43
44 = 2 ^ 2 * 11
45 = 3 ^ 2 * 5
46 = 2 * 23
47 = 47
48 = 2 ^ 4 * 3
49 = 7 ^ 2
50 = 2 * 5 ^ 2
51 = 3 * 17
52 = 2 ^ 2 * 13
53 = 53
54 = 2 * 3 ^ 3
55 = 5 * 11
56 = 2 ^ 3 * 7
57 = 3 * 19
58 = 2 * 29
59 = 59
60 = 2 ^ 2 * 3 * 5
61 = 61
62 = 2 * 31
63 = 3 ^ 2 * 7
64 = 2 ^ 6
65 = 5 * 13
66 = 2 * 3 * 11
67 = 67
68 = 2 ^ 2 * 17
69 = 3 * 23
70 = 2 * 5 * 7
71 = 71
72 = 2 ^ 3 * 3 ^ 2
73 = 73
74 = 2 * 37
75 = 3 * 5 ^ 2
76 = 2 ^ 2 * 19
77 = 7 * 11
78 = 2 * 3 * 13
79 = 79
80 = 2 ^ 4 * 5
81 = 3 ^ 4
82 = 2 * 41
83 = 83
84 = 2 ^ 2 * 3 * 7
85 = 5 * 17
86 = 2 * 43
87 = 3 * 29
88 = 2 ^ 3 * 11
89 = 89
90 = 2 * 3 ^ 2 * 5
91 = 7 * 13
92 = 2 ^ 2 * 23
93 = 3 * 31
94 = 2 * 47
95 = 5 * 19
96 = 2 ^ 5 * 3
97 = 97
98 = 2 * 7 ^ 2
99 = 3 ^ 2 * 11
100 = 2 ^ 2 * 5 ^ 2

Posted in fun, sql | Tagged: , | 1 Comment »

A Greedy Algorithm using Recursive subquery factoring ( or better – read the comments – using pattern matching)

Posted by Matthias Rogel on 22. May 2015

Today is friday and I like the twitter-hashtag #FibonacciFriday,
so I tweeted

 

Don’t be afraid of having a look at the wikipedia-site, the math is not complicated at all ( you don’t need more than adding natural numbers smaller than hundred ), nevertheless the theorem is nice from a mathematical point of view.

And there is also mentioned

… For any given positive integer, a representation that satisfies the conditions of Zeckendorf’s theorem can be found by using a greedy algorithm, choosing the largest possible Fibonacci number at each stage. …

After thinking a bit about it, I came to the idea to implement it solely in SQL which might show the strength of this language.

Here we go, we compute the Zeckendorf representations of the first 200 natural numbers in SQL.

If you are not familiar with the xmlquery-part of it, see what Tom Kyte learned from me


with
n as
(
 /* the first 200 natural numbers */
 select level as n
 from dual
 connect by level <= 200
),
f(n, a, b) as
(
 /* construct fibonaccis, 30 are surely enough ... */
 select 1 as n, 1 as a, 1 as b
 from dual
 union all
 select n+1, b, a+b
 from f
 where n<=30
)
,
fibonaccis as
(
 select
 f.a as f
 from f
),
decomp(n, s) as
(
 /* here is the magic recursive subquery factoring */
 select
 n.n,
 (select cast(max(fibonaccis.f) as varchar2(100)) from fibonaccis where fibonaccis.f <= n.n)
 from n
 union all
 select
 d.n,
 rtrim
 (
 d.s || ' + ' ||
 (
 select cast(max(fibonaccis.f) as varchar2(10))
 from fibonaccis
 where fibonaccis.f <= d.n - xmlquery(d.s returning content).getNumberVal()
 ),
 ' + '
 )
 from decomp d
 where
 d.s !=
 rtrim
 (
 d.s || ' + ' ||
 (
 select cast(max(fibonaccis.f) as varchar2(10))
 from fibonaccis
 where fibonaccis.f <= d.n - xmlquery(d.s returning content).getNumberVal()
 ),
 ' + '
 )
)
/* we only want "the last" decomp, the one with maximal length */
select
 decomp.n ||
 ' = ' ||
 min(decomp.s) keep(dense_rank first order by length(decomp.s) desc)
 as zeckendorf_representation
from decomp
group by decomp.n
order by decomp.n
/

ZECKENDORF_REPRESENTATION
------------------------------------------------------------------------------------------------------------------------------------------------------------
1 = 1
2 = 2
3 = 3
4 = 3 + 1
5 = 5
6 = 5 + 1
7 = 5 + 2
8 = 8
9 = 8 + 1
10 = 8 + 2
11 = 8 + 3
12 = 8 + 3 + 1
13 = 13
14 = 13 + 1
15 = 13 + 2
16 = 13 + 3
17 = 13 + 3 + 1
18 = 13 + 5
19 = 13 + 5 + 1
20 = 13 + 5 + 2
21 = 21
22 = 21 + 1
23 = 21 + 2
24 = 21 + 3
25 = 21 + 3 + 1
26 = 21 + 5
27 = 21 + 5 + 1
28 = 21 + 5 + 2
29 = 21 + 8
30 = 21 + 8 + 1
31 = 21 + 8 + 2
32 = 21 + 8 + 3
33 = 21 + 8 + 3 + 1
34 = 34
35 = 34 + 1
36 = 34 + 2
37 = 34 + 3
38 = 34 + 3 + 1
39 = 34 + 5
40 = 34 + 5 + 1
41 = 34 + 5 + 2
42 = 34 + 8
43 = 34 + 8 + 1
44 = 34 + 8 + 2
45 = 34 + 8 + 3
46 = 34 + 8 + 3 + 1
47 = 34 + 13
48 = 34 + 13 + 1
49 = 34 + 13 + 2
50 = 34 + 13 + 3
51 = 34 + 13 + 3 + 1
52 = 34 + 13 + 5
53 = 34 + 13 + 5 + 1
54 = 34 + 13 + 5 + 2
55 = 55
56 = 55 + 1
57 = 55 + 2
58 = 55 + 3
59 = 55 + 3 + 1
60 = 55 + 5
61 = 55 + 5 + 1
62 = 55 + 5 + 2
63 = 55 + 8
64 = 55 + 8 + 1
65 = 55 + 8 + 2
66 = 55 + 8 + 3
67 = 55 + 8 + 3 + 1
68 = 55 + 13
69 = 55 + 13 + 1
70 = 55 + 13 + 2
71 = 55 + 13 + 3
72 = 55 + 13 + 3 + 1
73 = 55 + 13 + 5
74 = 55 + 13 + 5 + 1
75 = 55 + 13 + 5 + 2
76 = 55 + 21
77 = 55 + 21 + 1
78 = 55 + 21 + 2
79 = 55 + 21 + 3
80 = 55 + 21 + 3 + 1
81 = 55 + 21 + 5
82 = 55 + 21 + 5 + 1
83 = 55 + 21 + 5 + 2
84 = 55 + 21 + 8
85 = 55 + 21 + 8 + 1
86 = 55 + 21 + 8 + 2
87 = 55 + 21 + 8 + 3
88 = 55 + 21 + 8 + 3 + 1
89 = 89
90 = 89 + 1
91 = 89 + 2
92 = 89 + 3
93 = 89 + 3 + 1
94 = 89 + 5
95 = 89 + 5 + 1
96 = 89 + 5 + 2
97 = 89 + 8
98 = 89 + 8 + 1
99 = 89 + 8 + 2
100 = 89 + 8 + 3
101 = 89 + 8 + 3 + 1
102 = 89 + 13
103 = 89 + 13 + 1
104 = 89 + 13 + 2
105 = 89 + 13 + 3
106 = 89 + 13 + 3 + 1
107 = 89 + 13 + 5
108 = 89 + 13 + 5 + 1
109 = 89 + 13 + 5 + 2
110 = 89 + 21
111 = 89 + 21 + 1
112 = 89 + 21 + 2
113 = 89 + 21 + 3
114 = 89 + 21 + 3 + 1
115 = 89 + 21 + 5
116 = 89 + 21 + 5 + 1
117 = 89 + 21 + 5 + 2
118 = 89 + 21 + 8
119 = 89 + 21 + 8 + 1
120 = 89 + 21 + 8 + 2
121 = 89 + 21 + 8 + 3
122 = 89 + 21 + 8 + 3 + 1
123 = 89 + 34
124 = 89 + 34 + 1
125 = 89 + 34 + 2
126 = 89 + 34 + 3
127 = 89 + 34 + 3 + 1
128 = 89 + 34 + 5
129 = 89 + 34 + 5 + 1
130 = 89 + 34 + 5 + 2
131 = 89 + 34 + 8
132 = 89 + 34 + 8 + 1
133 = 89 + 34 + 8 + 2
134 = 89 + 34 + 8 + 3
135 = 89 + 34 + 8 + 3 + 1
136 = 89 + 34 + 13
137 = 89 + 34 + 13 + 1
138 = 89 + 34 + 13 + 2
139 = 89 + 34 + 13 + 3
140 = 89 + 34 + 13 + 3 + 1
141 = 89 + 34 + 13 + 5
142 = 89 + 34 + 13 + 5 + 1
143 = 89 + 34 + 13 + 5 + 2
144 = 144
145 = 144 + 1
146 = 144 + 2
147 = 144 + 3
148 = 144 + 3 + 1
149 = 144 + 5
150 = 144 + 5 + 1
151 = 144 + 5 + 2
152 = 144 + 8
153 = 144 + 8 + 1
154 = 144 + 8 + 2
155 = 144 + 8 + 3
156 = 144 + 8 + 3 + 1
157 = 144 + 13
158 = 144 + 13 + 1
159 = 144 + 13 + 2
160 = 144 + 13 + 3
161 = 144 + 13 + 3 + 1
162 = 144 + 13 + 5
163 = 144 + 13 + 5 + 1
164 = 144 + 13 + 5 + 2
165 = 144 + 21
166 = 144 + 21 + 1
167 = 144 + 21 + 2
168 = 144 + 21 + 3
169 = 144 + 21 + 3 + 1
170 = 144 + 21 + 5
171 = 144 + 21 + 5 + 1
172 = 144 + 21 + 5 + 2
173 = 144 + 21 + 8
174 = 144 + 21 + 8 + 1
175 = 144 + 21 + 8 + 2
176 = 144 + 21 + 8 + 3
177 = 144 + 21 + 8 + 3 + 1
178 = 144 + 34
179 = 144 + 34 + 1
180 = 144 + 34 + 2
181 = 144 + 34 + 3
182 = 144 + 34 + 3 + 1
183 = 144 + 34 + 5
184 = 144 + 34 + 5 + 1
185 = 144 + 34 + 5 + 2
186 = 144 + 34 + 8
187 = 144 + 34 + 8 + 1
188 = 144 + 34 + 8 + 2
189 = 144 + 34 + 8 + 3
190 = 144 + 34 + 8 + 3 + 1
191 = 144 + 34 + 13
192 = 144 + 34 + 13 + 1
193 = 144 + 34 + 13 + 2
194 = 144 + 34 + 13 + 3
195 = 144 + 34 + 13 + 3 + 1
196 = 144 + 34 + 13 + 5
197 = 144 + 34 + 13 + 5 + 1
198 = 144 + 34 + 13 + 5 + 2
199 = 144 + 55
200 = 144 + 55 + 1

200 rows selected.

Elapsed: 00:01:28.71

Posted in fun, sql | Tagged: , | 5 Comments »

Yet Another Elementary SQL Bug

Posted by Matthias Rogel on 20. February 2014

Environment

sokrates@12.1 > select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE	12.1.0.1.0	Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

sokrates@12.1 > select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL32UTF8
sokrates@12.1 > !echo $NLS_LANG
american_america.UTF8

Setup is

create table t ( r varchar2(1 char));
insert into t values('£');
commit;

alter session set nls_length_semantics=BYTE;
alter table t add( v as ( cast(r as varchar2(1))));

alter session set nls_length_semantics=CHAR;
create index t_idx on t(v);

exec dbms_stats.set_table_stats(null, 'T', numrows => 1e7)

We get different result for count(v) when using length-semantics BYTE, depending if a table scan or an index scan is used:

sokrates@12.1 > alter session set nls_length_semantics=BYTE;

Session altered.

sokrates@12.1 > select /* index full scan used */ count(v) from t;

  COUNT(V)
----------
	 1

sokrates@12.1 > select /* full table scan used */ count(*), count(v) from t;

  COUNT(*)   COUNT(V)
---------- ----------
	 1	    0

Same behaviour was observed on 11.2

Posted in 11.2, 12c, Bug, sql | 4 Comments »

How 2 Bytes can fit into 1 Byte

Posted by Matthias Rogel on 20. February 2014

Chris Saxon posted a nice quiz regarding a pitfall when creating tables with VARCHAR2-columns: when you are not explicit in specifying the length-semantics ( CHAR or BYTE ), a session parameter, which may vary, is used.

This is a short follow-up of his story, which shows more pitfalls when creating views and selecting from those.
We end up in a structure which can – by definition – contain only 1 byte, but actually shows containing two bytes !

sokrates@11.2 > alter session set nls_length_semantics=CHAR;

Session altered.

sokrates@11.2 > create table i_can_insert (
  2    into_this varchar2(1)
  3  );

Table created.

sokrates@11.2 > insert into i_can_insert (into_this)
  2  values ('£');

1 row created.

sokrates@11.2 > create view i_can_select (from_that)
  2  as
  3  select cast(into_this as varchar2(1))
  4  from i_can_insert;

View created.

sokrates@11.2 > describe i_can_insert
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 INTO_THIS                                          VARCHAR2(1)

sokrates@11.2 > describe i_can_select
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FROM_THAT                                          VARCHAR2(1)

sokrates@11.2 > select * from i_can_select;

FROM_THAT
----------
£

sokrates@11.2 > REM now we change the length semantics ...
sokrates@11.2 > alter session set nls_length_semantics=BYTE;

Session altered.

sokrates@11.2 > REM note that the output of describe changes !
sokrates@11.2 > describe i_can_insert
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 INTO_THIS                                          VARCHAR2(1 CHAR)

sokrates@11.2 > describe i_can_select
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FROM_THAT                                          VARCHAR2(1 CHAR)

sokrates@11.2 > create view can_i_select (from_that)
  2  as
  3  select cast(into_this as varchar2(1))
  4  from i_can_insert;

View created.

sokrates@11.2 > describe can_i_select
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FROM_THAT                                          VARCHAR2(1)

sokrates@11.2 > REM now, can I select ?
sokrates@11.2 > set null nada
sokrates@11.2 > select * from can_i_select;

FROM_THAT
----------
nada

sokrates@11.2 > REM no I cannot, output would be too long in my current semantics, so it is trimmed ( nothing left )
sokrates@11.2 >
sokrates@11.2 > REM now, can I select from i_can_select ?
sokrates@11.2 > select * from i_can_select;

FROM_THAT
----------
nada

sokrates@11.2 > REM no
sokrates@11.2 > select * from i_can_insert;

INTO_THIS
----------
£

sokrates@11.2 > REM but still from the table itself, I can select !
sokrates@11.2 >
sokrates@11.2 > REM let us change back the semantics
sokrates@11.2 > alter session set nls_length_semantics=CHAR;

Session altered.

sokrates@11.2 > describe can_i_select
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FROM_THAT                                          VARCHAR2(1 BYTE)

sokrates@11.2 > select * from can_i_select;

FROM_THAT
----------
£

sokrates@11.2 > select lengthb(from_that) from can_i_select;

LENGTHB(FROM_THAT)
------------------
                 2

So, CAN_I_SELECT.FROM_THAT can only hold 1 byte, but the byte-length of its content is 2 !
-> looks like a bug to me.

Note, that USER_VIEWS.TEXT shows the same for both views, even DBMS_METADATA is not explicit , only COLS shows the difference

sokrates@11.2 > select text from user_views where view_name='CAN_I_SELECT';

TEXT
--------------------------------------------------------------------------------
select cast(into_this as varchar2(1))
from i_can_insert


sokrates@11.2 > select text from user_views where view_name='I_CAN_SELECT';

TEXT
--------------------------------------------------------------------------------
select cast(into_this as varchar2(1))
from i_can_insert


sokrates@11.2 > select dbms_metadata.get_ddl('VIEW', 'CAN_I_SELECT') from dual;

DBMS_METADATA.GET_DDL('VIEW','CAN_I_SELECT')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SOKRATES"."CAN_I_SELECT" ("FROM_THAT") AS
  select cast(into_this as varchar2(1))
from i_can_insert


sokrates@11.2 > select dbms_metadata.get_ddl('VIEW', 'I_CAN_SELECT') from dual;

DBMS_METADATA.GET_DDL('VIEW','I_CAN_SELECT')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SOKRATES"."I_CAN_SELECT" ("FROM_THAT") AS
  select cast(into_this as varchar2(1))
from i_can_insert


sokrates@11.2 > select table_name, column_name, data_length from cols where table_name in ('I_CAN_SELECT', 'CAN_I_SELECT');

TABLE_NAME                     COLUMN_NAME                    DATA_LENGTH
------------------------------ ------------------------------ -----------
CAN_I_SELECT                   FROM_THAT                                1
I_CAN_SELECT                   FROM_THAT                                4

Posted in sql | 4 Comments »

A Restriction of the Cardinality Hint

Posted by Matthias Rogel on 17. January 2014

Here is a restriction of the cardinality hint in conjunction with the materialize-hint ( note: both are undocumented but sometimes of great use ):
we cannot tell the optimizer in the outer query ( the one that uses the materialized subquery ) about the cardinality of the materialization, this can only – and then not always – be done within the materializing query.


Update 21/01/2014.
Randolf Geist shows in this comment that this is not true and gives techniques how to achive this.

The example to show that is stolen from Tom Kyte’s Presentation S13961_Best_Practices_for_Managing_Optimizer_Statistics_Short.pptx from ukoug 2013.zip:

sokrates@12.1 > create type str2tbltype is table of varchar2(100);
  2  /

Type created.

sokrates@12.1 > create function str2tbl( p_str in varchar2 ) return str2tblType
  as
  l_str   long default p_str || ',';
  l_n	     number;
  l_data    str2tblType := str2tblType();
  begin
  loop
  l_n := instr( l_str, ',' );
  exit when (nvl(l_n,0) = 0);
 l_data.extend;
 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
 l_str := substr( l_str, l_n+1 );
 end loop;
 return l_data;
 end;
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16  /

Function created.

sokrates@12.1 > create table t as select object_id, object_name from dba_objects;

Table created.

sokrates@12.1 > create index t_idx on t( object_name );

Index created.

sokrates@12.1 > exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

sokrates@12.1 > variable in_list varchar2(100)
sokrates@12.1 > exec :in_list := 'DBMS_OUTPUT,UTL_FILE,DBMS_PIPE'

PL/SQL procedure successfully completed.

sokrates@12.1 > select count(*) from table(cast( str2tbl( :in_list) as str2tblType) ) t;

  COUNT(*)
----------
	 3

The optimizer does know nothing about the cardinality of this “table(cast( str2tbl( :in_list) as str2tblType) )”.
A clever human could prove that the cardinality of this “table” can never exceed 33.000, so humans sometimes are more clever than the optimizer in estimating cardinalities. In our example, we want to tell the optimizer that the cardinality of this table is approximately 10, which will influence the execution plan of a select which joins this table to a real table.

Without cardinality-hint, the optimizer uses a default cardinality and chooses the wrong hash join:

sokrates@12.1 > with data as
( select *
   from table(cast( str2tbl( :in_list) as str2tblType) ) t
)
select t.object_id, t.object_name
  from data, t
 where t.object_name = data.column_value
  2    3    4    5    6    7    8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 386533642

----------------------------------------------------------------------------------------------
| Id  | Operation			   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		   |	     | 14005 |	 437K|	 154   (1)| 00:00:01 |
|*  1 |  HASH JOIN			   |	     | 14005 |	 437K|	 154   (1)| 00:00:01 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| STR2TBL |	8168 | 16336 |	  29   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL		   | T	     | 90964 |	2664K|	 124   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."OBJECT_NAME"=VALUE(KOKBF$))

When we tell the optimizer via cardinality hint the correct magnitude of the real cardinality, the right access path “nested loops and index” is used

sokrates@12.1 > with data as
( select /*+cardinality(t, 10) */ *
   from table(cast( str2tbl( :in_list) as str2tblType) ) t
)
select t.object_id, t.object_name
  from data, t
 where t.object_name = data.column_value
  2    3    4    5    6    7    8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2392632293

-----------------------------------------------------------------------------------------------
| Id  | Operation			    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |	      |    17 |   544 |    59	(0)| 00:00:01 |
|   1 |  NESTED LOOPS			    |	      |       |       | 	   |	      |
|   2 |   NESTED LOOPS			    |	      |    17 |   544 |    59	(0)| 00:00:01 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2TBL |    10 |    20 |    29	(0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN		    | T_IDX   |     2 |       |     2	(0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID	    | T       |     2 |    60 |     3	(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T"."OBJECT_NAME"=VALUE(KOKBF$))

Note, that we can “pull this hint into the outer query” and still get the exact same execution plan ( even the same plan hash value ):

sokrates@12.1 > with data as
( select *
   from table(cast( str2tbl( :in_list) as str2tblType) ) t
)
select /*+cardinality(data, 10) */ t.object_id, t.object_name
  from data, t
 where t.object_name = data.column_value
  2    3    4    5    6    7    8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2392632293

-----------------------------------------------------------------------------------------------
| Id  | Operation			    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |	      |    17 |   544 |    59	(0)| 00:00:01 |
|   1 |  NESTED LOOPS			    |	      |       |       | 	   |	      |
|   2 |   NESTED LOOPS			    |	      |    17 |   544 |    59	(0)| 00:00:01 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2TBL |    10 |    20 |    29	(0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN		    | T_IDX   |     2 |       |     2	(0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID	    | T       |     2 |    60 |     3	(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T"."OBJECT_NAME"=VALUE(KOKBF$))

This behaviour changes when we materialize the inner query.
First we hint the cardinality in the materializing query:

sokrates@12.1 > with data as
( select /*+materialize cardinality(t, 10) */*
   from table(cast( str2tbl( :in_list) as str2tblType) ) t
)
select t.object_id, t.object_name
  from data, t
 where t.object_name = data.column_value
  2    3    4    5    6    7    8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2115576147

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name			| Rows	| Bytes | Cost (%CPU)| Time	|
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |				|    17 |  1394 |    61   (0)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION	    |				|	|	|	     |		|
|   2 |   LOAD AS SELECT		    | SYS_TEMP_0FD9D666D_268859 |	|	|	     |		|
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2TBL			|    10 |    20 |    29   (0)| 00:00:01 |
|   4 |   NESTED LOOPS			    |				|	|	|	     |		|
|   5 |    NESTED LOOPS 		    |				|    17 |  1394 |    32   (0)| 00:00:01 |
|   6 |     VIEW			    |				|    10 |   520 |     2   (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL		    | SYS_TEMP_0FD9D666D_268859 |    10 |    20 |     2   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN		    | T_IDX			|     2 |	|     2   (0)| 00:00:01 |
|   9 |    TABLE ACCESS BY INDEX ROWID	    | T 			|     2 |    60 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("T"."OBJECT_NAME"="DATA"."COLUMN_VALUE")

Still the right plan.

This changes when we pull the cardinality-hint into the outer query:

sokrates@12.1 > with data as
( select /*+materialize */ *
   from table(cast( str2tbl( :in_list) as str2tblType) ) t
)
select /*+cardinality(data, 10) */ t.object_id, t.object_name
  from data, t
 where t.object_name = data.column_value
  2    3    4    5    6    7    8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 4042153407

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name			| Rows	| Bytes | Cost (%CPU)| Time	|
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |				| 14005 |  1121K|   157   (1)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION	    |				|	|	|	     |		|
|   2 |   LOAD AS SELECT		    | SYS_TEMP_0FD9D666F_268859 |	|	|	     |		|
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2TBL			|  8168 | 16336 |    29   (0)| 00:00:01 |
|*  4 |   HASH JOIN			    |				| 14005 |  1121K|   128   (1)| 00:00:01 |
|   5 |    VIEW 			    |				|  8168 |   414K|     3   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL		    | SYS_TEMP_0FD9D666F_268859 |  8168 | 16336 |     3   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL		    | T 			| 90964 |  2664K|   124   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T"."OBJECT_NAME"="DATA"."COLUMN_VALUE")

The cardinality-hint was ignored and the wrong plan was chosen.

This is a pity because when the materialized subquery is more complicated than in this example ( think of a multi-join where the developer just “knows” the magnitude of the result set for some reason the optimizer cannot be aware of – there are always such cases -), we have no chance in helping the optimizer to find the “right” plan just via cardinality hint.

Posted in sql | 4 Comments »

Issue with updatable views

Posted by Matthias Rogel on 7. January 2014

It’s sometimes amazing, how many bugs there are still with elementary SQL.

Here is one concerning updatable views:

sokrates@12.1 > create table t ( v varchar2(30) );

Table created.

sokrates@12.1 > create view v as
  2  select v as dontdothatman, v as canbelostwheninserted
  3  from t; 

View created.

sokrates@12.1 > insert /* this is fine */ into v 
  2  values('fine', 'fine');

1 row created.

sokrates@12.1 > select * from v;

DONTDOTHATMAN		       CANBELOSTWHENINSERTED
------------------------------ ------------------------------
fine			       fine

sokrates@12.1 > insert /* exception expected because 1st value is lost */ into v
  2  values('this one is lost', 'why isnt that one lost ?');

1 row created.

sokrates@12.1 > select * from v;

DONTDOTHATMAN		       CANBELOSTWHENINSERTED
------------------------------ ------------------------------
fine			       fine
why isnt that one lost ?       why isnt that one lost ?

Posted in 12c, Bug, sql | 4 Comments »

Best Practice in 12c

Posted by Matthias Rogel on 4. December 2013

Since PL/SQL now is closely integrated into SQL, we hence can happily state

sokrates@12.1 > with function bestpractice return varchar2
  2  is
  3  begin
  4     return 'Do not use PL/SQL when it can be done with SQL alone !';
  5  end bestpractice;
  6  select bestpractice() from dual
  7  /

BESTPRACTICE()
--------------------------------------------------------------------------------
Do not use PL/SQL when it can be done with SQL alone !

Posted in 12c, Allgemein, fun, sql | Tagged: | 2 Comments »

Partition Info in V$SESSION_LONGOPS

Posted by Matthias Rogel on 10. May 2013

Oracle’s advanced partitioning has some deficiencies. For example, partition info is missing in V$SESSION_LONGOPS for scan-operations ( full table scans, full index scans ). V$SESSION_LONGOPS.TARGET only shows OWNER.TABLE_NAME in these cases, even when the underlying table/index is partitioned, though the longop doesn’t refer to the whole segment but only to one (sub-)partition of it.
I filed an enhancement request several years ago concerning this matter, but never received any feedback.
However, there is a workaround to that. In many cases, we can find out on which (sub-) partition the longop is working on: V$SESSION_WAIT’s P1- and P2-info can be used for that in case the session is waiting mainly on I/O ( which might be most likely for many systems. )
Here is an extension to V$SESSION_LONGOPS which tries to figure out this additional info.

Update 27/02/2014
Note that the original version has been improved by Jonathan Lewis. I have marked the relevant part with a corresponding comment.
I haven’t observed so far that I wasn’t able to get the partition information from v$session.row_wait_obj# ( as suggested by him ), but from the part marked as “superfluous most likely” ( my original version ). However, I have no proof that this is not possible.

select
   coalesce(
        (
            select 'does not apply'
            from dual
            where slo.TARGET not like '%.%'
            or slo.TARGET is null
        ),
        (
            select 'does not apply'
            from dba_tables dt
            where dt.OWNER=substr(slo.target, 1, instr(slo.target, '.') - 1)
            and dt.TABLE_NAME=substr(slo.target, instr(slo.target, '.') + 1)
            and dt.PARTITIONED='NO'       
        ),
        (
            -- Jonathan Lewis, see http://jonathanlewis.wordpress.com/2014/01/01/nvl-2/#comment-62048
            select
               ob.subobject_name || ' (' || ob.object_type || ')'
            from v$session s, dba_objects ob
            where
              ob.object_id = s.row_wait_obj#
            and s.sid = slo.sid
            and ob.OBJECT_TYPE like '%PARTITION%'            
        ),
        (
            -- superfluous most likely
            select
               de.partition_name || ' (' || de.segment_type || ') NOT SUPERFLUOUS IF YOU SEE THAT'
            from v$session_wait sw, dba_extents de
            where
              sw.sid=slo.sid
            and slo.opname like '%Scan%'
            and sw.P1TEXT like 'file%'
            and sw.P1 = de.FILE_ID and sw.P2 between de.BLOCK_ID and de.BLOCK_ID + de.BLOCKS - 1
            and de.owner = substr(slo.target, 1, instr(slo.target, '.') - 1)
            and de.segment_type in
            (
               'TABLE PARTITION', 'TABLE SUBPARTITION',
               'INDEX PARTITION', 'INDEX SUBPARTITION'            
            )
            and de.segment_name in
            (
                 -- table
                 select
                    substr(slo.target, instr(slo.target, '.') + 1)
                 from dual
                 union all
                 -- index
                 select di.index_name
                 from dba_indexes di
                 where di.owner=substr(slo.target, 1, instr(slo.target, '.') - 1)
                 and di.TABLE_NAME = substr(slo.target, instr(slo.target, '.') + 1)
            )
         ),
        'unknown'
      )
   as partition_info,     
   slo.*
from v$session_longops slo
where slo.TIME_REMAINING > 0

Note that this might take a bit longer than a simple

select slo.*
from v$session_longops slo
where slo.TIME_REMAINING > 0

, though due to coalesce’s short circuiting it is quite efficient.

Posted in Allgemein, sql | Tagged: | 2 Comments »

select the scale of a number

Posted by Matthias Rogel on 10. August 2012

with qs as
(
select
round(
dbms_random.value(-20, 20),
dbms_random.value(1, 15)
) q
from dual
connect by level<=10
)
select
qs.q,
case
when qs.q = floor(qs.q) then
0
when qs.q is null then null
else
(
select max(level)
from dual
connect by round(qs.q, level - 1) != qs.q
)
end as "scale(q)"
from qs

Posted in sql | Leave a Comment »