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 Go to next message
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 #272929 is a reply to message #272928] Mon, 08 October 2007 04:06 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

By creating index.

Table don't have primary key!
Re: How to make table scan faster [message #272930 is a reply to message #272928] Mon, 08 October 2007 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First don't compare dates and strings.

Regards
Michel
Re: How to make table scan faster [message #272940 is a reply to message #272928] Mon, 08 October 2007 05:00 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 like
and a.today_date < to_date('8-oct-2007','dd-mon-yyyy')
because it will be looking at the majority of the table.

Re: How to make table scan faster [message #272944 is a reply to message #272928] Mon, 08 October 2007 05:28 Go to previous messageGo to next message
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 Go to previous message
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


Previous Topic: Help reading TOAD Explain Plan
Next Topic: SQL Query for tuning
Goto Forum:
  


Current Time: Sun Dec 04 06:44:46 CST 2016

Total time taken to generate the page: 0.04370 seconds