RE: Date comparison question

Message-Id: <10746.127164@fatcity.com> From: David Wagoner Date: Fri, 19 Jan 2001 11:50:08 -0500 Subject: RE: Btmap Index This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C08237.E1755870 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable The SQL query should have the same column order in the select statement = as the order in the bitmap index or it will not use the index. =20 =20 david =20 =20 -----Original Message----- From: Kevin Kostyszyn [mailto:kevin@dulcian.com] Sent: Monday, January 08, 2001 6: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 = 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_001_01C08237.E1755870 Content-Type: text/html; charset="iso-8859-1" RE: Date comparison question

The SQL query should have the same column order in the select statement as the order in the bitmap index or it will not use the index.

david

-----Original Message-----
From: Kevin Kostyszyn [mailto:kevin@dulcian.com]
Sent: Monday, January 08, 2001 6: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@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-----
> From: Lisa Yates [mailto:cosnit@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.