RE: Btmap Index

From: Krishna Prasad <krishna_at_nehanet.com>
Date: Mon, 8 Jan 2001 20:40:21 -0800
Message-Id: <10735.126076@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_0004_01C079B3.382687D0 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: 8bit

RE: Date comparison questionThanks - 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

Original Message ----- From: Krishna Prasad To: Multiple recipients of list ORACLE-L Sent: Monday, January 08, 2001 5:45 PM Subject: Btmap Index

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" (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ër
      (949) 754-8816
      Quest Software, Inc.

      8001 Irvine Center Drive
      Irvine, California 92618
      U.S.A.
      http://www.quest.com

------=_NextPart_000_0004_01C079B3.382687D0 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=3D453413904-09012001>Thanks=20
- I'll try the Hint - the table has 2 million rows (with about 600 = distinct=20
values for this 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 = 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 = back. =20
  If the database is set to use the CBO, it might not use the = index. =20
  Someone on this list gave a great reason why, perhaps the table = is just=20
  very small and the CBO just decided to do a full table scan = instead.  You=20
  could use a hint and tell it to use rule based on that query and it = will use=20
  the index on the where clause.  That's what I did.  The = question is,=20
  how big is the table and how much data does it have in it?  If it = is not=20
  a lot, there really is nothing to be concerned about.  Oh yeah, = and=20
  almost 100% positive that there is 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 = class=3D953563822-08012001>Is=20

there any init.ora parameter that needs to be turned to activate = bitmap=20

indexes?</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN = class=3D953563822-08012001>I=20

have a bitmap index on one column C1, but when i examine the = explain=20

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=20
      comparison question<BR><BR></DIV></FONT>
      <P><FONT size=3D2>&gt; -----Original Message-----</FONT> <BR><FONT =

      size=3D2>&gt; From: Lisa Yates [<A=20
      =

href=3D"mailto:cosnit_at_creighton.edu">mailto:cosnit_at_creighton.edu</A>]</FO= NT>=20

<BR><FONT size=3D2>> Sent: lundi, 8. janvier 2001 11:27</FONT> = <BR><FONT=20

size=3D2>> </FONT><BR><FONT size=3D2>> Why doesn't this = query ever=20

return...</FONT> <BR><FONT size=3D2>> </FONT><BR><FONT = size=3D2>> where=20

run_date =3D to_date('01-03-2001 16:34:59','mm-dd-yyyy = </FONT><BR><FONT=20

size=3D2>> hh24:mi:ss')</FONT> <BR><FONT size=3D2>> = </FONT><BR><FONT=20

size=3D2>> but this query does....</FONT> <BR><FONT = size=3D2>>=20

      </FONT><BR><FONT size=3D2>&gt; where to_char(run_date) =3D=20
      to_char(to_date('01-03-2001</FONT> <BR><FONT size=3D2>&gt;=20
      16:34:59','mm-dd-yyyy hh24:mi:ss'))</FONT> <BR><FONT size=3D2>&gt; =

      </FONT><BR><FONT size=3D2>&gt; and so does this query....</FONT> =
<BR><FONT=20
      size=3D2>&gt; </FONT><BR><FONT size=3D2>&gt; where=20
      to_char(run_date,'mm-dd-yyyy hh24:mi:ss') =3D '01-03-2001 =
</FONT><BR><FONT=20
      size=3D2>&gt; 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 I=20
      misunderstood the issue. When you say "why doesn't the query ever =
return",=20
      do you mean it returns zero rows, or do you mean that the query is =
taking=20
      an abnormally long time? If it's taking an abnormally long time, =
an=20
      explain plan on the two queries may reveal 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 =
size=3D2>Irvine,=20
      California 92618</FONT> <BR><FONT size=3D2>U.S.A.</FONT> <BR><FONT =

Received on Mon Jan 08 2001 - 22:40:21 CST