| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Tuning Oracle SQL
Jerry Metz wrote:
>
> Steve Chapman wrote:
> >
> > I think what you need is 5 indexes:
> >
> > t1.col1
> > t1.col2
> > t2.col1
> > t2.col2
> > t1.col3
> >
> > Make sure you size the indexes properly:
> >
> > create index i_stuff on stuff_tbl (col1)
> > tablespace mytblspace storage (initial 1M next 100K);
> >
> > and the base table, too!
> >
> > Check the Oracle Tuning books for specifics on sizing.
> >
> > Use Explain Plan to benchmark the various possibilities.
> >
> > enjoy!
> >
> > steve chapman
> >
> > Matthew James wrote:
> > >
> > > Hi
> > >
> > > I'm after advice on tuning of Oracle SQL statements. I have some statements
> > > of the basic form:
> > >
> > > select <columns>
> > > from t_1, t_2
> > > where t_1.col1 = t_2.col1
> > > and t_1.col2 = t_2.col2
> > > order by t_1.col1 asc, t_1.col3 asc;
> > >
> > > I want to try and speed this up (some currently run ~45s -> 1 min), how
> > > should I best do this:
> > >
> > > - composite index on t_1(col1, col2) // already there
> > > - composite index on *both* t_1(col1, col2) and t_2(col1, col2)
> > > - composite index on t_1(col1, col3)
> > > - some combination of the above
> > >
> > > I guess what I'm asking is:
> > > - should indexes only go on where conditions or do they have effect for
> > > order by as well ?
> > > - if I have an index on both tables will that make things quicker ? I
> > > tried this but there is always a FULL scan for one of the tables (from
> > > explain plan output). My thinking is that Oracle should be able to
> > > run down one index and cross check with the other index and only hit
> > > the tables if there is a match - where's the catch ?
> > >
> > > Thanks
> > > Matt
>
> I don't necessarily disagree with the proposed solution, but the first
> question you need to ask is : How many rows are in each table? If each
> table has a few hundred rows, an index won't really help much (and may
> in fact, slow things down during the insert phase). If on the other
> hand, the tables have millions of rows each, an index could be quite
> helpful, if you pick the right columns to index upon, AND your select
> statement does not avoid use of the index because of something that you
> may be doing in your where clause. I think that a trip to your DBA's
> office might be useful so that TOGETHER you can devise something that
> will help you and not provide an adverse affect to the rest of the
> ORACLE user community.
> Jerry
I'm a DBA, most of the users don't like to ask, but they should! Really good hint.
Some other tips:
+ If you don't specify any condition in addition to the join,
one FULL SCAN is ok, or am I missing something?
+ Your order by clause suggests, that your primary key holds
meaningful data. Avoid that, create a sequence and use it as
primary key on inserts with a single column. create the foreign
key and create an index on it, use another tablespace at another
disk drive to hold the indexes, example below.
+ Avoid the order by clause! Something tricky. For example:
create an index on (col1, col3)
write your statement as
select <columns>
from t_1, t_2
where t_1.col1 = t_2.col1
and t_1.col2 = t_2.col2
and t_1.col1 > <lowest possible value>
and t_1.col3 > <lowest possible value>
Maybe it doesn't work with two columns, I didn't try that before.
For a single column the t_1.col1 > <lowest possible value>
condition forces a range scan and returns already sorted rows.
If it doesn't work try to give the optimizer hints in comments,
check out your docs.
Example of an alternate data structure,
adjust the sizes, they are really small :)
define indexts = &1
create sequence seq$keygen start with 100000 ;
/* useful start, you can insert some records by hand using
some lower numbers like 1,2,3 to test your views and triggers */ create table t_1 (
key#1 number not null,
col1 varchar2 (30) not null,
col2 varchar2 (30) not null,
col3 varchar2 (30) not null,
constraint pk_t_1
primary key (key#1)
using index tablespace &indexts
storage (initial 40k next 20k pctincrease 0),
/* or whatever your storage needs are */
constraint sk_t_1
unique (col1, col2)
using index tablespace &indexts
storage (initial 80k next 32k pctincrease 0)
) pctfree 5 storage (initial 4k next 4k pctincrease 0) ;
create index key_1_t_1
on t_1 (col1, col3)
tablespace &indexts ;
create table t_2 (
key#2 number not null, /* if you need a primary key */
key#1 number not null,
primary key (key#2)
using index tablespace &indexts,
constraint fk1_t_2
foreign key (key#1)
references t_1 (key#1)
on t_2 (key#1)
tablespace &indexts ;
Use the sequence to fill key#1 and key#2 (just like an object id). The key fk1_t_2 ensures fast access for key#1 values. Your query looks now:
select <columns>
from t_1, t_2
where t_1.key#1 = t_2.key#1
and t_1.col1 > <lowest possible value>
and t_1.col3 > <lowest possible value>
Hope it helps (so many days after :)
Stephan
-- --------------------------------------------------------------- <stephan.witt_at_beusen.de> | "beusen" Software+Systeme GmbH fon: +49 30 549932-62 | Landsberger Allee 392 fax: +49 30 549932-21 | 12681 Berlin, Germany ---------------------------------------------------------------Received on Fri Dec 13 1996 - 00:00:00 CST
 |
 |