ansaurus

Question

Oracle query optimization using dynamic dates

Answer 1

A:

Response to Cagcowboy

There is no issue with the to_date on the other side of the = from the column.

Only casting a COLUMN away from type is an issue.

Example:

create table index_test
as
select object_name, created as create_dt from all_objects;

create index it_create_dt_idx on index_test (create_dt) compute statistics;

INDEX is used with no issue with TO_DATE is NOT on the Column

set autotrace on explain
select * from index_test where create_dt = to_date('17-SEP-2000', 'DD-MON-YYYY');

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=26)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'INDEX_TEST' (TABLE) (Cos
          t=1 Card=1 Bytes=26)

   2    1     INDEX (RANGE SCAN) OF 'IT_CREATE_DT_IDX' (INDEX) (Cost=1
           Card=1)

A function on THE INDEXED COLUMN is obviates the index.

SQL> select * from index_test where trunc(create_dt) = to_date('17-SEP-2000', 'DD-MON-YYYY');


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=59 Card=3 Bytes=78
          )

   1    0   TABLE ACCESS (FULL) OF 'INDEX_TEST' (TABLE) (Cost=59 Card=
          3 Bytes=78)

Stephanie Page 2010-09-20 20:00:59

Oh well, he deleted his wrong advice. I'm leaving this here though in case someone else decides to post the wrong answer.

Stephanie Page 2010-09-20 20:02:28

Didn't read the question properly.

cagcowboy 2010-09-20 20:08:51

Answer 2

+3 A:

You have two queries

select * from view where date = '17-sep-10'
select * from view where date = to_date(...

In the first, the 'date' column in the view is being compared to a character literal. In the second it is being compared to a date.

If the first is working (and quickly), it is possible that the 'date' column in the view is a VARCHAR2 column rather than a date. The fact that you are doing a TO_DATE on a column called current_business_date suggests that you may be using the wrong datatypes.

Do a DESC VIEW in SQL*Plus (or equivalent in whatever tool you use).

Also do an EXPLAIN PLAN for both select * from view where date = '17-sep-10' and select * from view where date = date '2010-09-17'

Gary 2010-09-20 23:48:57

I think if the NLS settings are right Oracle might do the date casting to the literal. You might be onto something however - another way to check would be "select * from view where date = to_date('17-sep-10', 'DD-MON-YY')"

dpbradley 2010-09-21 00:25:41

I did the explain plan using that query and it runs fast. It only slows down when I use our v_business_day view

Dan 2010-09-23 14:00:12

Answer 3

A:

On the execution plans, it would also be helpful to know on which step the date predicate was being applied.

The actual response time culprit in the second plan is the full scan on POSITIONS. In the first plan we are doing an index range scan using POSITIONS_IDX_01. What columns are in that index? In particular, do any of them relate to the business_date column in the view?

I'm guessing that the v_business_day view is based on PARAMETER_CONTROL, since that seems to be referenced more times in the second plan than in the first. The portions of the second plan that are based entirely on this table have estimated cardinalities of 1, so it does not appear that the problem is that Oracle thinks the subquery might be returning multiple rows (which jives with your statement that adding the condition on ROWNUM to the subquery did not change the performance).

Response to comment: It sounds like the statistics on POSITIONS.BUSINESS_DATE are such that the optimizer can see that the literal date value will be highly selective, but that an arbitrary date value is likely to be highly unselective. You should look into the statistics -- in USER_TABLES, USER_INDEXES, and USER_HISTOGRAMS -- to see if they are out of date and what they indicate about the data distribution of this column.

The simplest way out of this may be to do one query to get the current business date, then insert it as a literal into the query against the complex view.

Dave Costa 2010-09-22 15:49:18

positions_idx_01 has columns "BUSINESS_DATE", "SOURCE", and "ACCOUNT_NUMBER". I did try and add an index hint on the 2nd query but that did not help.

Dan 2010-09-23 13:15:29

Answer 4

A:

I'm going to stick my head out and put this down to column stats or skew.

One plan has

|* 57 | TABLE ACCESS BY INDEX ROWID| POSITIONS        | 2432 | 767K| 1549 (1)| 00:00:19 |       
|* 58 |  INDEX RANGE SCAN          | POSITIONS_IDX_01 | 5676 |     |  737 (2)| 00:00:09 |

The other has

|* 46 |  TABLE ACCESS FULL         | POSITIONS        |  806K| 248M| 365K (3)| 01:13:09 |

Given the date '17-Sep-2010', Oracle is assuming several thousand rows will be returned. Given another non-specific date, Oracle is assuming a much larger number, so large that the date index is useless. I suspect most of the rows in the table were for the same small number of dates (probably less than a hundred distinct values - but you can look in NUM_DISTINCT in USER_TAB_COLUMNS).

I say were because I think the stats may be out of date. There'll be a LOW_VALUE and HIGH_VALUE on USER_TAB_COLUMNS for that date column in the table (in RAW format, not as actual dates). When faced with a query for a value above the recorded high_value, 10g makes some assumptions. If there are a million rows for a hundred distinct values, it will assume 10,000 rows for each value [in the absence of histograms] but for a value above the HIGH_VALUE it will assume less than 10,000 rows. It gets all statistical in its calculations, so its hard to be precise. But its enough that the database is thinking that rows for Sept 17th are sufficiently small in number as to make it worth using the index.

So there's four things to determine. How many rows are in the table, and what is the distribution over dates. How many rows do the stats indicate are in the table and what do the stats suggest is the distribution over dates.

Gary 2010-09-24 00:51:03

ansaurus

tags:

views:

answers:

Oracle query optimization using dynamic dates

related questions