I have a view which joins two table and according to some stuff I have seen
Oracle should eliminate the join if I refer to only columns in one table. I am
hoping someone has tried this and has a URL they can point me to.
both tables are partitioned. I created a global index on the tables. I tried
turning one into a PK. I guess I can keep trying things but if someone has
succeeded in this and can point to a document that explains what needs to be
done I would appreciate it.
That sounds pretty wicked cool Erwin, first i’ve heard of that feature though…
Curiosity got the better of me. I found this using Google.
http://optimizermagic.blogspot.com/2008/06/why-are-some-of-tables-in-my-query.htm
l
Dave
Hey Erwin;
I’ve never played around with observing that specific feature, but you
triggered my curiosity.
So… from the link:
http://optimizermagic.blogspot.com/2008/06/why-are-some-of-tables-in-my-
query.html
A rule: " A table is redundant if its columns are only referenced to in
join predicates, and it is guaranteed that those joins neither filter
nor expand the resulting rows."
Setup (between the stars):
Caution: the table names are “fruit” and “color” so if those tables
actually exist for you, don’t run the process “as is” because it will
drop the tables first before creating.
Hi Erwin,
I have a view which joins two table and according to some
stuff I have seen Oracle should eliminate the join if I
refer to only columns in one table. I am hoping someone has
tried this and has a URL they can point me to.
It's 11g onwards I think.
http://optimizermagic.blogspot.com/2009/09/whats-changed-between-my-new-
query-plan.html
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Good Morning Norm;
Or perhaps late afternoon for you at the moment 
It's 11g onwards I think.
What I found works in 10g. Specifically I tested it on a 10.2.0.4
database with the optimizer set to Choose. The results were the same
using:
I) The rule based optimizer (no stats on the tables or indexes)
ii) The cost based optimizer (full stats on the tables/indexes)
iii) A view created linking the two tables then running the query
against the view
So... unless what I observed was different functionality, the link I
supplied identifies it was introduced in 10gR2.
Roger S.
PLEASE NOTE:
This communication, including any attached documentation,
is intended only for the person or entity to which it is addressed,
and may contain confidential, personal and/or privileged information.
Any unauthorized disclosure, copying, or taking action on the contents
is strictly prohibited. If you have received this message in error,
please contact us immediately so we may correct our records.
Please then delete or destroy the original transmission and any subsequent reply.
Thank you.
Hi Roger,
Good Morning Norm;
Or perhaps late afternoon for you at the moment
It was afternoon indeed!
What I found works in 10g. Specifically I tested it on a 10.2.0.4
database with the optimizer set to Choose.
This is why I always advise people to read the docs, and then test
anyway! Sometimes what is documented for "this" release was actually
working (undocumented) in the previous release.
So... unless what I observed was different functionality, the link I
supplied identifies it was introduced in 10gR2.
Indeed - thanks.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
A big thank you to the ones that replied. I especially like the reply about
using the dbms_xplan.diff_plan_outline. I learned about something I didnt
know existed and can probably become very useful.
Back to the original question .
I got the JOIN ELIMINATION to work.
Here is why we needed or wanted it.
We have a large table with 325 columns and 2.5 million rows which takes up
1.86Gb
Unfortunately Oracle doesnt compress tables with more than 255 columns so
we decided to divide the table in half and put the most commonly used columns in
t1 and the seldom used columns in T2. And create a view that joins T1 and T2 for
end-user transparency. Now each table can be compressed.
T1 is 523Mb
T2 is 103Mb
So now I have 2 tables which after compression take up only 1/3 of the space
that would be required if it were one table.
I therefore created a view that joins the two tables so to the end-users nothing
has changed. When we tried that originally in 10g we got the space compression
but we got a performance hit because of the join. With JOIN ELIMINATION we are
hoping to get the compression without a performance hit. I still have a lot of
testing to do since the tables are also portioned by YEAR concatenated with ID.
Most of the time we are interested in a particular year. Again this works fine
when the second table is eliminated by the optimizer but if I refer to a column
in the second table then only the appropriate partition is used for T1 and all
the partitions are used for T@. This I have to work on.
JOIN ELIMINATION works: