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, 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
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
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
Hi!
the env. is 9.2.0.3/win2k-sp3.
it is built on "profile" table .
the index has the columns "entpc,sex,flag,pref,entday" in it.
the order of the columns in the index is also the same as i have
mentioned.
1.so ,does it mean that "idx_profile_shinki" is a bad
Hi Tanel,
quote
did you analyze your table in addition to index as well?
first time you were probably using RBO, which always counts index access
better than table access.
/quote
i have analyzed PROFILE table also and hope it's CBO by default in 9i.
anyway,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
Murali
If I'm not mistaken, the first Oracle version that offers real support
for this is Oracle9i. I think what you are doing is about all that can be
done -- capture SQL, run explain plan on it and make a list of the indexes
that are used. If you look around, there are scripts to help and
Murali ,
I have attached a script.
i use the same to find index usage.
hope this helps.
Regards,
Jp.
FindIndexUsage.sql
Description: Binary data
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,
V$access appears to be pretty unreliable. V$db_object_cache does not appear
to carry indexes but does have some good information on tables.
-Original Message-
Sent: Wednesday, January 31, 2001 2:51 PM
To: Multiple recipients of list ORACLE-L
And starting with the time immemorial,
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
31 matches
Mail list logo
