Explain Simple Performance Issue

[dukeslater]

Can someone please explain why the first query runs in a few seconds while the second query takes forever to run?

1. select todatefunction(numericfield), otherfields
from table
where numericfield = 1050424

2. select todatefunction(numericfield), otherfields
from table
where numericfield = tonumberfunction(sysdate)

The database stores dates as numbers, so I use two functions to convert those numbers to true date fields or to convert a date to their number format.

My understanding has always been that since we're comparing the field value to a constant there should be no difference in the performance between the two queries. Clearly I'm wrong, but I don't know why (or how to write this to process quickly).

Thanks as always for your help.

 

[SantaMufasa]

Duke,

Here are my presumptions as to why your two queries differ in performance:

1) You have many rows in your table.
2) You have an index on <numericfield>.

When you use a function in your WHERE clause (e.g., "tonumberfunction(sysdate)") Oracle no longer uses the index. When it doesn't use the index, Oracle must do a full-table scan, thus causing excruciating performance.

Does all this match your reality?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[dukeslater]

You're right about the table size - about 2 million. However, upon further investigation I find that this table is actually a view to a linked server - who needs naming conventions?

So that may limit my options, but if you have any ideas as to making this run more efficiently I'm all ears. The fast version takes about 10 seconds; the slow version takes five minutes. It would run at night so it's not the end of the world if we can't improve upon it.

Thanks for you input and quick reply.

 

[SantaMufasa]

Duke,

Although "the table" is actually "a view", in the underlying table (providing input to the view), I'm still betting that "You have an index on <numericfield>", correct? The index on the underlying table still makes all the difference in the world.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[dukeslater]

I'm sure you're right - it would be a likely candidate for an index, but I don't know that I can verify that. I have rights to the view, but not rights to the database link. If we assume that's true, can I force the query to use the index?

Thanks again.

 

[SantaMufasa]

One way to force it to use the index is to use a WHERE clause in the following format:

...WHERE <indexed-column-name> = <raw expression>...

...where neither <indexed-column-name> nor <raw expression> are modified by functions or operators.

Did this answer your question?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[dukeslater]

Understood, thanks. I actually lucked out a little bit since this will ultimately be a SQL Server query to a linked Oracle Server. Once I moved the date restriction to outside the Oracle query, it knocked it down to about 2 minutes. I can live with that.

The main thing is now I understand what the problem was - thanks for all your help.