UNION SELECT 1

[jestrada101]

Would it be quicker to return data by specifying a UNION like the following, or should I include all columns?

select * from tableA
union
select * from tableB

Thanks for any info.
JE

 

[SantaMufasa]

JE,

I don't quite understand your question, "Would it be quicker to return data by specifying a UNION like the following, or should I include all columns ?"...Your example does include all columns and it is a UNION. Regardless of my misunderstanding, you typically do not need to fear the use of Oracle's SET operators, including UNION: SET operators are among Oracle's tightest and quickest code, gram for gram, even when you SELECT all columns.

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:42 (18May04) UTC (aka "GMT" and "Zulu"), 08:42 (18May04) Mountain Time)

 

[jestrada101]

Thanks Mufasa...

what I was trying to say... would it there be any difference between using the "*" or specifying all the columns...

Thanks for the info.

JE

 

[SantaMufasa]

JE,

Now I understand the question...you mean, "Is there a performance difference between implicit definition of all columns (i.e., "*") versus explicit definition of all columns?" I have no documentation to back it up, but my intuition tells me that "*" would be slightly faster than the processing required during the interpreter step to verify proper spelling and existance of each individual column. But, again, that's just my "guess".

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:08 (18May04) UTC (aka "GMT" and "Zulu"), 09:08 (18May04) Mountain Time)

 

[aryeh1010]

It's not just a question of which is quicker.

What happens when you change on of the tables by adding, dropping, or modifying a column.

If the select .. union is just an one-time deal, no problem.

If it's going to become a part of a stored procedure, it's not a good practice.

Aryeh Keefe

 

[SantaMufasa]

Aryeh,

I don't quite understand your concern. If you modify the table, regardless of whether you use "*" or explicit column references, Oracle flags any referencing stored procedure as "INVALID", thus causing either an explicit or implicit re-compile.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:01 (18May04) UTC (aka "GMT" and "Zulu"), 10:01 (18May04) Mountain Time)

 

[aryeh1010]

Mufasa,
You are correct about needing to recompile.

I'm just saying that in this situation it would be wise to do it explicitly.

When doing select * from two different tables, if someone adds a column to table 1, but not to table2, then the procedure won't recompile successfully. If the columns are explicitly named, then the recompile will succeed.

There might be a need to add the new column to the select list, but at least the procedure would run in the meanwhile.

I also said that the select * is valid for a one time anonymous sql block.

Is that clearer? I speak based on my experience of finding the problem with a developers process that suddenly didn't work when they changed the table structure in a select * situation.

That sure brings back fond memories.

Aryeh Keefe