Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Stephen.

Asked: February 24, 2017 - 9:14 pm UTC

Last updated: February 28, 2017 - 5:32 am UTC

Version: 11

Viewed 1000+ times

You Asked

Hi Connor and Chris (and welcome Maria),

I've read a few entries regarding stored procedures and ref cursor to support parameterized views. However my situation is slightly different.

In my case I'm given n tables (TBL_1, TBL_2, ...) each defined with the same schema holding millions records for product 1, product 2, ... respectively. For abstraction purposes, would it possible to write a pipelined function/stored procedure that would select the underlying table based on the argument.

In report (example):
SELECT * FROM TABLE(whatsup(1)) w LEFT JOIN someothertable s ON w.col1 = s.col2 -- whatsup would dynamically return TBL_1.

Even if this was possible, would it be performant? As an alternative I already tried creating one view consolidating all the tables to simplify things but queries are too slow.

Thanks so much!

and Connor said...

I would revisit your consolidation approach. If you define the view so that tables can be eliminated by the optimizer, then things should run optimally, eg


SQL> drop table t1 cascade constraints purge;

Table dropped.

SQL> drop table t2 cascade constraints purge;

Table dropped.

SQL> drop table t3 cascade constraints purge;

Table dropped.

SQL> drop table t4 cascade constraints purge;

Table dropped.

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table t2 as select * from dba_objects;

Table created.

SQL> create table t3 as select * from dba_objects;

Table created.

SQL> create table t4 as select * from dba_objects;

Table created.

SQL>
SQL> create or replace
  2  view V as
  3  select 'T1' tname, t1.* from t1
  4  union all
  5  select 'T2' tname, t2.* from t2
  6  union all
  7  select 'T3' tname, t3.* from t3
  8  union all
  9  select 'T4' tname, t4.* from t4
 10  /

View created.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from v
  2  where tname = 'T2';

Execution Plan
----------------------------------------------------------
Plan hash value: 289899379

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   104K|    36M|   312   (1)| 00:00:01 |
|   1 |  VIEW                | V    |   104K|    36M|   312   (1)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    FILTER            |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T1   |   104K|    11M|   312   (1)| 00:00:01 |
|   5 |    TABLE ACCESS FULL | T2   |   104K|    11M|   312   (1)| 00:00:01 |
|*  6 |    FILTER            |      |       |       |            |          |
|   7 |     TABLE ACCESS FULL| T3   |   104K|    11M|   312   (1)| 00:00:01 |
|*  8 |    FILTER            |      |       |       |            |          |
|   9 |     TABLE ACCESS FULL| T4   |   104K|    11M|   312   (1)| 00:00:01 |
-----------------------------------------------------------------------------

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

   3 - filter(NULL IS NOT NULL)
   6 - filter(NULL IS NOT NULL)
   8 - filter(NULL IS NOT NULL)

SQL> set autotrace off


Notice the FILTER operations - they are "always false" so the scans on T1, T3 and T4 are never even done, because we provided tname='T2'

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Parameterized views revisited

Stephen, February 28, 2017 - 5:04 am UTC

I tried the predicate trick and the consolidated view seems to work with the smaller T1..T4 (1-2M rows each). However when I union with T5 and T6 that have 500M rows each, the queries tank. Querying T5 or T6 directly is ok since they are tables that have been indexed but with the consolidated view, I guess I'm out of luck?
Connor McDonald
February 28, 2017 - 5:32 am UTC

I think I'd need some evidence for that, because here's an example showing that the optimizer is fine with that kind of scenario

SQL> drop tablespace demo including contents and datafiles;

Tablespace dropped.

SQL> drop table t1 cascade constraints purge;

Table dropped.

SQL> drop table t2 cascade constraints purge;

Table dropped.

SQL> drop table t3 cascade constraints purge;

Table dropped.

SQL> drop table t4 cascade constraints purge;

Table dropped.

SQL>
SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table t2 as select * from dba_objects;

Table created.

SQL> create table t3 as select * from dba_objects;

Table created.

SQL> create table t4 tablespace large_ts nologging as select * from dba_objects;

Table created.

SQL>
SQL> insert /*+ APPEND */ into t4 select * from t4;

104185 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into t4 select * from t4;

208370 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into t4 select * from t4;

416740 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into t4 select * from t4;

833480 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into t4 select * from t4;

1666960 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into t4 select * from t4;

3333920 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into t4 select * from t4;

6667840 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into t4 select * from t4;

13335680 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into t4 select * from t4;

26671360 rows created.

SQL> commit;

Commit complete.

SQL> create index t4ix on t4 ( object_name ) tablespace large_ts nologging;

Index created.

SQL> exec dbms_stats.gather_table_stats('','T4',cascade=>false)

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from v
  2  where tname = 'T2';

Execution Plan
----------------------------------------------------------
Plan hash value: 289899379

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   104K|    36M|   312   (1)| 00:00:01 |
|   1 |  VIEW                | V    |   104K|    36M|   312   (1)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    FILTER            |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T1   |   104K|    11M|   312   (1)| 00:00:01 |
|   5 |    TABLE ACCESS FULL | T2   |   104K|    11M|   312   (1)| 00:00:01 |
|*  6 |    FILTER            |      |       |       |            |          |
|   7 |     TABLE ACCESS FULL| T3   |   104K|    11M|   312   (1)| 00:00:01 |
|*  8 |    FILTER            |      |       |       |            |          |
|   9 |     TABLE ACCESS FULL| T4   |    53M|  5799M|   155K  (1)| 00:00:07 |
-----------------------------------------------------------------------------

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

   3 - filter(NULL IS NOT NULL)
   6 - filter(NULL IS NOT NULL)
   8 - filter(NULL IS NOT NULL)

SQL>
SQL>
SQL> set autotrace traceonly explain
SQL> select * from v
  2  where tname = 'T4'
  3  and  object_name = 'T4';

Execution Plan
----------------------------------------------------------
Plan hash value: 2442212741

----------------------------------------------------------------------------------------------
| Id  | Operation                             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |      |   904 |   328K|   645   (0)| 00:00:01 |
|   1 |  VIEW                                 | V    |   904 |   328K|   645   (0)| 00:00:01 |
|   2 |   UNION-ALL                           |      |       |       |            |          |
|*  3 |    FILTER                             |      |       |       |            |          |
|*  4 |     TABLE ACCESS FULL                 | T1   |     2 |   228 |   311   (1)| 00:00:01 |
|*  5 |    FILTER                             |      |       |       |            |          |
|*  6 |     TABLE ACCESS FULL                 | T2   |     2 |   228 |   311   (1)| 00:00:01 |
|*  7 |    FILTER                             |      |       |       |            |          |
|*  8 |     TABLE ACCESS FULL                 | T3   |     2 |   228 |   311   (1)| 00:00:01 |
|   9 |    TABLE ACCESS BY INDEX ROWID BATCHED| T4   |   901 |   100K|   645   (0)| 00:00:01 |
|* 10 |     INDEX RANGE SCAN                  | T4IX |   901 |       |     8   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   3 - filter(NULL IS NOT NULL)
   4 - filter("T1"."OBJECT_NAME"='T4')
   5 - filter(NULL IS NOT NULL)
   6 - filter("T2"."OBJECT_NAME"='T4')
   7 - filter(NULL IS NOT NULL)
   8 - filter("T3"."OBJECT_NAME"='T4')
  10 - access("T4"."OBJECT_NAME"='T4')
  
  


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library