Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Kai.

Asked: October 29, 2008 - 5:06 am UTC

Answered by: Tom Kyte - Last updated: August 01, 2017 - 12:38 am UTC

Category: Database - Version: 10.2.0.4

Viewed 1000+ times

You Asked

Hello Tom,

I'm working at a client's site who has a data warehouse with partitioned fact tables with about 400 columns. Performance is not too good and I am trying to optimize the system a bit.
The client told me that most of the columns are queried rarely if ever but they want to store the data anyway in case it is ever needed, so my idea for speeding up the inevitable FTS by dropping all never used columns was rejected. Obviously I could store a redundant set of skinnier tables and use compression on that tables to reduce the size even more, but storage space is an issue (as in "we can't afford any more storage") and users would have to know which table to query for which column. They want only ETL logic in the database but no query logic at all because "that's what we have our BI tools for", so - do you have any suggestions on how to tune such a database?

Regards
Kai

and we said...

... "we can't afford any more storage" ...

well, if they want performance - they just might need to rethink that.

However, you might be able to use vertical partitioning here - you will add a new primary key constraint - which will result in a index (but in general, you'll want that index to put back to the results sometimes!)

If you tell Oracle there is a 1:1 mandatory relationship between the two tables, we can do table elimination in the plan. In order to be 1:1 mandatory - there will be a primary key on the join column and there will be reciprocal foreign keys (which can be validated or not, if you load the data clean, it'll be OK to just tell us they are there, this example does that, just tells the database 'foreign key exists'


ops$tkyte%ORA10GR2> create table t1 as select
  2  OBJECT_ID,
  3  OWNER, OBJECT_NAME, SUBOBJECT_NAME,
  4  DATA_OBJECT_ID, OBJECT_TYPE
  5  from all_objects where 1=0;

Table created.

ops$tkyte%ORA10GR2> alter table t1 add constraint t1_pk primary key(object_id) rely;

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t2 as select
  2  OBJECT_ID,
  3  CREATED, LAST_DDL_TIME, TIMESTAMP,
  4  STATUS, TEMPORARY, GENERATED, SECONDARY
  5  from all_objects where 1=0;

Table created.

ops$tkyte%ORA10GR2> alter table t2 add constraint t2_pk primary key(object_id) rely;

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T1', numrows => 100000000, numblks => 1000000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T2', numrows => 100000000, numblks => 1000000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace view vw
  2  as
  3  select
  4  t1.OBJECT_ID,
  5  t1.OWNER, t1.OBJECT_NAME, t1.SUBOBJECT_NAME,
  6  t1.DATA_OBJECT_ID, t1.OBJECT_TYPE,
  7  t2.CREATED, t2.LAST_DDL_TIME, t2.TIMESTAMP,
  8  t2.STATUS, t2.TEMPORARY, t2.GENERATED, t2.SECONDARY
  9  from t1, t2
 10  where t1.object_id = t2.object_id;

View created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t2 add constraint t2_fk_t1 foreign key(object_id) references t1(object_id) rely disable novalidate;

Table altered.

ops$tkyte%ORA10GR2> alter table t1 add constraint t1_fk_t2 foreign key(object_id) references t2(object_id) rely disable novalidate;

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from vw;

Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835

------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)|
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100M|    13G|       |  1490K  (3)|
|*  1 |  HASH JOIN         |      |   100M|    13G|  6198M|  1490K  (3)|
|   2 |   TABLE ACCESS FULL| T2   |   100M|  5054M|       |   317K  (4)|
|   3 |   TABLE ACCESS FULL| T1   |   100M|  8392M|       |   316K  (4)|
------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

ops$tkyte%ORA10GR2> select
  2  OWNER, OBJECT_NAME, SUBOBJECT_NAME,
  3  DATA_OBJECT_ID, OBJECT_TYPE from vw;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100M|  8392M|   316K  (4)| 00:25:03
|   1 |  TABLE ACCESS FULL| T1   |   100M|  8392M|   316K  (4)| 00:25:03
------------------------------------------------------------------------

ops$tkyte%ORA10GR2> @atoff
ops$tkyte%ORA10GR2> set autotrace off


Note: this unnecessary table elimination was new in 10gr2....


and yes, use COMPRESS on the tables!

and you rated our response

  (14 ratings)

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

Reviews

October 29, 2008 - 11:37 am UTC

Reviewer: Kai from Germany

Great, this approach should work. It can even be made transparent to the application.

However:

INSERT INTO vw
(object_id, owner, object_name, subobject_name, data_object_id,
object_type, created, last_ddl_time, TIMESTAMP, status,
TEMPORARY, GENERATED, secondary)
SELECT object_id, owner, object_name, subobject_name, data_object_id,
object_type, created, last_ddl_time, TIMESTAMP, status, TEMPORARY,
GENERATED, secondary
FROM all_objects
WHERE 1 = 0;

ORA-01776: cannot modify more than one base table through a join view

INSERT INTO vw
(object_id, owner, object_name, subobject_name, data_object_id,
object_type)
SELECT object_id, owner, object_name, subobject_name, data_object_id,
object_type
FROM all_objects
WHERE 1 = 0;

0 rows inserted

If Oracle could be coerced somehow to recognize the mandatory 1:1 relationship so it wouldn't throw the ORA-01776 it would be perfect. Do you think this would be worth an enhancement request?

But for now, I can just INSERT ALL into the tables...works for me.
Tom Kyte

Followup  

October 29, 2008 - 1:43 pm UTC

right, your ETL process WILL HAVE TO CHANGE, no doubt about that.

Tell your customer "sorry, but we lost the ability to perform magic in the middle ages - right after the time of Merlin. Therefore, we have to utilize real life facts..."

Given that we can already do this via:

a) instead of triggers (do not do this)
b) insert ALL - a multi table insert

I doubt there would be much interest.

October 30, 2008 - 10:21 am UTC

Reviewer: Kai from Germany

As I've said...no problem with the INSERT ALL approach, being able to insert into a join view iff it is a 1:1 mandatory join would just have been icing on the cake.

I've started experimenting with introducing vertical partitioning into the existing horizontal partitioning and have seen a curious effect.

This extends your demonstration from above by partitioning the tables T1 and T2:

create table t1
PARTITION BY LIST (RUN_ID)
(
PARTITION P_1 VALUES (1),
PARTITION P_2 VALUES (2)
)
as select
1 AS run_id,OBJECT_ID,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
DATA_OBJECT_ID, OBJECT_TYPE
from all_objects where 1=0;

CREATE UNIQUE INDEX REPORTING.T1_PK ON REPORTING.T1
(RUN_ID, OBJECT_ID) LOCAL;

alter table t1 add constraint t1_pk primary key(run_id, object_id) rely;


create table t2
PARTITION BY LIST (RUN_ID)
(
PARTITION P_1 VALUES (1),
PARTITION P_2 VALUES (2)
)
as select
1 AS run_id,OBJECT_ID,
CREATED, LAST_DDL_TIME, TIMESTAMP,
STATUS, TEMPORARY, GENERATED, SECONDARY
from all_objects where 1=0;

CREATE UNIQUE INDEX REPORTING.T2_PK ON REPORTING.T2
(RUN_ID, OBJECT_ID) LOCAL;

alter table t2 add constraint t2_pk primary key(run_id,object_id) rely;

exec dbms_stats.set_table_stats( user, 'T1', numrows => 200000000, numblks => 2000000 );

exec dbms_stats.set_table_stats( user, 'T1', numrows => 100000000, partname => 'P_1', numblks => 1000000 );

exec dbms_stats.set_table_stats( user, 'T1', numrows => 100000000, partname => 'P_2', numblks => 1000000 );

exec dbms_stats.set_table_stats( user, 'T2', numrows => 200000000, numblks => 2000000 );

exec dbms_stats.set_table_stats( user, 'T2', numrows => 100000000, partname => 'P_1', numblks => 1000000 );

exec dbms_stats.set_table_stats( user, 'T2', numrows => 100000000, partname => 'P_2', numblks => 1000000 );

create or replace view vw
as select
t1.run_id,
t1.OBJECT_ID,
t1.OWNER, t1.OBJECT_NAME, t1.SUBOBJECT_NAME,
t1.DATA_OBJECT_ID, t1.OBJECT_TYPE,
t2.CREATED, t2.LAST_DDL_TIME, t2.TIMESTAMP,
t2.STATUS, t2.TEMPORARY, t2.GENERATED, t2.SECONDARY
from t1, t2
where t1.run_id = t2.run_id AND t1.object_id = t2.object_id;

alter table t2 add constraint t2_fk_t1 foreign key(run_id,object_id)
references t1(run_id,object_id) rely disable novalidate;

alter table t1 add constraint t1_fk_t2 foreign key(run_id,object_id)
references t2(run_id,object_id) rely disable novalidate;

select
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
DATA_OBJECT_ID, OBJECT_TYPE from vw where run_id=1;

Ausführungsplan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=44314 Card=1000000
Bytes=126000000)

1 0 NESTED LOOPS (Cost=44314 Card=1000000 Bytes=126000000)
2 1 PARTITION LIST (SINGLE) (Cost=4381 Card=1000000 Bytes=10
0000000)

3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T1' (TABLE) (C
ost=4381 Card=1000000 Bytes=100000000)

4 3 INDEX (RANGE SCAN) OF 'T1_PK' (INDEX (UNIQUE)) (Cost
=2 Card=200000)

5 1 PARTITION LIST (SINGLE) (Cost=0 Card=1 Bytes=26)
6 5 INDEX (UNIQUE SCAN) OF 'T2_PK' (INDEX (UNIQUE)) (Cost=
0 Card=1 Bytes=26)

As you can see, the access to T2 is not eliminated any more, there is an index scan of T2's primary key index in the plan. Is this a limitation of table elimination or am I doing anything wrong?
Tom Kyte

Followup  

November 02, 2008 - 2:54 pm UTC

... would just have been icing on the cake. ...

and that is why I said "probably not", given that there are ways to accomplish this already.



did you notice, if you remove partitioning from the example you have - it is the same (that is, partitioning is a 'red herring' - change one thing at a time to see what is really 'the difference')

I'm traveling all week, but when I get back, I have the test case, will play more with it and probably file a bug (it is the compound key... not partitioning)

indexing?

October 30, 2008 - 1:24 pm UTC

Reviewer: Dan from MI

As an alternative to creating new skinny tables, you could create index(es) on the commonly used columns. Then if Oracle can, it will avoid the table entirely and just get the information from the index, completely transparent to the end-user.

This will obviously require some more space, but with how cheap storage is...

Vertical Partitioning?

November 03, 2008 - 6:44 am UTC

Reviewer: Sam

Hi Tom,

I thought you don't favor vertical partitioning.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3361148209692#1274564800346125150

Regards,
Sam
Tom Kyte

Followup  

November 11, 2008 - 4:48 pm UTC

never say never
never say always
I always say

if something should always be done a certain way - there wouldn't be any other way to do it (eg: if you should ALWAYS bind - we would not permit literals of any sort in sql - it would FORCE you to bind)

if something should usually not be done a certain way - that doesn't mean it should never be done.

Take the question above where I suggested vertical partitioning in context, in the entire context of the question - there vertical partitioning and the "evil of the join" (which is why you almost always want to avoid it) provides concrete benefit.

Never rule out ANYTHING.
Never always do someTHING in one particular way.

Everything is a tool, understanding what the pro's and con's are lets you pick which tool to use to attack any given problem.

I like that line...

November 11, 2008 - 12:00 pm UTC

Reviewer: A.Non

> Never always do someone in one particular way
^^^^^^^
:-P
Tom Kyte

Followup  

November 11, 2008 - 4:48 pm UTC

doh.... blush.

ok, so i fixed that above...

Update of view

May 27, 2010 - 12:50 pm UTC

Reviewer: Sujit from USA

Tom,
While doing an update in the view , I do see it is doing a nested loop between two underline table , though the update statement has only the columns from one table.
That cause the update slower than having a single table.

Do you have any workaround for this , so that we don't have to change a huge pile of code.

update shipment_header set pol_fk=pol_fk where shipment_header_pk=9109061704

Plan hash value: 1681204651

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 5 (100)| |
| 1 | UPDATE | SHIPMENT_HEADER | | | | |
| 2 | VIEW | SHIPMENT_HEADER | 1 | 1422 | 5 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 299 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| SHIPMENT_HEADER_EXTN | 1 | 131 | 3 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | SHIPMENT_HEADER_EXTN_PK | 1 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| SHIPMENT_HEADER_BASE | 1 | 168 | 2 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SHHDB_PK | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

5 - access("T2"."SHIPMENT_HEADER_PK"=9109061704)
7 - access("T1"."SHIPMENT_HEADER_PK"=9109061704)
Tom Kyte

Followup  

May 27, 2010 - 12:55 pm UTC

Sujit,

change places with me for a second. Suppose I posted this to you - what would you be able to do with this?

since you don't know the tables?
since you don't know the versions?
since you don't know the constraints in place?

since you don't know what the work to do the update of the join is as compared to the update without the join (eg: have I proven, observed, measured what the performance would be without the joining going on?? would it make that much of a materialial difference).

since you don't know how ofen this work is done.
etc.

Actually when I see this query, I'd say the fastest way to execute it would be:

<this space left intentionally blank>


Look at it, update t set x=x where y = something;

just don't do it maybe?



and you don't even want to know what I think about statements like this:
... so that we don't have to change a huge
pile of code. ....



Update of view

May 27, 2010 - 1:21 pm UTC

Reviewer: Sujit from USA

Tom,
  I am really sorry , I should have been more clear with the example , I know how important your time is.  I´m deeply ashamed…for this.

Here are the details:
SQL> select * from v$version;

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

Example copied from original question:

 create table t1 as select
    OBJECT_ID,
    OWNER, OBJECT_NAME, SUBOBJECT_NAME,
    DATA_OBJECT_ID, OBJECT_TYPE
    from all_objects where 1=0;

alter table t1 add constraint t1_pk primary key(object_id) rely;

 create table t2 as select
    OBJECT_ID,
    CREATED, LAST_DDL_TIME, TIMESTAMP,
    STATUS, TEMPORARY, GENERATED, SECONDARY
    from all_objects where 1=0;

 alter table t2 add constraint t2_pk primary key(object_id) rely;

 exec dbms_stats.set_table_stats( user, 'T1', numrows => 100000000, numblks => 1000000 );

 exec dbms_stats.set_table_stats( user, 'T2', numrows => 100000000,numblks => 1000000 );

 create or replace view vw
    as
    select
    t1.OBJECT_ID,
    t1.OWNER, t1.OBJECT_NAME, t1.SUBOBJECT_NAME,
    t1.DATA_OBJECT_ID, t1.OBJECT_TYPE,
    t2.CREATED, t2.LAST_DDL_TIME, t2.TIMESTAMP,
    t2.STATUS, t2.TEMPORARY, t2.GENERATED, t2.SECONDARY
    from t1, t2
   where t1.object_id = t2.object_id;

 alter table t2 add constraint t2_fk_t1 foreign key(object_id) references t1(object_id) rely disable novalidate;

 alter table t1 add constraint t1_fk_t2 foreign key(object_id) references t2(object_id) rely disable novalidate;

 select * from vw;

 select * from v$sql where lower(sql_text) like '%select * from vw%'
select * from table(dbms_xplan.display_cursor('47kcqsnb4urc4'))

PLAN_TABLE_OUTPUT
SQL_ID  47kcqsnb4urc4, child number 0
-------------------------------------
select * from vw
 
Plan hash value: 2959412835
 
-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |       |  1753K(100)|          |
|*  1 |  HASH JOIN         |      |   100M|    13G|  6198M|  1753K  (2)| 05:50:46 |
|   2 |   TABLE ACCESS FULL| T2   |   100M|  5054M|       |   277K  (3)| 00:55:31 |
|   3 |   TABLE ACCESS FULL| T1   |   100M|  8392M|       |   276K  (3)| 00:55:22 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
 


 select
    OWNER, OBJECT_NAME, SUBOBJECT_NAME,
    DATA_OBJECT_ID, OBJECT_TYPE from vw;
 
 select * from v$sql where sql_text like '%OBJECT_TYPE from vw%'
select * from table(dbms_xplan.display_cursor('g33kchy3smsxg')) 

PLAN_TABLE_OUTPUT
SQL_ID  g33kchy3smsxg, child number 0
-------------------------------------
select     OWNER, OBJECT_NAME, SUBOBJECT_NAME,     DATA_OBJECT_ID, 
OBJECT_TYPE from vw
 
Plan hash value: 3617692013
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   276K(100)|          |
|   1 |  TABLE ACCESS FULL| T1   |   100M|  8392M|   276K  (3)| 00:55:22 |
--------------------------------------------------------------------------
 

***************************************************************
So everything work as expected till this point......Now when I do the update

**********************************************************
update vw set SUBOBJECT_NAME=SUBOBJECT_NAME where object_id=1

 select * from v$sql where lower(sql_text) like '%update vw set %'
select * from table(dbms_xplan.display_cursor('6203dqd2bs6b0'))
PLAN_TABLE_OUTPUT
SQL_ID  6203dqd2bs6b0, child number 0
-------------------------------------
update vw set SUBOBJECT_NAME=SUBOBJECT_NAME where object_id=1
 
Plan hash value: 3817222631
 
-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |       |       |       |     2 (100)|          |
|   1 |  UPDATE             | T1    |       |       |            |          |
|   2 |   NESTED LOOPS      |       |     1 |    43 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN| T1_PK |     1 |    30 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| T2_PK |     1 |    13 |     0   (0)|          |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T1"."OBJECT_ID"=1)
   4 - access("T2"."OBJECT_ID"=1)
 

   In the above explain plan you can see its refereing to T2_PK ,and a nested loop ,  though the update statement only has reference to T1 columns.
   
   
   This nested loop and scan to T2_PK make the update of T1 slower and more costly than if it would have been a table instead of  a view.
   
   My question , is there anyway to have the update plan behave like SELECT plan , that is no reference to T2 , if we are updating only the columns those are belong to T1?

Tom Kyte

Followup  

May 27, 2010 - 1:52 pm UTC

... This nested loop and scan to T2_PK make the update of T1 slower and more
costly than if it would have been a table instead of a view.
...

yeah, but, it is one row - unless you are doing this a million times, like I said above "will it make a difference".

and if you are doing it a million times, than any speed you save here will be overshadowed by the speed you would get by doing it right in the first place (a bulky process, not slow by slow).


You haven't done the other bit I asked for :)


since you don't know what the work to do the update of the join is as compared to the update without the join (eg: have I proven, observed, measured what the performance would be without the joining going on?? would it make that much of a materialial difference).

since you don't know how ofen this work is done.


the update isn't doing the table removal - I don't see a way to avoid the second table without a recode of the query.

(now you know why I like to have 1:1 relations as a singe table... well, one of the reasons anyway)

summary

May 27, 2010 - 7:30 pm UTC

Reviewer: babloo from usa

Hi Tom,
the original question( from kai).
Did I understand the summary of your ansers correctly ?
break it into two tables t1 and t2 and then create a view to keep it tranparent to users.
select will be optimized to get data from needed table only but updates will still scan the both tables .
I could not underdtand the second part. whey oracle goes to second part. can you please explain to us?

Tom Kyte

Followup  

May 28, 2010 - 7:40 am UTC

because it currently does, the optimization for table elimination seems to be in effect for SELECT plans only.

June 04, 2010 - 3:28 am UTC

Reviewer: David Aldridge

If extra space were available then a materialized view on the commonly queries columns could be a handy performance tool here.

Similar problem to Kai..(table elimination not working when horizontal partitioned)..Oct 30, 2008

December 04, 2012 - 12:31 am UTC

Reviewer: Prashant from Mumbai, India

The scripts are mostly similar to your case with slight modifications for partitioning. I am sharing the same.

The version of our database is 11.2.0.2.0

--table 1
create table t1_sdw_part
partition by list(object_type)
(
partition t1_1 values('TABLE','TABLE PARTITION','TABLE SUBPARTITION'),
partition t1_2 values('INDEX','INDEX PARTITION','INDEX SUBPARTITION'),
partition t1_other values (DEFAULT)
)
as select OBJECT_ID,OWNER, OBJECT_NAME, SUBOBJECT_NAME,DATA_OBJECT_ID, OBJECT_TYPE
from all_objects where 1=2;

--table 2
create table t2_sdw_part
PARTITION BY LIST (object_type)
(
partition t2_1 values('TABLE','TABLE PARTITION','TABLE SUBPARTITION'),
partition t2_2 values('INDEX','INDEX PARTITION','INDEX SUBPARTITION'),
partition t2_other values (DEFAULT)
)
as select OBJECT_ID,OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP,
STATUS, TEMPORARY, GENERATED, SECONDARY from all_objects where 1=2;

--stats
exec dbms_stats.set_table_stats( user, 'T1_SDW_PART', numrows => 300000000, numblks => 3000000 );
exec dbms_stats.set_table_stats( user, 'T1_SDW_PART', numrows => 100000000, partname => 'T1_1', numblks => 1000000 );
exec dbms_stats.set_table_stats( user, 'T1_SDW_PART', numrows => 100000000, partname => 'T1_2', numblks => 1000000 );
exec dbms_stats.set_table_stats( user, 'T1_SDW_PART', numrows => 100000000, partname => 'T1_OTHER', numblks => 1000000 );
exec dbms_stats.set_table_stats( user, 'T2_SDW_PART', numrows => 300000000, numblks => 3000000 );
exec dbms_stats.set_table_stats( user, 'T2_SDW_PART', numrows => 100000000, partname => 'T2_1', numblks => 1000000 );
exec dbms_stats.set_table_stats( user, 'T2_SDW_PART', numrows => 100000000, partname => 'T2_2', numblks => 1000000 );
exec dbms_stats.set_table_stats( user, 'T2_SDW_PART', numrows => 100000000, partname => 'T2_OTHER', numblks => 1000000 );

--constraints
alter table t1_sdw_part add constraint t1_sdw_part_pk primary key(object_id) rely disable novalidate;
alter table t2_sdw_part add constraint t2_sdw_part_pk primary key(object_id) rely disable novalidate;

alter table t2_sdw_part add constraint t2_fk_t1_sdw_part foreign key(object_id) references t1_sdw_part(object_id) rely disable novalidate;
alter table t1_sdw_part add constraint t1_fk_t2_sdw_part foreign key(object_id) references t2_sdw_part(object_id) rely disable novalidate;

--view
create or replace view vw_sdw_part
as select
t1.OBJECT_ID,
t1.OWNER, t1.OBJECT_NAME, t1.SUBOBJECT_NAME,
t1.DATA_OBJECT_ID, t1.OBJECT_TYPE,
t2.CREATED, t2.LAST_DDL_TIME, t2.TIMESTAMP,
t2.STATUS, t2.TEMPORARY, t2.GENERATED, t2.SECONDARY
from t1_sdw_part t1, t2_sdw_part t2
where t1.object_id = t2.object_id and t1.object_type=t2.object_type;

explain plan for select * from vw_sdw_part where object_type = 'TABLE';
select * from table(dbms_xplan.display(NULL,NULL,'BASIC PARTITION ROWS BYTES PREDICATE'));

----------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes |TempSpc| Pstart| Pstop |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |  1000K|   144M|       |       |       |
|*  1 |  HASH JOIN               |             |  1000K|   144M|    72M|       |       |
|   2 |   PART JOIN FILTER CREATE| :BF0000     |  1000K|    61M|       |       |       |
|   3 |    PARTITION LIST SINGLE |             |  1000K|    61M|       |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL    | T2_SDW_PART |  1000K|    61M|       |     1 |     1 |
|   5 |   PARTITION LIST SINGLE  |             |  1000K|    83M|       |KEY(AP)|KEY(AP)|
|*  6 |    TABLE ACCESS FULL     | T1_SDW_PART |  1000K|    83M|       |     1 |     1 |
----------------------------------------------------------------------------------------

explain plan for select OWNER, OBJECT_NAME, SUBOBJECT_NAME,
DATA_OBJECT_ID from vw_sdw_part where object_type = 'TABLE';
select * from table(dbms_xplan.display(NULL,NULL,'BASIC PARTITION ROWS BYTES PREDICATE'));

----------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes |TempSpc| Pstart| Pstop |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |  1000K|   106M|       |       |       |
|*  1 |  HASH JOIN               |             |  1000K|   106M|    34M|       |       |
|   2 |   PART JOIN FILTER CREATE| :BF0000     |  1000K|    22M|       |       |       |
|   3 |    PARTITION LIST SINGLE |             |  1000K|    22M|       |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL    | T2_SDW_PART |  1000K|    22M|       |     1 |     1 |
|   5 |   PARTITION LIST SINGLE  |             |  1000K|    83M|       |KEY(AP)|KEY(AP)|
|*  6 |    TABLE ACCESS FULL     | T1_SDW_PART |  1000K|    83M|       |     1 |     1 |
----------------------------------------------------------------------------------------

In the above case all the columns are selected from first table but still I get a join with second table. I have tried without partitioning (exactly similar to your example) and it works fine.

Out of frustration I played around with various options but still couldn't get it to work
a) Added PKEY to constraint
b) Removed object type join from view definition and kept the constraint only on object id.
c) Changed hidden parameters on predicate push down and transitive closure etc

Please guide.

Thanks
Prashant
Tom Kyte

Followup  

December 04, 2012 - 9:48 am UTC

what if I put

OBJECT_ID=1, object_type=table in T1

OBJECT_ID=1, object_type=foobar in T2

it would conform to your data model, it would not able to be processed using just T1, we *have* to probe into T2 to see not only if that object_id exists - but that that object id exists AND has object_type = TABLE!!!


think about it - how could we drop T2 from this???

your example is not like mine.
when you did your single table example you did something entirely different!


Ok. Let me re-phrase

December 04, 2012 - 9:09 pm UTC

Reviewer: Prashant from Mumbai, India

I have a "fat" table with 170 columns (average row length of 1600 bytes) and its partitioned on a key (in the above example that key was object_type). The primary key on this table lets say is object_id.

My frequently used (90%) reports uses 50 columns out of 170 contributing to an average row length of 200 bytes. The other 120 columns contribute to 1400 bytes which may be used for ad-hoc reports. 200/1600 is significant improvement. Hence I wanted to use this vertical partitioning example to benefit those 90% cases.

When I test with 2 tables (T1 with 50 columns and T2 with remaining 120 colums and a view) without partitioning and object_id as the primary key. I am able to eliminate second table T2 for 90% of those cases i.e. canned reports.

However since my current table is partitioned I had to partition both T1 and T2 and test it.

I tested with various scenarios for creating constraints and view definition i.e. with or without object_type but was unable to get it to work. Hence I am looking for your guidance.

Composite keys...limitation

December 11, 2012 - 1:57 am UTC

Reviewer: Prashant from Mumbai, India

Hi Tom,

I think I got my answer. I did some more tests. Its not partitioning but composite key limitation which is not supported for table elimination.

Do you know of any workarounds or if there is a enhancement in the works?

Thanks
Prashant

1:1 relationship

December 11, 2012 - 3:56 am UTC

Reviewer: A reader

Hi Tom,

I was once asked how to make a 1:1 relationship?
Also asked by how to split table to 2 tables, 1 for frequent columns and 1 for random accessed columns.

Your post helped me out and i am really exciting:).

But got below couple of questions for you, thanks in advanced.

1. what does rely indicate here?
Its purpose is just to tell optimizor that even 'disabled and novalidate', but data is reliable, so that 'disable-novalidate'
constraint can be used as well.
2. why do not you also make PK 'disable' and 'novalidate' in this case? if i assumption of question 1 is correct, PK is already
rely, does not 'disable' and 'novalidate' it does not make much sense?
3. how about we use normal PK-FK + unique-constraint-on-FK-of-child-table to make sure the 1:1 relationship?
Tom Kyte

Followup  

December 17, 2012 - 2:49 pm UTC

1) rely wasn't necessary, it would be if the constraints where "novalidate" and/or "disable"

it is a message to the optimizer saying "use this constraint for optimization even if you haven't validated or are not enforcing this constraint"

2) the primary key in this example is validated and enabled.

3) same way? not sure what you mean? but you wouldn't use UNIQUE, you'd want to use primary key (as it is the primary key). Unique would permit nulls and you don't want that...

Table Eliminiation

July 28, 2017 - 3:16 am UTC

Reviewer: Rajeshwaran, Jeyabal

Team:

Was reading through this optimizer blog post on Table elimination

https://blogs.oracle.com/optimizer/why-are-some-of-the-tables-in-my-query-missing-from-the-plan


<quote>
Referring to the join key elsewhere in the query will prevent table elimination. For an inner join, the join keys on each side of the join are equivalent, but if the query contains other references to the join key from the table that could otherwise be eliminated, this prevents elimination. A workaround is to rewrite the query to refer to the join key from the other table (we realize this is not always possible).
</quote>

could you help us to understand the "if the query contains other references to the join key from the table that could otherwise be eliminated" better? dont understand "Other references to the join key" in this context.


Maria Colgan

Followup  

August 01, 2017 - 12:38 am UTC

In the original query used in the blog post:

SELECT e.employee_name 
FROM   employees e, 
       departments d 
WHERE  e.department_id = d.department_id;


“The join key from the table that could otherwise be eliminated” is d.department_id.

The restriction listed at the end of the blog means if the d.department_id column is referenced anywhere else in the query, other than the join condition in the where clause, table elimination can not occur.

HOWEVER, this restriction only relates to Oracle Database releases before 11.1.0.7. After 11.1.0.7 this restriction has been removed.

For example, if we added the join key to the select list and added an additional filter predicate on the join key as follows:
SELECT e.employee_name, d.department_id 
FROM   employees e, 
       departments d 
WHERE  e.department_id = d.department_id
AND    .department_id between 100 and 110; 

And set OFE to 10.2.0.4, then table elimination cannot occur due to this restriction as you can see from below.

ALTER SESSION SET optimizer_features_enable='10.2.0.4';


Session altered.


SELECT e.employee_name, d.department_id 
FROM   employees e, 
       departments d 
WHERE  e.department_id = d.department_id
And   d.department_id between
100 and 110; 

SELECT * 
FROM table(dbms_Xplan.display_cursor(format=>'+outline'));


PLAN_TABLE_OUTPUT

---------------------------------------------------------

SQL_ID 3quhg5gssgybp, child number 1

-------------------------------------

select e.employee_name , d.department_id from employees e, departments
d where e.department_id = d.department_id And d.department_id between
100 and 110


Plan hash value: 3147123527

---------------------------------------------------------

| Id  | Operation        | Name      | Rows| Bytes |
---------------------------------------------------------
|   0 | SELECT STATEMENT   |       |    |      
|   1 |  NESTED LOOPS   |       | 3  |   159 | 
|*  2 |   TABLE ACCESS FULL| EMPLOYEES   | 8  |   320 |
|*  3 |   INDEX UNIQUE SCAN| SYS_C0028813 | 1  |    13 |
---------------------------------------------------------

Outline Data

-------------
  
/*+
      
BEGIN_OUTLINE_DATA
      
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
DB_VERSION('11.2.0.4')    
ALL_ROWS     
OUTLINE_LEAF(@"SEL$1")     
FULL(@"SEL$1" "E"@"SEL$1")     
INDEX(@"SEL$1" "D"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID"))     
LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
      USE_NL(@"SEL$1" "D"@"SEL$1")     
END_OUTLINE_DATA 
*/


Predicate Information (identified by operation id):

---------------------------------------------------
   
1 - filter(("D"."DEPARTMENT_ID">=100 AND "D"."DEPARTMENT_ID"<=110))


But if we set OFE to 11.1.0.7 or higher this restriction no longer applies.

ALTER SESSION SET optimizer_features_enable='11.1.0.7';

Session altered.

SELECT e.employee_name, d.department_id 
FROM   employees e, 
       departments d 
WHERE  e.department_id = d.department_id
And   d.department_id between
100 and 110; 

SELECT * 
FROM table(dbms_Xplan.display_cursor(format=>'+outline'));


PLAN_TABLE_OUTPUT
---------------------------------------------------------

SQL_ID 3quhg5gssgybp, child number 2

-------------------------------------
select e.employee_name , d.department_id from employees e, departments
d where e.department_id = d.department_id And d.department_id between
100 and 110

Plan hash value: 1445457117
---------------------------------------------------------

| Id  | Operation        | Name      | Rows| Bytes |
---------------------------------------------------------
|   0 | SELECT STATEMENT   |       |    |      
|   1 |   TABLE ACCESS FULL| EMPLOYEES   | 8  |   320 |
---------------------------------------------------------

Outline Data
-------------
/*+      
BEGIN_OUTLINE_DATA     
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.2.0.4')      
ALL_ROWS     
OUTLINE_LEAF(@"SEL$F7859CDE")     
ELIMINATE_JOIN(@"SEL$1" "D"@"SEL$1")
OUTLINE(@"SEL$1")     
FULL(@"SEL$F7859CDE" "E"@"SEL$1")     
END_OUTLINE_DATA 
*/


Predicate Information (identified by operation id):
---------------------------------------------------  
1 - filter(("E"."DEPARTMENT_ID">=100 AND "E"."DEPARTMENT_ID"<=110))


I have updated the blog to make it clear that this restriction only applies to database versions before 11.1.0.7.

More to Explore

DBMS_STATS

More on PL/SQL routine DBMS_STATS here