|
|
|
|
Re: Bitmap index for nullable timestamp column ? [message #660874 is a reply to message #660863] |
Tue, 28 February 2017 01:36 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:Bitmap index would be a bit better than B-tree index in this case right ? (low DML, first column has low cardinality and contains NULLs etc.) Why would that column ever have a NULL? You need to put a constraint on that.
Low number of distinct values? I would have thought it would be close to unique.
Will your inserts be in approximately data order? If so, a b-tree index is perfect because the clustering factor will be perfect.
And of course you have to use proper type casting in your predicates.
|
|
|
Re: Bitmap index for nullable timestamp column ? [message #660910 is a reply to message #660874] |
Tue, 28 February 2017 15:36 |
|
kevinz
Messages: 7 Registered: February 2017
|
Junior Member |
|
|
BlackSwan wrote on Mon, 27 February 2017 17:25[url]
DATE datatype always include time component with granularity down to whole seconds.
[...]
Thanks for the links !
I always thought that a date datatype did not include the time !!! Probably because every server/client I have used so far were not configured to display the time for this datatype.
So indeed, for what I need to do I won't need timestamp datatype.
John Watson wrote on Tue, 28 February 2017 01:36Why would that column ever have a NULL? You need to put a constraint on that.
When the file transfer fails.
'TRANSFER_TIMESTAMP' is the date+time for when the file has been successfully transferred to the customer. If the transfer fails, I have no value to populate this column with (will be null).
I could have a default value like '01/01/1901' in this case, but what would be the added value ?
John Watson wrote on Tue, 28 February 2017 01:36
Low number of distinct values? I would have thought it would be close to unique.
'TRANSFER_TIMESTAMP' will only have unique values and 'CUST_ID' only about 30 distinct values (30 different customers)
John Watson wrote on Tue, 28 February 2017 01:36
Will your inserts be in approximately data order? If so, a b-tree index is perfect because the clustering factor will be perfect.
Inserts will be done with a 'TRANSFER_TIMESTAMP' that is increasing at every new insert (and only one row will be inserted at a time)
eg:
01/03/2017 11:15:02
01/03/2017 11:18:07
[...]
02/03/2017 09:13:21
02/03/2017 09:19:56
cheers !
[Updated on: Tue, 28 February 2017 15:40] Report message to a moderator
|
|
|
Re: Bitmap index for nullable timestamp column ? [message #660915 is a reply to message #660910] |
Wed, 01 March 2017 02:00 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
So the TRANSFER_TIMESTAMP is unique, but if you strip of the time element there will be only a few hundred values?
In that case, you could create a b-tree index on the column or a bitmap index on trunc(transfer_timestamp). I would create both and run a few queries. See what plans and exec times you get. Experiment with a second bitmap index on cust_id, and appending cust_id to the b-tree index.
Or you could add a primary key populated from a sequence, and create the table as an IOT.
|
|
|
|
Re: Bitmap index for nullable timestamp column ? [message #660949 is a reply to message #660930] |
Thu, 02 March 2017 07:17 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
The nice thing about using a default date to find records that need be transferred is that the date will be indexed and quick to find. For example if your default date is
to_date('12/31/4000','mm/dd/yyyy')
you can find every customer to transfer by simply using
and trunc(transfer_timestamp) = to_date('12/31/4000','mm/dd/yyyy')
By the way, 12/31/4000 is the default date that oracle uses in many of it's applications for this same purpose.
The index command to build your function index would be
CREATE INDEX MY_TABLE_I1 ON MY_TABLE(trunc(transfer_timestamp));
|
|
|
|
|
Re: Bitmap index for nullable timestamp column ? [message #660956 is a reply to message #660952] |
Thu, 02 March 2017 08:23 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
To be honest that could actually happen.
Sure nothing written now will exist in y4k, it'll be the great*great*great... grandchildren of existing systems written in coding languages that we probably couldn't even envision.
But some of them will keep copying that logic.
And every so often some one will notice and make a joke about it because y4k is still a millenium away, then 500 years, 100, 50, 10, 5, 3, 2, ohhhh $"!%!£"
And there'll be tonnes of overtime as everyone replaces it with the y10k bug (because somebody decided it'd be cool to allow 5 digit years back in 3000) and there'll be mass media panic about interstellar commerce breaking down.
It'll all get fixed, nothing will go wrong and by 4005 you'll have lots of people insisting the whole thing was a hoax so that programmers could bilk the taxpayer
And you'll still have people using 2 digit years.
Or the planet could have been destroyed long before any of that could take place
|
|
|
Re: Bitmap index for nullable timestamp column ? [message #660961 is a reply to message #660956] |
Thu, 02 March 2017 13:23 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
We could always use December 31, 9999, which is the absolute maximum date allowed in current versions of the oracle database. So use
to_date('12/31/9999','mm/dd/yyyy')
If you go above that you get oracle error ORA-01841. The same restriction is also on timestamps.
Wow, I should just be about ready to retire by then.
[Updated on: Thu, 02 March 2017 13:27] Report message to a moderator
|
|
|
Re: Bitmap index for nullable timestamp column ? [message #660962 is a reply to message #660961] |
Thu, 02 March 2017 13:36 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Well, OP did say[code]
Quote:The table will be mainly read for reporting using the following queries:
select * from TABLE where TRANSFER_TIMESTAMP > '28/02/2017' which won't work if NULLs are replaced with a high value. There is also a hypothesis that using a dummy value such as December 31, 9999 instead if NULL will confuse the optimizer. The new hybrid histograms should help with that. But if they don't he might get fired for destroying the application long before reaching year 9999.
|
|
|
|
|
|
|
|
|