Home » RDBMS Server » Performance Tuning » How to make table scan faster
| How to make table scan faster [message #272928] |
Mon, 08 October 2007 04:01  |
legna
Messages: 8
Registered: October 2007
|
Junior Member |
|
|
Hi ,
Suppose i have 2 mini tables with the following data,
create table abc ( id number, today_date date, name varchar2(20));
create table abc123 ( id number, last_date date, name varchar2(20));
insert into abc (id, today_date,name) values (1, sysdate, '1');
insert into abc (id, today_date,name) values (1, sysdate - 1, '2');
insert into abc (id, today_date,name) values (1, sysdate - 2, '3');
insert into abc (id, today_date,name) values (1, sysdate - 2, '4');
insert into abc (id, today_date,name) values (1, sysdate - 2, '5');
insert into abc (id, today_date,name) values (1, sysdate - 2, '6');
insert into abc (id, today_date,name) values (1, sysdate - 2, '7');
insert into abc (id, today_date,name) values (1, sysdate - 2, '8');
insert into abc (id, today_date,name) values (1, sysdate - 2, '9');
insert into abc (id, today_date,name) values (1, sysdate - 2, '10');
insert into abc (id, today_date,name) values (1, sysdate - 2, '11');
insert into abc (id, today_date,name) values (1, sysdate - 2, '12');
insert into abc (id, today_date,name) values (1, sysdate - 2, '13');
insert into abc (id, today_date,name) values (1, sysdate - 2, '14');
insert into abc (id, today_date,name) values (1, sysdate - 2, '15');
insert into abc (id, today_date,name) values (1, sysdate - 2, '16');
insert into abc (id, today_date,name) values (1, sysdate - 2, '17');
insert into abc (id, today_date,name) values (1, sysdate - 2, '18');
insert into abc (id, today_date,name) values (1, sysdate - 2, '19');
insert into abc (id, today_date,name) values (1, sysdate - 2, '20');
insert into abc123 (id, last_date,name) values (1, sysdate - 2, '1');
insert into abc123 (id, last_date,name) values (1, sysdate - 2, '2');
insert into abc123 (id, last_date,name) values (1, sysdate, '3');
insert into abc123 (id, last_date,name) values (1, sysdate, '4');
insert into abc123 (id, last_date,name) values (1, sysdate, '5');
insert into abc123 (id, last_date,name) values (1, sysdate, '6');
insert into abc123 (id, last_date,name) values (1, sysdate, '7');
and i did this query
select * from abc a, abc123 b
where a.name=b.name and a.today_date < '8-oct-2007'
and b.last_date > '6-oct-2007'
It did a table scan on the original table abc (even though it has an index on column today_date) which looks at a few months' data since the original table holds data for a number of months. At times, it's almost one year of data.
Is there a way to re-write the query such that the table scan is faster or to make it use the index?
Thanks in advance!
|
|
|
|
|
|
|
|
| Re: How to make table scan faster [message #272940 is a reply to message #272928] |
Mon, 08 October 2007 05:00  |
legna
Messages: 8
Registered: October 2007
|
Junior Member |
|
|
Tables abc and abc123 are miniature of the original tables which i have. In my original tables, there are indexes on the columns. However, it is not used for my query.
Anyway, thanks for you suggestions!
|
|
|
|
| Re: How to make table scan faster [message #272942 is a reply to message #272940] |
Mon, 08 October 2007 05:11 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Without knowing what the indexes are, it's hard (read: impossible) to say what the best access path would be.
Ideally you'd tell us something about the distributrion of data in the tables (how many times each (name,today_date) combination would appear, how many records there are for each Today_date or last date etc).
Working with what you've given us, I'd say that if you've got data for the last few months, you're unlikely to get the index on ABC.TODAY_DATE used for a clause likeand a.today_date < to_date('8-oct-2007','dd-mon-yyyy')
|
|
|
|
| Re: How to make table scan faster [message #272944 is a reply to message #272928] |
Mon, 08 October 2007 05:28 |
legna
Messages: 8
Registered: October 2007
|
Junior Member |
|
|
That's what i believed too. Is there a work around for it? This query occurs quite often and i'm afraid that it's going to be a bottleneck. (though it seems to be now)
There's approximately 800 plus records for each today_date/last_date. As for the (name, today_date) combination, the combination is almost distinct such that there's approximately 800 (name, today_date) for each day.
Hope this helps!
Thanks!
|
|
|
|
| Re: How to make table scan faster [message #272945 is a reply to message #272944] |
Mon, 08 October 2007 05:46 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
From what you say, it sounds like your queries are going to return nearly all the rows from ABC.
If there are about 800 records for name/last_date then the query on ABC123 will return pretty much all the distinct NAMEs.
This will then get you all the records in ABC for each of those NAMES, which will be pretty much the entire table.
I think the questions you need to ask is: Do I need all these rows
|
|
|
|
Goto Forum:
Current Time: Tue Jun 25 20:17:43 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
