| displaying 80% column data [message #9410] |
Sun, 09 November 2003 00:08  |
Natraj
Messages: 53
Registered: March 2003
|
Member |
|
|
hello friends,
> I want to write a query for displaying just 90% of
> colums in my table . my table has 1000 colums, i
> need
> a query that will retrieve 900 colums, obviously i
> dont want to type all 900 columns or use the minus
> clause, whereby i will have to type the remaining
> 100
> columns, also i do not want to make the columns
> unused, alter system set unused columns.... etc
> becoz
> that will render the columns unusable for other
> users,
> they shud have access to all 1000 coulmns.. can
> anybody throw some light on this problem?This query
> is
> needed for many different tables, and my tablespaces
> are also nearly full, so i cant create any views so
> pls dont ask me to create a view for 900 columns
> etc,
> because the no. of columns needed by me keeps
> changing.for 1 query i may need 800 colums, for
> another 850.. i need a optimised query. i dont want
> to
> create any view etc ...can anyone throw some light
> on
> this requirement?
> cheers Natraj
|
|
|
|
| Re: displaying 80% column data [message #9418 is a reply to message #9410] |
Sun, 09 November 2003 23:30  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Views are not more than stored selects, they don't consume a lot of space (unless, of course, you're talking about materialized views). But if you don't want to use views, that's fine.
And are you talking about columns? How can you eliminate 100 columns by using MINUS? I think you're talking about records, aren't you?
If you are really talking about columns, you'll need some dynamic SQL here.
If you need to limit the number of records, you need to specify an order by first. After you sorted the records you can limit the output by using something like:
Select *
From ( Select column1
, column2
, ...
, columnX
From some_table
Order By ColumnY
) v
Where rownum < number_of_desired_records+1
Once again, are you really talking about columns?
MHE
|
|
|
|
| Re: displaying 80% column data [message #9435 is a reply to message #9418] |
Mon, 10 November 2003 17:30 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
Each column represents a particular type of information about a row ('NAME', 'ID', 'HIREDATE' etc), so you can't deal with them in bulk, "give me 50% of the columns" type of thing. You always have to specify which columns you want, or '*' for all of them. That's the way SQL works.
1000 columns is way too many IMHO. What on Earth do they all represent?
|
|
|
|
| Re: displaying 80% column data [message #9443 is a reply to message #9418] |
Mon, 10 November 2003 22:54 |
Natraj
Messages: 53
Registered: March 2003
|
Member |
|
|
hi,
i m talking about columns, i m sorry we cant use minus witrh columns:) i got a bit carried away:) but yes definitely i want to remove few columns, maybe 20 columns out of 1000 columns, or 50 columns out of 1000.. so, kindly tell me any proper means:)
|
|
|
|
| Re: displaying 80% column data [message #9451 is a reply to message #9443] |
Tue, 11 November 2003 05:31 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
First of all, its hard to believe that you have 1000 columns in a relational table(although Oracle permits )! Its probably time for you to revisit the data model,if this is the case. Imagine all the full table scans running through all the columns ..
Secondly, you not having space to create stored queries(ie views) is unbelievable.
Even if the number of columns are not fixed in queries, how many different combinations against the same table does your app use ? 5 , 10 ? ...I am not sure if you would say you will need 900 different column combinations...again if this is the case ,something else is not right.
What type of app is this ? Do you think it might be possible to filter out the columns in the middle tier ?
You may get away with using dynamic sql,but I am not suggesting it..
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
