Home » SQL & PL/SQL » SQL & PL/SQL » Conditional full outer join
Conditional full outer join [message #299276] Mon, 11 February 2008 03:10 Go to next message
WOberegger
Messages: 3
Registered: February 2008
Junior Member
I have the problem, that for some GUI I need to UNION 2 tables with identical structure, where one table is quite small (daily records) and the other is really big (records of the last year). In the normal case, the operator just wants to see the record of the actual day, so I just want to query the small table DAILY. But in some situations he wants to set some flag (which I put as a value into a temporary table, which just holds 1 record, which is either 'Y' or 'N'), that he wants to see all rows, which match the criteria, not only the daily ones. And in such a situation he accepts a slower response time. So I thought about writing something like below:
CREATE OR REPLACE VIEW FULLVIEW (ID, InvoiceNo, CustNo)
AS
SELECT ID, InvoiceNo, CustNo FROM DAILY
UNION ALL /*+ USE_NL (TEMP_TAB, YEARLY) */
SELECT ID, InvoiceNo, CustNo FROM YEARLY, TEMP_TAB
WHERE TEMP_TAB.VALUE = 'Y';

Problem is, that even with the USE_NL hint (and the TEMP_TAB.VALUE being 'N'), ORACLE runs thru the big table YEARLY and the performance is bad, when I run a statement as below:
DELETE TEMP_TAB;
INSERT INTO TEMP_TAB VALUES('N');
SELECT * FROM FULLVIEW WHERE CustNo='Miller';

Any ideas?
P.S.: The DAILY table does not contain any records of the YEARLY table, i.e. the yearly table holds records from yesterday backwards, so no duplicates possible.
Re: Conditional full outer join [message #299280 is a reply to message #299276] Mon, 11 February 2008 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why a temporary table and not a (context) variable?
Why not simply 2 queries?

Regards
Michel

[Updated on: Mon, 11 February 2008 03:25]

Report message to a moderator

Re: Conditional full outer join [message #299284 is a reply to message #299280] Mon, 11 February 2008 03:43 Go to previous messageGo to next message
WOberegger
Messages: 3
Registered: February 2008
Junior Member
ad 1) I thought of using a temporary table to be able to give USE_NL precedence in the join. Would this also work e.g. with a package variable (I would even appreciate this solution)?

ad 2) I do not want to touch existing GUI application too much. There are several places, where this existing view is used, so I just would need to introduce one method to set the temp value for a particular session and all the rest could remain identical.

By the way: The version with the temporary table actually worked fine (as expected), it was just the wrong output of "autotrace", which confused me. But I would prefer to have a similar version with a package variable.
Re: Conditional full outer join [message #299287 is a reply to message #299284] Mon, 11 February 2008 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) With a variable (package or context one) there is no more need of join so USE_NL is irrelevant

Regards
Michel
Re: Conditional full outer join [message #299382 is a reply to message #299287] Mon, 11 February 2008 15:29 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Options:-
1. You could add a functional index to YEARLY that returns a constant (Y).

Then when you access the table with

select * from yearly, tab_test
where function(column) = tab.test.VALUE

the result would be nothing if TAB_TEST was 'N' and Everything where TAB_TEST was 'Y'

2. Add a column to YEARLY and set all to Y, then bitmap index it. Same result as above without the function.

Re: Conditional full outer join [message #299433 is a reply to message #299382] Tue, 12 February 2008 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As far I understand Y is not part of data but it is a parameter given by the user (or deduced from one of the options he gave).
So function or index is irrelevant.

Regards
Michel

[Updated on: Tue, 12 February 2008 01:04]

Report message to a moderator

Re: Conditional full outer join [message #299440 is a reply to message #299433] Tue, 12 February 2008 01:11 Go to previous messageGo to next message
WOberegger
Messages: 3
Registered: February 2008
Junior Member
Right, this is a user provided information, that he wants to see all data, whereas another user in a different session in the same GUI screen wants to see only the DAILY data. I have meanwhile fixed the problem with a package variable (which is set before in the filter screen), as you have proposed.
Thanks for your assistance.

The proposal of coleing with the functional index does also not sound too bad, but the version with the package variable is more suitable for my needs.

Anyway: The problem originally just came up due to the fact, that I had trusted the output of "autotrace", which always showed costs of selecting YEARLY table, but actually this was not the case, so it was already fine in my own first variant. But I now use a variable instead of a second table, as proposed by you.

Thanks again!
Re: Conditional full outer join [message #299634 is a reply to message #299276] Tue, 12 February 2008 16:01 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Michael,

My point with the functional index was to "invent" data on the table that wasnt there (ie. a fixed value of Y).

Then when the user supplied 'Y' to the

"where function(column)" part of the sql, all rows would be returned.

However, if he suplied 'N' to the function, no rows would be found in the index (and very quickly too).
Re: Conditional full outer join [message #299731 is a reply to message #299634] Wed, 13 February 2008 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no "function(column)" the data does not come from the table it comes from a user input. Which function and which column do you think?
Post an example, I really don't understand what you're thinking about.

Regards
Michel
Re: Conditional full outer join [message #300041 is a reply to message #299276] Thu, 14 February 2008 01:32 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Below is an example of artifically generating a Y against each column using a functional index, so that it can be compared against a user variable to return all rows, or no rows with a Y or N.

drop table tab1;
create table tab1(field1 varchar2(20), field2 varchar2(20));
insert into tab1 values ('Dan1','Val1');
insert into tab1 values ('Dan2','Val2');
insert into tab1 values ('Dan3','Val3');
insert into tab1 values ('Dan4','Val4');
insert into tab1 values ('Dan5','Val5');
insert into tab1 values ('Dan6','Val6');
insert into tab1 values ('Dan7','Val7');
insert into tab1 values ('Dan8','Val8');
insert into tab1 values ('Dan9','Val9');
insert into tab1 values ('Dan10','Val10');
commit;

create or replace function f_dan_test(col_input in varchar2) return varchar2
deterministic is
begin
       return 'Y';
end;
/

-- Create the functional index
create index tab1_idx on tab1(f_dan_test(field1));

select count(1) from tab1 where f_dan_test(field1) = 'Y';
--10
select count(1) from tab1 where f_dan_test(field1) = 'N';
--0
Re: Conditional full outer join [message #300043 is a reply to message #300041] Thu, 14 February 2008 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But this is a completly different problem.
Here the variable does not depend on a field, it is given by the user. And this "constant variable" is checked against 'Y' (variable between each call, constant inside the current call).

Regards
Michel
Re: Conditional full outer join [message #300276 is a reply to message #300043] Thu, 14 February 2008 20:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The nested loops would "work" if you reversed the order of the join.

If the join gets YEARLY first, then it will attempt to join to TEMP for every row and fail. If it goes to TEMP first, then it picks up no rows and does not bother with YEARLY.

You could probably counter it with an ORDERED hint, but this method is a bit hit and miss.

You should try to encapsulate the condition on temp as a scalar.You could use a package variable as suggested (that would be best), but you could also encapsulate it in a function or a scalar sub-query.

CREATE OR REPLACE VIEW FULLVIEW (ID, InvoiceNo, CustNo)
AS
SELECT ID, InvoiceNo, CustNo FROM DAILY
UNION ALL /*+ USE_NL (TEMP_TAB, YEARLY) */
SELECT ID, InvoiceNo, CustNo FROM YEARLY
WHERE get_temp_tab_value() = 'Y';


CREATE OR REPLACE VIEW FULLVIEW (ID, InvoiceNo, CustNo)
AS
SELECT ID, InvoiceNo, CustNo FROM DAILY
UNION ALL /*+ USE_NL (TEMP_TAB, YEARLY) */
SELECT ID, InvoiceNo, CustNo FROM YEARLY
WHERE 'Y' = (SELECT /*+UNNEST*/value FROM temp_tab);


The first one requires a PL/SQL function, but I don't like the second one because it is subject to the same potential performance problem you are experiencing. The UNNEST hist should take care of it though.

Ross Leishman
Re: Conditional full outer join [message #301425 is a reply to message #299276] Wed, 20 February 2008 10:01 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
go with Ross's idea, but use a sys_context variable. The query Ross has provided is the conerstone of what we old timers remember as "MANUALLY PARTITIONED VIEWS" from 7.3 days.

It is still quit a valuable feature and if I am understanding your need, it is exactly the reason why. I use this to add historical perspective to my databases, and to create "what if" schemas using hypothetical views (but this is for another day).

Assuming you have a context area set up for your sessions, you can easily do this.

select *
from sometable
where 'Y' = sys_context('yourcontextarea','yourvariable)
union all
select *
from someothertable
where 'N' = sys_context('yourcontextarea','yourvariable)
/


there are few points to note about this:

Quote:
1) you are using manually partitioned views
2) sys_context if I am not mistaken is considered a constant to oracle (just like the system variable user etc.)
3) because of #2, each select in your union all select has a constant = constant predicate. Oracle is smart and knows that each of these will never change during the life of the query and so it will evaluate these right away.
4) conveniently, at any given time, the constant test of one of your queries will be true and that the other will be false. Oracle will evaluate the false one and figure out he does not have to do any more with that select.

Thus you achieve a conditional query in the sense that you can control which select statement(s) get executed and not incur any performance penalty for it.

So, construct the appropriate view, rename the original table your apps were pointing to, substitute a synonym to point to your new view and tell you boss you can do it but it will take two weeks of your dedicated time and then take it easy for two weeks.

Good luck, Kevin
Re: Conditional full outer join [message #301473 is a reply to message #301425] Wed, 20 February 2008 16:40 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hey, I won't have you dis'ing Partition Views. Old-timers, 7.3. Indeed? Wink Just becuase Oracle removed all trace of them from the doco doesn't mean they're no longer useful.
  • Prior to 11g, they were a handy way to perform LIST-LIST or LIST-RANGE partitioning.

  • Fast Refresh MVs don't support UNION_ALL with Partition Change Tracking (so you cannot partition-refresh a UNION_ALL MV if you truncate a partition in one of the sources). A Partition View of many MVs circumvents this.

  • The CBO will not perform a STAR_TRANSFORMATION on a UNION_ALL view unless it complies with the Partition View criteria.

  • I also suspect they may still be useful in distributed environments, but I have no practical experience.

Ross Leishman
Re: Conditional full outer join [message #301489 is a reply to message #299276] Wed, 20 February 2008 18:25 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
hehehe Laughing , it is indeed fun to find people around the world who have been doing the Oracle thing as long or longer than me. I learn something everytime I see your posts. Your recent article was great by the way.

I use Manually Partitioned Views like this all the time. I am in fact in the throws of retro fitting an Oracle system to do historical context, without requiring changes from the application end of things. So many people believing it can't be done. Me and a little lady gonna get it done on the sly whilst the rest of them argue about it. Ha! what fun.

But every time I do this I make a camp of believers. They are starting to call me "The K Train".

Kevin
Previous Topic: Problem with Implicit Cursor
Next Topic: complex count
Goto Forum:
  


Current Time: Thu Dec 08 04:02:32 CST 2016

Total time taken to generate the page: 0.19027 seconds