Re: Using just a part of a union select

Hmmmm... You need to show us the output of the explain.

I presume you require the query to run quicker. You don't say why you don't want Oracle to access the docs tables.

It might be what your looking for is a nested join with rights being the inner table. For this to work you need an index docs1.id and docs2.id. The optimizer will then only access docs when a matching row is found on rights.

There is a hint to force index use or nested joins. I can't remember the exact details but have a scan through the hints and you should find the ones to use quite easily.

Also you didn't say whether you are using the rule or cost based optimizer. (i.e. Have you analysed the tables).

Honza Pazdziora wrote:

> 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,
>
> --
> ------------------------------------------------------------------------
> Honza Pazdziora | adelton@fi.muni.cz | http://www.fi.muni.cz/~adelton/
> make vmlinux.exe -- SGI Visual Workstation Howto
> ------------------------------------------------------------------------
Received on Wed Mar 03 1999 - 03:15:11 CST