Here's a link from AskTom that goes into good detail on what you want to
accomplish. He also demonstrates how to use concatenated function based
indexes which I think what you're looking for. Even though his example is
based upon NULLS you can easily modify his code to work for you.
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
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
Naveen , these parameters look to be already set
Doesn't FBI come in with 8.1.7 and Prem is on 8.16 and a comapatible of 8.10 is in
place
Failing that, as my second guess, 2 privileges need to be assigned to the user
(QUERY_REWRITE and CREATE INDEX). The 2nd one is obviously in place to allow
FBI is supported on 8.1.6 (that is what I use them on), but you may be right
about the compatible setting.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, May 29, 2003 7:46 AM
To: Multiple
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
Don't remember but you need a few parameters set appropriately for the FB
indexes to work.
I guess one of the parameters is QUERY_REWRITE_ENABLED which should be set to
TRUE and you also need to set QUERY_REWRITE_INTEGRITY to some appropriate
value.
Regards
Naveen
-Original Message-
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
I created a test table and build an index on columns t1 and t2.
Explain plan for: select min(t1) from test where t2=4;
was resolved using the index only so I think that will be very efficient.
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL
Cherie
Oracle doesn't allow you to use aggregate functions in function-based
indexes but that's fine as you should be OK if you just specify a standard
b*tree index with the columns you mentioned.
Regards,
Mike Hately
-Original Message-
Sent: 11 December 2002 21:09
To: Multiple
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
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
Subject: RE: Function-Based Index not working
I think everythying is fine. Did you try index hint? try that and see.
if that also doesn't work, then either we are missing something or the
Optimizer thinks so
Naveen
-Original Message-
From: Marul Mehta [mailto
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
Cary,
in the nick of time I have a very small table (4 rows) that will be
accessed as part of a view. But this view will be accessed a LOT during
the day. I hadn't thought to index the table but
now, it's a single column table (just a list of codes to include in the
join but I don't
=
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
...Before you implement it, test your idea against the other
possibilities you can think of:
a) full-table scan (a.k.a. heap-organized table)
b) table with an index (different possibilities here: B*-tree, bitmap,
...)
c) index-organized table
Cary Millsap
Hotsos Enterprises, Ltd.
Rachel,
With a table that small I would consider caching the table to
eliminate the io.
I do not know if you can cache an IOT but then it should be even
faster.
Ron
ROR
[EMAIL PROTECTED] 09/05/02 04:28PM
Cary,
in the nick of time I have a very small table (4 rows) that will
be
accessed
And if you adopt the 9i ASSM model for segment space,
then not indexing small tables can hurt you even
more...
Which brings me to my hypothesis:
If you do not index small tables, then there is no
such thing as a small table
Comments anyone?
Cheers
Connor
--- Cary Millsap [EMAIL PROTECTED]
=
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
Given the fact that the table is so small and frequently accessed, it will get
cached 'automagically'. No need to do anything.
Anjo.
On Thursday 05 September 2002 23:43, you wrote:
Rachel,
With a table that small I would consider caching the table to
eliminate the io.
I do not know if
I love automagic things :) so I can leave the table alone
right now there are all of 7 rows in it
Rachel
--- Anjo Kolk [EMAIL PROTECTED] wrote:
Given the fact that the table is so small and frequently accessed, it
will get
cached 'automagically'. No need to do anything.
Anjo.
=
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
Marul,
1. you
don't have table analyzed in which case Rule based optimizer will be used. CBO
is used if atleast one of the tables in the query is
ANALYZED
2.
There is no data in your table. Optimizer goes for a full tablescan if it thinks
that it will be moer advisable to do a full table
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
) IS NOT NULL;
Please tell me what else should I do to make this query use the index which
is created.
TIA,
Marul.
- Original Message -
From:
Naveen Nahata
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 3:03
PM
Subject: RE: Function-Based
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
Hi,
Try
running the query with a hint that forces the index. If it still doesn't
use the index, then you missed one of the steps needed to enable function-based
indexes. If it does use the index, then you've done everything right, but
the optimizer is deciding the fts is a better option.
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
=FIRST_ROWS;
And
+ can't use IS NULL IS NOT NULL
clause.
+ can't use Like operator.
Regards,
Marul.
- Original Message -
From:
Marul Mehta
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 6:33
PM
Subject: Re: Function-Based Index not
working
PROTECTED]]Sent: Saturday, August 31, 2002 6:33
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
analyze table employees compute
statistics
you may need to enable query rewrite
too.
ALTER SESSION SET
QUERY_REWRITE_ENABLED = TRUE;ALTER SESSION SET QUERY_REWRITE_INTEGRITY =
TRUSTED;
steve
- Original Message -
From:
Marul Mehta
To: Multiple recipients of list
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
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
QUERY_REWRITE must be enabled. Either for the
instance (i.e. init.ora or alter system set), for the session (alter session
set) orfor a single SQL statement(i.e. SQL hint
"rewrite")...
You'll also need to analyze the index with either
ANALYZE or DBMS_STATS...
- Original Message
[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 to the
You need a simple index on the column that gets queried Max(columnname).
The index is always sorted.
-Original Message-
Sent: Tuesday, November 20, 2001 8:00 AM
To: Multiple recipients of list ORACLE-L
hi everyone
we have a query with several max functions in it.
The performance
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 for this
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 in it.
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
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
Oracle manual states that in order to use function based indexes in queries
the query_rewrite_enabled parameter needs to be set to 'true', and the
query_rewrite_integrity parameter to 'trusted', apart from granting the
query rewrite privilege.
rgds
amar
-Original Message-
Sent:
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
66 matches
Mail list logo
