Re: Btmap Index

From: Kevin Kostyszyn <kevin_at_dulcian.com>
Date: Mon, 8 Jan 2001 18:57:01 -0500
Message-Id: <10735.126067@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_000E_01C079A4.C8F6F880 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

RE: Date comparison questionI 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=20
> 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_000E_01C079A4.C8F6F880 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>I ran into a similar problem a while =

back.  If=20
the database is set to use the CBO, it might not use the index.  = Someone on=20
this list gave a great reason why, perhaps the table is just very = small and=20
the CBO just decided to do a full table scan instead.  You could = use a hint=20
and tell it to use rule based on that query and it will use the index on = the=20
where clause.  That's what I did.  The question is, how big is = the=20
table and how much data does it have in it?  If it is not a lot, = there=20
really is nothing to be concerned about.  Oh yeah, and almost 100% = positive=20
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 = href=3D"mailto:ORACLE-L_at_fatcity.com"=20
  title=3DORACLE-L_at_fatcity.com>Multiple 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 pan=20
  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 =

  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 = class=3D953563822-08012001>the=20
  index but doing a full table 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 =

[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=20

question<BR><BR></DIV></FONT>
<P><FONT size=3D2>> -----Original Message-----</FONT> <BR><FONT = size=3D2>>=20

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>> =

</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 = to_char(run_date,'mm-dd-yyyy=20

hh24:mi:ss') =3D '01-03-2001 </FONT><BR><FONT size=3D2>> = 16:34:59'</FONT>=20
</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 an=20

abnormally long time? If it's taking an abnormally long time, an = explain=20

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 - 17:57:01 CST