Here's are 2 runs with timings as requested. In the second case (count) is there an overhead execution cost on my system associated with the union of the tables to build the view regardless of the scan optimization?
RUN CASE 1:
set timing on
set autotrace on
select * from p4_rev_view where p4port=1777 and dfile = 'foo';
select * from p4_1777_rev where dfile = 'foo';
Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
no rows selected
Elapsed: 00:00:00.158
Plan hash value: 1254424064
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 3900 | 6 (0)| 00:00:01 |
| 1 | VIEW | P4_REV_VIEW | 6 | 3900 | 6 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| P4_1666_REV | 1 | 147 | 6 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | P4_1666_REV_DFILE | 1 | | 5 (0)| 00:00:01 |
|* 6 | FILTER | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| P4_1700_REV | 1 | 142 | 5 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | P4_1700_REV_DFILE | 1 | | 4 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | P4_1777_REV | 1 | 159 | 6 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | P4_1777_REV_DFILE | 1 | | 5 (0)| 00:00:01 |
|* 11 | FILTER | | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID| P4_1888_REV | 1 | 138 | 6 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | P4_1888_REV_DFILE | 1 | | 5 (0)| 00:00:01 |
|* 14 | FILTER | | | | | |
| 15 | TABLE ACCESS BY INDEX ROWID| P4_1999_REV | 2 | 354 | 6 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | P4_1999_REV_DFILE | 2 | | 5 (0)| 00:00:01 |
|* 17 | FILTER | | | | | |
| 18 | TABLE ACCESS BY INDEX ROWID| P4_7004_REV | 2 | 246 | 5 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | P4_7004_REV_DFILE | 2 | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NULL IS NOT NULL)
5 - access("P1666"."DFILE"='foo')
6 - filter(NULL IS NOT NULL)
8 - access("P1700"."DFILE"='foo')
10 - access("P1777"."DFILE"='foo')
11 - filter(NULL IS NOT NULL)
13 - access("P1888"."DFILE"='foo')
14 - filter(NULL IS NOT NULL)
16 - access("P1999"."DFILE"='foo')
17 - filter(NULL IS NOT NULL)
19 - access("P7004"."DFILE"='foo')
Statistics
-----------------------------------------------------------
5 DB time
15 Requests to/from client
15 SQL*Net roundtrips to/from client
1 buffer is not pinned count
406 bytes received via SQL*Net from client
27325 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
49152 cell physical IO interconnect bytes
6 consistent gets
5 consistent gets - examination
6 consistent gets from cache
2 execute count
52057 file io wait time
6 free buffer requested
1 index scans kdiixs1
49152 logical read bytes from cache
1 no work - consistent read gets
21 non-idle wait count
5 non-idle wait time
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
6 physical read IO requests
49152 physical read bytes
6 physical read total IO requests
49152 physical read total bytes
6 physical reads
6 physical reads cache
1 session cursor cache count
6 session logical reads
5 shared hash latch upgrades - no wait
1 sorts (memory)
679 sorts (rows)
5 user I/O wait time
16 user calls
no rows selected
Elapsed: 00:00:00.041
Plan hash value: 3883277224
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 316 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| P4_1777_REV | 1 | 316 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | P4_1777_REV_DFILE | 1 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DFILE"='foo')
Statistics
-----------------------------------------------------------
16 Requests to/from client
15 SQL*Net roundtrips to/from client
1 buffer is not pinned count
390 bytes received via SQL*Net from client
27581 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
6 consistent gets
5 consistent gets - examination
6 consistent gets from cache
1 consistent gets from cache (fastpath)
2 execute count
1 index scans kdiixs1
49152 logical read bytes from cache
1 no work - consistent read gets
15 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
2 parse count (total)
1 session cursor cache count
6 session logical reads
1 sorts (memory)
679 sorts (rows)
16 user calls
RUN CASE 2:
set timing on
set autotrace on
select count(*) from p4_rev_view where p4port=1777;
select count(*) from p4_1777_rev;
Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
COUNT(*)
----------
632812903
Elapsed: 00:00:25.461
Plan hash value: 1111651613
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 297K (2)| 00:59:26 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | VIEW | P4_REV_VIEW | 628M| 1798M| 297K (2)| 00:59:26 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
| 5 | INDEX FAST FULL SCAN| SYS_C00287738 | 519M| | 244K (2)| 00:48:55 |
|* 6 | FILTER | | | | | |
| 7 | INDEX FAST FULL SCAN| SYS_C00287594 | 22M| | 10156 (3)| 00:02:02 |
| 8 | INDEX FAST FULL SCAN | SYS_C00288006 | 628M| | 297K (2)| 00:59:26 |
|* 9 | FILTER | | | | | |
| 10 | INDEX FAST FULL SCAN| SYS_C00287714 | 120M| | 54797 (2)| 00:10:58 |
|* 11 | FILTER | | | | | |
| 12 | INDEX FAST FULL SCAN| SYS_C00287688 | 76M| | 34160 (3)| 00:06:50 |
|* 13 | FILTER | | | | | |
| 14 | INDEX FAST FULL SCAN| SYS_C00287570 | 27M| | 12461 (3)| 00:02:30 |
------------------------------------------------------------------------------------------
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)
Statistics
-----------------------------------------------------------
2542 CPU used by this session
2542 CPU used when call started
2541 DB time
16 Requests to/from client
1336649 consistent gets
1336649 consistent gets from cache
1336649 consistent gets from cache (fastpath)
15 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
1336649 session logical reads
16 user calls
COUNT(*)
----------
632812903
Elapsed: 00:00:14.093
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 |
-------------------------------------------------------------------------------
Statistics
-----------------------------------------------------------
1408 CPU used by this session
1408 CPU used when call started
1408 DB time
4 Requests to/from client
1336649 consistent gets
1336649 consistent gets from cache
1336649 consistent gets from cache (fastpath)
3 non-idle wait count
2 opened cursors cumulative
1 opened cursors current
1 pinned cursors current
1336649 session logical reads
4 user calls
=======================
Follow information to 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
Now here is the additional information as requested. Thanks.
set timing on
set autotrace on
select count(*) from p4_rev_view where p4port=1777 and dfile is not null;
select count(*) from p4_1777_rev where dfile is not null;
Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
COUNT(*)
----------
632336315
Elapsed: 00:19:33.474
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)
Statistics
-----------------------------------------------------------
11958 CPU used by this session
11958 CPU used when call started
117328 DB time
3 Requests to/from client
4 messages sent
667371 non-idle wait count
108977 non-idle wait time
2 opened cursors cumulative
1 opened cursors current
667368 physical read total IO requests
665866 physical read total multi block requests
1 pinned cursors current
10720344 session logical reads
108977 user I/O wait time
4 user calls
COUNT(*)
----------
632336315
Elapsed: 00:03:10.676
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 |
-------------------------------------------------------------------------------
Statistics
-----------------------------------------------------------
2344 CPU used by this session
2344 CPU used when call started
19062 DB time
4 Requests to/from client
1 enqueue releases
1 enqueue requests
83628 non-idle wait count
17168 non-idle wait time
2 opened cursors cumulative
1 opened cursors current
83628 physical read total IO requests
83277 physical read total multi block requests
1 pinned cursors current
1 recursive calls
1336153 session logical reads
17168 user I/O wait time
4 user calls
---------------------------------------------------------------------------------------------------------------------------
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 we 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