Compute sum of in plain Oracle SQL

Once again a post about Oracle analytics. Indeed the best thing since sliced bread.

In sqlplus we have the posibility to insert breaks and to trigger basic events on these breaks. However in plain SQL it is not as easy, not that it is very hard either. Using analytics and the cool classic "decode rownum" hack we can get sort of the same results. On the last returned row you will get the full sum of the previous rows, in a new column however. But at least it's all done at the database layer and in plain SQL.

An example as usual

SQL> break on report
SQL> compute sum of sal on report
SQL> select ename, sal from emp
  2  /

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300
JAMES
           ----------
sum             29025

15 rows selected.

SQL> clear breaks
breaks cleared
SQL> select e.*, (decode(rownum,count(*)over(),sum(e.sal)over(),null)) sum
  2  from (select ename, sal from emp union 
  3  select null,null from dual order by ename) e
  4  /

ENAME             SAL        SUM
---------- ---------- ----------
ADAMS            1100
ALLEN            1600
BLAKE            2850
CLARK            2450
FORD             3000
JAMES             950
JAMES
JONES            2975
KING             5000
MARTIN           1250
MILLER           1300
SCOTT            3000
SMITH             800
TURNER           1500
WARD             1250
                           29025

16 rows selected.

Update
You can also use the "group by rollup" features, I've writen a small article about it here.

Oracle group_concat() updated

Update (again)! 2006-08-04
Some even better code was posted here!

Found a quick nifty peace of code over at asktom.
The code gives a very cheap and simple way to do group concatiation, same as group_concat() in MySQL. This without using PL/SQL. The code uses the new collect function introduced in Oracle 10g, so unfortunatly it is 10g only. 9i people will have to stay with other methods such as the PL/SQL function stragg().

CREATE OR REPLACE TYPE ntt_varchar2 AS TABLE
OF VARCHAR2(4000);

CREATE OR REPLACE FUNCTION to_string (
  nt_in        IN ntt_varchar2,
  delimiter_in IN VARCHAR2 DEFAULT ','
  ) RETURN VARCHAR2 IS

  v_idx PLS_INTEGER;
  v_str VARCHAR2(32767);
  v_dlm VARCHAR2(10);

BEGIN
  v_idx := nt_in.FIRST;
  WHILE v_idx IS NOT NULL LOOP
    v_str := v_str || v_dlm || nt_in(v_idx);
    v_dlm := delimiter_in;
    v_idx := nt_in.NEXT(v_idx);
  END LOOP;
  RETURN v_str;
END to_string;
/

And a quick example:

SQL> select deptno, cast(collect(ename) as ntt_varchar2) as vals
  from emp group by deptno

    DEPTNO VALS
---------- -------------------------------------------------------------------
        10 NTT_VARCHAR2('CLARK', 'KING', 'MILLER')
        20 NTT_VARCHAR2('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
        30 NTT_VARCHAR2('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')
           NTT_VARCHAR2('JAMES')

SQL> select deptno, to_string(cast(collect(ename) as ntt_varchar2)) as vals 
  from emp group by deptno;

    DEPTNO VALS
---------- -------------------------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
           JAMES

Cool!
The origianl thread can be found here.

Thanks to Adrian Billington for coming up with the code.

Oracle analytical functions

Recently I've found myself using the analytical functions in Oracle more and more. Not really to do in depth analysis, but just to do normal queries.
The analytical functions give you a bigger perspective to work with in a query, you can access more than the current row in a query. Compare it to a "row level aggregator". Things usually done by a group by expression can be done straight in the select clause. You can access the previous rows and following rows in the resultset to compute things.
Very handy indeed.

Quick example using the very basic functions of analytics.

select 
  distinct(deptno), 
  (count(deptno)over(partition by deptno)) /
  (count(deptno)over())*100 dshare 
from 
  emp
where 
  deptno is not null 
order 
  by deptno
/
    DEPTNO DSHARE
---------- ------
        10     21
        20     36
        30     43

This query does two things and divide the two things to get the size of the deptartment in percent. first it finds the size of the current department, then the total head count (excluding top dog). Notice how both functions look at a bigger window than the current row without aggregating the data.

Some things never change

Looking back at the mid 1980's and the introduction of random strange graphical user interfaces I found two quite funny and very different video clips on YouTube.

How do you introduce a new operationg system?
1. The Microsoft way
2. The Apple way

How could things go so wrong?

Do Oracle still charge for standby databases?

I'm looking in to some upgrade costs for a few systems and was told by our happy Oracle rep. that full Oracle EE licenses are required for all standby databases (i.e. physical standby data guard). Costly indeed.

Now I stumbled across this PDF on the Oracle website.

From the PDF:
"System: shall be defined as each distinct production database. Test, development, failover and standby databases are not required to be licensed as systems; however, you may run the program on these databases."

Not sure whom to believe.

Oracle Validated Configurations

Oracle have started giving end-to-end recommendation for database software and hardware platforms. Recommending a server, storage system and operating system for new deployments. No need to ask around any more. Just go to this page

Oddly enough, Oracle chose Linux x86_64 for all their recommended configurations so far, perhaps the Solaris choices are to obvious to list. They claim they see a very high demand for Linux x86_64 systems at the moment. Not suprised by that, large SGA allocation on 32-bit platforms has always been a pain in the neck.
We've had a Sun v20z running 64-bit CentOS for six months or so and haven't seen any major problems. Did see one core dump issue in data guard when configuring the standby database in dgmgrl.

I'm sure we'll see more (Unix) recommended configurations in time.
And still no sight of Ubuntu.

hdesc beta1 release

I'm pleased to introduce "halis desc" (hdesc for short) for sqlplus :)

Hdesc is a quick and simple plsql program to display information about a table in sqlplus, it aims to be an extension of the normal desc command with the same level of output as the "\d" command in PostgreSQL. I wrote it the other day and haven't done any extensive testing. Since it's a beta release i just included the basic stored procedure, no public synonyms or stuff like that.

Planed features are constraint output format cleanup, list of triggers on table, more partitioning information and possibly table statistics.

Download the code here http://halisway.hifichoice.com/hdesc.sql

Load the stored proc and example

OPS$HLINDEN@orcl> @hdesc

Procedure created.

OPS$HLINDEN@orcl> set serverout on
OPS$HLINDEN@orcl> exec hdesc('t2')
HDesc information for: t2
Column name                    | Data type            | Nullable | Default
------------------------------ | -------------------- | -------- | ----------
T2_ID                          | NUMBER(5)            | NO       |
USER_ID                        | NUMBER               | YES      |
COST                           | NUMBER(4,4)          | YES      |
NUFF                           | VARCHAR2(30)         | YES      |
OBJECT_ID                      | NUMBER               | YES      |
------------------------------ | -------------------- | -------- | ----------
Tablespace: USERS               Monitoring: YES          Partitioned: NO
Last analyzed: 2006/06/08       Row movement: DISABLED   Compression: DISABLE
Constraints:
Primary key T2_PK on column(s): T2_ID
Foreign key: SYS_C0096241 on (USER_ID) references T1(ID)
Foreign key: T2_NUFF_FK on (NUFF,OBJECT_ID) references CT2(OBJECT_NAME,OBJECT_ID)
Unique: T2_COST_UNIQ on (COST)

PL/SQL procedure successfully completed.

Please send any feedback you may have.

Cost-Based Oracle Fundamentals

Just got a new book about Oracle from Amazon. The book Cost-Based Oracle Fundamentals written by Jonathan Lewis describes the Oracle Cost-Based optimizer in a easy and understandable way. Jonathan Lewis is the director of the UK Oracle user group and is the author of several quite good books, this is however his best book so far.
My only disappointment so far (haven't fine read all sections yet) is that the book doesn't really cover how to "manually" influence the CBO in other ways than SQL HINTS, such as "faking" your own DBMS_STATS and costs.

Any how, the best book on Oracle I've seen in a long time.
Go get it, both for DBAs and developers.

SQL_CALC_FOUND_ROWS in Oracle

People keep telling me about all these new fancy features MySQL have and how nice it would be if Oracle had the same. My usuall response is "Uhm, had that for 15 years or so". Oracle just call the feature something difrerent. My latest MySQL "fancy feature" question was about SQL_CALC_FOUND_ROWS.

Here's how you get the same functionality it in Oracle

select found_rows, empno, job from 
  (select count(*) over () found_rows, empno, job, rownum rn 
     from emp order by empno) 
where 
  rn between 1 and 5
/
FOUND_ROWS      EMPNO JOB
---------- ---------- ---------
        15       7369 CLERK
        15       7499 SALESMAN
        15       7521 SALESMAN
        15       7566 MANAGER
        15       7654 SALESMAN

You do get a bit more data transfered to the application layer, one value for each column. To avoid this and only get the count on the first row you can modify the inner query to use decode() like this

select decode(rownum,1,count(*) over ()), empno, job 
  from emp order by empno

Ubuntu dapper PXE network install

I've been testing out the Ubuntu network install environment today. All you need to use the PXE installer is to configure a DHCP server and TFTP server on your network, if you want a more hands off install you can use a "preseed" file. Quite easy to use and it is much more flexible then the ananconda kickstart files.
Ubuntu actually support the kickstart file format as well, so if you don't want to learn the new preconfigure "preseed" format you can just write a normal kickstart syntax file and netboot Ubuntu exactly the same way you would a Redhat/Centos/Fedora box.

Simple instructions on how to configure a Ubuntu netboot server. My install server "tessy" has IP 192.168.95.20 in this example and I'm installing the VIA C3 shoebox PC "elmer" with IP 192.168.95.30.
Don't forget to disable any other DHCP servers you may have on the network such as in broadband routers. Download the netboot.tar.gz and Ubuntu ISO of your choice. I saved both files in the /dl volume on my server.

apt-get install tftpd-hpa
apt-get install dhcp3-server
mkdir -p /var/www/ubuntu-606-server-i386/
mount -o loop /dl/ubuntu-6.06-server-i386.iso /var/www/ubuntu-606-server-i386/
tar xzvf /dl/netboot.tar.gz -C /var/lib/tftpboot

Update the following files to suite your setup:

/etc/default/tftpd-hpa
RUN_DAEMON="yes"
OPTIONS="-l -s /var/lib/tftpboot"

/etc/dhcp3/dhcpd.conf
ddns-update-style none;
option domain-name "hifichoice.com";
option domain-name-servers 83.146.21.6, 212.158.248.5;
ping-check = 1;
default-lease-time 600;
max-lease-time 7200;
log-facility local7;
subnet 192.168.95.0 netmask 255.255.255.0 {
   range 192.168.95.100 192.168.95.150;
   option routers 192.168.95.1;
   filename="pxelinux.0";
   next-server 192.168.95.20;
}

/var/lib/tftpboot/pxelinux.cfg/default
DISPLAY ubuntu-installer/i386/boot-screens/boot.txt
PROMPT 1
TIMEOUT 0
label install-simple
        kernel ubuntu-installer/i386/linux
        append initrd=ubuntu-installer/i386/initrd.gz \ -
        ramdisk_size=14984 root=/dev/rd/0 rw preseed/locale=en_US \ -
        kbd-chooser/method=us netcfg/wireless_wep= netcfg/choose_interface=eth0 \ -
        netcfg/get_hostname=elmer preseed/url=http://192.168.95.20/preseed-606-auto.cfg --
LABEL install
        kernel ubuntu-installer/i386/linux
        append vga=normal initrd=ubuntu-installer/i386/initrd.gz ramdisk_size=14332 \ -
        root=/dev/rd/0 rw  --
My /var/www/preseed-606-auto.cfg

You'll get a few prompts, but nothing annoying. I'll have a deeper look into the preseed file format some other day. I'm sure a hands-off install is possible.

The Ubuntu wiki has a netboot article here and you can read more about the preseed file here.

Oracle index skip scan

Before we had 9i a composite index practically create left most implicit indeces for the columns in the indexes. We know that we should sort the columns in a composite index with the column with the lowest cardinality to the left. Starting in 9i Oracle introduced a new index scan method called "index skip scan". Even though it's been available for a just over 4 years I haven't seen much news or design articles about this method. With a skip scan you can use any part of a composite index in (almost) any type of query.
It comes with a cost of course since Oracle will have to probe the index the same number of times as the leading column(s). I.e. if you leading column have 10 distinct values, Oracle will have to do 10 probes in the binary tree. Skip scans should usually be avoided in extremly heavy use queries but may be a nice feature to keep in mind for reporting queries and other lower use queries.
The benefit is that you can avoid having several indexes on the same columns, saving you update time and a bit of disk space.

Let us do a little test

OPS$HLINDEN@orcl> create table ct as select * from all_objects;

Table created.

OPS$HLINDEN@orcl> insert into ct select * from ct;

...

OPS$HLINDEN@orcl> commit;

Commit complete.

OPS$HLINDEN@orcl> create index ct_test_idx 
  2  on ct(owner,object_type,object_name);

Index created.

OPS$HLINDEN@orcl> EXEC DBMS_STATS.gather_table_stats('OPS$HLINDEN', 'CT');

PL/SQL procedure successfully completed.

OPS$HLINDEN@orcl> set autotrace traceonly exp
OPS$HLINDEN@orcl> select status from ct where owner='OPS$HLINDEN'
  2  and object_type='TABLE' and object_name='T9';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=49)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CT' (TABLE) (Cost=4 Card
          =1 Bytes=49)

   2    1     INDEX (RANGE SCAN) OF 'CT_TEST_IDX' (INDEX) (Cost=3 Card
          =1)

OPS$HLINDEN@orcl> select status from ct where object_name='T9';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=44 Card=29 Bytes=9
          57)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CT' (TABLE) (Cost=44 Car
          d=29 Bytes=957)

   2    1     INDEX (SKIP SCAN) OF 'CT_TEST_IDX' (INDEX) (Cost=15 Card
          =29)

Notice the extra cost for scanning the index, eventhough we have fewer conditions we have a five fold cost. Now if the index would have been created backwards ,like ct(object_name,object_type,owner), Oracle would hesitate to even use it unless all three columns had conditions since the number of left most distinct values would have been to high (about 14k in this example, with only between 20 and 25 for the other two columns).

Skip scans can be good, but for intense queries do create indexes on the required columns.

Read more here.