Re: How to select only columns having values..
- From: Maaz Anjum <maazanjum@xxxxxxxxx>
- To: ric.van.dyke@xxxxxxxxxx
- Date: Mon, 17 Feb 2014 08:52:28 -0500
I agree with Ric.
I assume you need these populated rows at execution time. This is just a
thought, but if statistics are up-to-date on the table then perhaps
checking the user_tab_columns for the num_nulls column compared against the
total number of rows (user_tables.num_rows) might help you. The point
being, you query meta data that is less costly than the actual table. I
might be wrong, but it's just an idea.
Either way, it doesn't seem you can avoid a complicated SQL statement.
Cheers,
Maaz
On Mon, Feb 17, 2014 at 8:40 AM, Ric Van Dyke <ric.van.dyke@xxxxxxxxxx>wrote:
> Offhand this certainly has the sound of some very bad data modeling but
> that likely is beyond your control. Is this some sort of "generic table"?
> If so that will just lead to problems and you're only just starting to have
> them. Simply put, this table breaks just about every rule of relational
> theory and hence is not going to perform well no matter what you do.
>
>
>
> It seems to me the "best" thing would be just select the whole row(s) and
> then have the application dissect the row(s) and pull out where there is
> data and where there isn't. Creating the dynamic SQL as you do works, but
> will always be slow. Getting the entire row and then parsing thru it to
> find data is likely going to be faster.
>
>
>
>
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Raja Kannan Sengoden
> *Sent:* Monday, February 17, 2014 2:36 AM
> *To:* Chitale, Hemant K
>
> *Cc:* oracle-l@xxxxxxxxxxxxx
> *Subject:* RE: How to select only columns having values..
>
>
>
> Thank a lot Hemant K Chitale.
>
>
>
> As per the real scenario, all the columns will have the value.
>
>
>
> But for the particular condition, only limited columns will have value.
>
>
>
> At this time, I don't want to do analyze the data and maintain.
>
>
>
> Hence I requested, is there any simple way..
>
>
>
> Attached query helped me to get the expected result.
>
>
>
> Thanks to Tony who helped me to get the expected result.
>
>
>
> Thanks & Regards,
>
> Raja
>
>
>
> *From:* Chitale, Hemant K
> [mailto:Hemant-K.Chitale@xxxxxx<Hemant-K.Chitale@xxxxxx>]
>
> *Sent:* Monday, February 17, 2014 3:12 PM
> *To:* Raja Kannan Sengoden
> *Cc:* oracle-l@xxxxxxxxxxxxx
> *Subject:* RE: How to select only columns having values..
>
>
>
> > If I don't know exactly, Is there any way to select only those 10
> columns?
>
> Without actually querying the table, you cannot identify the 10 columns.
>
>
>
> You could periodically query the table for all the rows and identify
> columns with NOT NULL values and then maintain a list of such columns as
> "meta data". However, there is no guarantee that a column that had NULL
> values for all 1000 rows yesterday or even a minute ago still has NULL
> values because there might have been an INSERT or UPDATE that set one row's
> value to a non-NULL.
>
>
>
> How do you know that the 190 columns have NULL values ?
>
>
>
> Hemant K Chitale
>
>
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *On
> Behalf Of *Raja Kannan Sengoden
> *Sent:* Monday, February 17, 2014 12:38 PM
> *To:* oracle-l@xxxxxxxxxxxxx
> *Subject:* How to select only columns having values..
>
>
>
> Dear Experts,
>
>
>
> I have a table with 200 columns and having above 1000 Records.
>
>
>
> But for a particular where clause, there is only 3 records fetched.
>
>
>
> As the table having 200 columns, but the particular 3 record having only
> 10 columns with some value, and 190 columns having null value.
>
>
>
> If I am sure, which columns I want or which column having value, then I
> can select only those columns.
>
>
>
> If I don't know exactly, Is there any way to select only those 10 columns?
>
>
>
> If I get the data like this, It will save few minutes for the data
> arrangement.
>
>
>
>
>
> Thanks in advance.
>
>
>
> Raja.
>
>
> This email and any attachments are confidential and may also be
> privileged. If you are not the intended recipient, please delete all copies
> and notify the sender immediately. You may wish to refer to the
> incorporation details of Standard Chartered PLC, Standard Chartered Bank
> and their subsidiaries at https://www.sc.com/en/incorporation-details.html
> .
>
--
A life yet to be lived...
Other related posts:
