based index?
Thank you!
Janet
__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Janet Linsy
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538
be A or D)
IDNUMBER
I'd like to build a unique index on column ID when the
status='A', how to create this function based index?
Thank you!
Janet
__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
--
Please see
Hi,
Can someone shed some light on the differences of gathering statistics on a
function-based index using the following two methods?
method 1: analyze table table_name compute statistics
vs
method 2: create unique index index_name on table_name
(upper(columne_name)) compute statistics;
I could
of Service Status Q/A To Development
Base Bug N/A Fixed in Product Version 10.0.0
Problem statement:
ORA-904:GATHER_TABLE_STATS FAILS ON TABLE WITH WIH FUNCTION-BASED INDEX
*** 02/03/03 10:59 am *** TAR
to list
the contents of user_indexes for the index after each of the analyzes?
At 05:45 AM 6/2/2003 -0800, you wrote:
Hi,
Can someone shed some light on the differences of gathering statistics on
a function-based index using the following two methods?
method 1: analyze table table_name compute
blush! Thanks...
Having been through two books and having aborted the third, I can only
repeat what a friend told me after completing his MBA at night school: You
can work your job, live your life, and go to school. But, only two at a
time...
Substitute write a book for go to school and that
Wolfgang,
Thanks for your respond.
I tried gathering stats on the function based index using
- analyze index ACFD_INDX1 compute statistics;
- exec
dbms_stats.gather_table_stats(ownname='ACPO',tabname='AC_FORWARD_DEST',cascade=TRUE);
- exec dbms_stats.gather_index_stats('ACPO','ACFD_INDX1
To clarify my Book! Book! Book! :)...
This is my request for Tim to write [another] one.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic 101 in Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney
- Visit www.hotsos.com for schedule details...
Cary, I will be first one to get a copy of the book.
And now this is my turn to request Tim ;)
Book! Book! Book!
Jp.
2003/06/02 18:54:38, Cary Millsap [EMAIL PROTECTED] wrote:
To clarify my Book! Book! Book! :)...
This is my request for Tim to write [another] one.
Cary Millsap
--
Please
Tim,
First, i would like to thank U a million.
It was a real GOOD explanation.
I don't know why should you apologize for helping me.
I should be thankful to u for helping me in time.
GREAT to have guys like u in this list.
Knowing is GOOD.
but making others know it , is GREAT.
thanx for your
JP,
In the EXPLAIN PLAN, it says Card=262146, indicating that the query
expects to retrieve over a quarter-million rows. Is that in fact correct?
If so, the CBO is making the correct decision to perform a FULL table scan.
What was the comparison of elapsed times between the two plans, the one
PROTECTED]
Sent: Thursday, May 29, 2003 12:25 PM
To: Multiple recipients of list ORACLE-L
Subject: Function Based Index - Not Used ???
Guys,
create table Tab1 ( name varchar2(100),age int,state
varchar2(100),country varchar2(100));
insert into tab1 values ('SCOTT',25,'TN','India');
I
appropriate
value.
Regards
Naveen
-Original Message-
From: Prem Khanna J [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 29, 2003 12:25 PM
To: Multiple recipients of list ORACLE-L
Subject: Function Based Index - Not Used ???
Guys,
Wow, there sure are a lot of Scott's in India...
-Original Message-
Sent: Thursday, May 29, 2003 9:30 AM
To: Multiple recipients of list ORACLE-L
JP,
In the EXPLAIN PLAN, it says Card=262146, indicating that the query
expects to retrieve over a quarter-million rows. Is that in fact
John,
I tried it with COMPATIBLE=8.1.6.
no nulls in that particular column
but it's the same again.
As Tim pointed out,
CARD=262146 -- as per the EXPLAIN PLAN for SELECT w/o HINT.
the SELECT retuens about 2.5 million rows.
which is why it goes for a FTS than a Index scan
is my understanding
Thanks Tim.
But the SELECT returns just 2 of 20,00,000 records.
and the Time elapsed for Index scan is 0.7 secs where as
it is 5 secs for FTS.
Hell a lot of lousy things here Tim.
just mending it one by one.
Regards,
Jp.
2003/05/29 22:30:02, Tim Gorman [EMAIL PROTECTED] wrote:
JP,
In the
AGE
whose value cannot not be found from the index.
Regards
Naveen
-Original Message-
From: Prem Khanna J [mailto:[EMAIL PROTECTED]
Sent: Friday, May 30, 2003 8:45 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: RE: Function Based Index - Not Used ???
John,
I tried
JP,
I apologize in advance for the long email, but I think you'll find it
rewarding to read it all the way through...
The CBO is just a mathematical processor, and a rather good one at that. It
is choosing the best plan given the data it has been given, which is
admittedly often incomplete.
Wow, too good!
-Original Message-
From: Tim Gorman [mailto:[EMAIL PROTECTED]
Sent: Friday, May 30, 2003 11:00 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Function Based Index - Not Used ???
JP,
I apologize in advance for the long email, but I think you'll find
Book! Book! Book! :)
Speaking of query optimizers, here are some simple things about Oracle query
optimizers that I think a lot of people miss:
- CBO is just a module that chooses an execution plan
- RBO is another module that chooses an execution plan
- In 8.1.6+, CBO usually does a better job
Guys,
create table Tab1 ( name varchar2(100),age int,state varchar2(100),country
varchar2(100));
insert into tab1 values ('SCOTT',25,'TN','India');
I have 20,00,000 records like above.
create index idx1 on tab1 (upper(name));
analyze table tab1 compute statistics;
analyze index idx1 compute
-
From: Prem Khanna J [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 29, 2003 12:25 PM
To: Multiple recipients of list ORACLE-L
Subject: Function Based Index - Not Used ???
Guys,
create table Tab1 ( name varchar2(100),age int,state
varchar2(100),country varchar2(100));
insert
Oops! didnot read your full message.
try setting OPTIMIZER_INDEX_COST_ADJ with alter session to a lower value
Regards
Naveen
-Original Message-
From: Naveen Nahata
Sent: Thursday, May 29, 2003 1:20 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Function Based Index
PROTECTED]
Sent: Thursday, December 12, 2002 12:34 AM
why would you want to create function based index on a column that you are
not using in the where clause.
for your sql statement a index on cnfr_no would work great...
babu
- Original Message -
To: Multiple recipients of list ORACLE
recipients of list ORACLE-L
I have the following statement that I would like to create a function-based
index for:
SELECT MIN(tran_dt)
FROM ach_tran
WHERE cnfr_no = :b1
I'd like the index to include columns cnfr_no and tran_dt (in that order).
The examples I've seen are
just single-column indexes
Title: RE: Function-based Index
Cherie,
you could you a MV (with query_rewrite) ro do what you want ... make sure you use refresh on commit ... FBI won't be of much use in this scenario ...
Raj
__
Rajendra Jamadagni MIS, ESPN Inc
I have the following statement that I would like to create a function-based
index for:
SELECT MIN(tran_dt)
FROM ach_tran
WHERE cnfr_no = :b1
I'd like the index to include columns cnfr_no and tran_dt (in that order).
The examples I've seen are
just single-column indexes. Is it possible
why would you want to create function based index on a column that you are
not using in the where clause.
for your sql statement a index on cnfr_no would work great...
babu
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, December 11, 2002
Title: RE: Function-based Index
Cherie,
AFAIK, a plain old index works just fine with Min and Max functions. However, you might need tran_dt first in the index or have its own index.
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-Original
On 2002.09.05 22:18 Rachel Carmichael wrote:
I love automagic things :) so I can leave the table alone
right now there are all of 7 rows in it
Rachel
Given the size of the the table, may be you should try partitioning it?
--
Mladen Gogala
--
Please see the official ORACLE-L
There has been some good stuff on the Usenet list lately about the debating
the usefulness of CACHE as opposed to KEEP buffer pool.
Ethan Post
perotdba (AIM), epost1 (Yahoo)
-Original Message-
Sent: Thursday,
you know, I was thinking about that. I can't decide between hash
partitioning or list partitioning though :)
--- Mladen Gogala [EMAIL PROTECTED] wrote:
On 2002.09.05 22:18 Rachel Carmichael wrote:
I love automagic things :) so I can leave the table alone
right now there are all
Probably composite partitioning!
-Original Message-
Sent: Friday, September 06, 2002 1:04 PM
To: Multiple recipients of list ORACLE-L
you know, I was thinking about that. I can't decide between hash
partitioning or list partitioning though :)
--- Mladen Gogala [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 4:23
PM
Subject: Re: Function-Based Index not
working
Hi All,
Thanks a lot to you all. At lastI got the
function-based index working properly.
This is whatI noticed :-
Have to alter session/system
Message-From: Yechiel Adar
[mailto:[EMAIL PROTECTED]]Sent: Thursday, September 05, 2002
10:19 AMTo: Multiple recipients of list ORACLE-LSubject:
Re: Function-Based Index not working
Hello
I think that the amount of records you read is also
taken into account.
If you run a query
Middlefart Denmark
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]] On Behalf Of Fink, Dan
Sent: Thursday, September 05, 2002
12:19 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Function-Based Index
not working
Not necessarily... Cary's IOUG-A
presentation
, Marul Mehta wrote:
Even after giving the hint its not working.
I guess you can't have IS clause and Like with function-based index.
Marul.
- Original Message -
From: Naveen Nahata
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 7:28 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: Function-Based Index not working
Even when the high-water mark thing isn't a problem, it's sometimes more
efficient to read every row in a table through
PROTECTED] recipients of list
ORACLE-L
Sent: Saturday, August 31, 2002 4:23 PM
Hi All,
Thanks a lot to you all. At last I got the function-based index
working
properly.
This is what I noticed :-
Have to alter session/system for :-
+ alter session set
=
Cary Millsap [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
09/05/2002 11:13 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:RE: Function-Based Index not working
Even when the high-water
- Original Message -
To: Multiple mailto:[EMAIL PROTECTED] recipients of list
ORACLE-L
Sent: Saturday, August 31, 2002 4:23 PM
Hi All,
Thanks a lot to you all. At last I got the function-based index
working
properly.
This is what I noticed :-
Have
Hi All,
Thanks a lot to you all. At last I got the function-based index
working
properly.
This is what I noticed :-
Have to alter session/system for :-
+ alter session set QUERY_REWRITE_ENABLED=TRUE;
+ alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
+ alter session set
the
function-based index working
properly.
This is what I noticed :-
Have to alter session/system for :-
+ alter session set QUERY_REWRITE_ENABLED=TRUE;
+ alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
+ alter session set optimizer_mode=FIRST_ROWS;
And
+ can't use IS NULL
=
Cary Millsap [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
09/05/2002 11:13 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: Function-Based Index not working
Even when the high-water mark thing
to you all. At last I got the function-based index
working
properly.
This is what I noticed :-
Have to alter session/system for :-
+ alter session set QUERY_REWRITE_ENABLED=TRUE;
+ alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
+ alter session set optimizer_mode=FIRST_ROWS
,
Thanks a lot to you all. At last I got the function-based index
working
properly.
This is what I noticed :-
Have to alter session/system for :-
+ alter session set QUERY_REWRITE_ENABLED=TRUE;
+ alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
+ alter session set
=
Cary Millsap [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
09/05/2002 11:13 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:RE: Function-Based Index not working
Even when the high-water mark thing isn't
Try changing optimizer mode to FIRST_ROWS ...
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have
IS clause and Like with function-based index.
Marul.
- Original Message -
From: Naveen Nahata
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 7:28 PM
Subject: RE: Function-Based Index not working
I think everythying is fine. Did you try index
Hi,
Can you please help me out in solving this weird
problem of funcation-based index not being used when I query the
table.
This is the comand I fired and the result it
returned me.
1. SQL create table employees (last_name
varchar2(20));
Table created.
2. SQL CREATE INDEX upper_ix ON
scan
will be done instead of INDEX scan, to avoid doubling of
work.
Naveen
-Original Message-From: Marul Mehta
[mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 2:18
PMTo: Multiple recipients of list ORACLE-LSubject:
Function-Based Index not working
Hi,
Can you
If CBO think - cost of FTS will be less than index scan then it will not use index.
HTH.
-Original Message-
From: Marul Mehta [mailto:[EMAIL PROTECTED]]
Sent: Sat 8/31/2002 11:48
To: Multiple recipients of list ORACLE-L
Cc:
Subject:Function-Based Index not working
-LSubject:
Function-Based Index not working
Hi,
Can you please help me out in solving this
weird problem of funcation-based index not being used when I query the
table.
This is the comand I fired and the result it
returned me.
1. SQL create table
into the table, and
then put it in a big loop. Analyze table and thn run the same
query.
It should work
naveen
-Original
Message-From: Marul Mehta
[mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 4:03
PMTo: Multiple recipients of list ORACLE-LSubject: Re:
Function-Based Index
.
- Original Message -
From:
Naveen Nahata
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 4:58
PM
Subject: RE: Function-Based Index not
working
See
the table's size is very small. Till it atleast 2 times the value
.
HTH,
Beth
-Original Message-From: Marul Mehta
[mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 4:48
AMTo: Multiple recipients of list ORACLE-LSubject:
Function-Based Index not working
Hi,
Can you please help me out in solving this weird
problem of funcation-based index
PMTo: Multiple recipients of list ORACLE-LSubject: Re:
Function-Based Index not working
Hi Naveen,
Thanks a lot for the efforts you are putting in
for me for such a simple problem, but unfortunately, for me all the tips and
tricks are not solving the problem.
Now these are my current
Hi All,
Thanks a lot to you all. At lastI got the
function-based index working properly.
This is whatI noticed :-
Have to alter session/system for :-
+ alter session set
QUERY_REWRITE_ENABLED=TRUE;+ alter session set
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set
optimizer_mode
Even after giving the hint its not working.
I guess you can't have IS clause and Like with
function-based index.
Marul.
- Original Message -
From:
Naveen Nahata
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 7:28
PM
Subject: RE
-L
Sent: Saturday, August 31, 2002 3:48
AM
Subject: Function-Based Index not
working
Hi,
Can you please help me out in solving this weird
problem of funcation-based index not being used when I query the
table.
This is the comand I fired and the result it
returned
e
tracefile.
steve
- Original Message -
From:
Marul Mehta
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 8:03
AM
Subject: Re: Function-Based Index not
working
Hi Naveen,
Thanks a lot for the efforts you are putting in
for me for s
Hi,
We have created function based indexes on 2 tables using UPPER function
one view is created on these tables that uses these function based indexes.
When i create the indexes and try to select from view i get error ORA-03113.
If i analyze these tables then select from view works
I have created a function based index on one column
, but query is still noy using it . What should be the reason ?
oracle 8.1.7
cost based optimizer
table and index analyzed recently
the
query_rewrite_enabled init.ora parameter has to be set
properly.
-Original Message-From: Big Planet
[mailto:[EMAIL PROTECTED]]Sent: Monday, April 22, 2002 4:15
PMTo: Multiple recipients of list ORACLE-LSubject:
function based index
I have created a function based
Title: Message
You
should make sure the QUERY_REWRITE_ENABLED init.ora parameter is set to
true.
-Original Message-From: Big Planet
[mailto:[EMAIL PROTECTED]] Sent: Monday, April 22, 2002 4:15
PMTo: Multiple recipients of list ORACLE-LSubject:
function based index
I
al Message -
From:
Big
Planet
To: Multiple recipients of list ORACLE-L
Sent: Monday, April 22, 2002 2:15
PM
Subject: function based index
I have created a function based index on one
column , but query is still noy using it . What should be the reason
?
oracle 8.
Hi,
If I have a function based index such as
CREATE INDEX emp_idx01 on emp(SUBSTR(first_name,1,20))
Is there a way to build index where 1,20 portion can be parameterized? If
so how would this be done?
Thanks
Rick
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author
[EMAIL PROTECTED] wrote:
Hi,
If I have a function based index such as
CREATE INDEX emp_idx01 on emp(SUBSTR(first_name,1,20))
Is there a way to build index where 1,20 portion can be parameterized? If
so how would this be done?
Thanks
Rick
No. The function is simply applied
of the query is not so well, can i achieve better
performance with function based index on
max(columnname)?
according the manuals this is not allowed because max is a group function.
anyone with a solution for this ?
vr. gr.
g.g. kor
rdw ict groningen
--
Please see the official ORACLE-L FAQ
(MEMBERNAME)='198915';
and this query will not use the index at all.
I can't understand why the optimizer won't choose to
use the function based index when there is an OR
clause. I've tried the same query with just
MEMBERNAME= and using an index on MEMBERNAME, and
the optimizer will use
SHOPCART
WHERE UPPER(MEMBERNAME) ='19891' OR
UPPER(MEMBERNAME)='198915';
and this query will not use the index at all.
I can't understand why the optimizer won't choose to
use the function based index when there is an OR
clause. I've tried the same query with just
)='198915';
and this query will not use the index at all.
I can't understand why the optimizer won't choose to
use the function based index when there is an OR
clause. I've tried the same query with just
MEMBERNAME= and using an index on MEMBERNAME, and
the optimizer will use
)='198915';
and this query will not use the index at all.
I can't understand why the optimizer won't choose to
use the function based index when there is an OR
clause. I've tried the same query with just
MEMBERNAME= and using an index on MEMBERNAME, and
the optimizer will use
UPPER(MEMBERNAME) ='19891' OR UPPER(MEMBERNAME)='198915';
and this query will not use the index at all.
I can't understand why the optimizer won't choose to use the function based index when
there is an OR clause. I've tried the same query with just MEMBERNAME= and using an
index
hi everyone
we have a query with several max functions in it.
The performance of the query is not so well, can i achieve better
performance with function based index on
max(columnname)?
according the manuals this is not allowed because max is a group function.
anyone with a solution
hi everyone
we have a query with several max functions in it.
The performance of the query is not so well, can i achieve better
performance with function based index on
max(columnname)?
according the manuals this is not allowed because max is a group function.
anyone with a solution
I think, you need materialized view, function based index.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, November 20, 2001 8:00 AM
hi everyone
we have a query with several max functions
Hello folks!
I need to create a function based index, but when I do this, Oracle gives me
the message ORA-00439, function not installed, so I have tried and tried to
figure out how to install this function, but I have failed, therefore this
e-mail. I have looked in ORACLE_HOME/rdbms/admin
What version of Oracle are you using?
Function based indexes only work in EE.
Rick
-Original Message-
Sent: Friday, August 17, 2001 6:46 AM
To: Multiple recipients of list ORACLE-L
p
Hello folks!
I need to create a function based index, but when I do this, Oracle gives me
the message
, function based index and
table p
What version of Oracle are you using?
Function based indexes only work in EE.
Rick
-Original Message-
Sent: Friday, August 17, 2001 6:46 AM
To: Multiple recipients of list ORACLE-L
table
p
Hello folks!
I need to create a function based index, but when
hi
i found this on metalink
the proper privilege required to create function-based indexes.
Connect as dba and provide the user with the privileges required to create a
function based index.
If the user is creating indexes in their own schema:
SVRMGR grant query rewrite to ;
If the user
Hi Gurus !
Please help me. I can't create function based index because it says:
ERROR at line 1:
ORA-01031: insufficient privileges
I have a table which contains a column called lang_name. I try to create
function based index on the table with the following sql statement:
create index
When in doubt, check the manual ;)
Manuals are available in pdf format from metalink and
in html and pdf format from http://technet.oracle.com
(free registration).
Per the SQL Reference manual on the create index
statement:
To create a function-based index in your own schema on
your own table
Thank you ! This solved the problem.
Gyula
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andor, Gyula
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing
, May 28, 2001 3:00 PM
To: Multiple recipients of list ORACLE-L
hi
i found this on metalink
the proper privilege required to create function-based indexes.
Connect as dba and provide the user with the privileges required to create a
function based index.
If the user is creating indexes
You need to set a couple of parameters in the init.ora, these are
query_rewrite_enabled = true
query_rewrite_integrity = trusted
and you need to grant the privilege QUERY_REWRITE to the user.
HTH.
Zabair
_
Get Your
Cherie,
Yes funcidx_status will be ENABLED and there'll be a row in
User_Ind_Expressions for each function-based index.
Jack
Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED
ORACLE-L
Cherie,
Yes funcidx_status will be ENABLED and there'll be a row in
User_Ind_Expressions for each function-based index.
Jack
Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, I
89 matches
Mail list logo
