Board index » delphi » Oracle 8.1.5 - Union on same table

Oracle 8.1.5 - Union on same table

I'm populating a field in a view with the following union subselect:
(SELECT CTAC_ID, NAME_FIRST NAME
FROM CTAC
UNION ALL
SELECT CTAC_ID, NAME_MIDDLE
FROM CTAC
UNION ALL
SELECT CTAC_ID, NAME_LAST
FROM CTAC
UNION ALL
SELECT CTAC_ID, NAME_CASUAL
FROM CTAC)

All the selects are from one table and this results in 4 full table scans on
the same table.  Is there a better way to accomplish this.  I am using this
technique frequently in these views and the performance is terrible.

Thanks

--
Jim Poe (j...@fulcrumit.com)

 

Re:Oracle 8.1.5 - Union on same table


Why do you need to do this?

Quote
Jim Poe <j...@fulcrumit.com> wrote in message news:3aa58e65$2_1@dnews...
> I'm populating a field in a view with the following union subselect:
> (SELECT CTAC_ID, NAME_FIRST NAME
> FROM CTAC
> UNION ALL
> SELECT CTAC_ID, NAME_MIDDLE
> FROM CTAC
> UNION ALL
> SELECT CTAC_ID, NAME_LAST
> FROM CTAC
> UNION ALL
> SELECT CTAC_ID, NAME_CASUAL
> FROM CTAC)

> All the selects are from one table and this results in 4 full table scans
on
> the same table.  Is there a better way to accomplish this.  I am using
this
> technique frequently in these views and the performance is terrible.

> Thanks

> --
> Jim Poe (j...@fulcrumit.com)

Re:Oracle 8.1.5 - Union on same table


This is part of a view that users use in a search dialog.  I populated one
column, NAME, in the view with this union.  This allowed the user to search
on any of the underlying field values in the exposed field.  Unfortunately,
for each row in the main table of the view, this subselect was doing 4 full
table scans.  Add to that, I was using this technique multiple times in the
same view.
I received the following solution on another newsgroup.

Quote
> You could try creating a 4 row table
> (or the equivalent) and hash joining
> it with the base table, then use a decode
> to get four different answers per input:

> select /*+ no merge (dv) use_hash (ct) */
>     ctac_id,
>     decode (n1,
>         1, name_first,
>         2, name_middle,
>         3, name_last,
>         4, name_casual
>     ) name
> from
>     (select n1 from (
>         select 1 n1 from dual
>          union all
>         select 2 n1 from dual
>          union all
>         select 3 n1 from dual
>          union all
>         select 4 n1 from dual
>         )
>     ) dv,
>     (select
>         ctac_ic, name_first, name_middle,
>         name_last, name_casual
>     from
>         ctac
>     )    ct

This is much more efficient.

Quote
"Mike Evans" <p...@mcs.net> wrote in message

news:98bd1s$sas8@bornews.inprise.com...

- Show quoted text -

Quote
> Why do you need to do this?

> Jim Poe <j...@fulcrumit.com> wrote in message news:3aa58e65$2_1@dnews...
> > I'm populating a field in a view with the following union subselect:
> > (SELECT CTAC_ID, NAME_FIRST NAME
> > FROM CTAC
> > UNION ALL
> > SELECT CTAC_ID, NAME_MIDDLE
> > FROM CTAC
> > UNION ALL
> > SELECT CTAC_ID, NAME_LAST
> > FROM CTAC
> > UNION ALL
> > SELECT CTAC_ID, NAME_CASUAL
> > FROM CTAC)

> > All the selects are from one table and this results in 4 full table
scans
> on
> > the same table.  Is there a better way to accomplish this.  I am using
> this
> > technique frequently in these views and the performance is terrible.

> > Thanks

> > --
> > Jim Poe (j...@fulcrumit.com)

Re:Oracle 8.1.5 - Union on same table


Hi,

As long you don't use where clauses, Oracle uses full scan.
Because in the unions you access 4 different fields from the dataset, it
means 4 different statements, which must be executed 4 times.

--

Valent Levente
vale...@broadband.hu

Quote
"Jim Poe" <j...@fulcrumit.com> wrote in message news:3aa58e65$2_1@dnews...
> I'm populating a field in a view with the following union subselect:
> (SELECT CTAC_ID, NAME_FIRST NAME
> FROM CTAC
> UNION ALL
> SELECT CTAC_ID, NAME_MIDDLE
> FROM CTAC
> UNION ALL
> SELECT CTAC_ID, NAME_LAST
> FROM CTAC
> UNION ALL
> SELECT CTAC_ID, NAME_CASUAL
> FROM CTAC)

> All the selects are from one table and this results in 4 full table scans
on
> the same table.  Is there a better way to accomplish this.  I am using
this
> technique frequently in these views and the performance is terrible.

> Thanks

> --
> Jim Poe (j...@fulcrumit.com)

Re:Oracle 8.1.5 - Union on same table


Perhaps, if you could give us a better idea of the ultimate goal, we
could be more helpful. On the face of it, it is an unusual view.

Clive

Quote
>> I'm populating a field in a view with the following union subselect:
>> (SELECT CTAC_ID, NAME_FIRST NAME
>> FROM CTAC
>> UNION ALL
>> SELECT CTAC_ID, NAME_MIDDLE
>> FROM CTAC
>> UNION ALL
>> SELECT CTAC_ID, NAME_LAST
>> FROM CTAC
>> UNION ALL
>> SELECT CTAC_ID, NAME_CASUAL
>> FROM CTAC)

Other Threads