 |
Optimize SELECE SQL Query
Hi, Oracle client Version 9, OS WinXP, About 6 million rows, index on
a.name.
I have a following SQL query.
Select a.id,a.name,a.age,a.class,a.tel from table01 a, table02 b
where a.name=USER and (a.id=b.id01 or a.id=b.id02 or a.id=b.id03 or
a.id=b.id04 or a.id=b.id05 or. a.id=b.id06)
The above query took long time.
How can I optimize this for better result?
Thanks.
beerora wrote: > Hi, > Oracle client Version 9, OS WinXP, About 6 million rows, index on
> a.name.
> I have a following SQL query.
> Select a.id,a.name,a.age,a.class,a.tel from table01 a, table02 b
> where a.name=USER and (a.id=b.id01 or a.id=b.id02 or a.id=b.id03 or
> a.id=b.id04 or a.id=b.id05 or. a.id=b.id06)
> The above query took long time.
> How can I optimize this for better result?
> Thanks.
There is no such thing as version 9. Is that 9.0.1 or 9.2.0.8 or some other version inbetween? Additionally, what is required to tune the statement is knowledge
of which indexes exist, the Explain Plan (created with DBMS_XPLAN),
the optimizer mode (presumably CBO), and current statistics
created with DBMS_STATS using the CASCADE=>TRUE option.
Post those and we can, perhaps, help.
BTW: What hardware
--
Daniel A. Morgan
University of Washington
damor@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
-----------------------------------------------Reply-----------------------------------------------
On Apr 24, 8:39 am, beerora <beersa.bee @gmail.com> wrote:
> Hi, > Oracle client Version 9, OS WinXP, About 6 million rows, index on
> a.name.
> I have a following SQL query.
> Select a.id,a.name,a.age,a.class,a.tel from table01 a, table02 b
> where a.name=USER and (a.id=b.id01 or a.id=b.id02 or a.id=b.id03 or
> a.id=b.id04 or a.id=b.id05 or. a.id=b.id06)
> The above query took long time.
> How can I optimize this for better result?
> Thanks.
What indexes do you have on the id column in the a or b tables? It looks like something is horribly wrong with the relational design
of at least the b table part if not more.
-----------------------------------------------Reply-----------------------------------------------
On 24 Apr., 14:39, beerora <beersa.bee @gmail.com> wrote: > Select a.id,a.name,a.age,a.class,a.tel from table01 a, table02 b > where a.name=USER and (a.id=b.id01 or a.id=b.id02 or a.id=b.id03 or > a.id=b.id04 or a.id=b.id05 or. a.id=b.id06) > How can I optimize this for better result?
IMHO it looks like you'd better optimize your data model, not the query, so you would not have to cross-join table02 to table01. However, maybe you can optimize the query by using exists: Select a.id,a.name,a.age,a.class,a.tel from table01 a
where a.name=USER and exists
(select * from table02 where a.id in=b.id01 or a.id=b.id02 or
a.id=b.id03 or
a.id=b.id04 or a.id=b.id05 or. a.id=b.id06)
Or as table01 seems to be very big and table02 assumingly rather
small, build a set of ids you are looking for first:
Select a.id,a.name,a.age,a.class,a.tel from table01 a
where a.name=USER and a.id in
(select id01 from table02
union
select id02 from table02
union
select id03 from table02
union
select id04 from table02
union
select id05from table02
union
select id06 from table02
)
-----------------------------------------------Reply-----------------------------------------------
On Apr 24, 8:39 am, beerora <beersa.bee @gmail.com> wrote:
> Hi, > Oracle client Version 9, OS WinXP, About 6 million rows, index on
> a.name.
> I have a following SQL query.
> Select a.id,a.name,a.age,a.class,a.tel from table01 a, table02 b
> where a.name=USER and (a.id=b.id01 or a.id=b.id02 or a.id=b.id03 or
> a.id=b.id04 or a.id=b.id05 or. a.id=b.id06)
> The above query took long time.
> How can I optimize this for better result?
> Thanks.
Where is the explain plan? What kind of table is table02 where the key of table01 can be equal to
any of six columns in a table02 row?
I suspect that you table design is not properly normalized. If
table02 is a derived table then you might be better off to go back to
the source to get the table02 data.
As written I would think you should have a single column index on all
six columns of table02 otherwise Oracle will have to full scan table02
up to six times in the case of the match being in id06 or when no
match exists in table02.
HTH -- Mark D Powell --
|
 |
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc
