Home » RDBMS Server » Performance Tuning » Simple query taking too long - looking for Index Suggestion (9.2.0.7)
Simple query taking too long - looking for Index Suggestion [message #366278] Fri, 12 December 2008 10:40 Go to next message
agurm
Messages: 3
Registered: December 2008
Location: Toronto
Junior Member
Hello,

I have simple query as below:

SELECT KEY, SURNAME, GIVEN, BEN_CALCDATE, TERM FROM B_CALC
WHERE BEN_CALCDATE IN (SELECT MAX(BEN_CALCDATE) FROM B_CALC WHERE KEY=0363146)

Let me describe the table and index a bit. The KEY field is an varchar field. There two separate indexes, one on KEY and one on BEN_CALCDATE.

If in where clause I have KEY=0363146, it takes too long and understandably does not use index (TABLE ACCESS FULL). If I have KEY='0363146', the query comes back instantly. I know the eventual fix is very simple, that is to modifiy the program that dynamically builds the queries based on key type. But code change bureaucracy prolongs the change process!

My question: In the meantime, is there any way I can build the index so query without quotes will run faster?
Thanks.

Re: Simple query taking too long - looking for Index Suggestion [message #366281 is a reply to message #366278] Fri, 12 December 2008 11:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it takes too long

Compare to what.

Read OraFAQ Forum Guide and forum sticky to know what are the information you have to gave and how to post.

Regards
Michel

[Updated on: Fri, 12 December 2008 11:23]

Report message to a moderator

Re: Simple query taking too long - looking for Index Suggestion [message #366310 is a reply to message #366281] Fri, 12 December 2008 13:27 Go to previous messageGo to next message
agurm
Messages: 3
Registered: December 2008
Location: Toronto
Junior Member
Sorry, I thought I had all the information.

Query as KEY=0363146 is slow compared to KEY='0363146'. I am actually surprised that it was not an SQL error (KEY=0363146) as KEY is actually VARCHAR. But Oracle does return a row but after a long time.

Yes, I did read the FAQ and other posts. Usually I can get my answer in one of the post.

Thanks.
Re: Simple query taking too long - looking for Index Suggestion [message #366315 is a reply to message #366310] Fri, 12 December 2008 14:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Query as KEY=0363146 is slow compared to KEY='0363146'.

Because there is an implicit conversion.

Quote:
. Usually I can get my answer in one of the post.

Surely because others are much smarter than you are and can fill the voids you let.

Quote:
I did read the FAQ and other posts

No you didn't otherwise you should post the usual and requested information for Performances and tuning question.

Regards
Michel
Re: Simple query taking too long - looking for Index Suggestion [message #366353 is a reply to message #366315] Fri, 12 December 2008 23:22 Go to previous messageGo to next message
agurm
Messages: 3
Registered: December 2008
Location: Toronto
Junior Member
Thank you Michel!

You know more about me than I do. Don't know what you are pissed at. You are the one who actually did not even read my original post correctly. Maybe it was too simple for your complex brain.

Thanks again for the great welcome for new member. I guess there are only smart people in this forum and not for one like myself. I won't waste another byte past the smilie! Sad

Re: Simple query taking too long - looking for Index Suggestion [message #366367 is a reply to message #366353] Sat, 13 December 2008 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The answer is directly related to my previous answer: create an index on "TO_NUMBER(KEY)".

If you got (and posted as requested in guidelines) an explain plan you'd see there is this conversion and filter.

SQL> create table t (col varchar2(10));

Table created.

SQL> insert into t select to_char(object_id) from all_objects;

47712 rows created.

SQL> create index t_i on t(col);

Index created.

SQL> set autotrace traceonly explain
SQL> select * from t where col=12345;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |    19   (6)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     7 |    19   (6)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("COL")=12345)

SQL> set autotrace off
SQL> create index t_i2 on t(to_number(col));

Index created.

SQL> set autotrace traceonly explain
SQL> select * from t where col=12345;

Execution Plan
----------------------------------------------------------
Plan hash value: 42090455

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |     7 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_I2 |   169 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(TO_NUMBER("COL")=12345)


Regards
Michel

[Edit: emphasize FILTER]

[Updated on: Sat, 13 December 2008 09:12]

Report message to a moderator

Re: Simple query taking too long - looking for Index Suggestion [message #376081 is a reply to message #366367] Mon, 15 December 2008 23:58 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you cannot add quotes to the query, can you add a TO_CHAR() around the constant value?

Ross Leishman
Previous Topic: Redo Log Tuning
Next Topic: Histograms: 32 character limit generates inaccurate stats
Goto Forum:
  


Current Time: Sat Dec 10 03:00:46 CST 2016

Total time taken to generate the page: 0.08390 seconds