| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Using just a part of a union select
Hello,
I'd appreciate a hint about tuning union select where only some of the tables could be visited, but in reallity they are all selected thru.
I have two tables, docs1 and docs2, which both contain id of a document and some other columns. Then I have a table that lists rights for these documents. The columns in this table rights are like
right integer /* id of a right */ person integer /* foreign key to a table of people */ type char(1) /* decides which docs* table to use */ doc integer /* id of the document in that table */
I have a select like
select * from rights,docs1 where type = '1' and doc = docs1.id union select * from rights,docs2 where type = '2' and doc = docs2.id
So where in the table rights the type is 1, I want to join with table docs1 and where it is 2 I want to join with docs2. I wanted to make the query above to a view to make things easier.
However, if the rights table contains only a row
88,2413,1,45
meaning right 88 is given to person 2413 for document 45 from docs1 and I select
where right = 88
and for right 88 no record points to docs2, I'd like Oracle to avoid touching docs2 completely. However, according to explain plain, the docs2 is still searched, even if the join has to fail, since the type is never 2 for right 88.
It there a way (with some hint, possibly) to make Oracle search the rights table first by the right column and then only join with those tables that are needed? Should I go for PL/SQL function instead? I thought keeping it a view could help Oracle to optimize the whole thing. In reality, the number of tables I try to merge with this thing is much higher, so searching only those that are needed is really rather important.
Any recommendation is highly welcome,
--
 |
 |