sorry, the second query uses equality operator..
WHERE UPPER(col1) = 'xyz';
index hint is not helping.
regards,
B S Pradhan
--
On Thu, 08 Jan 2004 zions swordfish wrote :
hi, pradhan,
I don't see any kind of differences with your two queries, but
I suggest you to use
Hi All,
I have the query like:
SELECT *
FROM tab1
WHERE UPPER(col1) 'abc';
Obviously, the function based index i have is not hetting used because of the
ineqality.
When using
SELECT *
FROM tab1
WHERE UPPER(col1) 'abc';
index is getting used.
How can I possibly use index in the 1st case.
hi, pradhan,I don't see any kind of differences with your two queries, butI suggest you to use hint in your query to activate index.Regards,Sony
- Original Message -
DATE: Thu, 08 Jan 2004 07:09:26
From: "bhabani s pradhan" [EMAIL PROTECTED]
To: Multiple recipients of list
Am I missing something? It looks to me like these queries are the same...
-Original Message-
Sent: Thursday, January 08, 2004 9:09 AM
To: Multiple recipients of list ORACLE-L
Hi All,
I have the query like:
SELECT *
FROM tab1
WHERE UPPER(col1) 'abc';
Obviously, the function based
Thanks.
Regards,
B S Pradhan
-
On Fri, 26 Dec 2003 zhu chao wrote :
Hi,
To see why oracle choose FTS, alter session set events '10053 trace name context
forever,level 2';
You can do alter session to change index_adj and optimizer_index_caching to
change only
Hi,
Nice example and it should behave similarly in my case also. I have to research more..
Regards,
B S Pradhan
---
On Wed, 24 Dec 2003 zhu chao wrote :
Hi,
It cound be possible that without Hint, oracle will choose FTS for second SQL,
because with col3 clause, if using
Hi All,
Agreed.. and it should behave that way i.e
if (cost of ind1 scan + then based on c1's selection table access for c3) (direct
table access for c1 and c1) then oracle will use FTS with cost based optimization.
So, w/o a hint that is expected.
But why it is not picking the index in my
Subject: Re: Re: Index usage
Hi All,Agreed.. and it should behave that way i.eif
(cost of ind1 scan + then based on c1's selection table access for c3)
(direct table access for c1 and c1) then oracle will use FTS with cost based
optimization.So, w/o a hint that is expected.But why
Hi,
To see why oracle choose FTS, alter session set events '10053 trace name context
forever,level 2';
You can do alter session to change index_adj and optimizer_index_caching to
change only your session, or using hint.
Regards
Zhu Chao.
- Original Message -
To: Multiple
Hi All,
Merry Christmas to all.
I have this interesting problem..
For this query index ind1 on (c1,c2) columns is getting used.
SELECT 'x'
FROM tab ta
WHERE ta.c1='val1';
(gives index ind1 range scan)
But for
SELECT 'x'
FROM tab ta
WHERE ta.c1='val1'
AND ta.c3 = 'val2';
(gives FTS)
index
You answered your own question.
ta.c3 is a nonindexed column, this means that the only way to satisfy the
predicate is to perform a full table scan. Since this predicate condition forces
a full table scan on ta, which will retrieve the ta.c1 column values at the same
time, there is no need to use
No.
The index should get used.Thequery result for query 2 is a subset of rows with ta.c1='val1' will get selected. Subset of query 1.
So there is no need for a full table scan.The index can be used in the following way :
1) Use index ind1 to get rows with ta.c1='val1' (which is query 1). This
The difference is that the first query never has to go to the table
(because you're selecting a constant 'x'). The second query has to go
to the table to filter on c3.
Mike
anu wrote:
No.
The index should get used. The query result for query 2 is a subset of
rows with ta.c1='val1' will
Hi,
It cound be possible that without Hint, oracle will choose FTS for second SQL,
because with col3 clause, if using index, oracle will have to do a range scan on index
ind1 and than table access by rowid.
If CBO thinks that col1='val1' will get a lot of rows then doing FTS may be
Note that 9.2 has the nice family of v$segment_statistic views that can give
you this level of information very easily. It has advantages over
v$object_usage in that is gives you an indication on how often indexes are
used, rather than that they've been used.
Although sampling and other factors
Richard ... thanks for this advise ... now I can add that to my list of observations
...
Thanks
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any
Check this
http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:531147287002
HTH
GovindanK
Oracle Certified Professional(8,8i)
Brainbench Certified Master DBA(8)
On Sun, 09 Nov 2003 20:59:25 -0800, Arvind Kumar
[EMAIL PROTECTED] said:
Hi all,
is there a way to monitor
Hi all,
is there a way to monitor index usages in oracle
8i ,like 9i v$object_usage?
Thanks
Arvind
Kumar
Once again thanx a lot Tanel for spending your
precious time to make me understand.
Regards,
Jp.
28-07-2003 22:59:25, Tanel Poder [EMAIL PROTECTED] wrote:
The point is, that index access is cheap in reality, but CBO thinks it's
very expensive and chooses next best executin plan in it's
My only doubt is:
1.After analyzing the index,CBO doesn't read the index because
it is costlier in terms of I/O and other resources.
The point is, that index access is cheap in reality, but CBO thinks it's
very expensive and chooses next best executin plan in it's opinion, but this
opinion is
Hi Tanel,
thanks a lot for your wonderful explanation.
sorry to pester u you again with my doubts on
the first day of the week.
My only doubt is:
1.After analyzing the index,CBO doesn't read the index because
it is costlier in terms of I/O and other resources.
2.But
Hi!
but there are seperate indexes on column PREF, FLAG and SEX.
when those indexes are used ,cost=999.
whereas when IDX_PROFILE_SHINKI ( a composite index on ENTPC,
FLAG,SEX,PREF,ENTDAY) is used , cost=7.
1.whys there is a huge difference in COST ?
As Wolfgang said, since your
Tanel,
quoteAre your other indexes analyzed?/quote
Yes.
Case 1:
If IDX_PROFILE_SHINKI is analyzed,CBO decides that index scan is costlier.
Case 2:
Now IDX_PROFILE_SHINKI is not analyzed (which works fine as of now),
CBO takes default values and decides that index scan is
Thanx a lot Irfan.
let me also go thro' metalink and try out something.
Regards,
Jp.
25-07-2003 18:46:51, Irfan Khan [EMAIL PROTECTED] wrote:
From what i understand when u analyze , use the keyword of size to set the
histogram buckets
Generally it should be no of distinct values in a column +
Hi!
Your original post shows that when index was not analyzed, optimizer used
default statistics and found out that index scan is quite cheap. Also, no
sorting had to be done, since descending index range scan could be used to
satisfy your order by clause.
Whe index was analyzed, then CBO
Hi!
25-07-2003 18:46:51, Irfan Khan [EMAIL PROTECTED] wrote:
From what i understand when u analyze , use the keyword of size to set
the
histogram buckets
Generally it should be no of distinct values in a column + som ball park
no.
say ten.
Max nr. of buckets is 254 per column, thus in some
Guys,
SQL SELECT NAME,AGE,MEMNO,BLOODTYPE,HIGHT,LOOKSLIKE,CONTENT,PICID FROM PROFILE
WHERE PREF = :PREF AND SEX = :OP_SEX AND FLAG = :FLAG AND ENTPC = :ENTPC
AND NAME IS NOT NULL AND FACCESS SYSDATE - 14 ORDER BY ENTDAY DESC;
NO ROWS SELECTED
ELAPSED: 00:00:00.07
EXECUTION PLAN
Hi!
(B
(B the env. is 9.2.0.3/win2k-sp3.
(B
(B it is built on "profile" table .
(B the index has the columns "entpc,sex,flag,pref,entday" in it.
(B the order of the columns in the index is also the same as i have
(Bmentioned.
(B
(B 1.so ,does it mean that "idx_profile_shinki" is a bad
Hi Tanel,
(B
(Bquote
(Bdid you analyze your table in addition to index as well?
(Bfirst time you were probably using RBO, which always counts index access
(Bbetter than table access.
(B/quote
(B
(Bi have analyzed PROFILE table also and hope it's CBO by default in 9i.
(Banyway,it is CBO
The trace seems to be from when the index is not analyzed. The CBO then
uses defaults for the index statistics - leaf_blocks=25 and clustering
factor=800. These defaults are much lower than when the index is analyzed
and the resulting cost for using the index is very low (7 compared to 1676
Thanx Breitling.
quoteLeave the index un-analyzed if it works for you/quote
as u said,index IDX_PROFILE_SHINKI is not analyzed now.
that works fine.
but there are seperate indexes on column PREF, FLAG and SEX.
when those indexes are used ,cost=999.
whereas when IDX_PROFILE_SHINKI ( a composite
How do you monitor if the indexes are being used in 8i? Individually the tkprof and explain plans do the trick. However from a running system, how can the index usage be determined?
If the index is being used? How many times did the index get used during a specific time frame etc.
Regards
MenonDo
monitor if the indexes are being used in 8i? Individually the
tkprof and explain plans do the trick. However from a running system, how
can the index usage be determined?
If the index is being used? How many times did the index get used during a
specific time frame etc.
Regards
Menon
Murali ,
I have attached a script.
i use the same to find index usage.
hope this helps.
Regards,
Jp.
FindIndexUsage.sql
Description: Binary data
Title: NVL and index usage
Does anyone know off hand if using NVL on an indexed column negate use of an index in CBO? This is 9202 ... and the column will be a varchar2(1).
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni
Title: NVL and index usage
In a quick test on 9013 it changed the index which
it was using and went from Range scan to fast full scan.
- Original Message -
From:
Jamadagni, Rajendra
To: Multiple recipients of list ORACLE-L
Sent: Thursday, January 02, 2003 9:48
AM
It won't be able to use the index as far as I know. Unless it's a functional
index ;).
Mogens
Jamadagni, Rajendra wrote:
NVL and index usage
Does anyone know off hand if using NVL on an
indexed column negate use of an index in CBO? This is 9202 ... and the column
Title: RE: NVL and index usage
Thanks ... the development is rolling out a new change by adding a new nullable column to a table and adding following to all appropriate queries ...
and nvl(new_column,'A') = nvl(some_value,'A')
I learned of this few minutes ago and luckily
If you mean
where nvl(col,:x) = :y then yes it will be negated.
If memory serves, I've even had problems in the past
using nvl in a function based index, the workaround
doing the equivalent with decode, but I can't remember
the specifics
hth
connor
--- Jamadagni, Rajendra
[EMAIL PROTECTED]
Title: NVL and index usage
Raj,
the
index will not be used.
think
about it.
for
those records that do not have a value (are null) for the indexed column-
there is not an entry in the index - so they can not be evaluated to return a
row.
secondly, even if all rows were represented
Title: RE: NVL and index usage
Thanks Tom and everyone ...
as I mentioned they plan to use it as follows ...
and nvl(new_column,'A') = nvl(some_value,'A')
So I asked them to see the possibility of creating the column with a DEFAULT VALUE of 'A' so the where clause can be written
Title: RE: NVL and index usage
No idea ... my guess is few years down the line, they will make it varchar2(2) ...
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal
: NVL and index usage
Sent by:
[EMAIL PROTECTED
Title: NVL and index usage
Just
curios, why varchar2(1) and not char(1)?
Waleed
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02,
2003 12:48 PMTo: Multiple recipients of list
ORACLE-LSubject: NVL and index usage
Does
Title: NVL and index usage
Just
curious, why varchar2(1) and not char(1)?
Regards,
Waleed
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02,
2003 12:48 PMTo: Multiple recipients of list
ORACLE-LSubject: NVL and index usage
What is the difference?
Just curios, why varchar2(1) and not char(1)?
Waleed
-Original Message-
Sent: Thursday, January 02, 2003 12:48 PM
To: Multiple recipients of list ORACLE-L
Does anyone know off hand if using NVL on an indexed
column negate use of an index in
I think that there is another dimension to the problem that you may not be
aware of and that is even when you think that you succeeded to eliminate the
use of the index on the batch_date using a hint (no_index), Oracle still has
to go the table using the rowid (after conversion from bitmap) to
:
Sent by: Subject: Partition and Index Usage
[EMAIL PROTECTED]
om
Listers,
I'll be digging into this a bit more, playing around with a 10053 trace,
reviewing the stats, and trying to tie back why this occurs, but here's the
scenario.
8.1.7.4
Partitioned table, by month, on a date column called batch_date. 30 some odd
million rows per partition, 750+ million
Hi Larry,
First I would suggest doing daily partitioning and dropping the index on the
batch_date.
Regarding your sql: partitions eliminations never substitutes the necessity
to validate any predicates on the partitioning key in the where clause.
If it's not feasible to partition by day, I
Hi Larry,
First I would suggest doing daily partitioning and dropping the
index on the
batch_date.
That's been kicked around. It's not a bad idea -- it would make sure the
index, since it wouldn't exist, doesn't get in the way. FWIW, it's
partitioned on a monthly basis to fall more in line
Hi,
Why not to use otrace? Of cource, you may need some space to save
trace results, but you'll definitely get complete statistics.
Vadim Gorbounov
Oracle DBA
-Original Message-
Sent: Tuesday, January 30, 2001 3:57 PM
To: Multiple recipients of list
/NNIB/NNNG)
Subject: RE: Index Usage Monitoring
Hi,
Why not to use otrace? Of cource, you may need some space to save
trace results,
Pine Cone Systems has software to do this.
There is also another company that has software to do this,
but I can't recall their name at the moment.
If you talk to Pine Cone, ask them who their competitors are.
Be forewarned, this stuff is very expensive.
Jared
On Tue, 30 Jan 2001, PRINCE,
there will be a new view called
V$OBJECT_USAGE (Name may change ??) can be used to
monitor the index usage (!!)
=
Have a nice day !!
Best Regards,
K Gopalakrishnan,
Bangalore, INDIA.
__
Get personalized
We have a purchased application with over 1,300 indexes.
Can someone suggest a method to monitor the system to
determine which indexes are actively being used over time? I'm assuming
that some are old/not necessary and would like to save the overhead
of maintaining them.
Oracle 8.0.6
Is there anyway to find out which indexes
are being used and which ones are not
being used for any given table?
Thanks in Advance
Alan
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Browning, Alan
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858)
On Tuesday 30 January 2001 23:47, [EMAIL PROTECTED] wrote:
run a query using explain plan
... and use sql trace
oli
-Original Message-
Alan
Sent: Tuesday, January 30, 2001 4:40 PM
To: Multiple recipients of list ORACLE-L
Is there anyway to find out which indexes
are being
58 matches
Mail list logo
