| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> update performance
This is a multi-part message in MIME format.
------=_NextPart_000_0045_01C07A98.0C310250 Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
RE: Date comparison questionI 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
update cmd:
update tableA A
set colA = (select B.colC fdrom tableB B where B.tableB.colA =
A.tableA.colA )
I tried it in PL/SQL and as the straight SQL but it takes FOREVER without
coming back...
should I be doiing something better?
(I have an index on the two columns on TableB)
Thanks
-Krishna.
-----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)
-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.
Sincerely,
Kevin
Hi,
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
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...
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-----
> From: Lisa Yates [mailto:cosnit_at_creighton.edu]
> Sent: lundi, 8. janvier 2001 11:27
>
> Why doesn't this query ever return...
>
> where run_date = to_date('01-03-2001 16:34:59','mm-dd-yyyy
> hh24:mi:ss')
>
> but this query does....
>
> where to_char(run_date) = to_char(to_date('01-03-2001
> 16:34:59','mm-dd-yyyy hh24:mi:ss'))
>
> and so does this query....
>
> where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') = '01-03-2001
> 16:34:59'
Well, I guess my suggestion that run_date may contain a "BCE"
Jacques R. Kilchoër
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com
------=_NextPart_000_0045_01C07A98.0C310250 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.2920.0" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =class=3D125045507-10012001>I ahve=20
size=3D2>-----Original Message-----<BR><B>From:</B> root_at_fatcity.com =
[mailto:root_at_fatcity.com]<B>On Behalf Of </B>Kevin = Kostyszyn<BR><B>Sent:</B>=20
Monday, January 08, 2001 3:50 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 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 the=20
table is just very small and the CBO just decided to do a full table = scan=20
instead. You could use a hint and tell it to use rule based on = that=20
query and it will use the index on the where clause. That's = what I=20
did. The question is, how big is the table and how much data = does it=20
have in it? If it is not a lot, there really is nothing to be=20 concerned about. Oh yeah, and almost 100% positive that there = is no=20
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
<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 be=20
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 when=20
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 =
PM<BR><B>To:</B>=20
Multiple recipients of list ORACLE-L<BR><B>Subject:</B> RE: Date =
comparison 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> =
<BR><FONT=20
size=3D2>> </FONT><BR><FONT size=3D2>> where=20
to_char(run_date,'mm-dd-yyyy hh24:mi:ss') =3D '01-03-2001 =
</FONT><BR><FONT=20
size=3D2>> 16:34:59'</FONT> </P>
<P><FONT size=3D2>Well, I guess my suggestion that run_date may =
contain a=20
"BCE" (before common era) date was not the answer to your =
problem. Maybe=20
I misunderstood the issue. When you say "why doesn't the query =
ever=20
return", do you mean it returns zero rows, or do you mean that =
the query=20
is taking an abnormally long time? If it's taking an abnormally =
long=20
time, an explain plan on the two queries may reveal the=20
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 =
size=3D2>Irvine,=20
Received on Wed Jan 10 2001 - 01:58:22 CST
 |
 |