Re: Index usage question

#1

Mark,
Don't rely on the implicit string to date conversion. Explicitly convert
it.
eg

AND reportdatentime >= '25-Mar-2003'
AND reportdatentime < '21-Jun-2003'
should be:
AND reportdatentime >= to_date('25-Mar-2003','dd-mmm-yyyy')
AND reportdatentime < to_date('21-Jun-2003','dd-mmm-yyyy')

If someone changes the nls date format default on the client the query won't
work. This method is much safer.
Jim

--
Replace part of the email address: kennedy-down_with_spammers (AT) attbi (DOT) com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"MThomas" <markt (AT) wrx-ca (DOT) com> wrote

Quote:

Hi, Daniel:

Thanks for responding.

I have brought the stats up to date as of this morning (It was a couple of
days old).

I seem to get two different plan results depending on whether I use the
SQL
Scratchpad or SQL*Plus. SQL*Plus claims it is using the correct index
while
the scrathpad claims a full scan (I'm not sure which is correct, though I
suspect the SQL*Plus may be as I am drawing the information from the
plan_table directly) Have you seen this type of behaviour before?

Cheers,

Mark.

"Daniel Morgan" <damorgan (AT) exxesolutions (DOT) com> wrote in message
news:3EF731EE.B79E129F (AT) exxesolutions (DOT) com...
MThomas wrote:

Good morning:

I am having some difficulty understanding the behaviour of Oracle 9i
(9.2.0.2 on Windows 2000 Server) during a retrival.

The query is in the form:
SELECT * FROM iohistory
WHERE ioid IN (63515, 63516)
AND reportdatentime >= '25-Mar-2003'
AND reportdatentime < '21-Jun-2003'

When the query is executed for one or two ioid values , the proper
index
is
used. However when a third point is addes to the retrieval a full
table
scan is initiated (this is a problem as the table currently contains
~60,000,000 records). Ideally it would always (or nearly always) use
the
index.

The index is unique on the ioid and reportdatentime columns of the
table.

Would anyone have an idea how I may correct this behaviour?

Thanks for your help.

Mark.

Are you keeping statistics current for the CBO with DBMS_STATS?
What is the EXPLAIN PLAN with three?
Have you tried hints?

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

#2

Good morning.

Thank you Jim, Daniel and Brian for you help sorting myself out.

I have been able to get Oracle to rely on the index I require. A secondary
index on the table also seemed to be interferring with Oracle's selection of
the best way to select. After removing the extraneous index and resetting
the statistics, the performance seems to have come back into line (we now
seem to be bound through the network, but that's another project.

Have a good day all,

Mark.

"Jim Kennedy" <kennedy-down_with_spammers (AT) attbi (DOT) com> wrote

Quote:

Mark,
Don't rely on the implicit string to date conversion. Explicitly convert
it.
eg

AND reportdatentime >= '25-Mar-2003'
AND reportdatentime < '21-Jun-2003'
should be:
AND reportdatentime >= to_date('25-Mar-2003','dd-mmm-yyyy')
AND reportdatentime < to_date('21-Jun-2003','dd-mmm-yyyy')

If someone changes the nls date format default on the client the query
won't
work. This method is much safer.
Jim

--
Replace part of the email address: kennedy-down_with_spammers (AT) attbi (DOT) com
with family. Remove the negative part, keep the minus sign. You can
figure
it out.
"MThomas" <markt (AT) wrx-ca (DOT) com> wrote in message
news:5CIJa.259$Fy1.10062 (AT) localhost (DOT) ..
Hi, Daniel:

Thanks for responding.

I have brought the stats up to date as of this morning (It was a couple
of
days old).

I seem to get two different plan results depending on whether I use the
SQL
Scratchpad or SQL*Plus. SQL*Plus claims it is using the correct index
while
the scrathpad claims a full scan (I'm not sure which is correct, though
I
suspect the SQL*Plus may be as I am drawing the information from the
plan_table directly) Have you seen this type of behaviour before?

Cheers,

Mark.

"Daniel Morgan" <damorgan (AT) exxesolutions (DOT) com> wrote in message
news:3EF731EE.B79E129F (AT) exxesolutions (DOT) com...
MThomas wrote:

Good morning:

I am having some difficulty understanding the behaviour of Oracle 9i
(9.2.0.2 on Windows 2000 Server) during a retrival.

The query is in the form:
SELECT * FROM iohistory
WHERE ioid IN (63515, 63516)
AND reportdatentime >= '25-Mar-2003'
AND reportdatentime < '21-Jun-2003'

When the query is executed for one or two ioid values , the proper
index
is
used. However when a third point is addes to the retrieval a full
table
scan is initiated (this is a problem as the table currently contains
~60,000,000 records). Ideally it would always (or nearly always)
use
the
index.

The index is unique on the ioid and reportdatentime columns of the
table.

Would anyone have an idea how I may correct this behaviour?

Thanks for your help.

Mark.

Are you keeping statistics current for the CBO with DBMS_STATS?
What is the EXPLAIN PLAN with three?
Have you tried hints?

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Index usage question

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Index usage question - 06-23-2003 , 10:23 PM

Re: Index usage question - 06-24-2003 , 09:52 AM