2012-04-04

Miss leading ORA-44425

Error message for ORA-44425 is miss leading.

ORA-44425: XML DB needs to be installed for creating an XMLType table or column
Cause: An attempt was made to create an XMLType table or column with out XML DB installed.

The correct information in the message is that you do not have XML DB installed in your database. The fact is that you are trying to store your XMLType column as BINARY XML. You actually do not have to have XML DB installed to use XMLType column. SQL Developer generates binary option to DDL for a XMLType column by default.

CREATE TABLE TABLE_1 
    ( 
     Column_1 XMLTYPE 
    ) 
    XMLTYPE COLUMN Column_1 STORE AS BINARY XML 
;
You have the option to STORE as CLOB.

CREATE TABLE TABLE_1 
    ( 
     Column_1 XMLTYPE 
    ) 
    XMLTYPE COLUMN Column_1 STORE AS CLOB XML 
;


If you need BINARY stored XML install XML DB option to your database as the error message states. Otherwise you have the option to change the store method to database default CLOB. This may be found from Relational Models - Physical Models - Tables - TABLE_1 - Columns - Column_1 - Right click mouse - Properties - Store as - CLOB

There is also option of STORE AS OBJECT RELATIONAL. Then you need to deal with ORA-19002: Missing XMLSchema URL. The same properties page has XML Schema URL and XML Schema Elem properties to be filled. Once filled the generated DDL throws an error ORA-31159: XML DB is in an invalid state. Maybe this error should be the same as ORA-44425.

The latest 11.2.0.3 create table XMLType_column_properties documentation instructs not to use CLOB storage as it might be deprecated in a future release.

Maybe it would be wise just to install the XML DB if a XMLType column is used. There is many kinds of XML out there. Unfortunately so often no knowledge of schema used. In this case maybe CLOB store is ok. No need to install XML DB and make Oracle patching take longer.

2012-04-02

Keep first

Green is good. In database console it is saying that the database is doing work with CPU. It is not waiting. It might be thou that the end user is waiting for the query to finnish. These problems are not to be dealt with buying more CPU. Maybe faster CPU. But more performance may be gained by not doing stuff.

drop table a cascade constraints;

create table a as 
select mod(level,5) b,level c,'foo'||level d from dual connect by level < 100000;

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

select * from a where rownum <= 10;

1    1    foo1
2    2    foo2
3    3    foo3
4    4    foo4
0    5    foo5
1    6    foo6
2    7    foo7
3    8    foo8
4    9    foo9
0    10    foo10

A simple aggregation from the data:

select b,min(c) c
  from a
 group by b
;

Plan
SELECT STATEMENT  ALL_ROWSCost: 92  Bytes: 40  Cardinality: 5          
    2 HASH GROUP BY  Cost: 92  Bytes: 40  Cardinality: 5      
        1 TABLE ACCESS FULL TABLE RAFU.A Cost: 88  Bytes: 799.992  Cardinality: 99.999  

1    1
2    2
4    4
3    3
0    5

But when we want to have also d columns included in the result of aggregation we need to write sql a bit differently.

0    5    foo5
1    1    foo1
2    2    foo2
3    3    foo3
4    4    foo4

Here is a analytic approach to get the desired rows.
select b,c,d 
  from (
 select b,c,d, row_number()over(partition by b order by c) rn
   from a
)
where rn=1
;

Plan
SELECT STATEMENT  ALL_ROWSCost: 643  Bytes: 12.699.873  Cardinality: 99.999              
    3 VIEW RAFU. Filter Predicates: "RN"=1  Cost: 643  Bytes: 12.699.873  Cardinality: 99.999          
        2 WINDOW SORT PUSHED RANK  Filter Predicates: ROW_NUMBER() OVER ( PARTITION BY "B" ORDER BY "C")<=1  Cost: 643  Bytes: 1.699.983  Cardinality: 99.999      
            1 TABLE ACCESS FULL TABLE RAFU.A Cost: 88  Bytes: 1.699.983  Cardinality: 99.999  

;
The analytic way is sorting all rows and restricting rows after. How about collecting the needed information while aggregating. An alternative using keep and first reserved words in SQL.
select b,min(c) c,min(d) keep (dense_rank first order by c) dd
  from a
 group by b
;

Plan
SELECT STATEMENT  ALL_ROWSCost: 93  Bytes: 85  Cardinality: 5          
    2 SORT GROUP BY  Cost: 93  Bytes: 85  Cardinality: 5      
        1 TABLE ACCESS FULL TABLE RAFU.A Cost: 88  Bytes: 1.699.983  Cardinality: 99.999  
Plans are taken from Toad. The cardinality information there is more accurate in the aggregate version. This may have huge influence if such code is a part of a bigger query. Comparing analytic to aggregate keep first approach we get 77% improvement in execution time. It is all CPU time. Using Tom Kyte runstats.

DECLARE
  CURSOR c_rn
  IS
select b,c,d 
  from (
 select b,c,d, row_number()over(partition by b order by c) rn
   from a
)
where rn=1
;
--
  CURSOR c_keepfirst
  IS
select b,min(c) c,min(d) keep (dense_rank first order by c) dd
  from a
 group by b
;
--
BEGIN
   runstats_pkg.rs_start;
  FOR i IN 1 .. 100 LOOP
     FOR rec IN c_rn LOOP
        NULL;
     END LOOP;
  END LOOP;
   runstats_pkg.rs_middle;
  FOR i IN 1 .. 100 LOOP
     FOR rec IN c_keepfirst LOOP
        NULL;
     END LOOP;
  END LOOP;
   runstats_pkg.rs_stop;
END;
/

STAT...CPU used by this sessio       1,872         426      -1,446
STAT...recursive cpu usage           1,872         424      -1,448
STAT...Elapsed Time                  1,873         424      -1,449


2012-03-21

OUG Harmony 2012 registration open

OUG Harmony 2012 registration is open. Act before 30.4.2012 to be an early bird and save money.

2012-03-20

join strategies to cross join

There are three different join startegies the optimizer may perform a join. One of those is different considering a cross join.

In this example join is executed using nested loops. It may be hinted also to a merge join path.

select * from dual d1 cross join dual d2;

select * from table(dbms_xplan.display_cursor(format=>'basic'));

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  NESTED LOOPS      |      |
|   2 |   TABLE ACCESS FULL| DUAL |
|   3 |   TABLE ACCESS FULL| DUAL |
-----------------------------------

select /*+use_merge(d1 d2)*/ * from dual d1 cross join dual d2;

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  MERGE JOIN CARTESIAN|      |
|   2 |   TABLE ACCESS FULL  | DUAL |
|   3 |   TABLE ACCESS FULL  | DUAL |
-------------------------------------

The third approach would be hash join. But it is not possible.

select /*+use_hash(d1 d2)*/ * from dual d1 cross join dual d2;

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  NESTED LOOPS      |      |
|   2 |   TABLE ACCESS FULL| DUAL |
|   3 |   TABLE ACCESS FULL| DUAL |
-----------------------------------

Change the query to inner join and have a join condition. The USE_HASH hint is obeyed.
select /*+use_hash(d1 d2)*/ * from dual d1 inner join dual d2 on d1.dummy = d2.dummy;

select * from table(dbms_xplan.display_cursor(format=>'basic +predicate'));

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN         |      |
|   2 |   TABLE ACCESS FULL| DUAL |
|   3 |   TABLE ACCESS FULL| DUAL |
-----------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("D1"."DUMMY"="D2"."DUMMY")

2012-03-19

many to many select -view efficiently -the problem

My last post was describing a solution to problem that I did not explain beforehand. I also chose a bad example of two things to be localized. The original problem did not have relationship between the things to be localized. The example has the citycountry table there and it should be used in the citycountrynames view. It was yet again difficult to generate a good example. But the original problematic view was something like:
create or replace view citycountrynames_slow as 
select cl.lang
     , ci.city
     , co.country
     , cl.name  cityname
     , col.name countryname
  from city ci 
  cross join country co 
  left outer join citylang cl on cl.city=ci.city
  left outer join countrylang col on col.country=co.country and col.lang=cl.lang
;
Cross join there. And that is not the problem. The plan for the query was

select cn.cityname,cn.countryname,sh.shop_name
  from citycountrynames_slow cn 
 inner join shop sh 
    on sh.city=cn.city and sh.country=cn.country
 where cn.city=:city
   and cn.lang=:lang
;

-------------------------------------------------------------
| Id  | Operation                     | Name                |
-------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |
|   1 |  NESTED LOOPS OUTER           |                     |
|   2 |   NESTED LOOPS                |                     |
|   3 |    VIEW                       |                     |
|   4 |     NESTED LOOPS              |                     |
|   5 |      NESTED LOOPS             |                     |
|   6 |       INDEX UNIQUE SCAN       | CITYLANG_PK         |
|   7 |       INDEX UNIQUE SCAN       | CITY_PK             |
|   8 |      INDEX FAST FULL SCAN     | COUNTRY_PK          |
|   9 |    TABLE ACCESS BY INDEX ROWID| SHOP                |
|  10 |     INDEX RANGE SCAN          | SHOP_COUNTRY_FK_IDX |
|  11 |   INDEX UNIQUE SCAN           | COUNTRYLANG_PK      |
-------------------------------------------------------------

One problem there is INDEX FAST FULL SCAN COUNTRY_PK. And the country table was a big one. The view query has the problem of outer joining to several tables. That is why the optimizer is not able to merge the view to the main query.
select cl.lang
     , ci.city
     , co.country
     , cl.name  cityname
     , col.name countryname
  from city ci, country co, citylang cl, countrylang col 
 where cl.city(+)=ci.city
   and col.country(+)=co.country 
   and col.lang(+)=cl.lang
;

ORA-01417: a table may be outer joined to at most one other table
One can avoid ORA-01417 by rewriting the query
select xx.*, col.name countryname
 from (
select cl.lang
     , ci.city
     , co.country
     , cl.name  cityname
  from city ci, country co, citylang cl
 where cl.city(+)=ci.city) xx, countrylang col 
where  col.country(+)=xx.country 
   and col.lang(+)=xx.lang
;
Actually a bit similar structure may be seen in the plan line 3. A view without a name is appearing to the plan. The tuned previous post view is behaving much more efficiently for the query. No full scans. The tuning was to add the lang table to the view query. Move the many parts to select list sub query.
select cn.cityname,cn.countryname,sh.shop_name
  from citycountrynames cn 
 inner join shop sh 
    on sh.city=cn.city and sh.country=cn.country
 where cn.city=:city
   and cn.lang=:lang
;

----------------------------------------------------------
| Id  | Operation                     | Name             |
----------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |
|   1 |  INDEX UNIQUE SCAN            | CITYLANG_PK      |
|   2 |  INDEX UNIQUE SCAN            | COUNTRYLANG_PK   |
|   3 |  NESTED LOOPS                 |                  |
|   4 |   NESTED LOOPS                |                  |
|   5 |    NESTED LOOPS               |                  |
|   6 |     INDEX UNIQUE SCAN         | CITY_PK          |
|   7 |     INDEX UNIQUE SCAN         | LANG_PK          |
|   8 |    TABLE ACCESS BY INDEX ROWID| SHOP             |
|   9 |     INDEX RANGE SCAN          | SHOP_CITY_FK_IDX |
|  10 |   INDEX UNIQUE SCAN           | COUNTRY_PK       |
----------------------------------------------------------

2012-03-18

many to many select -view efficiently

The problem to be solved here is described in the next post. A while ago I wrote about creating a view on top of many to many relation. In that article there was no need to select any columns from the many to many relation table. A localization view might be something that you want to have columns received from. Here is an example of such. A bit of preparation first. We have localized names for country and city names first. Create a view of those to be used from several usages. And a query example that joins a shop table to the view.
drop table shop;

drop table countrylang;

drop table citycountry;

drop table citylang;

drop table country;

drop table city;

drop table lang;



create table lang (lang varchar2(2) constraint lang_pk primary key);

insert into lang values ('fi');

insert into lang values ('en');

insert into lang values ('sv');



create table country(country varchar2(3) constraint country_pk primary key);

insert into country values ('FI');

insert into country values ('SV');


create table countrylang(country constraint countrylang_country_fk references country
                       , lang constraint countrylang_lang_fk references lang
                       , name varchar2(200) not null
                       , constraint countrylang_pk primary key (country,lang)
                       ) organization index compress 1
;

insert into countrylang values ('FI','fi','Suomi');

insert into countrylang values ('FI','sv','Finland');

insert into countrylang values ('FI','en','Finland');

insert into countrylang values ('SV','fi','Ruotsi');

insert into countrylang values ('SV','sv','Sweden');

insert into countrylang values ('SV','en','Sweden');


create table city(city varchar2(3) constraint city_pk primary key);

insert into city values ('tpe');

insert into city values ('hki');

insert into city values ('sto');

create table citylang(city constraint citylang_city_fk references city
                    , lang constraint citylang_lang_fk references lang
                    , name varchar2(200) not null
                    , constraint citylang_pk primary key (city,lang)
                    ) organization index compress 1
;

insert into citylang values ('tpe','fi','Tampere');

insert into citylang values ('tpe','sv','Tammerfors');

insert into citylang values ('tpe','en','Tampere');

insert into citylang values ('hki','fi','Helsinki');

insert into citylang values ('hki','sv','Helsingfors');

insert into citylang values ('hki','en','Helsinki');


create table citycountry(city references city, country references country, constraint citycountry_pk primary key (city,country));

insert into citycountry values('tpe','FI');

insert into citycountry values('hki','FI');

insert into citycountry values('sto','SV');


create view citycountrynames as 
select la.lang
     , ci.city
     , co.country
     , (select cl.name from citylang cl where cl.city=ci.city and cl.lang=la.lang) cityname
     , (select cl.name from countrylang cl where cl.country=co.country and cl.lang=la.lang) countryname
  from lang la, city ci, country co
;

So that was the view. Be sure to populate the tables underneath correctly to avoid nulls.

create table shop (shop int constraint shop_pk primary key
                 , shop_name varchar2(200) not null
                 , city references city not null
                 , country references country
                 , constraint shop_citycountry_fk foreign key (city,country) references citycountry)
;

create index shop_city_fk_idx on shop(city);

create index shop_country_fk_idx on shop(country);

insert into shop values (1,'Helsinki shop','hki','FI');

insert into shop values (2,'Tampere shop','tpe','FI');

insert into shop values (3,'Stockholm shop','sto','SV');

commit;

select cn.cityname,cn.countryname
  from citycountrynames cn 
 inner join shop sh 
    on sh.city=cn.city and sh.country=cn.country
 where cn.city=:city
   and cn.lang=:lang
;
It is late Saturday evening now. I will not show any execution plans now. Try those out yourself. I will have another taste of Isle of Jura.
SQL> variable lang varchar2(2);
SQL> variable city varchar2(3);
SQL> exec :city := 'tpe'

PL/SQL procedure successfully completed.

SQL> exec :lang := 'fi'

PL/SQL procedure successfully completed.

SQL> select sh.shop_name, cn.cityname,cn.countryname
  2    from citycountrynames cn
  3   inner join shop sh
  4      on sh.city=cn.city and sh.country=cn.country
  5   where cn.city=:city
  6     and cn.lang=:lang
  7  ;

SHOP_NAME
------------------------------------------------------

CITYNAME
------------------------------------------------------

COUNTRYNAME
------------------------------------------------------

Tampere shop
Tampere
Suomi

2012-03-14

Store more days of history

Diagnostic pack purchased. The default amount of days stored work load repository is eight days. This is how it is changed to 365 days:
select snap_interval, retention
from dba_hist_wr_control;

SNAP_INTERVAL                                     
--------------------------------------------------
RETENTION                                         
--------------------------------------------------
+00 01:00:00.000000                               
+08 00:00:00.000000                               
                                                                                

execute dbms_workload_repository.modify_snapshot_settings(interval => 60,retention => 525600);

select snap_interval, retention
from dba_hist_wr_control;

SNAP_INTERVAL                                     
--------------------------------------------------
RETENTION                                         
--------------------------------------------------
+00 01:00:00.000000                               
+365 00:00:00.000000 

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.