Analytics in Views

The question:

Does embedding an analytic function into a view cause a WINDOW SORT every time the view is called?

I say no, only if it is specifically referenced.

SH@TESTING>SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

The view definition:

CREATE OR REPLACE
VIEW vw_sales
AS
SELECT
  prod_id,
  cust_id,
  time_id,
  channel_id,
  promo_id,
  quantity_sold,
  amount_sold,
  SUM( amount_sold ) OVER
    ( PARTITION BY
        prod_id,
        cust_id
      ORDER BY time_id ) amount_sold_running
FROM sales;

I run an explain plan as SELECT *

SH@TESTING>EXPLAIN PLAN FOR SELECT * FROM VW_SALES;

Explained.

Elapsed: 00:00:02.19
SH@TESTING>@EXPLAIN

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2700574370

----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |   918K|    87M|       |  7841   (3)| 00:01:35 |       |       |
|   1 |  VIEW                 | VW_SALES |   918K|    87M|       |  7841   (3)| 00:01:35 |       |       |
|   2 |   WINDOW SORT         |          |   918K|    25M|    84M|  7841   (3)| 00:01:35 |       |       |
|   3 |    PARTITION RANGE ALL|          |   918K|    25M|       |   389  (12)| 00:00:05 |     1 |    28 |
|   4 |     TABLE ACCESS FULL | SALES    |   918K|    25M|       |   389  (12)| 00:00:05 |     1 |    28 |
----------------------------------------------------------------------------------------------------

Now if I just select the columns I want/need, will Oracle perform the WINDOW SORT?

SH@TESTING>EXPLAIN PLAN FOR
  2  SELECT QUANTITY_SOLD, AMOUNT_SOLD
  3  FROM vw_sales;

Explained.

Elapsed: 00:00:00.02
SH@TESTING>@explain

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 1550251865

---------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |   918K|  7178K|   389  (12)| 00:00:05 |       |       |
|   1 |  PARTITION RANGE ALL|       |   918K|  7178K|   389  (12)| 00:00:05 |     1 |    28 |
|   2 |   TABLE ACCESS FULL | SALES |   918K|  7178K|   389  (12)| 00:00:05 |     1 |    28 |
---------------------------------------------------------------------------------------------

Oracle is smart enough not to perform the WINDOW SORT if the column is not selected.

Nice.

Views: Complex Join Use Primary Keys?

Views have been on my mind quite a bit lately.

Last night I began to wonder if it makes a difference which key you use in the view. Logically, I thought, it would make a difference.

Here's my create scripts:

CREATE TABLE t
(
  my_id NUMBER(12)
    CONSTRAINT pk_myid PRIMARY KEY
);

INSERT INTO t( my_id )
SELECT rownum
FROM dual
  CONNECT BY LEVEL <= 1000000;

CREATE TABLE t_child
AS
SELECT rownum child_id, my_id
FROM t;

ALTER TABLE t_child
  ADD CONSTRAINT pk_childid
  PRIMARY KEY ( child_id );

ALTER TABLE t_child
  ADD CONSTRAINT fk_myid_tchild
  FOREIGN KEY ( my_id )
  REFERENCES t( my_id );

CREATE INDEX idx_myid_tchild
  ON t_child( my_id );

CREATE TABLE t_child_2
AS
SELECT rownum child_id_2, child_id
FROM t_child;

ALTER TABLE t_child_2
  ADD CONSTRAINT pk_childid2
  PRIMARY KEY ( child_id_2 );

ALTER TABLE t_child_2
  ADD CONSTRAINT fk_childid_tchild2
  FOREIGN KEY ( child_id )
  REFERENCES t_child( child_id );

CREATE INDEX idx_childid_tchild2
  ON t_child_2( child_id );

So I wanted to see if Oracle took a different path depending on how I built the view.

CREATE OR REPLACE 
VIEW vw_test
AS
SELECT
  tc.my_id,--note that isn't the PK from T
  t2.child_id--again, not the PK from T_CHILD
FROM 
  t,
  t_child tc,
  t_child_2 t2
WHERE t.my_id = tc.my_id
  AND tc.child_id = t2.child_id;

So now I run a couple of tests to see what happens when I SELECT on those columns (reminder, those are not the primary keys, they are indexed foreign keys).

CJUSTICE@TESTING>EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM vw_test
  4  WHERE my_id = 1;

Explained.

Elapsed: 00:00:00.00
CJUSTICE@TESTING>
CJUSTICE@TESTING>SELECT * FROM TABLE( dbms_xplan.display );

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1671340153

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    14 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                     |     1 |    14 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_CHILD             |     1 |     9 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_MYID_TCHILD     |     1 |       |     3   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN           | IDX_CHILDID_TCHILD2 |     1 |     5 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   3 - access("TC"."MY_ID"=1)
       filter("TC"."MY_ID" IS NOT NULL)
   4 - access("TC"."CHILD_ID"="T2"."CHILD_ID")

OK, so it did what I expected, it failed to get the record based on the primary key. I'll do it again, with the same construct as the view, but using the PK from T.

CJUSTICE@TESTING>EXPLAIN PLAN FOR
  2  SELECT
  3    tc.my_id,
  4    t2.child_id
  5  FROM 
  6    t,
  7    t_child tc,
  8    t_child_2 t2
  9  WHERE t.my_id = tc.my_id
 10    AND tc.child_id = t2.child_id
 11    AND t.my_id = 1;

Explained.

Elapsed: 00:00:00.01
CJUSTICE@TESTING>
CJUSTICE@TESTING>@explain

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4007286110

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     1 |    18 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |                     |     1 |    18 |     7   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |                     |     1 |    13 |     5   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN          | PK_MYID             |     1 |     4 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| T_CHILD             |     1 |     9 |     3   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | IDX_MYID_TCHILD     |     1 |       |     2   (0)| 00:00:01 |
|*  6 |   INDEX RANGE SCAN            | IDX_CHILDID_TCHILD2 |     1 |     5 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   3 - access("T"."MY_ID"=1)
   5 - access("TC"."MY_ID"=1)
   6 - access("TC"."CHILD_ID"="T2"."CHILD_ID")

Good. Now I'll recreate the view using the primary key and see if we get the same result.

CREATE OR REPLACE
VIEW vw_test
AS
SELECT
  t.my_id,
  t2.child_id
FROM 
  t,
  t_child tc,
  t_child_2 t2
WHERE t.my_id = tc.my_id
  AND tc.child_id = t2.child_id;

CJUSTICE@TESTING>EXPLAIN PLAN FOR 
  2  SELECT *
  3  FROM vw_test
  4  WHERE my_id = 1;

Explained.

Elapsed: 00:00:00.03
CJUSTICE@TESTING>@explain

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4007286110

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     1 |    18 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |                     |     1 |    18 |     7   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |                     |     1 |    13 |     5   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN          | PK_MYID             |     1 |     4 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| T_CHILD             |     1 |     9 |     3   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | IDX_MYID_TCHILD     |     1 |       |     2   (0)| 00:00:01 |
|*  6 |   INDEX RANGE SCAN            | IDX_CHILDID_TCHILD2 |     1 |     5 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   3 - access("T"."MY_ID"=1)
   5 - access("TC"."MY_ID"=1)
   6 - access("TC"."CHILD_ID"="T2"."CHILD_ID")

Now I'll try the same test using the FK on T_CHILD_2, CHILD_ID. No need to change the view as it's already there.

CJUSTICE@TESTING>EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM vw_test
  4  WHERE child_id = 1;

Explained.

Elapsed: 00:00:00.01
CJUSTICE@TESTING>@EXPLAIN

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 474290160

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     1 |    18 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |                     |     1 |    18 |     6   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |                     |     1 |    13 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_CHILD             |     1 |     9 |     3   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | PK_CHILDID          |     1 |       |     2   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | PK_MYID             |   989K|  3863K|     1   (0)| 00:00:01 |
|*  6 |   INDEX RANGE SCAN            | IDX_CHILDID_TCHILD2 |     1 |     5 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   4 - access("TC"."CHILD_ID"=1)
   5 - access("T"."MY_ID"="TC"."MY_ID")
   6 - access("T2"."CHILD_ID"=1)

And now using the PK

CJUSTICE@TESTING>EXPLAIN PLAN FOR
  2  SELECT
  3    tc.my_id,
  4    t2.child_id
  5  FROM 
  6    t,
  7    t_child tc,
  8    t_child_2 t2
  9  WHERE t.my_id = tc.my_id
 10    AND tc.child_id = t2.child_id
 11    AND tc.child_id = 1;

Explained.

Elapsed: 00:00:00.01
CJUSTICE@TESTING>
CJUSTICE@TESTING>@explain

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3182888138

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    14 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                     |     1 |    14 |     5   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T_CHILD             |     1 |     9 |     3   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_CHILDID          |     1 |       |     2   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN           | IDX_CHILDID_TCHILD2 |     1 |     5 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - filter("TC"."MY_ID" IS NOT NULL)
   3 - access("TC"."CHILD_ID"=1)
   4 - access("T2"."CHILD_ID"=1)

Looks like a much better path when I use the PK in the view definition. Note to self, if building complex views, don't use the Foreign Key column, use the Primary Key column.

Admittedly, I can't explain all the "magic" behind it, I'm hoping someone out there could help to explain. Logically, it makes sense as the Primary Key allows you the fastest access to a specific record (discounting the rowid).

The Case For Views

I recently had to "defend" my use of views.

To me, they seem natural. Using them is almost always a good thing to do. I've met those that don't really care for them...I just never understood why. Then again, those same people are still not convinced of PL/SQL APIs. Maybe there is something to that mindset...

Being forced to articulate one's views is a good thing, it's part of why I blog. I won't lie though, it gets frustrating to have do this, seemingly, all the time.

I'm going to do it here, again.

Complex Joins
No, I'm not afraid of joins. I am afraid of others who are afraid of joins though. More specifically, I'm afraid of those who aren't proficient at writing SQL. Let me do it, once, and let everyone else have access to the view. Besides, I'm the subject matter expert (SME) on the given set of tables, so it follows that I should create the interface to those tables.

Yes, I said interface. It's exactly what a view is and interface to the underlying data.

Encapsulation
Write it once and let it propogate everywhere.

When I had to "defend" my use of views, I mistakenly used the example of adding columns. Oops. That would (possibly) require changes throughout the system. I meant to say remove columns, in which case you could keep the placeholder in the view using NULL without having to change all of the code. This does not mean that proper analysis does not need to be performed, it does, but you could possibly get away with not having to change everything that references the view.

My second example was a derived value. This makes more sense to some people thankfully. I've seen the same calculation done on a specific field done 10s, even 100s of times throughout the code. Why not do it one time? Perfect use for views.

Security
Following the least privileges necessary to perform a given action, views allow you to give access to the data without direct access to the tables. Views can also be used to hide or mask data that certain individuals should not have access to. In conjunction with VPD or Application Contexts, it's a powerful way to prevent unauthorized access.

Maintenance
Maintenance has been alluded to above, but not explicitly stated.

For derived values: If you have a derived or calculated value and that calculation is performed all over the place, what happens when it changes? You have to update it everywhere. If you had used a view, change it once and it propogates everywhere. What was once a project is now a "simple" code change. This affects IT in how they choose and assign resources as well as the Business.

For complex joins: What if one table is no longer used or needed? What if that table is littered throughout the code base? You have a project on your hands.

If that table were part of a view, you could "simply" remove it, keep the columns in the view and you're done. There might be places where code needs to be adjusted, but overall, you have a much smaller impact. That's a good thing.

Other
I tried putting the following statement in a category up above, but couldn't make it fit.

Records in a table typically constitute data. Tables, joined together, in a view, tend to turn that data into information.

Database Views: Love 'em or Leave 'em?

A view in Oracle is essentially a query you can store in the database.

I use them quite heavily in my own systems. There are a few reasons that I like them and use them and in no particular order, here they are:

Complex Joins

Say you want to flatten out a customer's data. Typically a customer is comprised of 1 or more phone numbers, 1 or more addresses, 1 or more email addresses and their demographic information (Gender, DOB, etc). If you use something like the Party Model, that will require at least 5 tables:

PERSON
ENTITY
ADDRESS
PHONE
EMAIL

Which in turn requires 4 joins. I typically make a view on those tables and then reference the view in all my code (naturally the exception is where there are INSERTs/UPDATEs/DELETEs).

I don't necessarily consider this a complex join, but it's a lot of typing going on and with that the potential for errors. My personal preference is to create the view and then grant access to the view, not the underlying tables (security).

Maintenance and Support

These 5 tables would probably be at the core of just about any system that requires user interaction. Let's say you had 50 packages referencing these tables and you need to make a change...either add a column, alter a column or just drop a column. You potentially have 50 packages that are now invalid because of this because they all reference one or more of these tables. You would then have to go through each package to determine whether or not a change was actually required, or you could just recompile the schema (DBMS_UTILITY.COMPILE_SCHEMA). After recompile though you still had 42 packages in an invalid state.

If you used the view, after it's update and recompile, you may (hopefully) have only 1 or 2 packages that require updating. Far and away a better prospect than 42!

Logic

What if you web application needed the address in a specific format? In the view you can create that one time

SELECT 
  name,
  address1,
  address2,
  city,
  state,
  zip,
  city || ', ' || state || '  ' || zip AS web_city_state_zip
FROM vw_person;

Of course that can be done on the table itself now with Virtual Columns. Let everyone know about it and they don't have to rewrite it (support and maintenance). Done!

That's my haphazard list for using database views.

What about you? Do you use them or prefer not to? If not, why not?