This content has been marked as final. Show 12 replies
Why not leave them null? What business requirement is there to update them to -999?
We do not want to have nulls in EXISTING_COLUMN because:
1. LARGE_TABLE2.EXISTING_COLUMN references KEY_COLUMN (a primary key) of another table DIMENSION_TABLE
2. There is a watchdog record in DIMENSION_TABLE with DIMENSION_TABLE.KEY_COLUMN =-999 (and other columns with meaningful values)
3. We want to query those two table using inner joins only
Edited by: SwPiotr on 2012-04-24 07:41
Well, use nvl(existing_column, -999) in the join1 person found this helpful
ALTER TABLE LARGE_TABLE1 ADD NEW_COLUMN NUMBER GENERATED ALWAYS AS nvl(col1, -999);
This will work great, but the joins are defined in a BI tool and the people taking care of it would probably not like the idea of complicating their job.
I wonder how much this would affect the performance.
Still, the idea is perfectly OK if we have no other option.
Mmm, OK but I suppose you could define a view for them.1 person found this helpful
However, the suggestion of a virtual column is probably easier.
Thanks a lot! I still work on 10g so some 11g features are new to me.
I would have to check for performance of such a solution but it looks great.
Yes, the view would be also OK. I was even thinking of creating a viev like:
to avoid calculating nvl().
SELECT date_col, col1, col2, -999 from LARGE_TABLE2 where date_col < SOME_SPECIFIED_DATE /*old partitions with nulls*/ UNION ALL SELECT date_col, col1, col2, EXISTING_COLUMN from LARGE_TABLE2 where date_col >= SOME_SPECIFIED_DATE /*new partitions with values*/
It is however tempting to have this value somewhere in the metadata as in scenario 1 to avoid any calculations. If we can manage this while adding a column, then why not doing it somehow for already exiting ones?
Thanks for quick reply.
to avoid calculating nvl().
Well NULL has to be checked somewhere - these queries you posted don't check the column for NULL at all. The first query just always uses 999. You can't be certain, based just on a date, that all of those records will have null in them.
For performance:1 person found this helpful
- Test your view to make sure it doesn't prevent partition pruning.
- In 11GR2, you can index (bitmap or standard) the virtual column and you can gather statistics on it.
- Before 11GR2, you can index nvl(existing_column, -999), and gather statistics on the "hidden column" that is created with the index.
method_opt=> 'FOR ALL HIDDEN COLUMNS SIZE ...');
I can be sure. As I wrote in my first post:
We have an existing column EXISTING_COLUMN (NUMBER) that contains 90% null values (90% of partitions contain only null values in the field and 10% of partitions contain only non-null values in that field)
to be specific:
- a table has daily partitions on a NUMBER column containing YYYYMMDD dates
- 90% of partitions up to a certain date contains only nulls
- 10% of partitions since a certain date contain non-null values only
Thank for tips we will test them if we have problems.