O SpecGold OracleBusIntApps7 clr



   Call us now 

  Manchester Office

  +44 (0) 8450 940 998




Welcome to the Beyond Blog

As you'd expect from the winners of the Specialized Partner of the Year: Business Analytics at the Oracle UKI Specialized Partner Awards 2014, Beyond work with leading edge BI Applications primarily within the UK Public Sector. We intend to share some of our ideas and discoveries via our blog and hopefully enrich the wider discussion surrounding Oracle Business Intelligence and driving improved insight for customers

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
  • Team Blogs
    Team Blogs Find your favorite team blogs here.
  • Login
    Login Login form
Subscribe to this list via RSS Blog posts tagged in sql

I'm quite a fan of row generators. If I know I need a row for every day of the year for example, I generally avoid holding (and maintaining) a table of these dates, and instead will generate them. This is by no means a universal rule, so take each case on its merits.

select trunc(sysdate,'YYYY') + level -1 dt
  from dual
connect by level <= add_months(trunc(sysdate,'YYYY'),12) - trunc(sysdate,'YYYY');

You can see a few methods (of many) of generating rows on one of my previous posts. The issue with generating rows in this manner is that the optimizer estimates cardinalities based on the number of rows in the table. Which for dual is... 1.
So when we look at the execution plan for such a query, we see this.

SQL_ID  16r2my83pj187, child number 0
select /*+gather_plan_statistics*/ trunc(sysdate,'YYYY') + level -1 dt  
 from dual connect by level <= add_months(trunc(sysdate,'YYYY'),12) - 
Plan hash value: 1236776825
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
|   0 | SELECT STATEMENT             |      |      1 |        |    365 |00:00:00.01 |       |       |          |
|   1 |  CONNECT BY WITHOUT FILTERING|      |      1 |        |    365 |00:00:00.01 |  2048 |  2048 | 2048  (0)|
|   2 |   FAST DUAL                  |      |      1 |      1 |      1 |00:00:00.01 |       |       |          |

We thought we would get just 1 row back - but we in fact got 365. Once you start joining out to other tables, the magnitude of that soon becomes more prominent. Let's say I want to generate a row for each employee who is a manager, for each day of the year.

Last modified on Continue reading
Tagged in: sql Tuning
in Technical 188 0

I was inspired to post this blog by a post on the OTN Forums where a user was asking about calculating the consumption of hours against a target - that is, given a target of X hours with N hours per day, how many days would it be until they reached the target? I knew I had done similar things several times in the past, and each time I always seem to forget the actual logic and end up having to look it up again. So hopefully this post will also be also be a little reminder for me if nothing else.
Queries such as this can actually take many guises, but they all share the same underlying principle. A common use is in a warehouse environment where we have stock located in many different locations (because we can of course only fit a finite quantity of an item in a single location/box/storage unit etc) and we get a big order for an item placed. We want to iteratively pick all the items from each locator until need less than is in a locator, then we pick just the amount that is remaining.

Warehouse Diagram

Using the above example, if we had a request for item ABC of quantity 60, we could easily walk from left to right and pick 20 from Locator A, 30 from Locator B, and then the remaining 10 from the 20 in Locator D.
Often though there are requirements for stock usage in warehouses such as FIFO (First In, First Out), which basically translates to Pick the oldest stock first. This is because we don't want stock with a limited shelf life perishing whilst pickers only ever take the stock which is nearest the door!

I've seen a number of different implementations of this within an Oracle environment, some good, some not so good. The latter tend to be approaches which loop around in PL/SQL and count up the quantity from each location. What I want to do here though is demonstrate a pure SQL way of doing this using Analytic Functions. It's not particularly complicated, but it's one of those things that when you come to do it you end up scratching your head for ages trying to remember how to do it!
I'm also a big fan of do it in a single statement where possible/sensible - there's something quite satisfying with solving (seemingly) complicated problems within a single query/command :) .

Last modified on Continue reading
Tagged in: sql
in Technical 1327 2

Since Oracle 10g it has been possible to hide data from specific users or under a set of specified conditions. This can be entire rows using Virtual Private Database (VPD) policies or specific columns in a table. In this post I'm going to look at the latter and how that has been enhanced in Oracle 12c. First, let's take a pre-12c example. Suppose I have the following table of data.

Create Table emps As
  Select '12345' emp_no, 'Alice' emp_name, '01234 567890' tel_no From Dual Union All
  Select '67890' emp_no, 'Bob' emp_name, '07800 123456' tel_no From Dual Union All
  Select '09876' emp_no, 'Charlie' emp_name, '07989 989898' tel_no From Dual Union All
  Select '54321' emp_no, 'Dave' emp_name, '01987 654321' tel_no From Dual Union All
  Select '13579' emp_no, 'Erin' emp_name, '07909 101010' tel_no From Dual;

Now suppose we have a requirement to hide the telephone from all users as we deem that to be sensitive information. We could of course put whatever logic we wanted in our function, however let's just keep it simple for the purposes of demonstration.

Create or Replace Function check_auth(p_owner In Varchar2, p_name In Varchar2)
Return Varchar2
  Return Null;

Then we create a policy against our emps table as follows.


Now look what happens when I select from my table emps as user KEYMEJ.

----- ------- ------
12345 Alice         
67890 Bob           
09876 Charlie       
54321 Dave          
13579 Erin      

I can also still insert data, I just can't read what I've inserted!

Insert Into emps(emp_no, emp_name, tel_no)
Values ('24680','Frank','07909 090909');

Select * From emps Where emp_no='24680';

1 row inserted.

----- ------- ------
24680 Frank         

Now that is all well and good, however wouldn't it be useful for say a customer support representative to at least be able to verify the data? I.e. What if they could just see the last four characters of the phone number? Prior to 12c we would have done something like this:

Last modified on Continue reading
Tagged in: sql
in Technical 1711 0

I've been posting some bits recently on some of the new Oracle Database 12.2 features, particularly analytic views. However as 12.2 on-premise is not yet released you needed a paid Oracle Cloud account to be able to do anything.

Recently however, Oracle Live SQL has been updated to version 12.2.

Live SQL Version

This means with a free Oracle account you can begin using and learning some of the new cool 12.2 features.

Last modified on Continue reading
Tagged in: Analytic Views sql
in Technical 1466 0

Back onto analytical views, today I'm going to demo using calculated fact measures in analytic views as opposed to standard fact measures.
You may recall from my Introduction to Analytic Views post that we can create simple fact measures within the fact table itself as follows.

Create or Replace Analytic View emp_sales_av
Using emp_sales
Dimension By (
    Key date_wid References row_wid Hierarchies (calendar_hier Default),
    Key emp_wid References row_wid Hierarchies (employee_hier Default)
Measures (
  amount Fact amount

Useful, and with a bit of manipulation, subqueries etc. we can do quite a bit with that basic measure using plan SQL. There is however no need to go through all that effort. Analytic Views support Calculated Measures, and that makes them a whole lot more useful. Taking our model used previously we can quickly and easily extend it to include many more derived measures; and these functions extend out far further than the existing Analytic Functions available in database <lt; 12.2 (although you will note syntactical similarities). We can build measures based on the hierarchies with the AV, and even on levels within the hierarchy. So to add a simple measure of Sales Year to Date, it's as easy as:

amount_ytd As (Sum(amount) Over (Hierarchy calendar_hier Between Unbounded Preceding and Current Member Within Ancestor At Level cal_year))
Last modified on Continue reading
Tagged in: Analytic Views sql
in Technical 1534 0