How to determine which view to use

Often times DBAs or application architects create views to conceal complex joins or aggregations in order to help simplify the SQL queries developers need to write.  However, as an application evolves, and the number of views grow, it can often be difficult for a developer to know which view to use.

It also become easier for a developer to write an apparently simple query, that results in an extremely complex SQL statement being sent to the database, which may execute unnecessary joins or aggregations.

The DBMS_UTILITY.EXPAND_SQL_TEXT procedure, introduced in Oracle Database 12.1, allows developers to expand references to views, by turning them into subqueries in the original statement, so you can see just exactly what tables or views are being accessed and what aggregations are being used.

Let’s imagine we have been asked to determine the how many “Flat Whites” we sold in our coffeeshops this month. As a developer, I know I need to access the SALES table to retrieve the necessary sales data and the PRODUCTS table to limit it to just our “Flat Whites” sales but I also know that the DBA has setup a ton of views to make developers lives easier. In order to determine what views I have access to, I’m going to query the dictionary table USER_VIEWS.

SELECT  view_name 
FROM    user_views
WHERE   view_name LIKE '%SALES%';
 
VIEW_NAME
-------------------------------
SALES_REPORTING2_V
SALES_REPORTING_V

Based on the list of views available to me, I would likely pick the view called SALES_REPORTING_V or SALES_REPORTING2_V but which would be better?

Let’s use the DBMS_UTILITY.EXPAND_SQL_TEXT procedure to find out. In order to see the underlying query for each view, we can use is a simple “SELECT *” query from each view. First, we will try ‘SELECT * FROM sales_reporting_v‘.

SET serveroutput ON
DECLARE
    l_clob CLOB;
BEGIN
    DBMS_UTILITY.Expand_sql_text(
    input_sql_text => 'SELECT * FROM SALES_REPORTING_V',
    output_sql_text => l_clob);
 
    DBMS_OUTPUT.Put_line(l_clob);
END;
/

The output from the procedure was

SELECT "A1"."ORDER_ID" "ORDER_ID",
       "A1"."TIME_ID" "TIME_ID",
       "A1"."C_NAME" "C_NAME",
       "A1"."PROD_NAME" "PROD_NAME",
       "A1"."AMOUNT_SOLD" "AMOUNT_SOLD"
FROM   (SELECT "A3"."ORDER_ID" "ORDER_ID",
               "A3"."TIME_ID" "TIME_ID",
               "A4"."C_NAME" "C_NAME",
               "A2"."PROD_NAME" "PROD_NAME",
               "A3"."AMOUNT_SOLD" "AMOUNT_SOLD"
        FROM   "COFFEESHOP"."CUSTOMERS" "A4",
               "COFFEESHOP"."SALES" "A3",
               "COFFEESHOP"."PRODUCTS" "A2"
        WHERE  "A4"."C_CUSTID"="A3"."CUST_ID"
        AND    "A2"."PROD_ID"="A3"."PROD_ID"
       )"A1"

In this case, the view (A1) does contain the columns I need (PRODUCT_NAME, TIME_ID and AMOUNT_SOLD). But if I used this view, I’d actually get a lot more data than I bargained for since it also joins to the CUSTOMERS table, which is not need for my query.

Let’s try ‘SELECT * FROM SALES_REPORTING2_V’.

SET serveroutput ON
DECLARE
    l_clob CLOB;
BEGIN
    DBMS_UTILITY.Expand_sql_text(
    input_sql_text => 'SELECT * FROM SALES_REPORTING2_V',
    output_sql_text => l_clob);
 
    DBMS_OUTPUT.Put_line(l_clob);
END;
/

The output from the procedure is

SELECT "A1"."ORDER_ID" "ORDER_ID",
        "A1"."TIME_ID" "TIME_ID",
        "A1"."PROD_NAME" "PROD_NAME",
        "A1"."AMOUNT_SOLD" "AMOUNT_SOLD" 
FROM  (SELECT "A3"."ORDER_ID" "ORDER_ID",
              "A3"."TIME_ID" "TIME_ID",
              "A2"."PROD_NAME" "PROD_NAME",
              "A3"."AMOUNT_SOLD" "AMOUNT_SOLD" 
       FROM   "COFFEESHOP"."SALES" "A3",
              "COFFEESHOP"."PRODUCTS" "A2" 
       WHERE  "A2"."PROD_ID"="A3"."PROD_ID"
      ) "A1"

From the output above, we see that this view contains all of the columns I need for my query but without any unnecessary tables. So, this is definitely the view I should use.

But what if your application uses synonyms to simplify view names for developers because the views are actually defined in some other schema. Will the DBMS_UTILITY.EXPAND_SQL_TEXT procedure determine a view definition if a synonym is used?

The answer is yes, but let’s take a look at an example to prove the point.

Let’s connect as a different user who sees the same views via synonyms and use the same set of steps as before.

CONNECT apps/******
 
Connected.
SELECT synonym_name, table_owner, table_name
FROM user_synonyms;
 
SYNONYM_NAME                    TABLE_OWNER   TABLE_NAME
------------------------------ ------------- ----------------------
SALES_CUSTOMERS_PRODUCTS_V      COFFEESHOP    SALES_REPORTING_V
SALES_PRODUCTS_V                COFFEESHOP    SALES_REPORTING2_V

Just as before, we have two views based off the original application schema views. Now lets run the DBMS_UTILITY.EXPAND_SQL_TEXT procedure on each of the synonyms. Let’s start with the synonym SALES_CUSTOMERS_PRODUCTS_V.

SET serveroutput ON
DECLARE
    l_clob CLOB;
BEGIN
    DBMS_UTILITY.Expand_sql_text(
    input_sql_text => 'SELECT * FROM SALES_CUSTOMERS_PRODUCTS_V',
    output_sql_text => l_clob);
 
    DBMS_OUTPUT.Put_line(l_clob);
END;
/
 
SELECT "A1"."ORDER_ID" "ORDER_ID",
       "A1"."TIME_ID" "TIME_ID",
       "A1"."C_NAME" "C_NAME",
       "A1"."PROD_NAME" "PROD_NAME",
       "A1"."AMOUNT_SOLD" "AMOUNT_SOLD"
FROM   (SELECT "A3"."ORDER_ID" "ORDER_ID",
               "A3"."TIME_ID" "TIME_ID",
               "A4"."C_NAME" "C_NAME",
               "A2"."PROD_NAME" "PROD_NAME",
               "A3"."AMOUNT_SOLD" "AMOUNT_SOLD"
        FROM   "COFFEESHOP"."CUSTOMERS" "A4",
               "COFFEESHOP"."SALES" "A3",
               "COFFEESHOP"."PRODUCTS" "A2" 
        WHERE "A4"."C_CUSTID"="A3"."CUST_ID"
        AND   "A2"."PROD_ID"="A3"."PROD_ID"
       ) "A1"                                                                                             
 
PL/SQL PROCEDURE successfully completed.

Just as before, we see that this view includes an extra table, CUSTOMERS. Let’s now try the synonym SALES_PRODUCTS_V.

SET serveroutput ON
DECLARE
    l_clob CLOB;
BEGIN
    DBMS_UTILITY.Expand_sql_text(
    input_sql_text => 'SELECT * FROM SALES_PRODUCTS_V',
    output_sql_text => l_clob);
 
    DBMS_OUTPUT.Put_line(l_clob);
END;
/
 
SELECT "A1"."ORDER_ID" "ORDER_ID",
       "A1"."TIME_ID" "TIME_ID",
       "A1"."PROD_NAME" "PROD_NAME",
       "A1"."AMOUNT_SOLD" "AMOUNT_SOLD"
FROM  (SELECT "A3"."ORDER_ID" "ORDER_ID",
              "A3"."TIME_ID" "TIME_ID",
              "A2"."PROD_NAME" "PROD_NAME",
              "A3"."AMOUNT_SOLD" "AMOUNT_SOLD"
       FROM   "COFFEESHOP"."SALES" "A3",
              "COFFEESHOP"."PRODUCTS" "A2"
       WHERE  "A2"."PROD_ID"="A3"."PROD_ID"
       ) "A1"
 
PL/SQL PROCEDURE successfully completed.

As you can see from the above output, the DBMS_UTILITY.EXPAND_SQL_TEXT procedure had no issue resolving the original view definition from the COFFEESHOP schema when the synonym name is used.

PLEASE NOTE: To use the DBMS_UTILITY.EXPAND_SQL_TEXT procedure on a view that’s built on tables within another schema, you do need to have the SELECT privileges on underlying tables used in the view.

But what if you are a developer without access to the DBMS_UTILITY package?

Don’t panic.

If you are using SQL Developer version 4.1 against a 12c database than you can automatically see the expand definition of any view via a tool tip. Jeff Smith has already blogged about this at but here’s an example of our original.

5 thoughts on “How to determine which view to use”

  1. ….
    But what if you are a developer without access to the DBMS_UTILITY package, after all it’s not granted to PUBLIC by default?
    ….

    I am on 12.2, but dbms_utility is granted to PUBLIC by default.

    demo@ORA12C> select grantee,privilege,grantor
    2 from dba_tab_privs
    3 where owner =’SYS’
    4 and table_name =’DBMS_UTILITY’
    5 and grantee = ‘PUBLIC’;

    GRANTEE PRIVILEGE GRANTOR
    ———- ———- ———-
    PUBLIC EXECUTE SYS

    demo@ORA12C>

    Reply

  2. thanks for the excellent post.

    It would be nice to have a quote about the privilege of the underlying object while using this “expand_sql_text” method from DBMS_UTILITY API.

    created a view and granted to “demo” schema. able to access the view, but unable to use the “expand_sql_text” due to privilege missing on the base tables.

    rajesh@ORA12C> create or replace view v1
    2 as
    3 select deptno,count(*) cnt
    4 from emp
    5 group by deptno;

    View created.

    rajesh@ORA12C> grant select on v1 to demo;

    Grant succeeded.

    rajesh@ORA12C> conn demo/demo@ora12c
    Connected.
    demo@ORA12C> select * from rajesh.v1;

    DEPTNO CNT
    ———- ———-
    30 6
    20 5
    10 3
    demo@ORA12C> declare
    2 l_out clob;
    3 l_in long;
    4 begin
    5 l_in :=’ select * from rajesh.v1′;
    6 dbms_utility.expand_sql_text(l_in,l_out);
    7 dbms_output.put_line(l_out);
    8 end;
    9 /
    declare
    *
    ERROR at line 1:
    ORA-24256: EXPAND_SQL_TEXT failed with ORA-01039: insufficient privileges on underlying objects of the view
    ORA-06512: at “SYS.DBMS_UTILITY”, line 1568
    ORA-06512: at line 6

    However if we grant the privilege on the base table in addition to the view, we are able to use the “expand_sql_text” method.

    demo@ORA12C> conn rajesh/oracle@ora12c
    Connected.
    rajesh@ORA12C> grant select on emp to demo;

    Grant succeeded.

    rajesh@ORA12C> conn demo/demo@ora12c
    Connected.
    demo@ORA12C> declare
    2 l_out clob;
    3 l_in long;
    4 begin
    5 l_in :=’ select * from rajesh.v1′;
    6 dbms_utility.expand_sql_text(l_in,l_out);
    7 dbms_output.put_line(l_out);
    8 end;
    9 /
    SELECT “A1″.”DEPTNO” “DEPTNO”,”A1″.”CNT” “CNT” FROM (SELECT “A2″.”DEPTNO” “DEPTNO”,COUNT(*) “CNT” FROM “RAJESH”.”EMP” “A2” GROUP B
    Y “A2″.”DEPTNO”) “A1”

    PL/SQL procedure successfully completed.

    demo@ORA12C>

    Reply

    1. Hi Rajesh,

      You make a good point. To use the DBMS_UTILITY.EXPAND_SQL_TEXT on a view that’s built on tables within another schema, you do need to have the SELECT privileges on underlying objects of the view. I’ve updated the blog post to reflect this restriction.

      Thanks,
      Maria

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: