Avoiding Analytic Functions

I’m a big fan of analytic functions in Oracle and I use them now and then ..but probably not enough. I know that a lot of experienced programmers use them less than me, using the excuse that all this malarkey with windows and partitions is too difficult to remember.

Well, let’s start small. Very small.

These days you might find yourself using sqlplus less and less frequently. SQL Developer and TOAD (other SQL GUIs are also available) are pretty good interactively and for some tasks are often a more attractive option than sqlplus. So this means that you’ve lost the nice formatting options in sqlplus as well as the ability to manipulate numbers e.g. averages and totals within sqlplus itself. For example:

set lines 120
break on report
compute sum of meg on report

SELECT tablespace_name, 
       SUM(bytes/1024/1024) AS meg
FROM  dba_data_files
GROUP BY tablespace_name
ORDER BY tablespace_name;
TABLESPACE_NAME                       MEG
------------------------------ ----------
APEX_1930613455248703                   7
EXAMPLE                                82
SYSAUX                               1125
SYSTEM                                861
UNDOTBS1                              208
USERS                                 225
                               ----------
sum                                  2508

So, what to do? How do we get the sum without sqlplus? I guess you could do 2 selects. Better still, lets use an analytic function. Don’t panic – it will look very similar to the code above and is just as easy to use:

SELECT NVL(tablespace_name,'** TOTAL **'),
SUM(bytes/1024/1024) AS meg
FROM dba_data_files
GROUP BY ROLLUP(tablespace_name)
ORDER BY INSTR(tablespace_name,'*'), tablespace_name;
NVL(TABLESPACE_NAME,'**TOTAL**        MEG
------------------------------ ----------
APEX_1930613455248703                   7
EXAMPLE                                82
SYSAUX                               1125
SYSTEM                                861
UNDOTBS1                              208
USERS                                 225
** TOTAL **                          2508

Not much difference – ‘GROUP BY ROLLUP(tablespace_name)‘ rather than ‘GROUP BY tablespace_name‘. The amount of work to derive the total is pretty small – and is a lot less than having 2 separate selects.

Here are the 2 plans. The first is for the SQL without the total and the second is the SQL with the total:

plan1

plan2

Once you’re happy with this, try listing space used by each segment type for each user in a database by using ‘ROLLUP’ again. Something along these lines:

SELECT OWNER,
       SEGMENT_TYPE,
       SUM(bytes/1024/1024) AS meg
FROM   dba_segments
GROUP BY ROLLUP(owner, segment_type);

To be honest I’ve not run this one yet but it looks about right! If it works, try replacing ‘ROLLUP’ with ‘CUBE’ and see what you get.

Advertisements

About dnadba

I work with databases and stuff
Quote | This entry was posted in Oracle. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s