Driving a road

At my last post I talked about different ways to write SQL. Here is an example of such situation. There are three different queries written to get the similar results out of two tables. Here I am driving a road. So the information here is kind of spatial stored in relational way.

create table roadpoint(roadpoint_id number constraint roadpoint_pk primary key, roadnumber number not null, distance number not null);

create table region(region_id number constraint region_pk primary key, startpoint references roadpoint not null, endpoint references roadpoint not null);

insert into roadpoint select level,1,level*10 from dual connect by level<=20000;  


insert into region values (1,1,2);  
insert into region values (2,4,6);  
insert into region values (3,7,10);  
insert into region values (4,40,50);  
insert into region values (5,4006,4010);  

exec dbms_stats.gather_table_Stats(user,'REGION');  
exec dbms_stats.gather_table_Stats(user,'ROADPOINT');    

select * 
  from region reg, roadpoint st, roadpoint en 
 where reg.startpoint = st.roadpoint_id
   and reg.endpoint = en.roadpoint_id ;  

The problem here is that roadpoints inside region are not selected.

The needed rows may be impressed with a query like following.

select reg.region_id, rp.roadpoint_id,rp.roadnumber,rp.distance
from region reg, roadpoint rp
where exists (select null from roadpoint st, roadpoint en where reg.startpoint = st.roadpoint_id
 and reg.endpoint = en.roadpoint_id
 and rp.distance between st.distance and en.distance
);

SQL_ID  2q2uj40br0df2, child number 0
-------------------------------------
select reg.region_id, rp.roadpoint_id,rp.roadnumber,rp.distance from
region reg, roadpoint rp where exists (select null from roadpoint st,
roadpoint en where reg.startpoint = st.roadpoint_id   and reg.endpoint
= en.roadpoint_id   and rp.distance between st.distance and en.distance
)

Plan hash value: 2380388577

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |     25 |00:00:00.55 |     208K|       |       |          |
|*  1 |  FILTER                       |              |      1 |        |     25 |00:00:00.55 |     208K|       |       |          |
|   2 |   MERGE JOIN CARTESIAN        |              |      1 |    100K|    100K|00:00:00.01 |      60 |       |       |          |
|   3 |    TABLE ACCESS FULL          | REGION       |      1 |      5 |      5 |00:00:00.01 |       7 |       |       |          |
|   4 |    BUFFER SORT                |              |      5 |  20000 |    100K|00:00:00.01 |      53 |   690K|   486K|  613K (0)|
|   5 |     TABLE ACCESS FULL         | ROADPOINT    |      1 |  20000 |  20000 |00:00:00.01 |      53 |       |       |          |
|   6 |   NESTED LOOPS                |              |    100K|      1 |     25 |00:00:00.35 |     208K|       |       |          |
|*  7 |    TABLE ACCESS BY INDEX ROWID| ROADPOINT    |    100K|      1 |   4078 |00:00:00.31 |     200K|       |       |          |
|*  8 |     INDEX UNIQUE SCAN         | ROADPOINT_PK |    100K|      1 |    100K|00:00:00.10 |     100K|       |       |          |
|*  9 |    TABLE ACCESS BY INDEX ROWID| ROADPOINT    |   4078 |      1 |     25 |00:00:00.01 |    8167 |       |       |          |
|* 10 |     INDEX UNIQUE SCAN         | ROADPOINT_PK |   4078 |      1 |   4078 |00:00:00.01 |    4089 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------

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

  1 - filter( IS NOT NULL)
  7 - filter("EN"."DISTANCE">=:B1)
  8 - access("EN"."ROADPOINT_ID"=:B1)
  9 - filter("ST"."DISTANCE"<=:B1)
  10 - access("ST"."ROADPOINT_ID"=:B1)    

Taking a look the generated plan one might consider something else. Here are two alternative cursors c_join and c_drivetheroad. Those are measured with Tom Kytes runstats

DECLARE
  CURSOR c_join
  IS
select reg.region_id, rp.roadpoint_id,rp.roadnumber,rp.distance
from region reg, roadpoint st, roadpoint en, roadpoint rp
where reg.startpoint = st.roadpoint_id
 and reg.endpoint = en.roadpoint_id
 and rp.distance between st.distance and en.distance
;
--
  CURSOR c_drivetheroad
  IS
select lv region_id,roadpoint_id,roadnumber,distance
from (
select region_id
,last_value(region_id ignore nulls)over(partition by roadnumber order by rp.distance) lv
,last_value(region_id ignore nulls)over(partition by roadnumber order by rp.distance desc) fv
,staend
,point
,rp.roadnumber
,roadpoint_id
,distance
from (
select *
from (
select reg.region_id, st.distance startdistance, en.distance enddistance
from region reg, roadpoint st, roadpoint en
where reg.startpoint = st.roadpoint_id
 and reg.endpoint = en.roadpoint_id
)
unpivot (point for staend in (startdistance as '1', enddistance as '-1'))
) re, roadpoint rp
where re.point (+)= rp.distance
) qw
where lv=fv
;
--
BEGIN
   runstats_pkg.rs_start;
  FOR i IN 1 .. 100 LOOP
     FOR rec IN c_join LOOP
        NULL;
     END LOOP;
  END LOOP;
   runstats_pkg.rs_middle;
  FOR i IN 1 .. 100 LOOP
     FOR rec IN c_drivetheroad LOOP
        NULL;
     END LOOP;
  END LOOP;
   runstats_pkg.rs_stop;
END;
/

Before looking at runstats results lets see the plans used. Does it seem like the first join method look like a bit easier than the second alternative. A-rows in the first one are 25 at most as those are 20000 in the second alternative.

SQL_ID  fu5n5fqtksa7b, child number 0
-------------------------------------
select reg.region_id, rp.roadpoint_id,rp.roadnumber,rp.distance from
region reg, roadpoint st, roadpoint en, roadpoint rp where
reg.startpoint = st.roadpoint_id   and reg.endpoint = en.roadpoint_id  
and rp.distance between st.distance and en.distance

Plan hash value: 2085902882

---------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |      1 |        |     25 |00:00:00.02 |     296 |
|   1 |  NESTED LOOPS                  |              |      1 |  25003 |     25 |00:00:00.02 |     296 |
|   2 |   NESTED LOOPS                 |              |      1 |      5 |      5 |00:00:00.01 |      31 |
|   3 |    NESTED LOOPS                |              |      1 |      5 |      5 |00:00:00.01 |      19 |
|   4 |     TABLE ACCESS FULL          | REGION       |      1 |      5 |      5 |00:00:00.01 |       7 |
|   5 |     TABLE ACCESS BY INDEX ROWID| ROADPOINT    |      5 |      1 |      5 |00:00:00.01 |      12 |
|*  6 |      INDEX UNIQUE SCAN         | ROADPOINT_PK |      5 |      1 |      5 |00:00:00.01 |       7 |
|   7 |    TABLE ACCESS BY INDEX ROWID | ROADPOINT    |      5 |      1 |      5 |00:00:00.01 |      12 |
|*  8 |     INDEX UNIQUE SCAN          | ROADPOINT_PK |      5 |      1 |      5 |00:00:00.01 |       7 |
|*  9 |   TABLE ACCESS FULL            | ROADPOINT    |      5 |   5001 |     25 |00:00:00.02 |     265 |
---------------------------------------------------------------------------------------------------------

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

  6 - access("REG"."STARTPOINT"="ST"."ROADPOINT_ID")
  8 - access("REG"."ENDPOINT"="EN"."ROADPOINT_ID")
  9 - filter(("RP"."DISTANCE">="ST"."DISTANCE" AND "RP"."DISTANCE"<="EN"."DISTANCE"))    



SQL_ID  8as0wh80pucty, child number 0
-------------------------------------
select lv region_id,roadpoint_id,roadnumber,distance  from (  select 
region_id  ,last_value(region_id ignore nulls)over(partition by 
roadnumber order by rp.distance) lv  ,last_value(region_id ignore 
nulls)over(partition by roadnumber order by rp.distance desc) fv  
,staend  ,point  ,rp.roadnumber  ,roadpoint_id  ,distance  from (  
select *  from (  select reg.region_id, st.distance startdistance, 
en.distance enddistance  from region reg, roadpoint st, roadpoint en  
where reg.startpoint = st.roadpoint_id    and reg.endpoint = 
en.roadpoint_id  )  unpivot (point for staend in (startdistance as '1', 
enddistance as '-1'))  ) re, roadpoint rp  where re.point (+)= 
rp.distance  ) qw  where lv=fv
 
Plan hash value: 1653289237
 
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |     25 |00:00:00.08 |      84 |       |       |          |
|*  1 |  VIEW                                |              |      1 |  20000 |     25 |00:00:00.08 |      84 |       |       |          |
|   2 |   WINDOW SORT                        |              |      1 |  20000 |  20000 |00:00:00.08 |      84 |   903K|   523K|  802K (0)|
|   3 |    WINDOW SORT                       |              |      1 |  20000 |  20000 |00:00:00.06 |      84 |   832K|   511K|  739K (0)|
|*  4 |     HASH JOIN RIGHT OUTER            |              |      1 |  20000 |  20000 |00:00:00.02 |      84 |   968K|   968K|  797K (0)|
|*  5 |      VIEW                            |              |      1 |     10 |     10 |00:00:00.01 |      31 |       |       |          |
|   6 |       UNPIVOT                        |              |      1 |        |     10 |00:00:00.01 |      31 |       |       |          |
|   7 |        NESTED LOOPS                  |              |      1 |        |      5 |00:00:00.01 |      31 |       |       |          |
|   8 |         NESTED LOOPS                 |              |      1 |      5 |      5 |00:00:00.01 |      26 |       |       |          |
|   9 |          NESTED LOOPS                |              |      1 |      5 |      5 |00:00:00.01 |      19 |       |       |          |
|  10 |           TABLE ACCESS FULL          | REGION       |      1 |      5 |      5 |00:00:00.01 |       7 |       |       |          |
|  11 |           TABLE ACCESS BY INDEX ROWID| ROADPOINT    |      5 |      1 |      5 |00:00:00.01 |      12 |       |       |          |
|* 12 |            INDEX UNIQUE SCAN         | ROADPOINT_PK |      5 |      1 |      5 |00:00:00.01 |       7 |       |       |          |
|* 13 |          INDEX UNIQUE SCAN           | ROADPOINT_PK |      5 |      1 |      5 |00:00:00.01 |       7 |       |       |          |
|  14 |         TABLE ACCESS BY INDEX ROWID  | ROADPOINT    |      5 |      1 |      5 |00:00:00.01 |       5 |       |       |          |
|  15 |      TABLE ACCESS FULL               | ROADPOINT    |      1 |  20000 |  20000 |00:00:00.02 |      53 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("LV"="FV")
   4 - access("unpivot_view_014"."POINT"="RP"."DISTANCE")
   5 - filter("unpivot_view_014"."POINT" IS NOT NULL)
  12 - access("REG"."STARTPOINT"="ST"."ROADPOINT_ID")
  13 - access("REG"."ENDPOINT"="EN"."ROADPOINT_ID")

And now some runstats numbers

Run1 ran in 126 hsecs
Run2 ran in 800 hsecs
run 1 ran in 15,75% of the time

Name                                  Run1        Run2        Diff
STAT...table scan blocks gotte      25,500       5,500     -20,000
STAT...no work - consistent re      25,955       5,714     -20,241
STAT...consistent gets from ca      27,661       6,221     -21,440
STAT...consistent gets              29,930       8,425     -21,505
STAT...consistent gets from ca      29,930       8,425     -21,505
STAT...session logical reads        29,990       8,459     -21,531
LATCH.cache buffers chains          57,767      15,172     -42,595
STAT...session uga memory max      123,452     410,072     286,620
STAT...session pga memory max      131,072     524,288     393,216
STAT...sorts (rows)                     33   4,000,000   3,999,967
STAT...table scan rows gotten   10,000,500   2,000,500  -8,000,000

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
60,427      20,974     -39,453    288.10%

We actually read less rows and sort them and use memory a bit more in the c_drivetheroad version. The cursor c_join seems to be faster in this case. But the note that the number of a-rows vs rows gotten are not in sync. Also logical reads are one magnitude more in c_join run.

Many ways writing a query

Iggy Fernandez is writing about SQL Which Query is Better?—Part III. I am checking here queries from the original article that he did not include in his post. And something else.

HASH JOIN plans

SELECT lname
FROM personnel
WHERE 199170 = any (
  SELECT salary 
  FROM payroll 
  WHERE personnel.empid = payroll.empid) ;

SELECT lname
FROM personnel
WHERE 199170 in ( 
  SELECT salary 
  FROM payroll 
  WHERE personnel.empid = payroll.empid) ;

SELECT lname
FROM personnel
WHERE empid = any (
  SELECT empid 
  FROM payroll
  WHERE salary = 199170);

The HASH JOIN RIGHT SEMI plan:

SELECT lname

FROM personnel

WHERE 0 < (

SELECT count(*)

FROM payroll

WHERE personnel.empid = payroll.empid AND salary = 199170);


Mr Date mentioned in EMEA Harmony 2010 about using any operator that it is behaving relationally. Even thou one should not start using it.

Two additional queries resulting HASH JOIN plan:

SELECT lname

FROM personnel, (select empid from payroll where salary = 199170) payr

WHERE personnel.empid = payr.empid;

SELECT lname

FROM personnel inner join payroll

ON personnel.empid = payroll.empid and salary = 199170;

HASH JOIN RIGHT ANTI plan also possible plan for the question here.

SELECT lname

FROM personnel

WHERE 0 = (

SELECT count(*)

FROM payroll

WHERE personnel.empid = payroll.empid

AND salary != 199170);

SQL_ID b90mkx99aux26, child number 1

-------------------------------------

SELECT lname FROM personnel WHERE 0 = (SELECT count(*) FROM payroll

WHERE personnel.empid = payroll.empid AND salary != 199170)

Plan hash value: 103534934

--------------------------------------------------------------------------------------------

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | 1004 |00:00:00.08 | 261 |

|* 1 | HASH JOIN RIGHT ANTI| | 1 | 9900 | 1004 |00:00:00.08 | 261 |

|* 2 | TABLE ACCESS FULL | PAYROLL | 1 | 8910 | 8896 |00:00:00.01 | 38 |

| 3 | TABLE ACCESS FULL | PERSONNEL | 1 | 9900 | 9900 |00:00:00.01 | 223 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("PERSONNEL"."EMPID"="PAYROLL"."EMPID")

2 - filter("SALARY"!=199170)

Without this rehersal I would not have writen anti join this way. Actually I would like to see an alternative sql resulting this plan.
The original article talked also about indexing the salary column.

create index payroll_salary_idx on payroll(salary);

With this data - no changes in the query plans. Trying a fat one

create index payroll_salary_fat_idx on payroll(salary,empid);

The only change in plans is that FULL TABLE SCAN of payroll changes to INDEX payroll_salary_fat_idx RANGE SCAN in some queries.The join method stays always the same.

Loading a collection with check option

Lets add a new requirement to inserting a collection of data. All rows inserted should be in some range. An example of such code may be found I my earlier post load using Java

We have a collection of data. At this point we do not know the values included. A new requirement should be implemented. The upper bound of values should be checked before committing the insert. Should we browse through the collection before throwing it in?

Alex Nuijten just posted Inline View Check Option. That could be used. So the inserting method might be something like

private static void insertArray(Connection c, List elems, int upperbound) 
            throws SQLException {
        ArrayDescriptor ad = ArrayDescriptor.createDescriptor("SIT", c);
        ARRAY a = new ARRAY(ad, c, elems.toArray());
        OraclePreparedStatement ops 
           = (OraclePreparedStatement)c.prepareStatement(
                        "insert into insert into " + 
                        " (select s, s2 " +
                        "    from si where s <= ? " +
                        "    with check option) " +
                        " select * " +
                        "    from table(?)");
        ops.setInt(1, upperbound);
        ops.setARRAY(2, a);

        System.out.println(ops.executeUpdate());
        ops.close();
        c.commit();
    }

If everything is ok there is no need to browse through the collection in client side. In invalid collection situation there is the round trip to the database to get the error "ORA-01402: view WITH CHECK OPTION where-clause violation". The List implementation for elems should be the place to handle such requirement. Just had to write something about a inline view with check option feature. Maybe some day there is similar pl/sql code that might benefit this.

ISO year week day to date

Trying to get date out of three of numbers. Three numbers are ISO standard year, ISO standard week and number of a day in a week. The first day of a week is Monday.

select to_date ('2010 40 4','iyyy iw d') from dual;

ORA-01820: format code cannot appear in date input format
01820. 00000 -  "format code cannot appear in date input format"

A date specification contained an invalid format code. Only the following
may be specified when entering a date: year, month, day, hours, minutes,
seconds, Julian day, A.M./P.M. and B.C./A.D.

select to_timestamp ('2010 40 4','iyyy iw d') from dual;

Surprise, no luck, the same error.

From Oracle support formely known as metalink can be found a statement that the feature has not been in such priority to be implemented. Build your own function. I am too lazy to do that. And as I know the timerange I am interested in I use brute force. Use data. We are in a database. It is built to store data. I will use that ability.

create table isoyearweekday_to_date(
isoyearweekday number(7) constraint isoyearweekday_to_date_pk primary key
, dat date not null) 
organization index;

insert into isoyearweekday_to_date 
select to_char(d,'iyyyiwd')
     , d 
  from (
   select to_date('17991231','yyyymmdd')+level d
     from dual 
   connect by level <= to_date('22000101','yyyymmdd')-to_date('18000101','yyyymmdd')+1
  )
;

select * from isoyearweekday_to_date where isoyearweekday = 2010404
;

2010404 07.10.2010

null - quilty or not

It is Friday evening. Watching a recording of Mentalist.

true - false - null
had some drinks - sober - a drink too much
innocent - pulled the trigger - too drunk to remember

Usefull indexes

Do we actually need such indexes?

select owner,table_name,index_name 
  from all_indexes 
 where distinct_keys < 2
   and num_rows > 100;

Ezconnect

Today I noticed the reason why I have not bothered to use ezconnect method with sqlplus connections.

sqlplus system/oracle@localhost/orcl

I do not like to reveal my passwords on screen if possible.

sqlplus system@localhost/orcl

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Additional quotes are needed with ezconnect, when I do not want to write the password on command line.

sqlplus system@\"localhost/orcl\"
Enter password:

or

sqlplus /nolog
SQL> conn system@"localhost/orcl"
Enter password:
Connected.

Seems like 11.2 client does not need NAMES.DIRECTORY_PATH= (ezconnect) line in sqlnet.ora anymore to use the method. 10.1, 10.2 and 11.1 version client installations need that line there.

extended audit_trail

Want to audit issued sql_text and binds in 11g database?

Documentation says that audit_trail parameter may have values

Parameter type String
Syntax AUDIT_TRAIL = { none | os | db [, extended] | xml [, extended] }

Trying

SQL> alter system set audit_trail='db, extended' scope=spfile;
alter system set audit_trail='db, extended' scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value db, extended for parameter audit_trail, must be from
among extended, xml, db_extended, false, true, none, os, db

Uups, the documented one did not work. I will try out the first suggested one.

SQL> alter system set audit_trail='extended' scope=spfile;
alter system set audit_trail='extended' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00096: invalid value EXTENDED for parameter audit_trail, must be from among
NONE | OS | DB | DB,EXTENDED | XML | XML,EXTENDED

Uups, Oracle did it again. DB,EXTENDED seems a bit closer to the one documented.

SQL> alter system set audit_trail='DB,EXTENDED' scope=spfile;
alter system set audit_trail='DB,EXTENDED' scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value DB,EXTENDED for parameter audit_trail, must be from
among extended, xml, db_extended, false, true, none, os, db

Back to the original error message. Yet another quess...

SQL> alter system set audit_trail='db_extended' scope=spfile;

System altered.

Yes I found it. After reboot sql_text and binds are collected.

Should the documentation say

Parameter type String
Syntax AUDIT_TRAIL = { none | true | false | os | db | db_extended | xml }

No. The problem is that issuing a String parameter as a quoted 'string'.

The correct way to do documented way

SQL> alter system set audit_trail=db, extended scope=spfile;

System altered.

This way also the new xml, extended is accepted, if someone likes it that way. Also some old (10.1) and alternative ways are also accepted. Although we should use only the documented ones.

SQL> alter system set audit_trail=true scope=spfile;
SQL> alter system set audit_trail=false scope=spfile;
SQL> alter system set audit_trail=db_extended scope=spfile;
SQL> alter system set audit_trail=db,extended scope=spfile;
SQL> alter system set audit_trail=db,  extended scope=spfile;
...

So minor issue, but should the ORA-00096 error messages be updated? Maybe. Compared to other product error messages the information Oracle gives in those are mostly understandable.

Anoying SQL Developer

Yet another new installation of SQL Developer. I am writing SQL. The query includes ANALYTIC functions not AGGREGATE ones. SQL Developer hits and adds some group by clause somewhere in between my clause. Getting rid of that.

Tools

Preferences...

Code editor

Completion Insight

unselect Autogenerate GROUP BY clause.

OK

I so wish this would be the default.

Lean

Just had a pleasure to participate a session with Jim Coplien. His earlier presentation about the main issues seems to be found also online. Need to listen that again later. MVC and DCI. Model and data in both the part closest to the storage seems to be the first words to name software architectures. Maybe also the Lean Architecture book to be read.

Baag

I have been involved in many situations that all come to the issue of knowing not quessing. The ideas behind BAAG should be considered in many problem solving situations. Avoid guessworking, RTFM (Read that fine manual), ...

To avoid guesworking recommended attendance Oracle User Group Finland Autumn Seminar 2010 4. November 2010 in Pörssitalo, Helsinki, Finland

locator index size

Just dealing a situation with 10.2.0.4 locator index. Size of a index had grown to 1.3G. After rebuilding it had size less than 50M.
Maybe there is going on something similar than Richard Foote is talking about bitmap indexes behaving in 9.2 and 10.2.
Trying a similar test with a locator index.

CREATE TABLE foo AS 
 SELECT mod(ROWNUM,1000)+1 ID
     , SYSDATE+mod(ROWNUM,1000)+1 fro
     , SYSDATE+mod(ROWNUM,1000)+2 til
     , 'FOO' NAME 
  FROM (SELECT NULL A FROM
(SELECT NULL A FROM dual CONNECT BY LEVEL <= 1000),
(SELECT NULL A FROM dual CONNECT BY LEVEL <= 1000));

CREATE OR REPLACE FUNCTION TF(FRO DATE, TIL DATE) RETURN SDO_GEOMETRY deterministic as 
BEGIN
RETURN MDSYS.SDO_GEOMETRY(2002,NULL, NULL, SDO_ELEM_INFO_ARRAY (1,2,1),
    SDO_ORDINATE_ARRAY(to_number(to_char(FRO,'j')),0,to_number(to_char(TIL,'j')),0));
END;
/

select min(to_number(to_char(FRO,'j'))),max(to_number(to_char(TIL,'j'))) from foo;

2455373 2456373

INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO)
  VALUES (
  'FOO',
  'RAFU.TF(FRO,TIL)',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 2455373, 2456373, 0.5),
    SDO_DIM_ELEMENT('Y', 0, 0, 0.5)
     )
  )
;

commit;

CREATE INDEX FOO_LOCATOR_IDX ON FOO(TF(FRO,TIL))
  INDEXTYPE IS mdsys.spatial_index
;

analyze index FOO_LOCATOR_IDX compute statistics;

SELECT index_name, blevel, leaf_blocks, num_rows FROM user_indexes WHERE index_name = 'FOO_LOCATOR_IDX';

"INDEX_NAME" "BLEVEL" "LEAF_BLOCKS" "NUM_ROWS"
"FOO_LOCATOR_IDX" "" "" ""

Nothing there. It is a domain index and storing its structures in a table.

select segment_name,bytes,blocks from user_segments where segment_name like 'MDRT_%';

MDRT_11976$ 92274688 11264

SELECT count(*) FROM MDRT_11976$;

33342

And now populating in a loop as Richard did with bitmap index.

CREATE TABLE bar AS 
 SELECT * FROM foo 
  WHERE 0=1
;

INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO)
  VALUES (
  'BAR',
  'RAFU.TF(FRO,TIL)',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 2455373, 2456373, 0.5),
    SDO_DIM_ELEMENT('Y', 0, 0, 0.5)
     )
  )
;

commit;

CREATE INDEX BAR_LOCATOR_IDX ON BAR(TF(FRO,TIL))
  INDEXTYPE IS mdsys.spatial_index
;

begin
 FOR i IN 1..1000 loop
  FOR j IN 1..1000 loop
   INSERT INTO bar VALUES (j, SYSDATE+j, SYSDATE+j, 'FOO');
   COMMIT;
  END loop;
 end loop;
end;
/



select segment_name,bytes,blocks from user_segments where segment_name like 'MDRT_%';

MDRT_11976$  92274688      11264
MDRT_119DF$ 125829120      15360


SELECT count(*) FROM MDRT_11976$;

33342

select count(*) from MDRT_119DF$;

58084

Well that does not explain the 1.3G size. Similar result with 11.2.0.1, 11.1.0.7 and 10.2.0.4.

Visual tuning

It was a nice opportunity to attend beta testing a tool that does something that I do with pen and paper quite often when trying to figure out query relations.

Could not attend a webinar last Thursday. Just waiting for the opportunity to see the replay.

Exclusion Constraints

In future Postgresql versions they are talking about exclusion constraints. Hitting just the thing I have been writing about in several posts about not overlapping and how to dirty hack implement it somehow with Oracle. Nice video presentation about the issue from San Francisco PostgreSQL User Group. And some pdf about the same issue.

Types of columns

Just participated C.J.Date two day seminar in 10 hours today. Thanks to the ash from Iceland. Monday was delayed.

Just a small thing picked up from the massive amount of information. To avoid type conversions behind the scenas while doing natural joins avoid using different types for columns named similarily. Just checking that

select * from (
select count(distinct data_type
                      ||'|'||cast(data_length as varchar2(30))
                      ||'|'||cast(data_precision as varchar2(30))
                      ||'|'||cast(data_scale as varchar2(30))
            ) over (partition by column_name) as dis
     , table_name
     , column_name
     , data_type
     , data_length
     , data_precision
     , data_scale 
  from user_tab_columns
) 
where dis > 1
;

Well on the other hand do not use select *. As an analogy I would suggess not to use natural joins. Mr Date suggested to use views on to of base tables. That makes sense. And a good thing here user_tab_columns has also the columns in views included.

How long will it take?

Reloading a table.

truncate table sometable;

insert /*+append*/ into sometable select ...

commit;

How long will it take?

Table is populated earlier and the space consumed will be about the same after reload.

With following technique I was able to get such an estimate after starting the insert.

Before truncate see how much space there is to be consumed.

select sum(bytes) from user_segments where segment_name = 'SOMETABLE';

4445110272

Check the execution plan of the insert statement. Ensure it will start consuming space at the beginning of execute.

Start the load. Identify the session doing the insert statement and estimate.

select ((targetb-currb)/currb)*interv+currt estimatedfinnish 
  from (
 select currt
       -(select sql_exec_start from v$session where sid = :sid)  interv
     , currb, currt
     , :b4445110272 targetb
   from (
  select systimestamp currt
       , sum(bytes) currb 
    from user_segments 
   where segment_name like 'SOMETABLE' 
));

That is giving some time in the future.

Reasons why the estimate is not correct.

1. The execution plan does not start consuming space at the beginning.
2. The space used before truncate was not the same that it will be at the end of load.
3. Indexes in the table to be populated.

If there are indexes they are builded after insert phase. Estimate was given for the table to be populated. Index building phase may be monitored by looking at V$SESSION_LONGOPS.

Not overlapping (MV approach)

Presented earlier a not overlapping function based unique indexes approach. In this post I am using a materialized view and a unique constraining that. Several commits seen in here because MV approach makes constraints kind of deferrable. The MV is refreshed at commit phase. As the dirty hack function based unique indexes on the table itself are violated straight at the insert.

DROP TABLE Z CASCADE CONSTRAINTS PURGE;

DROP TABLE YEARS CASCADE CONSTRAINTS PURGE;

DROP MATERIALIZED VIEW Z_MV;

CREATE TABLE Z(Z NUMBER(16) NOT NULL
             , VALIDFROM NUMBER(4) NOT NULL
             , VALIDTILL NUMBER(4) NOT NULL
             , CONSTRAINT FRO2000 CHECK (2000 < VALIDFROM)
             , CONSTRAINT TIL2050 CHECK (VALIDTILL <= 2050)
             , CONSTRAINT FROTIL CHECK (VALIDFROM <= VALIDTILL)
             );


CREATE TABLE YEARS AS 
SELECT 2000+LEVEL TIM FROM DUAL CONNECT BY LEVEL < (2051-2000)
;

CREATE MATERIALIZED VIEW LOG ON Z WITH ROWID
;

CREATE MATERIALIZED VIEW LOG ON YEARS WITH ROWID
;

CREATE MATERIALIZED VIEW Z_MV REFRESH FAST ON COMMIT AS
SELECT Z.ROWID ZRID,T.ROWID TRID,Z.Z,T.TIM 
  FROM Z INNER JOIN YEARS T ON VALIDFROM < T.TIM AND T.TIM <= VALIDTILL 
;

With Oracle 11.1.0.7 and 11.2.0.1 getting
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

No worries. It is about inner join syntax not so widely supported with MVs.

CREATE MATERIALIZED VIEW Z_MV REFRESH FAST ON COMMIT AS
SELECT Z.ROWID ZRID,T.ROWID TRID,Z.Z,T.TIM 
  FROM Z,YEARS T 
 WHERE VALIDFROM < T.TIM AND T.TIM <= VALIDTILL 
;

ALTER TABLE Z_MV ADD CONSTRAINT Z_MV_U UNIQUE (Z,TIM);

CREATE INDEX Z_MV_ZRID_IDX ON Z_MV(ZRID);

ZRID indexed to give the optimizer at least a possibility to do small updates "fast". More about the issue may be read from Alberto Dell'Era's Oracle blog

INSERT INTO Z VALUES(1,2001,2011);

INSERT INTO Z VALUES(1,2011,2011);

COMMIT;

INSERT INTO Z VALUES(1,2010,2012);

COMMIT;

SQL ERROR: ORA-12008: ERROR IN MATERIALIZED VIEW REFRESH PATH
ORA-00001: UNIQUE CONSTRAINT (RAFU.Z_MV_U) VIOLATED


INSERT INTO Z VALUES(2,2049,2050);

COMMIT;

INSERT INTO Z VALUES(2,2049,2050);

COMMIT;

SQL ERROR: ORA-12008: ERROR IN MATERIALIZED VIEW REFRESH PATH
ORA-00001: UNIQUE CONSTRAINT (RAFU.Z_MV_U) VIOLATED



INSERT INTO Z VALUES(2,2010,2012);

COMMIT;

INSERT INTO Z VALUES(2,2001,2049);

COMMIT;

SQL ERROR: ORA-12008: ERROR IN MATERIALIZED VIEW REFRESH PATH
ORA-00001: UNIQUE CONSTRAINT (RAFU.Z_MV_U) VIOLATED


INSERT INTO Z VALUES(2,2014,2017);

COMMIT;

SELECT * FROM Z ORDER BY Z, VALIDFROM;

1 2001 2011
1 2011 2011
2 2010 2012
2 2014 2017
2 2049 2050

SELECT COUNT(*) FROM Z_MV;

16

Pipelining

In reducing number of function calls I wrote about how to rewrite a query that is calling a function. Another approach to the issue is to alter the function. The function includes only a SQL clause. The whole result of the clause is bulk collected first and then returned. The usage of the function is in IN clause. IN predicate is satisfied if there is one equality coming out of the select. So in the best cases it is not needed to populate the whole bulk collect inside the function. Using pipelining as an alternative here.

Original bulk collect version.

SQL> create or replace type ns_typ is table of number;
  2  /

Type created.

SQL> create or replace function rn(n number) return ns_typ is
  2    ret ns_typ;
  3    begin
  4     dbms_lock.sleep(1);
  5     select level bulk collect into ret from dual connect by level <= n;
  6    return ret;
  7  end;
  8  /

Function created.

And the pipelined version of the function.

SQL> create or replace function rnpiped(n number) return ns_typ pipelined is
  2  begin
  3    for ret in
  4     (select level l from dual connect by level <= n)
  5    loop
  6     dbms_lock.sleep(1/n);
  7     pipe row (ret.l);
  8    end loop;
  9  end;
 10  /

If the whole result returned from the function is needed there is no great difference in the execution times. Actually it seems to be increasing a bit.

SQL> select * from table(rn(10));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

10 rows selected.

Elapsed: 00:00:01.00
SQL>
SQL> select * from table(rnpiped(10));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

10 rows selected.

Elapsed: 00:00:01.07

But when used in IN predicate the results with this data are even better than in the previous post.

SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:10.00
SQL>
SQL> select * from ta a where a.n in (select * from table(rnpiped(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:01.95

And putting both together.

SQL> with aa as (
  2    select *
  3      from ta a
  4    ), bb as (
  5    select distinct m
  6      from aa
  7    ), cc as (
  8    select /*+materialize*/ b.m, dd.column_value n
  9      from bb b, table(rn(b.m)) dd)
 10    select *
 11      from aa
 12     where (n,m) in (select n,m from cc)
 13  ;

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:03.09
SQL>
SQL> with aa as (
  2    select *
  3      from ta a
  4    ), bb as (
  5    select distinct m
  6      from aa
  7    ), cc as (
  8    select /*+materialize*/ b.m, dd.column_value n
  9      from bb b, table(rnpiped(b.m)) dd)
 10    select *
 11      from aa
 12     where (n,m) in (select n,m from cc)
 13  ;

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:00.68

Paging before join and aggregation

First test data.

drop table chi purge;

drop table par purge;


create table par 
( o varchar2(30)
, t varchar2(30)
, n varchar2(30)
, constraint par_pk primary key(o,t,n)) 
organization index ;

create table chi 
( o varchar2(30)
, t varchar2(30)
, n varchar2(30)
, c varchar2(30)
, constraint chi_pk primary key(o,t,n,c)
, constraint chi_par_fk foreign key(o,t,n) references par(o,t,n)) 
organization index ;

insert into par select distinct owner, object_type, object_name from dba_objects;

insert into chi(o,t,n,c) 
select distinct ta.owner, ob.object_type, ta.table_name, ta.column_name 
from all_tab_columns ta 
inner join all_objects ob 
   on ta.owner=ob.owner and TA.TABLE_NAME = ob. object_name
;

commit;

exec dbms_stats.gather_table_stats(user,'PAR');

exec dbms_stats.gather_table_stats(user,'CHI');

Query to start with and the info about execution.

SQL> SELECT n,t,o,cou
  2    FROM (  SELECT pa.o, pa.t, pa.n, COUNT ( * ) cou
  3              FROM par pa LEFT OUTER JOIN chi ch
  4                      ON pa.o = ch.o and pa.t = ch.t and pa.n = ch.n
  5          GROUP BY pa.o, pa.t, pa.n
  6          ORDER BY pa.t, pa.o, pa.n)
  7   WHERE ROWNUM < 3;

N                              T                              O                                     COU
------------------------------ ------------------------------ ------------------------------ ----------
C_COBJ#                        CLUSTER                        SYS                                     1
C_FILE#_BLOCK#                 CLUSTER                        SYS                                     3

Elapsed: 00:00:02.26

----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                  |        |      1 |        |      2 |00:00:02.19 |    2014 |
|   2 |   VIEW                          |        |      1 |    112K|      2 |00:00:02.19 |    2014 |
|*  3 |    SORT GROUP BY STOPKEY        |        |      1 |    112K|      2 |00:00:02.19 |    2014 |
|   4 |     MERGE JOIN OUTER            |        |      1 |    112K|    166K|00:00:01.93 |    2014 |
|   5 |      SORT JOIN                  |        |      1 |  64558 |  64558 |00:00:00.52 |     666 |
|   6 |       INDEX FULL SCAN DESCENDING| PAR_PK |      1 |  64558 |  64558 |00:00:00.06 |     666 |
|*  7 |      SORT JOIN                  |        |  64558 |    112K|    112K|00:00:00.87 |    1348 |
|   8 |       INDEX FULL SCAN           | CHI_PK |      1 |    112K|    112K|00:00:00.11 |    1348 |
----------------------------------------------------------------------------------------------------

The alternative to do paging first and aggregate only the rows to be retrieved.

Paging done in par inline view line 9, order by need to be done on both line 8 for paging and 14 to get the results back in order after joining and aggregating.

SQL>   SELECT pap.n,
  2           pap.t,
  3           pap.o,
  4           COUNT ( * ) cou
  5      FROM (SELECT *
  6              FROM (  SELECT pa.o, pa.t, pa.n
  7                        FROM par pa
  8                    ORDER BY pa.t, pa.o, pa.n)
  9             WHERE ROWNUM < 3
 10            ) pap
 11       LEFT OUTER JOIN chi ch
 12         ON pap.o = ch.o AND pap.t = ch.t AND pap.n = ch.n
 13  GROUP BY pap.o, pap.t, pap.n
 14  ORDER BY pap.t, pap.o, pap.n;

N                              T                              O                                     COU
------------------------------ ------------------------------ ------------------------------ ----------
C_COBJ#                        CLUSTER                        SYS                                     1
C_FILE#_BLOCK#                 CLUSTER                        SYS                                     3

Elapsed: 00:00:00.20

-----------------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  SORT GROUP BY NOSORT      |        |      1 |      2 |      2 |00:00:00.20 |     673 |
|   2 |   NESTED LOOPS OUTER       |        |      1 |      2 |      4 |00:00:00.20 |     673 |
|   3 |    VIEW                    |        |      1 |      2 |      2 |00:00:00.20 |     666 |
|*  4 |     COUNT STOPKEY          |        |      1 |        |      2 |00:00:00.20 |     666 |
|   5 |      VIEW                  |        |      1 |  64558 |      2 |00:00:00.20 |     666 |
|*  6 |       SORT ORDER BY STOPKEY|        |      1 |  64558 |      2 |00:00:00.20 |     666 |
|   7 |        INDEX FULL SCAN     | PAR_PK |      1 |  64558 |  64558 |00:00:00.06 |     666 |
|*  8 |    INDEX RANGE SCAN        | CHI_PK |      2 |      1 |      4 |00:00:00.01 |       7 |
-----------------------------------------------------------------------------------------------

Notice cou column cannot be in order by condition.
Join needs to be outer.

Reducing the number of function calls

Using a slow function call in your query? Maybe you are calling it unnecessarily.

SQL> create or replace type ns_typ is table of number;
  2  /

Type created.

SQL> create or replace function rn(n number) return ns_typ is
  2  ret ns_typ;
  3  begin
  4   dbms_lock.sleep(1);
  5   select level bulk collect into ret from dual connect by level <= n;
  6  return ret;
  7  end;
  8  /

Function created.

SQL>
SQL> create table ta as select level n, mod(level,3)+1 m from dual connect by level <= 10;

Table created.

SQL> select * from ta;

         N          M
---------- ----------
         1          2
         2          3
         3          1
         4          2
         5          3
         6          1
         7          2
         8          3
         9          1
        10          2

10 rows selected.

SQL> set timi on

SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:10.01

The query is calling rn function for each ten rows of ta table. Each call takes one second as the function is using dbms_lock. There are only three distinct values that the function is needed to be called.

SQL> with aa as (
  2  select *
  3    from ta a
  4  ), bb as (
  5  select distinct m
  6    from aa
  7  ), cc as (
  8  select /*+materialize*/ b.m, dd.column_value n
  9    from bb b, table(rn(b.m)) dd)
 10  select *
 11    from aa
 12   where (n,m) in (select n,m from cc)
 13  ;

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:03.03

Alternatively you might consider using result cache for the function.

SQL> create or replace function rn(n number) return ns_typ result_cache is
  2  ret ns_typ;
  3  begin
  4   dbms_lock.sleep(1);
  5   select level bulk collect into ret from dual connect by level <= n;
  6  return ret;
  7  end;
  8  /

Function created.

Elapsed: 00:00:00.04
SQL>
SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:03.01
SQL>
SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

         N          M
---------- ----------
         1          2
         2          3

Elapsed: 00:00:00.00

Cleanup

SQL> drop table ta purge;
SQL> drop function rn;
SQL> drop type ns_typ;

Packing rows

So worth tasting. Having a Keisari Strong Munchener 5,7% brewed by Nokian panimo. Figuring out a thing done today.

The case was that we have a relation that has the only one over time feature present. There is the possibility that the same attribute is the same in several continuous rows. And we want to put those rows together in a query result. So the question here was how to implement pack operator presented in C.J. Date book Temporal data and the relational model. Some good alternatives are presented in Joe Celko's thinking in sets: auxiliary, temporal, and virtual tables in SQL. He presents a OLAP Function Solution. Here we have the not overlapping present so the question is a bit simpler. And to a example...

CREATE TABLE Z(Z NUMBER(16) NOT NULL
             , VALIDFROM NUMBER(4) NOT NULL
             , VALIDTILL NUMBER(4) NOT NULL
);

INSERT INTO Z VALUES (1,2000,2003);

INSERT INTO Z VALUES (1,2003,2004);

INSERT INTO Z VALUES (2,2004,2005);

INSERT INTO Z VALUES (2,2005,2008);

INSERT INTO Z VALUES (1,2008,2010);

SELECT Z, MINVALIDFROM, MAX(VALIDTILL) MAXVALIDTILL
  FROM (
  SELECT Z, VALIDFROM,VALIDTILL,PREVTILL
       , MAX(CASE WHEN VALIDFROM <= PREVTILL
                  THEN NULL
                  ELSE VALIDFROM
              END) 
         OVER ( PARTITION BY Z ORDER BY VALIDFROM, VALIDTILL
                ROWS UNBOUNDED PRECEDING) MINVALIDFROM
    FROM (
    SELECT Z
         , VALIDFROM,VALIDTILL
         , LAG(VALIDTILL) 
           OVER (PARTITION BY Z ORDER BY VALIDTILL) PREVTILL
      FROM Z
   ) 
)
GROUP BY Z, MINVALIDFROM
ORDER BY MINVALIDFROM, Z
;

1 2000 2004
2 2004 2008
1 2008 2010

The simpler part is to use LAG.

Datafile HWM

Cleaning up "joins gone wild" application bug. First cleaned up a mess that consumed a lot of space in tablespaces. After that had to move objects with high block_id values so the datafile size may be redused. To avoid hitting the error ORA-03297: file contains used data beyond requested RESIZE value. Alter table move may be used only the objects are not used. If you need to do this as an online operation use DBMS_REDEFINITION.

Generators I used for tablespaces containing only tables.

SELECT file_id,'alter table '||owner||'.'||segment_name||' move'||
   case when segment_type='TABLE PARTITION' then ' PARTITION '||partition_name
        when segment_type='TABLE SUBPARTITION' then ' SUBPARTITION '||partition_name 
    end ||';' sq, 
MAX(BLOCK_ID) max_block_id,
SEGMENT_NAME, partition_name, SEGMENT_TYPE
  FROM DBA_EXTENTS
 WHERE FILE_ID in (4,5,6,7,8)
 group by file_id,owner,SEGMENT_NAME, partition_name, SEGMENT_TYPE
 having MAX(BLOCK_ID) > 100000
 order by file_id,max_block_id desc
;

select 'alter database datafile '||file_id||' resize '||(ceil(max(block_id)*db_block_size/1024/1024/1024))||'G;'
  from dba_extents, (
    select value db_block_size 
      from v$parameter 
     where name='db_block_size' 
     )
 where FILE_ID in (4,5,6,7,8)
 group by db_block_size,file_id
 order by 1
;

Indexes went broken.

select 'alter index '||owner||'.'||index_name||' rebuild;' from all_indexes where status = 'UNUSABLE' order by 1;

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||';' from all_ind_partitions where status = 'UNUSABLE' order by 1;

select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||partition_name||';' from all_ind_subpartitions where status = 'UNUSABLE' order by 1;

Tablespaces including indexes.

SELECT distinct 'alter index '||owner||'.'||segment_name||' rebuild'||
   case when segment_type='INDEX PARTITION' then ' PARTITION '||partition_name
        when segment_type='INDEX SUBPARTITION' then ' SUBPARTITION '||partition_name 
    end ||';' sq, 
MAX(BLOCK_ID) max_block_id
  FROM DBA_EXTENTS
 WHERE FILE_ID in (9,10,11,12,13)
 group by file_id,owner,SEGMENT_NAME, partition_name, SEGMENT_TYPE
 having MAX(BLOCK_ID) > 100000
 order by max_block_id desc
;

And after that datafile resize.

Anoying Toad

Two anoying features of Toad. Use SQLPLUS or SQL Developer instead.

create table pa(p number constraint pa_pk primary key) 
partition by hash(p) 
partitions 2;

select partition_name from user_tab_partitions where table_name = 'PA';

SYS_P61
SYS_P62

Truncating a partition with Toad gives an error.

alter table pa truncate partition SYS_P61;

ORA-14006: invalid partition name

Modifying index visibility with Toad gives an error.

alter index pa_pk invisible;

ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations

alter index pa_pk visible;

ORA-14141: ALTER INDEX VISIBLE|INVISIBLE may not be combined with other operations

Just get rid of the semi colon and you are able to use these commands with Toad.

alter index pa_pk visible

A dark side of Easter

It was an Easter a while ago. I had a bottle of Finnish porter beer. It was made by Laitilan breweries. My mother in law got some of it and made it clear that it tasted like mämmi. She put some vanilla sauce in the class and the taste was, well similar like mämmi with vanilla sauce. This year Laitilan breweries has a bottle that has a yellow lable saying Mämmi on it. They have actually made beer out of mämmi. Well if you do not know what mämmi is take alook of something else from Finnish kitchen called Kalakukko. They make that mainly in Kuopio. I had an exiting evening in Hakametsä. Ice hockey arena in Tampere. My favourite ice hockey team Tappara just won Kalpa - a team from Kuopio.

Putting things together that do not actually have nothing to do with each other make sometimes wheels running. Ice hockey and mämmi have nothing in common like vanilla sauce and Kuopio. During last year I have had several occasions when the ideas from spatial advisor have came to the rescue. There have been several cases when I have been dealing with query performance problems with temporal data. Well spatial information is more close to temporal than Kalpa is to Laitila. These ideas have come to the rescue several times when the number count in tables have increased from a million to ten or hundred million. Changing a traditional b-tree index to a function based locator index and a query where predicate to use that has helped the response time to be usable. Joining similar table with greater than or between comparison has also been a troublesome to deal with. Using those function based locator indexes and sdo_join have helped a lot.

Lets have a simple example not from those real life experiences like I did not reveal earlier. Lets copy the base situation from sum over time writing. Notable here is that the locator indexing is available also in free XE version and also SE and EE. The basic idea for function based locator index may be expressed like

DROP TABLE T CASCADE CONSTRAINTS PURGE;

DROP FUNCTION TF;


CREATE TABLE T (FRO DATE, TIL DATE, N NUMBER);

INSERT INTO T VALUES (TO_DATE('09.04.2009','dd.mm.yyyy'), TO_DATE('10.04.2009','dd.mm.yyyy'), 1);

INSERT INTO T VALUES (TO_DATE('10.04.2009','dd.mm.yyyy'), TO_DATE('12.04.2009','dd.mm.yyyy'), 2);

INSERT INTO T VALUES (TO_DATE('11.04.2009','dd.mm.yyyy'), TO_DATE('14.04.2009','dd.mm.yyyy'), 3);

COMMIT;

Query performing not with satisfactory response time.

SELECT * 
  FROM T 
 WHERE TIL >= TO_DATE('11.04.2009','dd.mm.yyyy') 
   AND FRO <= TO_DATE('14.04.2009','dd.mm.yyyy')
;

10.04.2009 12.04.2009 2
11.04.2009 14.04.2009 3

CREATE OR REPLACE FUNCTION TF(FRO DATE, TIL DATE) RETURN SDO_GEOMETRY deterministic as 
BEGIN
RETURN MDSYS.SDO_GEOMETRY(2002,NULL, NULL, SDO_ELEM_INFO_ARRAY (1,2,1),
    SDO_ORDINATE_ARRAY(to_number(to_char(FRO,'j')),0,to_number(to_char(TIL,'j')),0));
END;
/

SELECT TO_CHAR(to_date('19000101','yyyymmdd'),'J'),TO_CHAR(to_date('22000101','yyyymmdd'),'J') FROM dual;

--2415021 2524594

DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'T';

INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO)
  VALUES (
  'T',
  'RAFU.TF(FRO,TIL)',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('X', 2415021, 2524594, 0.5),
    SDO_DIM_ELEMENT('Y', 0, 0, 0.5)
     )
  )
;

COMMIT;

CREATE INDEX T_LOCATOR_IDX ON T(TF(FRO,TIL))
  INDEXTYPE IS mdsys.spatial_index;

SELECT * 
  FROM T 
 WHERE SDO_FILTER(TF(FRO,TIL),
                  TF(TO_DATE('11.04.2009','dd.mm.yyyy')
                    ,TO_DATE('14.04.2009','dd.mm.yyyy'))
                 ) = 'TRUE'
;

10.04.2009 12.04.2009 2
11.04.2009 14.04.2009 3

Just try with your own temporal data and PlanViz and compare the a-rows inside your query.

An another thing that I sayed in sum over time writing was "I like to see continous pair stored as open ended". It is just one thing to deal with this approach. SQL standard by the way says that the end in time interval should not be included in the interval. But if the end is the same than the start moment then the interval is presenting the one spot in the timeline. So in that case the end is not excluding.

Yet another thing about timelines and my earlier postings. Last year I wrote about some ideas about not overlapping daily. The idea presented in this post might near to the answer missing in that post. But it is not possible to create a domain index as a unique.

By the way Mämmi beer with vanilla sauce got called today white russian by my mother in law. Wonder what might Dude in Big Lebowski say about that.

Visualizing plans grants

A while ago I post about to use plan visualization by Tanel Poder. That has been a tool worth using. Thou that should be a tool also to other developers than DBAs. A-Rows and A-Time are such valuable information about the execution bottlenecs inside SQL execution.

How to get it working without dba rights.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

User has no SELECT privilege on V$SESSION

It is not possible to grant select rights straight to v$ views as Coscan has writen.

grant select on v_$session to rafu;

And a try

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

User has no SELECT privilege on V$SQL_PLAN_STATISTICS_ALL

Giving a grant

grant select on v_$sql_plan_statistics_all to rafu;

And after that no success. The same output.
11.2 documentation says that to use DBMS_XPLAN.DISPLAY_CURSOR the calling user must have SELECT privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN views.

grant select on v_$sql_plan to rafu;

grant select on v_$sql to rafu;

And we are there. User rafu may use DBMS_XPLAN.DISPLAY_CURSOR with sql_id=>null parameter. Also grant alter session to avoid the need to use gather_plan_statistics hint might be recommended. That is how you enable ALTER SESSION SET statistics_level = ALL;

grant alter session to rafu;

Also giving these grants through a role to developer users is worth considering.

Pivoting EAV

If you are responsible for designing a data model and just consider to invent again and create this fine generic entity attribute value structure, maybe you should consider attending some teaching about the issue. For example some available soon by C.J. Date in and near Finland.

Well maybe you have a EAV model that you have to deal with. Example

SQL> create table eav as
  2  select 1 e, 'first' a, 'Timo' v from dual union all
  3  select 1 e, 'last' a, 'Raitalaakso' v  from dual union all
  4  select 1 e, 'nic' a, 'Rafu' v  from dual union all
  5  select 2 e, 'first' a, 'John' v  from dual union all
  6  select 2 e, 'last' a, 'Doe' v from dual
  7  ;

Table created.

SQL> select * from eav;

         E A     V
---------- ----- -----------
         1 first Timo
         1 last  Raitalaakso
         1 nic   Rafu
         2 first John
         2 last  Doe

You should not query it in a basic case using joins. Most possibly you have tens of joins to the same table.

SQL> select la.e, fi.v firs, la.v las
  2    from eav la, eav fi
  3   where la.e=fi.e
  4     and fi.a='first'
  5     and la.a='last'
  6  ;

         E FIRS        LAS
---------- ----------- -----------
         1 Timo        Raitalaakso
         2 John        Doe

It is a pivot you want to do.

SQL> select e, firs, las
  2    from eav
  3    pivot (max(v) for a in ('first' as firs, 'last' as las))
  4  ;

         E FIRS        LAS
---------- ----------- -----------
         1 Timo        Raitalaakso
         2 John        Doe

With the pivot you get the nullable columns also easier without filtering out the whole entity

SQL> select e, firs, las, ni
  2    from eav
  3    pivot (max(v) for a in ('first' as firs, 'last' as las, 'nic' as ni))
  4  ;

         E FIRS        LAS         NI
---------- ----------- ----------- -----------
         1 Timo        Raitalaakso Rafu
         2 John        Doe

Maybe you do not have 11g features available.

SQL> select e
  2       , max(case when a = 'first' then v end) firs
  3       , max(case when a = 'last' then v end) las
  4    from eav
  5   group by e
  6  ;

         E FIRS        LAS
---------- ----------- -----------
         1 Timo        Raitalaakso
         2 John        Doe

And the best thing to do with it might be.

SQL> drop table eav purge;

Table dropped.

Parallel query distribution methods

Figuring out should I recommend parallelizing materialized view refresh for several mvs or use parallel partition wise joins. Found valuable help for the issue just yesterday by Tony Hasler. Will be using both. Unfortunately no blog writing coming up about the issue.

not in null countdown

Just a reminder about not in and nulls. Maybe consider using not exists or anti join if any of the columns in not in list may be null. Or maybe one of the following might be the result you want.

SQL> create table nm as
  2  with le as (select level ev from dual connect by level<4)
  3  select l.ev e,e.ev v from le l, le e;

Table created.

SQL> select count(*) from nm;

  COUNT(*)
----------
         9

SQL> select count(*) from nm where (e,v) not in ((1,1));

  COUNT(*)
----------
         8

SQL> select count(*) from nm where (e,v) not in ((1,1),(2,2));

  COUNT(*)
----------
         7

SQL> select count(*) from nm where (e,v) not in ((1,null));

  COUNT(*)
----------
         6

SQL> select count(*) from nm where (e,v) not in ((1,1),(2,null));

  COUNT(*)
----------
         5

SQL> select count(*) from nm where (e,v) not in ((null,1),(2,null));

  COUNT(*)
----------
         4

SQL> select count(*) from nm where (e,v) not in ((1,null),(2,null));

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

SQL> select count(*) from nm where (e,v) not in ((1,null),(2,null),(3,3));

  COUNT(*)
----------
         2

SQL> select count(*) from nm where (e,v) not in ((null,1),(null,2),(1,3),(2,3));

  COUNT(*)
----------
         1

SQL> select count(*) from nm where (e,v) not in ((null,null),(1,1));

  COUNT(*)
----------
         0

SQL> select count(*)
  2    from nm m
  3   where not exists (select n
  4                       from (select null n from dual) d
  5                      where m.v = d.n
  6                        and m.e = d.n);

  COUNT(*)
----------
         9

SQL> select count(*)
  2    from nm m
  3     left outer join (select 10 e, null n from dual) d
  4       on m.e=d.e
  5  where d.n is null;

  COUNT(*)
----------
         9

Data types in a view

Is there actually a TIME data type in Oracle? What is the data type of null? Boolean is a number or is it?

SQL> create view v as
  2  select null nul
  3       , time '12:00:00' tim
  4       , dbms_session.is_role_enabled('CREATE SESSION') boo
  5   from dual
  6  ;

View created.

SQL>
SQL> select column_name,data_type,data_length
  2    from user_tab_columns
  3   where table_name = 'V'
  4   order by 1
  5  ;

COLUMN_NAME  DATA_TYPE    DATA_LENGTH
------------ ------------ -----------
BOO          NUMBER                22
NUL          VARCHAR2               0
TIM          TIME(9)               20

SQL>
SQL> select nul from v;

N
-


SQL>
SQL> select tim from v;

TIM
--------------------------------------------------------------
12:00:00,000000000

SQL>
SQL> select boo from v;
select boo from v
                *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

null and time types from Laurent Schneider.

Open position

It has been a while since Pirjo Salo wrote about job opportunities for database specialist in Finland. Be aware something to be available soon.

Equality -comparing text

Should we use case clause or still use decode? Yet again someting to be aware. Seems like case clause is trimming before comparing. And the same with DB2 minus. Postgres does not trim.

Oracle

SQL> select case when 'a' = 'a ' then 'same' else 'different' end as test from dual;
 
TEST
---------
same
 
SQL> select 'a' from dual minus select 'a ' from dual;
 
'A
--
a

SQL> select decode('a','a ','same','different') testdecode from dual;

TESTDECOD
---------
different

DB2

db2 => select case when 'a' = 'a ' then 'same' else 'different' end as test from sysibm.sysdummy1
 
TEST
---------
same
 
 1 record(s) selected.
 
db2 => select 'a' from sysibm.sysdummy1 minus select 'a ' from sysibm.sysdummy1
 
1
--
 
 0 record(s) selected.

Postgres

postgres=# select case when 'a' = 'a ' then 'same' else 'different' end as test ;
   test
-----------
 different
(1 row)

postgres=# select 'a' as a except select 'a ' as a;
 a
---
 a
(1 row)

SQL Server

1> select case when 'a' = 'a ' then 'same' else 'different' end as test ;
2> go
test
----
same

(1 rows affected)
1>
2> select 'a'
3> except
4> select 'a '
5> go

--

(0 rows affected)

select into

How come lines 9 and 10 do not execute in the following is_active function? Documentation says that use into_clause only when there is one row in the result. Should the remaining code be ignored when there are no rows. Be sure to have exactly that one row available when using select into structure. Count(*) is a way here to be sure there is the one row.

SQL> CREATE OR REPLACE function is_active(i_is_active number)
 2      return number as
 3      ret number := 0;
 4  begin
 5      dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
 6      select 1 into ret
 7        from dual
 8       where i_is_active = 1;
 9      dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
10      if ret is null then ret := 0;
11      end if;
12      return ret;
13  end;
14  /

Function created.

SQL>
SQL> select is_active(1),is_active(0) from dual
 2  ;

IS_ACTIVE(1) IS_ACTIVE(0)
------------ ------------
          1

-0-before-1-
-1-after -1-
-0-before-0-
SQL>
SQL>
SQL> CREATE OR REPLACE function is_active_usingcount(i_is_active number)
 2      return number as
 3      ret number := 0;
 4  begin
 5      dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
 6      select count(*) into ret
 7        from dual
 8       where i_is_active = 1;
 9      dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
10      return ret;
11  end;
12  /

Function created.

SQL>
SQL> select is_active_usingcount(1),is_active_usingcount(0) from dual
 2  ;

IS_ACTIVE_USINGCOUNT(1) IS_ACTIVE_USINGCOUNT(0)
----------------------- -----------------------
                     1                       0

-0-before-1-
-1-after -1-
-0-before-0-
-0-after -0-

You get "ORA-01422: exact fetch returns more than requested number of rows" if there are more than one row. Should there be an exception also to the case when there are no rows?

Update 4.11.2010:
Well there is a exception thrown. Just not catching it anywhere.

CREATE OR REPLACE function is_active(i_is_active number)
       return number as
       ret number := 0;
  begin
       dbms_output.put_line('-'||ret||'-before-'||i_is_active||'-');
       select 1 into ret
       from dual
      where i_is_active = 1;
     dbms_output.put_line('-'||ret||'-after -'||i_is_active||'-');
     if ret is null then ret := 0;
     end if;
     return ret;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN return -1;
    end;
/

select is_active(1),is_active(0) from dual;

IS_ACTIVE(1) IS_ACTIVE(0)
------------ ------------
1            -1

Only one (yet again)

Is that not a normal DBA work week.
Installing Oracle on HP-UX cluster
Trying to resolve problematic CPU usage -Solaris
Rewriting loooong query more readable
Fiquring out 9.2 RAC
Talking about a PL/SQL function having comments from the 90's
Talking about modeling tools
Partitioning postgresql
A very good chinese dinner at Dong Bei Hu
Some dark beer
Night in a hotel
Reading awr reports and creating some of my own
Making ssh connections to a 10.2 RAC environment
Installing 11.2
Dumping a datafile block, boy that wait information in v$session mislead me. Should have used snapper. Well the blocks were the same both in primary and standby
Rewriting that loooong query so that it will perform when there will be 10M rows in the future
Sitting in a company meeting and hearing that Solita is recruiting new people


Luckily some actual modeling work also. How to constrain a model where there is a variation of the only one problem. The difference here was that a country does not have to have a default currency. But if it has any currencies there has to be one as a default. And also a restriction that no virtual columns. Oracle version 10.2. In the following example currencies goes to A table and countries to D.

drop  materialized view da_atleast_one_mv;

drop table a cascade constraints purge;

drop table d cascade constraints purge;


create table d(d_id number primary key, current_a_id number)
;

create table a(a_id number primary key
             , d_id references d not null
             , constraint a_u unique(d_id,a_id))
;

alter table d add constraint d_curr_fk foreign key (d_id,current_a_id) references a(d_id,a_id) deferrable initially deferred;


create materialized view log on d with rowid;

create materialized view log on a with rowid;

create materialized view da_atleast_one_mv 
refresh on commit as 
select d.rowid drid, a.rowid arid, d.d_id, a.a_id
  from d, a
 where d.current_a_id(+)=a.a_id
;

alter table da_atleast_one_mv modify d_id constraint da_atleast_one_nn not null;

insert into d values (0,null);

commit;

--now we have a country without any currencies

insert into d values (1,null);

insert into a values (1,1);

commit;
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into ("RAFU"."DA_ATLEAST_ONE_MV"."D_ID")

insert into d values (2,2);

insert into a values (2,2);

commit;

select * from da_atleast_one_mv;

update d set current_a_id=null where d_id = 2;

commit;
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into ("RAFU"."DA_ATLEAST_ONE_MV"."D_ID")

Potential not nulls

Something to send to a development team of the model or just run the clauses from these generators. Here are ways to figure out potential missing not null constraints.

First a test table

create table nnc(n number, m number);

insert into nnc values (1,2);

commit;

By counting the actual rows

select 'select ''--alter table '||table_name||' modify '||column_name||' not null;'' c from ( select count(*) from '||table_name||' having count(*)>0 and count(*) = sum(case when '||column_name||' is not null then 1 end));' 
  from user_tab_columns 
 where nullable='Y'
 order by table_name,column_name
;

select '--alter table NNC modify M not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when M is not null then 1 end));

select '--alter table NNC modify N not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when N is not null then 1 end));

--alter table NNC modify M not null;

--alter table NNC modify N not null;

Or another way by using stats

exec dbms_stats.gather_table_stats(user,'NNC');

select t.table_name, c.column_name, t.num_rows 
  from user_tab_cols c 
 inner join user_tables t 
    on t.table_name=c.table_name
 where c.nullable='Y'
   and t.temporary='N'
   and num_rows > 0
   and num_nulls = 0
 order by t.table_name,c.column_name
;


select '--alter table '||t.table_name||' modify '||c.column_name||' not null;'
  from user_tab_cols c 
 inner join user_tables t 
    on t.table_name=c.table_name
 where c.nullable='Y'
   and t.temporary='N'
   and num_rows > 0
   and num_nulls = 0
 order by t.table_name,c.column_name
;

And the thirdth option putting those together. Candidates from stats and after that checking the actual data.

select 'select ''--alter table '||table_name||' modify '||column_name||' not null;'' c from ( select count(*) from '||table_name||' having count(*)>0 and count(*) = sum(case when '||column_name||' is not null then 1 end));' 
  from user_tab_columns 
 where nullable='Y'
   and table_name in (
    select t.table_name
      from user_tab_cols c 
     inner join user_tables t 
        on t.table_name=c.table_name
     where c.nullable='Y'
       and t.temporary='N'
       and num_rows > 0
       and num_nulls = 0
   )
 order by table_name,column_name
;

select '--alter table NNC modify M not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when M is not null then 1 end));

select '--alter table NNC modify N not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when N is not null then 1 end));

--alter table NNC modify M not null;

--alter table NNC modify N not null;

Well maybe the stats version is the one to use.

I prefer having names also to the not null constraints. So after dealing with the developer

alter table NNC modify M constraint nnc_m_nn not null;

Visualizing plans

http://tech.e2sn.com/apps/planviz seems something that will help me tomorrow.

Rely constraint validated mess

Not so happy with the html support pages. Could not create a SR. Had to go back to flash pages.

Today's issue is about a constraint that is in validated state. Oracle documentation says that if you use rely constraints, you should know what you are doing. Maybe you did not know and want to go back to norely mode. Here is an example what you should not do.

SQL> create table pa (pa_id number constraint pa_pk primary key);

Table created.

SQL>
SQL> insert into pa
  2  select level pa_id from dual connect by level < 3;

2 rows created.

SQL>
SQL> create table ch(ch_id number constraint ch_pk primary key
  2                , pa_id not null constraint ch_pa_fk references pa
  3                )
  4  ;

Table created.

SQL>
SQL> insert into ch select level ch_id, level pa_id from dual connect by level < 3;

2 rows created.

SQL>
SQL> alter table ch modify constraint ch_pa_fk disable novalidate;

Table altered.

SQL>
SQL> alter table ch modify constraint ch_pa_fk rely;

Table altered.

SQL>
SQL> select status,validated,rely from user_constraints where constraint_name = 'CH_PA_FK';

STATUS   VALIDATED     RELY
-------- ------------- ----
DISABLED NOT VALIDATED RELY

SQL>
SQL> insert into ch select level+10 ch_id, level+10 pa_id from dual connect by level < 3;

2 rows created.

SQL> select * from pa;

     PA_ID
----------
         1
         2

SQL> select * from ch;

     CH_ID      PA_ID
---------- ----------
         1          1
         2          2
        11         11
        12         12

SQL>
SQL> alter table ch modify constraint ch_pa_fk enable;

Table altered.

SQL>
SQL> --Why was that possible?
SQL>
SQL> alter table ch modify constraint ch_pa_fk validate;

Table altered.

SQL>
SQL> --Why was that possible?
SQL>
SQL> alter table ch modify constraint ch_pa_fk norely;

Table altered.

SQL>
SQL> alter table ch modify constraint ch_pa_fk enable validate;

Table altered.

SQL>
SQL> select status,validated,rely from user_constraints where constraint_name = 'CH_PA_FK';

STATUS   VALIDATED     RELY
-------- ------------- ----
ENABLED  VALIDATED

SQL>
SQL> select *
  2    from ch c
  3    where not exists (select null from pa p where p.pa_id = c.pa_id)
  4  ;

no rows selected

SQL>
SQL> alter table ch modify constraint ch_pa_fk disable novalidate;

Table altered.

SQL>
SQL> select *
  2    from ch c
  3    where not exists (select null from pa p where p.pa_id = c.pa_id)
  4  ;

     CH_ID      PA_ID
---------- ----------
        11         11
        12         12

SQL> alter table ch modify constraint ch_pa_fk enable validate;
alter table ch modify constraint ch_pa_fk enable validate
                                 *
ERROR at line 1:
ORA-02298: cannot validate (SYSTEM.CH_PA_FK) - parent keys not found

Going back from rely mode to norely. Go first to norely and validate after.

New year even happier

After migrating to 11.2 flash really has been driving me nuts. Mainly the migration from 11.1 gave us improved performance. But being a SR generator has made me feel not so warm thoughts for flash interface. Not yet faced a problem that we have not been able to overcome. Hopefully supporthtml.oracle.com makes my life happier. Thank you Laurent Schneider.