| simple sql statement - performance issue [message #373688] |
Tue, 01 May 2001 16:35  |
Mike Oakes
Messages: 25
Registered: December 2000
|
Junior Member |
|
|
Hello, any help would be greatly appreciated.
I have a simple query and the performance is very slow.
My sql statment looks like.
select id from table where id like 'SAB' and action = 'COMMIT';
The table contains about 150,000 records. The id column is defined as char (10) and action as char(8) I have a unique index on id column and an index on action. It takes a couple minutes to return 5 records.
If i do both of these queries by themselves the result set is very quick, in a matter of seconds.
I used the explain plan but that didn't really give me any good info. It said i was using the correct index.
I also tried adding an index combining both columns but that didn't help performance.
Any ideas would be appreciated.
Thanks,
Mike Oakes
|
|
|
|
| Re: simple sql statement - performance issue [message #373691 is a reply to message #373688] |
Tue, 01 May 2001 19:15  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Your strategy should be to apply the criteria in order to discard data as soon as possible from the result set. In your case you need to further consider the efficiency of the filtering clause. The "= 'COMMIT'" will use an index, whereas the "like 'SAB'" will us a full table scan which is a lot more expensive.
Oracle generally applies the conditions from the bottom up i.e. action = 'COMMIT' and then like 'ABS'. Try swapping the conditions.
The "like 'ABS'" wont do much for you as you don't have any wildcards which is what the statement is used for - so I guess you are missing or or more '%' or '_' in your example.
Before proceeding, gather fresh stats on the table. It's bad to have no stats, it's worse to have incorrect stats which are badly out.
"analyze table ABC estimate statistics;" whenever the quantity of data of distribution of values changes a lot.
select table_name, num_rows, last_analyzed from user_tables where table_name = 'ABC';
select table_name, index_name, distinct_keys, num_rows, last_analyzed from user_indexes where table_name = 'ABC';
If your table has had a lot more than the current 150000 rows previously it may be a good candidate for rebuilding bith tables an indexes to free up unused space. there are other ways, but you could try these:
alter table ABC move tablespace USERS;
alter index XYZ rebuild;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
