Home » SQL & PL/SQL » SQL & PL/SQL » Bitmap index for nullable timestamp column ? (Oracle 11gR2 / Linux)
Bitmap index for nullable timestamp column ? [message #660863] Mon, 27 February 2017 16:09 Go to next message
kevinz
Messages: 7
Registered: February 2017
Junior Member
Hi,

I am creating an audit table that will log successful file transfers to customers.

The table will look like this:


TRANSFER_TIMESTAMP                CUST_ID              FILENAME
[...]
27/02/2017 09:31:02.000000000        1            FILE_CUST_1_27022027*.zip
27/02/2017 09:43:09.000000000        2            FILE_CUST_2_27022027*.zip
28/02/2017 14:26:38.000000000        1            FILE_CUST_1_28022028*.zip
28/02/2017 15:32:17.000000000        2            FILE_CUST_2_28022028*.zip
[...]

- The table will have 120k records after a year (1000 new records inserted everyday; no updates or deletes on this table)
- I have 30+ different CUST_IDs.
- TRANSFER_TIMESTAMP is a nullable timestamp column with <5% of its values that are null

The table will be mainly read for reporting using the following queries:

select * from TABLE where TRANSFER_TIMESTAMP > '28/02/2017'
or
select * from TABLE where TRANSFER_TIMESTAMP BETWEEN '27/02/2017' AND '28/02/2017'
or
select * from TABLE where TRANSFER_TIMESTAMP > '28/02/2017' and CUST_ID = '1'
or simply
select * from TABLE where CUST_ID ='1'

I was planning on creating the following non unique composite/bitmap index:
CREATE INDEX BITMAP myindex ON table (TRANSFER_TIMESTAMP, CUST_ID);

Am I getting this right given this example ?
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.)

Thanks for your advice Smile
Re: Bitmap index for nullable timestamp column ? [message #660866 is a reply to message #660863] Mon, 27 February 2017 16:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Is TIMESTAMP datatype (fraction of second resolution) required?
Would DATE datatype suffice?

>where TRANSFER_TIMESTAMP > '28/02/2017'
it is poor programming to rely on implicit datatype conversion.
Characters between single quote marks are strings.


'10-11-12'
Which is correct DATE below for string above?
I'll give you six guesses since the first 5 will be wrong.
Oct. 11 2012 'MM-DD-RR'
Nov. 10 2012 'DD-MM-RR'
Nov. 12 2010 'RR-MM-DD'
Dec. 11 2010 'RR-DD-MM'
Oct. 12 2011 'MM-RR-DD'
Dec. 10 2011 'DD-RR-MM'
Re: Bitmap index for nullable timestamp column ? [message #660867 is a reply to message #660866] Mon, 27 February 2017 17:07 Go to previous messageGo to next message
kevinz
Messages: 7
Registered: February 2017
Junior Member
Thanks for your reply BlackSwan,

I will not need fraction of second resolution, however the NLS of my DB is set as follow:

NLS_DATE_FORMAT = 'DD/MM/RRRR'
NLS_TIMESTAMP_FORMAT = 'DD/MM/RRRR HH24:MI:SSXFF'

I still need the time (with second precision).
So am I correct in saying that, given that the NLS_DATE_FORMAT of my database is only 'DD/MM/RRRR', columns with Date datatype are not storing values with the time and therefore I should use timestamp datatype instead ?

Also, for reporting purposes I might do addition, substraction or < > on the date and/or time (eg. selecting all records where transfer time > 10:00:00 am)
Re: Bitmap index for nullable timestamp column ? [message #660868 is a reply to message #660867] Mon, 27 February 2017 17:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://edstevensdba.com/oracle-data-types/understanding-oracle-date-formats/

DATE datatype always include time component with granularity down to whole seconds.

NLS_DATE_FORMAT is strictly for display purpose.

You should NEVER rely on implicit datatype conversion.
https://docs.oracle.com/database/121/SQLRF/functions216.htm#SQLRF06129

both RR & RRRR are hold overs from Y2K & should be replace by YYYY now & forever forward
https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#i116004
Re: Bitmap index for nullable timestamp column ? [message #660874 is a reply to message #660863] Tue, 28 February 2017 01:36 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
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:36
Why 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 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
icon14.gif  Re: Bitmap index for nullable timestamp column ? [message #660930 is a reply to message #660915] Wed, 01 March 2017 14:57 Go to previous messageGo to next message
kevinz
Messages: 7
Registered: February 2017
Junior Member
Thank you John.
I'll try your suggestions when I have a bit of time.
Re: Bitmap index for nullable timestamp column ? [message #660949 is a reply to message #660930] Thu, 02 March 2017 07:17 Go to previous messageGo to next message
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 #660951 is a reply to message #660949] Thu, 02 March 2017 08:11 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
but, but, but...
what happens in 1983 years time ?!?!?!?
That's the y4k bug right there!!!!

Re: Bitmap index for nullable timestamp column ? [message #660952 is a reply to message #660951] Thu, 02 March 2017 08:12 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
cookiemonster wrote on Thu, 02 March 2017 09:11
but, but, but...
what happens in 1983 years time ?!?!?!?
That's the y4k bug right there!!!!

LOL.... I almost messed my pants.
Re: Bitmap index for nullable timestamp column ? [message #660956 is a reply to message #660952] Thu, 02 March 2017 08:23 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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

Smile
Re: Bitmap index for nullable timestamp column ? [message #660961 is a reply to message #660956] Thu, 02 March 2017 13:23 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
Re: Bitmap index for nullable timestamp column ? [message #660977 is a reply to message #660962] Thu, 02 March 2017 15:22 Go to previous messageGo to next message
kevinz
Messages: 7
Registered: February 2017
Junior Member
Yes, that's why I was thinking of having a default value of 01/01/1901 if I go for a b-tree index. Usually I use 31/12/9999 to flag active records (eg. subscription of a customer with no expiry date)

But if I go for a bitmap index, I don't see any added value of having a default date value for nulls. As bitmap allow indexing of nulls from what I have read. (+allow use of some aggregate function; not needed yet, but maybe in the future)

Will need to test both type of index.

We are still on 11gr2, so no hybrid histograms for us :[
Re: Bitmap index for nullable timestamp column ? [message #660978 is a reply to message #660977] Thu, 02 March 2017 15:27 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
How active is the table going to be. A bitmap takes longer to alter when a row is inserted/updated/deleted then altering a coresponding B-tree indexes.
Re: Bitmap index for nullable timestamp column ? [message #660980 is a reply to message #660978] Thu, 02 March 2017 17:27 Go to previous messageGo to next message
kevinz
Messages: 7
Registered: February 2017
Junior Member
Only ~100 insert transactions per day. Only one row inserted at a time. Interval time between each insert transaction: ~60 seconds

[Updated on: Thu, 02 March 2017 17:28]

Report message to a moderator

Re: Bitmap index for nullable timestamp column ? [message #660981 is a reply to message #660980] Thu, 02 March 2017 17:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
kevinz wrote on Thu, 02 March 2017 15:27
Only ~100 insert transactions per day. Only one row inserted at a time. Interval time between each insert transaction: ~60 seconds
(60 sec/min) * (60 min/hour) *24 hours/day = 86,400 seconds / day

100 transactions /86400 seconds = 1 Insert/864 seconds or about 1 transaction every 15 minutes

1 transaction every minute = 1,440 transactions per day.

Values posted by OP above are internally inconsistent & there is no way to determine which is correct or not.
Re: Bitmap index for nullable timestamp column ? [message #660982 is a reply to message #660981] Thu, 02 March 2017 20:01 Go to previous messageGo to next message
kevinz
Messages: 7
Registered: February 2017
Junior Member
Example of what will happen everyday:

Application starts generating and transferring 100 files. It generates and transfers those files in sequential order:

1st row inserted at 9.00 a.m. to log transfer status for file 1
2nd row inserted at 9.01 a.m. to log transfer status for file 2
3rd row inserted at 9.02 a.m. to log transfer status for file 3
[...]
100th row inserted at 10.39 a.m. to log transfer status for file 100 (9.00 a.m. + 99*60 seconds)

Application 'shuts down' until the next day.


I will insert just 100 rows a day (and not 1 000 like stated in my first message) within a ~1h30min timeframe.
So the table will remain tiny, 36 500 (100 *365) records after a year, but I prefer to create an index nonetheless.
Re: Bitmap index for nullable timestamp column ? [message #660983 is a reply to message #660982] Thu, 02 March 2017 20:08 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
With such a small number of inserts the delay introduced by using a bitmap can be safely ignored.
Previous Topic: Convert select query to a cursor and that has to fetch first record
Next Topic: why cbo select FULL TABLE SCAN
Goto Forum:
  


Current Time: Thu Mar 28 13:21:48 CDT 2024