Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Stephen.

Asked: March 01, 2017 - 8:38 pm UTC

Last updated: March 07, 2017 - 3:15 am UTC

Version: 11

Viewed 1000+ times

You Asked

Hi Connor,

Thank you for your response to my question. I have a follow up question about the consolidated view. I did the following suggestion:

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

t3 and t4 tables each have about 500M rows. However I observe that 
  select * from V where tname='T4" and V.col1 = 'foo'
is slower than
  select * from t4 where t4.col1 = 'foo' 
-- note table t4 has an index


Is this to be expected or am I missing something?

Here's more details where a query to the consolidated view is slower in execution than doing the equivalent directly on the base table so 

>EXPLAIN PLAN SET STATEMENT_ID = 'testview' FOR select count(*) from p4_1777_rev where dfile is not null
>SELECT * FROM TABLE(dbms_xplan.display)

Plan hash value: 2349164066
 
----------------------------------------------------------------------------------------------
| Id  | Operation                | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                   |     1 |   517 |  2285K  (1)| 07:37:12 |
|   1 |  SORT AGGREGATE          |                   |     1 |   517 |            |          |
|   2 |   VIEW                   | P4_REV_VIEW       |   628M|   302G|  2285K  (1)| 07:37:12 |
|   3 |    UNION-ALL             |                   |       |       |            |          |
|*  4 |     FILTER               |                   |       |       |            |          |
|   5 |      INDEX FAST FULL SCAN| P4_1666_REV_DFILE |   519M|    45G|  1674K  (1)| 05:34:51 |
|*  6 |     FILTER               |                   |       |       |            |          |
|   7 |      INDEX FAST FULL SCAN| P4_1700_REV_DFILE |    22M|  2010M| 73400   (1)| 00:14:41 |
|   8 |     INDEX FAST FULL SCAN | P4_1777_REV_DFILE |   628M|    62G|  2285K  (1)| 07:37:12 |
|*  9 |     FILTER               |                   |       |       |            |          |
|  10 |      INDEX FAST FULL SCAN| P4_1888_REV_DFILE |   120M|  9770M|   354K  (1)| 01:10:59 |
|* 11 |     FILTER               |                   |       |       |            |          |
|  12 |      INDEX FAST FULL SCAN| P4_1999_REV_DFILE |    76M|  9140M|   321K  (1)| 01:04:16 |
|* 13 |     FILTER               |                   |       |       |            |          |
|  14 |      INDEX FAST FULL SCAN| P4_7004_REV_DFILE |    27M|  1889M| 70084   (1)| 00:14:02 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter(NULL IS NOT NULL)
   6 - filter(NULL IS NOT NULL)
   9 - filter(NULL IS NOT NULL)
  11 - filter(NULL IS NOT NULL)
  13 - filter(NULL IS NOT NULL)

vs.

>EXPLAIN PLAN SET STATEMENT_ID = 'testview' FOR select count(*) from p4_1777_rev where dfile is not null
>SELECT * FROM TABLE(dbms_xplan.display)

Plan hash value: 4011998830

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |   297K  (2)| 00:59:26 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C00288006 |   628M|   297K  (2)| 00:59:26 |
-------------------------------------------------------------------------------


The number of rows in the base table p4_1777_rev is 632M.
The number of rows in the consolidated view p4_rev_view is 2B.
The consolidated view p4_rev_view was created as:
"SELECT 1666 P4PORT, p1666."REVID",p1666."DFILE",p1666."REV",p1666."TYPE",p1666."ACTION",p1666."CHANGE",p1666."DATE",p1666."MODTIME" from P4_1666_REV p1666
union all
SELECT 1700 P4PORT, p1700."REVID",p1700."DFILE",p1700."REV",p1700."TYPE",p1700."ACTION",p1700."CHANGE",p1700."DATE",p1700."MODTIME" from P4_1700_REV p1700
union all
SELECT 1777 P4PORT, p1777."REVID",p1777."DFILE",p1777."REV",p1777."TYPE",p1777."ACTION",p1777."CHANGE",p1777."DATE",p1777."MODTIME" from P4_1777_REV p1777
union all
SELECT 1888 P4PORT, p1888."REVID",p1888."DFILE",p1888."REV",p1888."TYPE",p1888."ACTION",p1888."CHANGE",p1888."DATE",p1888."MODTIME" from P4_1888_REV p1888
union all
SELECT 1999 P4PORT, p1999."REVID",p1999."DFILE",p1999."REV",p1999."TYPE",p1999."ACTION",p1999."CHANGE",p1999."DATE",p1999."MODTIME" from P4_1999_REV p1999
union all
SELECT 7004 P4PORT, p7004."REVID",p7004."DFILE",p7004."REV",p7004."TYPE",p7004."ACTION",p7004."CHANGE",p7004."DATE",p7004."MODTIME" from P4_7004_REV p7004"




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 we 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

and Connor said...

"am I missing something?"

Yes :-)

Your script showing us the performance and response time of each test !

Show us:

set timing on
set autotrace on
select * from V where tname='T4" and V.col1 = 'foo'
select * from t4 where t4.col1 = 'foo'


=======================

You havent shown execution, you have show execution plan. The execution will *look* more complicated because we're doing filters etc, but see the "null is not null" lines - meaning that most of the stuff will never be run.

*Run* them, time the results

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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