The second form of your query will work if you make your mark your function as deterministic, meaning that for a given set of input values, it will always return the same result.
With that set, Oracle will only run the conversion once for each parameters in the where clause rather than for every row.
With this:
CREATE OR REPLACE FUNCTION TO_MINUTE_D (DATE_IN IN DATE)
RETURN NUMBER DETERMINISTIC AS
BEGIN
/* Minute 0 = 12/30/1899 12:00am */
RETURN
(TRUNC(DATE_IN, 'DD') - TO_DATE('12/30/1899', 'MM/DD/YYYY')) * 1440 +
TO_NUMBER(TO_CHAR(DATE_IN, 'HH24')) * 60 +
TO_NUMBER(TO_CHAR(DATE_IN, 'MI'));
END TO_MINUTE_D;
/
On a table filled with a large bunch of dummy rows (increasing ints), I get the following timings consistently:
SQL> SELECT * FROM MY_TABLE
WHERE START_MINUTE < TO_MINUTE(TO_DATE('2013-01-31', 'YYYY-MM-DD'))
AND STOP_MINUTE > TO_MINUTE(TO_DATE('2013-01-01', 'YYYY-MM-DD'));
no rows selected
Elapsed: 00:00:12.69
versus deterministic-annotated function:
SQL> SELECT * FROM MY_TABLE
WHERE START_MINUTE < TO_MINUTE_D(TO_DATE('2013-01-31', 'YYYY-MM-DD'))
AND STOP_MINUTE > TO_MINUTE_D(TO_DATE('2013-01-01', 'YYYY-MM-DD'));
no rows selected
Elapsed: 00:00:00.07
You should get very close to what you have with the values plugged in directly, and indexes on those columns can be used as if you'd plugged in literals.
(Putting the conversion function on the start|stop_minute columns isn't a good idea in general as you've discovered, unless you have a function-based index on those that matches exactly.)
I would use the CHECKSUM function to determine a numeric value for each SystemAccessLevelDesc. You could make this a persisted, computed column, such that the value is automatically calculated when data is inserted into the column.
From the CHECKSUM page on Microsoft's documentation site:
CHECKSUM computes a hash value, called the checksum, over its list of arguments. The hash value is intended for use in building hash indexes. If the arguments to CHECKSUM are columns, and an index is built over the computed CHECKSUM value, the result is a hash index. This can be used for equality searches over the columns.
CHECKSUM satisfies the properties of a hash function: CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator. For this definition, null values of a specified type are considered to compare as equal. If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using HashBytes instead.
There is a possibility, however extremely small, that CHECKSUM can return the same value for two different inputs. I've created a UNIQUE constraint on the table such that an insert where the checksum results in two identical values will fail. If you have a relatively small number of values for the SystemAccessLevelDesc column, I would not expect you to experience any checksum collisions.
Sometimes an example is helpful:
USE tempdb;
CREATE TABLE dbo.SystemRights
(
SystemID int NOT NULL
, SystemAccessLevelID AS CHECKSUM(SystemAccessLevelDesc) PERSISTED
CONSTRAINT UQ_SystemAccessLevelID
UNIQUE
, SystemAccessLevelDesc nvarchar(100) NOT NULL
, CONSTRAINT PK_SystemRights
PRIMARY KEY CLUSTERED
(SystemId, SystemAccessLevelID)
);
INSERT INTO dbo.SystemRights (SystemID, SystemAccessLevelDesc)
VALUES (1, 'SYSADMIN')
, (1, 'SYSACCESS')
, (1, 'SYSREAD');
SELECT *
FROM dbo.SystemRights;
The results from the SELECT above:
╔══════════╦═════════════════════╦═══════════════════════╗
║ SystemID ║ SystemAccessLevelID ║ SystemAccessLevelDesc ║
╠══════════╬═════════════════════╬═══════════════════════╣
║ 1 ║ -32772874 ║ SYSACCESS ║
║ 1 ║ 1727577266 ║ SYSADMIN ║
║ 1 ║ 2008724622 ║ SYSREAD ║
╚══════════╩═════════════════════╩═══════════════════════╝
If you are concerned about checksum collisions, and are not worried about space implications, you could use the HASHBYTES function to generate a 32-byte binary value instead of the int value returned by CHECKSUM:
IF OBJECT_ID(N'dbo.SystemRights', N'U') IS NOT NULL
DROP TABLE dbo.SystemRights;
CREATE TABLE dbo.SystemRights
(
SystemID int NOT NULL
, SystemAccessLevelID AS CONVERT(binary(32), HASHBYTES('SHA2_256', SystemAccessLevelDesc)) PERSISTED
CONSTRAINT UQ_SystemAccessLevelID
UNIQUE
, SystemAccessLevelDesc nvarchar(100) NOT NULL
, CONSTRAINT PK_SystemRights
PRIMARY KEY CLUSTERED
(SystemId, SystemAccessLevelID)
);
INSERT INTO dbo.SystemRights (SystemID, SystemAccessLevelDesc)
VALUES (1, 'SYSADMIN')
, (1, 'SYSACCESS')
, (1, 'SYSREAD');
SELECT *
FROM dbo.SystemRights;
The output:
╔══════════╦════════════════════════════════════════════════════════════════════╦═══════════════════════╗
║ SystemID ║ SystemAccessLevelID ║ SystemAccessLevelDesc ║
╠══════════╬════════════════════════════════════════════════════════════════════╬═══════════════════════╣
║ 1 ║ 0x09B006A24922040C18013ED244C62245B8DEA81A0AB3829FEF2471299138BEC5 ║ SYSACCESS ║
║ 1 ║ 0x98AC053FBB8875188E9DCB3F77FDB6245CA9A42B8BECFAA204F2FA3054DACB7A ║ SYSREAD ║
║ 1 ║ 0xDC61780D08D34D244226A6B414FE382E138819B40362B624888D775D31505C1A ║ SYSADMIN ║
╚══════════╩════════════════════════════════════════════════════════════════════╩═══════════════════════╝
Best Answer
I agree with Jonathan - as long as the function isn't supposed to return local data based on the calling database context, put the function in a utility database (this is also where I put things like numbers and calendar tables, splitting functions, etc):
Now, in every database that needs to access the function, just create a synonym:
This way each database can still reference the function using a simple, 2-part name - and they don't have to know or care where it actually exists. And you only have to maintain a single copy of the function.
(And in fact, you can put the synonym in the
modeldatabase, so that it is created in new databases automatically.)The reason I dislike putting user objects in
master- unless they really do need to be globally available and contextual to the calling database, like your own customized version ofsp_spaceusedorsp_helpindex- is that people aren't looking for user objects inmaster, and they're less discoverable there. They also make it harder to migrate your user databases elsewhere, because you need to remember the user stuff you put inmaster, too. If you are absolutely dead-set against creating or using a utility database for this, I thinkmsdbis a more practical choice for a central location.