| Simple query taking too long - looking for Index Suggestion [message #366278] |
Fri, 12 December 2008 10:40  |
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 #366310 is a reply to message #366281] |
Fri, 12 December 2008 13:27  |
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 #366367 is a reply to message #366353] |
Sat, 13 December 2008 01:43 |
 |
Michel Cadot
Messages: 68665
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
