Optimize Oracle Between Date Statement

Stu :

Based on the query above the functional index will provide no value. For a functional index to be used the predicate in the query would need to be written as follows:\n\nSELECT [fields] \nFROM MY_TABLE T \nWHERE TRUNC(T.EVT_END) BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 86399/86400\n AND T.TYPE = 123\n\n\nThe functional index on the column EVT_END, is being ignored. It would be better to have a normal index on the EVT_END date. For a functional index to be used the left hand of the condition must match the declaration of the functional index. I would probably write the query as:\n\nSELECT [fields] \nFROM MY_TABLE T \nWHERE T.EVT_END BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+1)\n AND T.TYPE = 123\n\n\nAnd I would create the following index:\n\nCREATE INDEX bla on MY_TABLE( EVT_END )\n\n\nThis is assuming you are trying to find the events that ended within a day.",

2012-11-27T17:43:53

Jon Heller :

Results\n\nIf your index is cached, a function-based index performs best. If your index is not cached, a compressed function-based index performs best.\n\nBelow are the relative times generated by my test code. Lower is better. You cannot compare the numbers between cached and non-cached, they are totally different tests.\n\n In cache Not in cache\nRegular 120 139\nFBI 100 138\nCompressed FBI 126 100\n\n\nI'm not sure why the FBI performs better than the regular index. (Although it's probably related to what you said about equality predicates versus range. You can see that the regular index has an extra \"FILTER\" step in its explain plan.) The compressed FBI has some additional overhead to uncompress the blocks. This small amount of extra CPU time is relevant when everything is already in memory, and CPU waits are most important. But when nothing is cached, and IO is more important, the reduced space of the compressed FBI helps a lot.\n\nAssumptions\n\nThere seems to be a lot of confusion about this question. The way I read it, you only care about this one specific query, and you want to know whether a function-based index or a regular index will be faster.\n\nI assume you do not care about other queries that may benefit from this index, additional time spent to maintain the index, if the developers remember to use it, or whether or not the optimizer chooses the index. (If the optimizer doesn't choose the index, which I think is unlikely, you can add a hint.) Let me know if any of these assumptions are wrong.\n\nCode\n\n--Create tables. 1 = regular, 2 = FBI, 3 = Compressed FBI\ncreate table my_table1(evt_end date, type number) nologging;\ncreate table my_table2(evt_end date, type number) nologging;\ncreate table my_table3(evt_end date, type number) nologging;\n\n--Create 1K days, each with 100K values\nbegin\n for i in 1 .. 1000 loop\n insert /*+ append */ into my_table1\n select sysdate + i - 500 + (level * interval '1' second), 1\n from dual connect by level <= 100000;\n\n commit;\n end loop;\nend;\n/\ninsert /*+ append */ into my_table2 select * from my_table1;\ninsert /*+ append */ into my_table3 select * from my_table1;\n\n--Create indexes\ncreate index my_table1_idx on my_table1(evt_end);\ncreate index my_table2_idx on my_table2(trunc(evt_end));\ncreate index my_table3_idx on my_table3(trunc(evt_end)) compress;\n\n--Gather statistics\nbegin\n dbms_stats.gather_table_stats(user, 'MY_TABLE1');\n dbms_stats.gather_table_stats(user, 'MY_TABLE2');\n dbms_stats.gather_table_stats(user, 'MY_TABLE3');\nend;\n/\n\n--Get the segment size.\n--This shows the main advantage of a compressed FBI, the lower space.\nselect segment_name, bytes/1024/1024/1024 GB\nfrom dba_segments\nwhere segment_name like 'MY_TABLE__IDX'\norder by segment_name;\n\nSEGMENT_NAME GB\nMY_TABLE1_IDX 2.0595703125\nMY_TABLE2_IDX 2.0478515625\nMY_TABLE3_IDX 1.1923828125\n\n\n--Test block.\n--Uncomment different lines to generate 6 different test cases.\n--Regular, Function-based, and Function-based compressed. Both cached and not-cached.\ndeclare\n v_count number;\n v_start_time number;\n v_total_time number := 0;\nbegin\n --Uncomment two lines to test the server when it's \"cold\", and nothing is cached.\n for i in 1 .. 10 loop\n execute immediate 'alter system flush buffer_cache';\n --Uncomment one line to test the server when it's \"hot\", and everything is cached.\n --for i in 1 .. 1000 loop\n\n v_start_time := dbms_utility.get_time;\n\n SELECT COUNT(*)\n INTO V_COUNT\n --#1: Regular\n FROM MY_TABLE1 T \n WHERE T.EVT_END BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 86399/86400;\n --#2: Function-based\n --FROM MY_TABLE2 T \n --WHERE TRUNC(T.EVT_END) = TRUNC(SYSDATE);\n --#3: Compressed function-based\n --FROM MY_TABLE3 T \n --WHERE TRUNC(T.EVT_END) = TRUNC(SYSDATE);\n\n v_total_time := v_total_time + (dbms_utility.get_time - v_start_time);\n end loop;\n\n dbms_output.put_line('Seconds: '||v_total_time/100);\nend;\n/\n\n\nTest Methodology\n\nI ran each block at least 5 times, alternated between run types (in case something was running on my machine only part of the time), threw out the high and the low run times, and averaged them. The code above does not include all that logic, since it would take up 90% of this answer.\n\nOther Things to Consider\n\nThere are still many other things to consider. My code assumes the data is inserted in a very index-friendly order. Things will be totally different if this is not true, as compression may not help at all.\n\nProbably the best solution to this problem is to avoid it completely with partitioning. For reading the same amount of data, a full table scan is much faster than an index read because it uses multi-block IO. But there are some downsides to partitioning, like the large amount of money\nrequired to buy the option, and extra maintenance tasks. For example, creating partitions ahead of time, or using interval partitioning (which has some other weird issues), gathering stats, deferred segment creation, etc.\n\nUltimately, you will need to test this yourself. But remember that testing even such a simple choice is difficult. You need realistic data, realistic tests, and a realistic environment. Realistic data is much harder than it sounds. With indexes, you cannot simply copy the data and build the indexes at once. create table my_table1 as select * from and create index ... will create a different index than if you create the table and perform a bunch of inserts and deletes in a specific order.",

2012-12-02T23:18:05

Lakshmanan Chidambaram :

@S1lence:\nI believe there would be a considerable time of thought behind this question being asked by you. And, I took a lot of time to post my answer here, as I don't like posting any guesses for answers.\nI would like to share my websearch experience on this choice of normal Index on a date column against FBIs.\nBased on my understanding on the link below, if you are about to use TRUNC function for sure, then you can strike out the option of normal index, as this consulting web space says that:\nEven though the column may have an index, the trunc built-in function will invalidate the index, causing sub-optimal execution with unnecessary I/O.\nI suppose that clears all. You've to go with FBI if you gonna use TRUNC for sure. Please let me know if my reply makes sense. \n\nOracle SQL Tuning with function-based indexes \n\nCheers,\nLakshmanan C.",

2012-11-28T11:00:41

Marlin Pierce :

Your index should be TYPE, EVT_END.\n\nCREATE INDEX PIndex\nON MY_TABLE (TYPE, EVT_END)\n\n\nThe optimizer plan will first go through this index to find the TYPE=123 section. Then under TYPE=123, it will have the EVT_END timestamps sorted, so it can search the b-tree for the first date in the range, and go through the dates sequentially until a data is out of the range.",

2012-11-21T13:54:05