RE: Why isn't Oracle Using My Index
- From: "Mercadante, Thomas F \(LABOR\)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <wjwagman@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 21 Dec 2006 14:10:44 -0500
Bill,
When you run the query, how fast is the response?
It could be that there are a series of queries to support the functional
piece of the application that are more the culprits.
Have you turned tracing on for the application's connection and looked
at all of the queries that get run? You might find something else that
is more interesting than this question.
My experience is that the Oracle optimizer is usually very good. So all
I'm saying is that you look at everything.
Trace the session when the users are running the application and see
what else pops up.
Tom
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
Sent: Thursday, December 21, 2006 2:05 PM
To: Mercadante, Thomas F (LABOR); oracle-l@xxxxxxxxxxxxx
Subject: RE: Why isn't Oracle Using My Index
Tom,
That is a good question. I am responding to users saying that the
particular piece of the application in which this code is executed is
too slow. So I am basically responding to users requests to speed things
up.
Thanks.
Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman@xxxxxxxxxxx
(530) 754-6208
-----Original Message-----
From: Mercadante, Thomas F (LABOR)
[mailto:Thomas.Mercadante@xxxxxxxxxxxxxxxxx]
Sent: Thursday, December 21, 2006 10:40 AM
To: William Wagman; oracle-l@xxxxxxxxxxxxx
Subject: RE: Why isn't Oracle Using My Index
Bill,
Instead of answering your question, let me ask another one?
Why do you think it will be faster to use an index rather than the way
that Oracle has decided to do it? The cost is relatively low. What is
the response time for the query?
Sometimes, Oracle *does* know best!
Tom
--------------------------------------------------------
This transmission may contain confidential, proprietary, or privileged
information which is intended solely for use by the individual or entity
to whom it is addressed. If you are not the intended recipient, you are
hereby notified that any disclosure, dissemination, copying or
distribution of this transmission or its attachments is strictly
prohibited. In addition, unauthorized access to this transmission may
violate federal or State law, including the Electronic Communications
Privacy Act of 1985. If you have received this transmission in error,
please notify the sender immediately by return e-mail and delete the
transmission and its attachments.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of William Wagman
Sent: Thursday, December 21, 2006 12:50 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Why isn't Oracle Using My Index
Greetings,
This is a question I have been looking at and puzzling over for a couple
of days and am unable to explain, I'm hoping someone can help me
understand what is going on. In a 9i database I have a table with 41550
rows on which stistics are generated weekly. In looking at a simple
select the query does not use an index and I am unable to figure out how
to make it use the index.
SQL> set autotrace traceonly explain;
SQL> SELECT C240000008 FROM aradmin.t185 WHERE C1 = 'HD0000000041608'
2 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1420 Card=413 Bytes=
627760)
1 0 TABLE ACCESS (FULL) OF 'T185' (Cost=1420 Card=413 Bytes=62
7760)
There is an index IT185 on column C1 and column C1 is unique. A hint
will force the use of the index but in that this is not a locally
developed application I am unable to change the code. Nevertheless, in
attempting to understand this I looked at the clustering factor for the
index.
TABLE_NAME BLEVEL AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- -----------------------
AVG_LEAF_BLOCKS_PER_KEY AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR
----------------------- ----------------------- -----------------
T185 1 1
1 10276 10276
I see that the clustering_factor is quite high (in fact equal to
avg_leaf_blocks_per_key) which as I understand it is why the optimizer
is not using the index. Following the discussion of
db_file_multiblock_read_count which has been taking place recently I
played with changing it. It is currently set to 8 and I reduced it to 1
but that made no difference. There are a large nukber of blocks in the
table
TABLE_NAME BLOCKS
------------------------------ ----------
T185 9345
Which as I understand it also goes into the optimizer's decision to do a
full table scan.
So, my question, how can I get this thing to use the index without
changing the code? Can I? I don't know if it worth looking at
OPTIMIZER_INDEX_CACHING or OPTIMIZER_INDEX_COST_ADJ or if there is some
other method whereby I can get the optimizer to do an index scan instead
of the table scan. Any suggestions greatly appreciated.
Thanks.
Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman@xxxxxxxxxxx
(530) 754-6208
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
