| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: update performance
This is a multi-part message in MIME format.
------=_NextPart_000_001B_01C08056.90CA6560 Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
RE: Date comparison questionHi KP;
Please ANALYZE your tables and run SQL.
SA
I ahve a requirement to update a table using a joing - details:
Table A: (colA, colB,...): 2 million rows
Table B: (colA, colC,...): 2 milion rows
=20
update cmd:
=20
update tableA A
set colA =3D (select B.colC fdrom tableB B where B.tableB.colA =3D =
A.tableA.colA )
=20
I tried it in PL/SQL and as the straight SQL but it takes FOREVER =
without coming back...
should I be doiing something better?
=20
(I have an index on the two columns on TableB)
=20
Thanks
-Krishna.
=20
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Krishna =
Prasad
Sent: Monday, January 08, 2001 8:41 PM To: Multiple recipients of list ORACLE-L Subject: RE: Btmap Index
Thanks - I'll try the Hint - the table has 2 million rows (with = about 600 distinct values for this column)
=20
-krishna.
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Kevin =
Kostyszyn
Sent: Monday, January 08, 2001 3:50 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Btmap Index
I ran into a similar problem a while back. If the database is set =
to use the CBO, it might not use the index. Someone on this list gave a =
great reason why, perhaps the table is just very small and the CBO just =
decided to do a full table scan instead. You could use a hint and tell =
it to use rule based on that query and it will use the index on the =
where clause. That's what I did. The question is, how big is the table =
and how much data does it have in it? If it is not a lot, there really =
is nothing to be concerned about. Oh yeah, and almost 100% positive =
that there is no setting in the init file for the index. =20
Sincerely,
Kevin
----- Original Message -----=20
From: Krishna Prasad=20
To: Multiple recipients of list ORACLE-L=20
Sent: Monday, January 08, 2001 5:45 PM
Subject: Btmap Index
Hi,
=20
Is there any init.ora parameter that needs to be turned to =
activate bitmap indexes?
I have a bitmap index on one column C1, but when i examine the =
explain pan of the=20
query: " select C1, b, c from table TT where C1 in ('dd','ff')" =
, it looks like it is not using
the index but doing a full table scan...
=20
thanks
-krishna
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of =
Jacques Kilchoer
Sent: Monday, January 08, 2001 2:01 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Date comparison question
> -----Original Message-----=20
> From: Lisa Yates [mailto:cosnit_at_creighton.edu]=20
> Sent: lundi, 8. janvier 2001 11:27=20
>=20
> Why doesn't this query ever return...=20
>=20
> where run_date =3D to_date('01-03-2001 16:34:59','mm-dd-yyyy =
> hh24:mi:ss')=20
>=20
> but this query does....=20
>=20
> where to_char(run_date) =3D to_char(to_date('01-03-2001=20
> 16:34:59','mm-dd-yyyy hh24:mi:ss'))=20
>=20
> and so does this query....=20
>=20
> where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') =3D =
'01-03-2001=20
> 16:34:59'=20
Well, I guess my suggestion that run_date may contain a "BCE" =
(before common era) date was not the answer to your problem. Maybe I =
misunderstood the issue. When you say "why doesn't the query ever =
return", do you mean it returns zero rows, or do you mean that the query =
is taking an abnormally long time? If it's taking an abnormally long =
time, an explain plan on the two queries may reveal the cause.
Jacques R. Kilcho=EBr=20
(949) 754-8816=20
Quest Software, Inc.=20
8001 Irvine Center Drive=20
Irvine, California 92618=20
U.S.A.=20
http://www.quest.com=20
------=_NextPart_000_001B_01C08056.90CA6560 Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD><TITLE>RE: Date comparison question</TITLE> <META content=3D"text/html; charset=3Diso-8859-1" =http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2614.3500" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT face=3DArial size=3D2>Hi KP;</FONT></DIV> <DIV><FONT face=3DArial size=3D2>Please ANALYZE your tables and run=20SQL.</FONT></DIV>
<DIV> </DIV> <DIV><FONT face=3DArial size=3D2>SA</FONT></DIV> <BLOCKQUOTE=20
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20 class=3D125045507-10012001>update tableA A</SPAN></FONT></DIV> <DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =class=3D125045507-10012001>set=20
size=3D2>-----Original Message-----<BR><B>From:</B> root_at_fatcity.com =
[mailto:root_at_fatcity.com]<B>On Behalf Of </B>Krishna = Prasad<BR><B>Sent:</B>=20
Monday, January 08, 2001 8:41 PM<BR><B>To:</B> Multiple recipients = of list=20
ORACLE-L<BR><B>Subject:</B> RE: Btmap Index<BR><BR></DIV></FONT> <DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20 class=3D453413904-09012001>Thanks - I'll try the Hint - the table = has 2=20
million rows (with about 600 distinct values for this=20
column)</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D453413904-09012001></SPAN></FONT> </DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D453413904-09012001>-krishna.</SPAN></FONT></DIV>
<BLOCKQUOTE style=3D"MARGIN-RIGHT: 0px">
<DIV align=3Dleft class=3DOutlookMessageHeader dir=3Dltr><FONT =
face=3DTahoma=20
size=3D2>-----Original Message-----<BR><B>From:</B> = root_at_fatcity.com=20
[mailto:root_at_fatcity.com]<B>On Behalf Of </B>Kevin=20
Kostyszyn<BR><B>Sent:</B> Monday, January 08, 2001 3:50 =
PM<BR><B>To:</B>=20
Multiple recipients of list ORACLE-L<BR><B>Subject:</B> Re: Btmap=20
Index<BR><BR></DIV></FONT>
<DIV><FONT face=3DArial size=3D2>I ran into a similar problem a =
while=20
back. If the database is set to use the CBO, it might not =
use the=20
index. Someone on this list gave a great reason why, =
perhaps=20
the table is just very small and the CBO just decided to do a full =
table=20
scan instead. You could use a hint and tell it to use rule =
based on=20
that query and it will use the index on the where clause. =
That's=20
what I did. The question is, how big is the table and how =
much data=20
does it have in it? If it is not a lot, there really is =
nothing to=20
be concerned about. Oh yeah, and almost 100% positive that =
there is=20
no setting in the init file for the index. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Sincerely,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Kevin</FONT></DIV>
<BLOCKQUOTE=20
style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; =
MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">
<DIV style=3D"FONT: 10pt arial">----- Original Message ----- =
</DIV>
<DIV=20
style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black"><B>From:</B>=20
<A href=3D"mailto:krishna_at_nehanet.com" =
title=3Dkrishna_at_nehanet.com>Krishna=20
Prasad</A> </DIV>
<DIV style=3D"FONT: 10pt arial"><B>To:</B> <A=20
href=3D"mailto:ORACLE-L_at_fatcity.com" =
title=3DORACLE-L_at_fatcity.com>Multiple=20
recipients of list ORACLE-L</A> </DIV>
<DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Monday, January 08, =
2001 5:45=20
PM</DIV>
<DIV style=3D"FONT: 10pt arial"><B>Subject:</B> Btmap =
Index</DIV>
<DIV><BR></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D953563822-08012001>Hi,</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D953563822-08012001></SPAN></FONT> </DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D953563822-08012001>Is there any init.ora parameter that =
needs to=20
be turned to activate bitmap indexes?</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D953563822-08012001>I have a bitmap index on one column =
C1, but=20
when i examine the explain pan of the </SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D953563822-08012001>query: " select C1, b, c from =
table TT=20
where C1 in ('dd','ff')" , it looks like it is not=20
using</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D953563822-08012001>the index but doing a full table=20
scan...</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D953563822-08012001></SPAN></FONT> </DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D953563822-08012001>thanks</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D953563822-08012001>-krishna</SPAN></FONT></DIV>
<BLOCKQUOTE style=3D"MARGIN-RIGHT: 0px">
<DIV align=3Dleft class=3DOutlookMessageHeader dir=3Dltr><FONT =
face=3DTahoma=20
size=3D2>-----Original Message-----<BR><B>From:</B> =
root_at_fatcity.com=20
[mailto:root_at_fatcity.com]<B>On Behalf Of </B>Jacques=20
Kilchoer<BR><B>Sent:</B> Monday, January 08, 2001 2:01=20
PM<BR><B>To:</B> Multiple recipients of list=20
ORACLE-L<BR><B>Subject:</B> RE: Date comparison=20
question<BR><BR></DIV></FONT>
<P><FONT size=3D2>> -----Original Message-----</FONT> =
<BR><FONT=20
size=3D2>> From: Lisa Yates [<A=20
=
<BR><FONT size=3D2>> Sent: lundi, 8. janvier 2001 = 11:27</FONT>=20
<BR><FONT size=3D2>> </FONT><BR><FONT size=3D2>> Why = doesn't this=20
query ever return...</FONT> <BR><FONT size=3D2>> = </FONT><BR><FONT=20
size=3D2>> where run_date =3D to_date('01-03-2001 = 16:34:59','mm-dd-yyyy=20
</FONT><BR><FONT size=3D2>> hh24:mi:ss')</FONT> <BR><FONT = size=3D2>>=20
</FONT><BR><FONT size=3D2>> but this query does....</FONT> = <BR><FONT=20
size=3D2>> </FONT><BR><FONT size=3D2>> where = to_char(run_date) =3D=20
to_char(to_date('01-03-2001</FONT> <BR><FONT size=3D2>>=20
16:34:59','mm-dd-yyyy hh24:mi:ss'))</FONT> <BR><FONT =
size=3D2>>=20
</FONT><BR><FONT size=3D2>> and so does this =
query....</FONT>=20
<BR><FONT size=3D2>> </FONT><BR><FONT size=3D2>> where=20
to_char(run_date,'mm-dd-yyyy hh24:mi:ss') =3D '01-03-2001=20
</FONT><BR><FONT size=3D2>> 16:34:59'</FONT> </P>
<P><FONT size=3D2>Well, I guess my suggestion that run_date =
may contain=20
a "BCE" (before common era) date was not the answer to your =
problem.=20
Maybe I misunderstood the issue. When you say "why doesn't the =
query=20
ever return", do you mean it returns zero rows, or do you mean =
that=20
the query is taking an abnormally long time? If it's taking an =
abnormally long time, an explain plan on the two queries may =
reveal=20
the cause.</FONT></P>
<P><FONT size=3D2>Jacques R. Kilcho=EBr</FONT> <BR><FONT =
size=3D2>(949)=20
754-8816</FONT> <BR><FONT size=3D2>Quest Software, Inc.</FONT> =
</P>
<P><FONT size=3D2>8001 Irvine Center Drive</FONT> <BR><FONT=20
Received on Tue Jan 16 2001 - 19:54:45 CST
 |
 |