Oracle SQL – How to Select Multiple Columns Between Columns

oracle

A question that was asked among a few developers, now deferred to DBA experts:

Is there a way to select multiple columns within a table instead of SELECT'ing columns piecemeal? For example, let's say example table contains 26 columns respectively titled by letters (e.g. A, B, C, etc.). Suppose I would like to retrieve all rows from columns H, I, J, K, L, M, N, O, P- is there a more efficient way to begin the SELECT statement of the following query?

SELECT H, I, J, K, L, M, N, O, P FROM table;

Perhaps something like so?

SELECT H-P FROM table;

Thank you!

Best Answer

This is a crazy idea and would makes future maintenance really tricky.

It is not possible to guarantee that column order will never change. There are plenty of situations where column order changes could potentially be beneficial. It may happen accidentally (update the wrong database) or it may be done for performance reasons (e.g. moving mostly NULL columns to the end).

Even if it is possible - don't do it.

Related Solutions

Difference Between SELECT and select in oracle sql

There is nothing specific about SELECT here. There is no semantic difference between SELECT, select and SeLeCt.

The issue being described is that if you issue these three semantically identical statements:

 SELECT thing FROM mytable;

 select thing from mytable;

 Select Thing From MyTable;

the database treats them as three entirely different entities, that will each occupy some space in the library cache. They will each have been parsed individually, costing CPU each time, even if the resulting execution plan are are identical.

Same thing if you have two identical queries but one with a comment and another without - they will be treated as different statements entirely by the engine, and each consume resources. (I believe even spacing matters.)

(This is actually helpful for debugging. If you're trying different settings to optimize a query without changing the query text, add a comment describing the current settings each time you run it. You're guaranteed a re-parse, and can find the individual runs in [g]v$sql easily with those comments. This wouldn't work if the engine "normalized" the query text before parsing.)

What this means for you is that you should be consistent with your query syntax. Choose a "coding" style and stick with it to avoid littering the various caches with semantically identical queries.

Example: notice the three different SQL IDs.

select a from foo;
Select a From foo;
SELECT a FROM foo;

select * from v$sql where sql_text like '%foo%';

SQL_ID        SQL_TEXT
------------- -------------------
dxdhrwqjpn6b7 Select a From foo  
0dqb3y3bask98 select a from foo  
1y13x9j2rwzuz SELECT a FROM foo  
...

Find columns referenced by virtual column expression

User_tab_cols shows that there is a dependancy when virtual_column contains 'YES', which answers your need to determine if there is any dependency at all.

In order to determine what the actual virtual column consists of, one still needs to parse data_default.

SELECT column_name , virtual_column , segment_column_id , internal_column_id FROM user_tab_cols WHERE table_name = 'T' ORDER BY column_id;

Best Answer

Related Solutions

Difference Between SELECT and select in oracle sql

Find columns referenced by virtual column expression

Related Question