Re: speedup the query ~~

If you are still using the rule based optimizer (which you really shouldn't do) you need to reverse the tables in the from list. IMO, there's not much that can be optimized here, a join is a join, and provided there is proper indexing, that's about it. Also IMO (before I divert into comments about clustering) either the design or the column naming is crappy. From the design it looks like those two tables should be one, but then there is no logical explanation for the multiple records in tableb, which brings me to the remark the column naming is crappy.
If there is a 1:n relationschip between a and b, the primary key of b is incorrect.

Hth,

Sybrand Bakker, Oracle DBA

feib <agi_at_feib.com.tw> schreef in berichtnieuws MPG.136fd7902753f451989687_at_news.gcn.net.tw...
> [This followup was posted to comp.databases.oracle.tools and a copy was
 sent to the cited author.]
>
> Hello,everyone
>
> I have two tables A,B (A 2,000 records, B 600,000 records)
>
> table A look like follows
> Name Null? Type
> ------------------------------- -------- ----
> BRNO NUMBER(3)
> UNINO NOT NULL VARCHAR2(11) -------PK
> ZIPCD NUMBER(5)
> ADDR VARCHAR2(72)
>
> table B look like follows
> Name Null? Type
> ------------------------------- -------- ----
> UNINO NOT NULL VARCHAR2(11) -------PK
> BRNO NUMBER(3)
> CADRZIPCD NUMBER(5)
> COMMADR VARCHAR2(62)
>
>
> select a.brno, a.unino, a.addr, b.commadr
> from tableA a, table B b
> where a.unino=b.unino
> /
>
>
> How to rewrite to speed up the query ??
>
> Best Regards,
> Agi Chen
>
>
>
Received on Wed Apr 26 2000 - 00:00:00 CDT