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 think the article Raj quoted (https://www.pythian.com/blog/analyze-index-validate-structure-dark-side/) describes this pretty well. "clustering factor" was also my first guess while reading the description of your problem. I also prefer to use RMAN to check for corruption.
RMAN> backup check logical validate database;
Afterwards you can query V$DATABASE_BLOCK_CORRUPTION
for details on any corrupt block.
Normally there is a reason for huge clustering. You can check the following details:
- How many rows does the index have?
- How many blocks does it consume?
This may help you to determine if the clustering_factor is high. You can also query dba_extents
to determine the clustering_factor.
- What type of management does the tablespace use? I guess it's not ASSM since the database is pretty old and was upgraded several times.
Why is clustering bad for performance? When Oracle reads data from disk or cache it always reads in block. If the block is half empty you loose 50% of your reading performance. In case you do a full table or full index scan Oracle scans all blocks belonging to the segment (index or table). It does not check if a block is empty or not. Oracle reads from the first block to the last (HWM). If your index has 10mil blocks but it only needs 1mil blocks Oracle reads 9mil trash blocks.
ASSM (Automatic Segment Space Management) helps a lot to reduce/prevent clustering. If possible you should migrate Manual Segment Space Managed Tbs to ASSM Tbs.
Why checking for corruption? I always start analyzing a database by gathering ASM or statspack reports from the production database (not a test/dev db). This does not hurt anyone and will give you a lot of details on the database and it's little secrets. Corruption does usually not affect performance since the database does not try to repair it -- it just crashes the current query.
Best Answer
Oracle's optimiser is clever, it makes decisions about how best to do certain things (e.g. joins) based on what the contents of the tables are. At least, it can do if it knows what the contents of the tables are.
The optimiser uses table statistics to achieve this. If it knows that there is a particular type of data, or a particular distribution of values, or any number of different things going on in the table, it can change the way it executes the query that's being asked of it.
If there are no statistics, the optimiser has to guess or use defaults, and while the optimiser's defaults may be suitable or not matter that much at small volumes of data, when the volume of data in the table increases or the skew changes, this can make a huge difference to how quickly a query runs.
By analysing the tables, the statistics for the tables are rebuilt, and the optimiser has up-to-date information on what the contents of the table are, and can make more informed decisions.
Have a look here (http://docs.oracle.com/cd/E25054_01/server.1111/e16638/stats.htm) for Oracle's documentation on optimiser statistics.