Message-Id: <10735.126067@fatcity.com> From: "Kevin Kostyszyn" Date: Mon, 8 Jan 2001 18:57:01 -0500 Subject: Re: Btmap Index 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@fatcity.com [mailto:root@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@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 RE: Date comparison question
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.  =
Sincerely,
Kevin
----- Original Message -----
From:=20 Krishna=20 Prasad
To: Multiple recipients of list ORACLE-L =
Sent: Monday, January 08, 2001 = 5:45=20 PM
Subject: Btmap Index

Hi,
 
Is=20 there any init.ora parameter that needs to be turned to activate = bitmap=20 indexes?
I=20 have a bitmap index on one column C1, but when i examine the = explain pan=20 of the
query: " select C1, b, c from table TT = where C1 in ('dd','ff')" , it looks like it is not=20 using
the=20 index but doing a full table scan...
 
thanks
-krishna
-----Original Message-----
From: root@fatcity.com = [mailto:root@fatcity.com]On Behalf Of Jacques=20 Kilchoer
Sent: Monday, January 08, 2001 2:01 = PM
To:=20 Multiple recipients of list ORACLE-L
Subject: RE: Date = comparison=20 question

> -----Original Message-----
>=20 From: Lisa Yates [mailto:cosnit@creighton.edu]=20
> Sent: lundi, 8. janvier 2001 11:27 =
>
> Why doesn't this query = ever=20 return...
>
> where=20 run_date =3D to_date('01-03-2001 16:34:59','mm-dd-yyyy =
> hh24:mi:ss')
> =
> but this query does....
> =
> where to_char(run_date) =3D=20 to_char(to_date('01-03-2001
>=20 16:34:59','mm-dd-yyyy hh24:mi:ss'))
>=20
> and so does this query.... =
>
> where = to_char(run_date,'mm-dd-yyyy=20 hh24:mi:ss') =3D '01-03-2001
> = 16:34:59'=20

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.

Jacques R. Kilcho=EBr
(949)=20 754-8816
Quest Software, Inc.

8001 Irvine Center Drive
Irvine,=20 California 92618
U.S.A.