RE: Function-Based Index not working

[Khedr, Waleed]

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]> 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 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 FAQ: http://www.orafaq.com
> -- 
> Author: Mladen Gogala
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).


__
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Function-Based Index not working

[Rachel Carmichael]

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 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 FAQ: http://www.orafaq.com
> -- 
> Author: Mladen Gogala
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).


__
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Function-Based Index not working

[Post, Ethan]

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, September 05, 2002 7:53 PM
To: Multiple recipients of list ORACLE-L


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 you can cache an IOT but then it should be even
> faster.
> Ron
> ROR
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Post, Ethan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Function-Based Index not working

[Mladen Gogala]

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 FAQ: http://www.orafaq.com
-- 
Author: Mladen Gogala
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Function-Based Index not working

[Khedr, Waleed]

I think the segment header requires 4 blocks reads regardless the number of
rows in the table.

Adding one row to the table will require an additional block read (data
block).

Waleed

-Original Message-
Sent: Thursday, September 05, 2002 8:43 PM
To: Multiple recipients of list ORACLE-L


FTS:

The default of one extent is allocated to the table. When the one row is 
inserted the HWM is bumped by 5 blocks. So it does 1 LIO for the Segment 
Header (to read the extent map) and finds out that the HWM is in the first 
extent and reads up to that block. 1 + 4 = 5

Index:

Index is small, fits into 1 block: The root block. So while accessing the
root 
block, it finds the rowid in of the row. Then Oracle goes to the rowid
(which 
is another block access) and reads the datarow. 1+1 =2 


Anjo.

(Ofcourse this all counting the LIOs and not measuring the cost of an LIO).





On Thursday 05 September 2002 22:18, you wrote:
> Cary,
>
> Two buffer gets for an Index Range scan vs. 5 buffer gets for a Full Table
> Scan.
>
> Why is that?
>
> Jared
>
> FTS
> Buffer gets:  5
> Time: < 1/100 second
> =
> PARSING IN CURSOR #3 len=21 dep=0 uid=19 oct=3 lid=19 tim=45692398
> hv=2775880792 ad='5304bbac'
> select * from onerow
> END OF STMT
> PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692398
> EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692398
> WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
> FETCH #3:c=0,e=0,p=0,cr=1,cu=4,mis=0,r=1,dep=0,og=4,tim=45692398
> WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
> FETCH #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692398
> WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
> WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
> STAT #3 id=1 cnt=1 pid=0 pos=0 obj=4651 op='TABLE ACCESS FULL ONEROW '
> =
>
> IRS
> Buffer gets: 2
> Time < 1/100 second
> =
> PARSING IN CURSOR #3 len=33 dep=0 uid=19 oct=3 lid=19 tim=45692399
> hv=2516078141 ad='531a22b8'
> select * from onerow where key=1
> END OF STMT
> PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692399
> EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692399
> WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
> FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=45692399
> WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
> FETCH #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692399
> WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
> WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
> STAT #3 id=1 cnt=1 pid=0 pos=0 obj=4651 op='TABLE ACCESS BY INDEX ROWID
> ONEROW '
> STAT #3 id=2 cnt=2 pid=1 pos=1 obj=4652 op='INDEX RANGE SCAN '
> WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
> WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
> =
>
>
>
>
>
> "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 a problem, it's sometimes more
> efficient to read every row in a table through an index than via a
> full-table scan.
>
> If you're curious, try this. Create a table with two columns, "key" and
> "value", and insert one row with key=1, value='x'. Create an index on
> "key". Then?
>
> alter session set events '10046 trace name context forever, level 8';
> select * from onerow;  /* just to make sure it's cached */
> select * from onerow;
> select * from onerow where key=1;  /* just to make sure it's cached */
> select * from onerow where key=1;
> exit;
>
> Now look at your trace data. You'll find that the full-table scan of this
> table is both cheaper and faster through the index.
>
> The age-old advice from many SQL tuning "experts" is badly wrong when they
> tell you never to index small tables. For applications that execute a lot
> of small-table queries, the performance impact really adds up.
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic, Oct 1?3 San Francisco, Oct 15?17 Dallas, Dec 9?

Re: Function-Based Index not working

[Rachel Carmichael]

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.
> 
> 
> 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 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 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 want to hard_code them into the view). SO I guess
> > I'll
> > just create it as a IOT, combining index and saving space at the
> same
> > time
> >
> > Rachel
> >
> > --- Cary Millsap <[EMAIL PROTECTED]> wrote:
> > > Even when the high-water mark thing isn't a problem, it's
> sometimes
> > > more
> > > efficient to read every row in a table through an index than via
> a
> > > full-table scan.
> > >
> > >
> > >
> > > If you're curious, try this. Create a table with two columns,
> "key"
> > > and
> > > "value", and insert one row with key=1, value='x'. Create an
> index
> >
> > on
> >
> > > "key". Then.
> > >
> > >
> > >
> > > alter session set events '10046 trace name context forever, level
> >
> > 8';
> >
> > > select * from onerow;  /* just to make sure it's cached */
> > >
> > > select * from onerow;
> > >
> > > select * from onerow where key=1;  /* just to make sure it's
> cached
> > > */
> > >
> > > select * from onerow where key=1;
> > >
> > > exit;
> > >
> > >
> > >
> > > Now look at your trace data. You'll find that the full-table scan
> of
> > > this table is both cheaper and faster through the index.
> > >
> > >
> > >
> > > The age-old advice from many SQL tuning "experts" is badly wrong
> >
> > when
> >
> > > they tell you never to index small tables. For applications that
> > > execute
> > > a lot of small-table queries, the performance impact really adds
> up.
> > >
> > >
> > >
> > > Cary Millsap
> > > Hotsos Enterprises, Ltd.
> > > http://www.hotsos.com
> > >
> > > Upcoming events:
> > > - Hotsos Clinic  , Oct 1-3
> >
> > San
> >
> > > Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu
> > > - 2003 Hotsos Symposium  
> on
> > > OracleR System Performance, Feb 9-12 Dallas
> > > - Next event: Miracle Database Forum  ,
> Sep
> > > 20-22 Middlefart Denmark
> > >
> > > -Original Message-
> > > Sent: Thursday, September 05, 2002 12:19 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > >
> > > Not necessarily... Cary's IOUG-A presentation covers this very
> well.
> > > One
> > > scenario is where the high water mark is set artificially high,
> and
> > > there are far more blocks allocated than actually contain data.
> In
> > > this
> > > case, a FTS will be reading far too many empty blocks.
> > >
> > > -Original Message-
> > > Sent: Thursday, September 05, 2002 10:19 AM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > > Hello
> > >
> > >
> > >
> > > I think that the amount of records you read is also taken into
> > > account.
> > >
> > > If you run a query that selects ALL the records in the tables
> > >
> > > it is ALWAYS more efficient to do full table scan then to access
> > >
> > > by index.
> > >
> > >
> > >
> > > Yechiel Adar
> > > Mehish
> > >
> > > - Original Message -
> > >
> > >
> > > To: Multiple   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 QUERY_REWRITE_ENABLED=TRUE;
> > > + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> > > + alter session set optimizer_mode=FIRST_ROWS;
> > >
> > >
> > >
> > > And
> > >
> > > + can't use IS NULL & IS NOT NULL clause.
> > >
> > > + can't use Like operator.
> > >
> > >
> > >
> > > Regards,
> > >
> > > Marul.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > - Original Message -
> > >
> > >
> > > To: Multiple   recipients of list
> > > ORACLE-L
> 
=== message truncated ===


__
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: [E

Re: Function-Based Index not working

[Anjo Kolk]

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 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 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 want to hard_code them into the view). SO I guess
> I'll
> just create it as a IOT, combining index and saving space at the same
> time
>
> Rachel
>
> --- Cary Millsap <[EMAIL PROTECTED]> wrote:
> > Even when the high-water mark thing isn't a problem, it's sometimes
> > more
> > efficient to read every row in a table through an index than via a
> > full-table scan.
> >
> >
> >
> > If you're curious, try this. Create a table with two columns, "key"
> > and
> > "value", and insert one row with key=1, value='x'. Create an index
>
> on
>
> > "key". Then.
> >
> >
> >
> > alter session set events '10046 trace name context forever, level
>
> 8';
>
> > select * from onerow;  /* just to make sure it's cached */
> >
> > select * from onerow;
> >
> > select * from onerow where key=1;  /* just to make sure it's cached
> > */
> >
> > select * from onerow where key=1;
> >
> > exit;
> >
> >
> >
> > Now look at your trace data. You'll find that the full-table scan of
> > this table is both cheaper and faster through the index.
> >
> >
> >
> > The age-old advice from many SQL tuning "experts" is badly wrong
>
> when
>
> > they tell you never to index small tables. For applications that
> > execute
> > a lot of small-table queries, the performance impact really adds up.
> >
> >
> >
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> >
> > Upcoming events:
> > - Hotsos Clinic  , Oct 1-3
>
> San
>
> > Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu
> > - 2003 Hotsos Symposium   on
> > OracleR System Performance, Feb 9-12 Dallas
> > - Next event: Miracle Database Forum  , Sep
> > 20-22 Middlefart Denmark
> >
> > -Original Message-
> > Sent: Thursday, September 05, 2002 12:19 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> > Not necessarily... Cary's IOUG-A presentation covers this very well.
> > One
> > scenario is where the high water mark is set artificially high, and
> > there are far more blocks allocated than actually contain data. In
> > this
> > case, a FTS will be reading far too many empty blocks.
> >
> > -Original Message-
> > Sent: Thursday, September 05, 2002 10:19 AM
> > To: Multiple recipients of list ORACLE-L
> >
> > Hello
> >
> >
> >
> > I think that the amount of records you read is also taken into
> > account.
> >
> > If you run a query that selects ALL the records in the tables
> >
> > it is ALWAYS more efficient to do full table scan then to access
> >
> > by index.
> >
> >
> >
> > Yechiel Adar
> > Mehish
> >
> > - Original Message -
> >
> >
> > To: Multiple   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 QUERY_REWRITE_ENABLED=TRUE;
> > + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> > + alter session set optimizer_mode=FIRST_ROWS;
> >
> >
> >
> > And
> >
> > + can't use IS NULL & IS NOT NULL clause.
> >
> > + can't use Like operator.
> >
> >
> >
> > Regards,
> >
> > Marul.
> >
> >
> >
> >
> >
> >
> >
> > - Original Message -
> >
> >
> > To: Multiple   recipients of list
> > ORACLE-L
> >
> > Sent: Saturday, August 31, 2002 6:33 PM
> >
> >
> >
> >
> > 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 statistics :-
> >
> >
> >
> > + alter session set QUERY_REWRITE_ENABLED=TRUE;
> > + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> > + alter session set optimizer_mode=FIRST_ROWS;
> > + alter session set DB_FILE_MULTIBLOCK_READ_COUNT=1;
> >
> >
> >
> > This procedure writes 180,000 records in employeees table
> >
> > + execute bulk_insert
> >
> >
> >
> > Analyzing table and rebuilding index (though its not necessary)
> >
> > + analyze table employees compute statistics;
> >
> > + alter index upper_ix rebuild;
> >
> > Maki

Re: Function-Based Index not working

[Anjo Kolk]

FTS:

The default of one extent is allocated to the table. When the one row is 
inserted the HWM is bumped by 5 blocks. So it does 1 LIO for the Segment 
Header (to read the extent map) and finds out that the HWM is in the first 
extent and reads up to that block. 1 + 4 = 5

Index:

Index is small, fits into 1 block: The root block. So while accessing the root 
block, it finds the rowid in of the row. Then Oracle goes to the rowid (which 
is another block access) and reads the datarow. 1+1 =2 


Anjo.

(Ofcourse this all counting the LIOs and not measuring the cost of an LIO).





On Thursday 05 September 2002 22:18, you wrote:
> Cary,
>
> Two buffer gets for an Index Range scan vs. 5 buffer gets for a Full Table
> Scan.
>
> Why is that?
>
> Jared
>
> FTS
> Buffer gets:  5
> Time: < 1/100 second
> =
> PARSING IN CURSOR #3 len=21 dep=0 uid=19 oct=3 lid=19 tim=45692398
> hv=2775880792 ad='5304bbac'
> select * from onerow
> END OF STMT
> PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692398
> EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692398
> WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
> FETCH #3:c=0,e=0,p=0,cr=1,cu=4,mis=0,r=1,dep=0,og=4,tim=45692398
> WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
> FETCH #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692398
> WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
> WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
> STAT #3 id=1 cnt=1 pid=0 pos=0 obj=4651 op='TABLE ACCESS FULL ONEROW '
> =
>
> IRS
> Buffer gets: 2
> Time < 1/100 second
> =
> PARSING IN CURSOR #3 len=33 dep=0 uid=19 oct=3 lid=19 tim=45692399
> hv=2516078141 ad='531a22b8'
> select * from onerow where key=1
> END OF STMT
> PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692399
> EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692399
> WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
> FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=45692399
> WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
> FETCH #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692399
> WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
> WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
> STAT #3 id=1 cnt=1 pid=0 pos=0 obj=4651 op='TABLE ACCESS BY INDEX ROWID
> ONEROW '
> STAT #3 id=2 cnt=2 pid=1 pos=1 obj=4652 op='INDEX RANGE SCAN '
> WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
> WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
> =
>
>
>
>
>
> "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 a problem, it's sometimes more
> efficient to read every row in a table through an index than via a
> full-table scan.
>
> If you're curious, try this. Create a table with two columns, "key" and
> "value", and insert one row with key=1, value='x'. Create an index on
> "key". Then?
>
> alter session set events '10046 trace name context forever, level 8';
> select * from onerow;  /* just to make sure it's cached */
> select * from onerow;
> select * from onerow where key=1;  /* just to make sure it's cached */
> select * from onerow where key=1;
> exit;
>
> Now look at your trace data. You'll find that the full-table scan of this
> table is both cheaper and faster through the index.
>
> The age-old advice from many SQL tuning "experts" is badly wrong when they
> tell you never to index small tables. For applications that execute a lot
> of small-table queries, the performance impact really adds up.
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic, Oct 1?3 San Francisco, Oct 15?17 Dallas, Dec 9?11 Honolulu
> - 2003 Hotsos Symposium on Oracle® System Performance, Feb 9?12 Dallas
> - Next event: Miracle Database Forum, Sep 20?22 Middlefart Denmark
> -Original Message-
> Sent: Thursday, September 05, 2002 12:19 PM
> To: Multiple recipients of list ORACLE-L
>
> Not neces

RE: Function-Based Index not working

[Connor McDonald]

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]> wrote: >
Even when the high-water mark thing isn't a problem,
> it's sometimes more
> efficient to read every row in a table through an
> index than via a
> full-table scan.
> 
>  
> 
> If you're curious, try this. Create a table with two
> columns, "key" and
> "value", and insert one row with key=1, value='x'.
> Create an index on
> "key". Then.
> 
>  
> 
> alter session set events '10046 trace name context
> forever, level 8';
> 
> select * from onerow;  /* just to make sure it's
> cached */
> 
> select * from onerow;
> 
> select * from onerow where key=1;  /* just to make
> sure it's cached */
> 
> select * from onerow where key=1;
> 
> exit;
> 
>  
> 
> Now look at your trace data. You'll find that the
> full-table scan of
> this table is both cheaper and faster through the
> index.
> 
>  
> 
> The age-old advice from many SQL tuning "experts" is
> badly wrong when
> they tell you never to index small tables. For
> applications that execute
> a lot of small-table queries, the performance impact
> really adds up.
> 
>  
> 
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> 
> Upcoming events:
> - Hotsos Clinic
>  , Oct 1-3
> San
> Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu
> - 2003 Hotsos Symposium
>   on
> OracleR System Performance, Feb 9-12 Dallas
> - Next event: Miracle Database Forum
>  , Sep
> 20-22 Middlefart Denmark
> 
> -Original Message-
> Sent: Thursday, September 05, 2002 12:19 PM
> To: Multiple recipients of list ORACLE-L
> 
>  
> 
> Not necessarily... Cary's IOUG-A presentation covers
> this very well. One
> scenario is where the high water mark is set
> artificially high, and
> there are far more blocks allocated than actually
> contain data. In this
> case, a FTS will be reading far too many empty
> blocks.
> 
> -Original Message-
> Sent: Thursday, September 05, 2002 10:19 AM
> To: Multiple recipients of list ORACLE-L
> 
> Hello
> 
>  
> 
> I think that the amount of records you read is also
> taken into account.
> 
> If you run a query that selects ALL the records in
> the tables
> 
> it is ALWAYS more efficient to do full table scan
> then to access
> 
> by index.
> 
>  
> 
> Yechiel Adar
> Mehish
> 
> - Original Message - 
> 
> 
> To: Multiple  
> 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 QUERY_REWRITE_ENABLED=TRUE;
> + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> + alter session set optimizer_mode=FIRST_ROWS;
> 
>  
> 
> And 
> 
> + can't use IS NULL & IS NOT NULL clause.
> 
> + can't use Like operator.
> 
>  
> 
> Regards,
> 
> Marul.
> 
>  
> 
>  
> 
>  
> 
> - Original Message - 
> 
> 
> To: Multiple  
> recipients of list ORACLE-L 
> 
> Sent: Saturday, August 31, 2002 6:33 PM
> 
> 
>  
> 
> 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 statistics :-
> 
>  
> 
> + alter session set QUERY_REWRITE_ENABLED=TRUE;
> + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> + alter session set optimizer_mode=FIRST_ROWS;
> + alter session set DB_FILE_MULTIBLOCK_READ_COUNT=1;
> 
>  
> 
> This procedure writes 180,000 records in employeees
> table
> 
> + execute bulk_insert 
> 
>  
> 
> Analyzing table and rebuilding index (though its not
> necessary)
> 
> + analyze table employees compute statistics;
> 
> + alter index upper_ix rebuild;
> 
> Making autotrace on
> 
> + set autotrace traceonly explain
> 
>  
> 
> Fired the query:
> 
> SELECT last_name FROM employees WHERE
> UPPER(last_name) IS NOT NULL
> ORDER BY UPPER(last_name);
> Elapsed: 00:00:00.00
> 
>  
> 
> Execution Plan
>
--
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=57
> Card=4001 Bytes=2
>   0005)
> 
>  
> 
>10   SORT (ORDER BY) (Cost=57 Card=4001
> Bytes=20005)
>21 TABLE ACCESS (FULL) OF 'EMPLOYEES'
> (Cost=38 Card=4001 By
>   tes=20005)
> 
>  
> 
> Any clues what is happening? Should I insert more
> records in the table.
> 
>  
> 
> TIA,
> 
> Marul.
> 
>  
> 
>  
> 
>  
> 
>  
> 
>  
> 
>  
> 
> - Original Message - 
> 
> 
> To: Multiple  
> recipients of list ORACLE-L 
> 
> Sen

RE: Function-Based Index not working

[Ron Rogers]

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 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 want to hard_code them into the view). SO I guess
I'll
just create it as a IOT, combining index and saving space at the same
time

Rachel

--- Cary Millsap <[EMAIL PROTECTED]> wrote:
> Even when the high-water mark thing isn't a problem, it's sometimes
> more
> efficient to read every row in a table through an index than via a
> full-table scan.
> 
>  
> 
> If you're curious, try this. Create a table with two columns, "key"
> and
> "value", and insert one row with key=1, value='x'. Create an index
on
> "key". Then.
> 
>  
> 
> alter session set events '10046 trace name context forever, level
8';
> 
> select * from onerow;  /* just to make sure it's cached */
> 
> select * from onerow;
> 
> select * from onerow where key=1;  /* just to make sure it's cached
> */
> 
> select * from onerow where key=1;
> 
> exit;
> 
>  
> 
> Now look at your trace data. You'll find that the full-table scan of
> this table is both cheaper and faster through the index.
> 
>  
> 
> The age-old advice from many SQL tuning "experts" is badly wrong
when
> they tell you never to index small tables. For applications that
> execute
> a lot of small-table queries, the performance impact really adds up.
> 
>  
> 
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com 
> 
> Upcoming events:
> - Hotsos Clinic  , Oct 1-3
San
> Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu
> - 2003 Hotsos Symposium   on
> OracleR System Performance, Feb 9-12 Dallas
> - Next event: Miracle Database Forum  , Sep
> 20-22 Middlefart Denmark
> 
> -Original Message-
> Sent: Thursday, September 05, 2002 12:19 PM
> To: Multiple recipients of list ORACLE-L
> 
>  
> 
> Not necessarily... Cary's IOUG-A presentation covers this very well.
> One
> scenario is where the high water mark is set artificially high, and
> there are far more blocks allocated than actually contain data. In
> this
> case, a FTS will be reading far too many empty blocks.
> 
> -Original Message-
> Sent: Thursday, September 05, 2002 10:19 AM
> To: Multiple recipients of list ORACLE-L
> 
> Hello
> 
>  
> 
> I think that the amount of records you read is also taken into
> account.
> 
> If you run a query that selects ALL the records in the tables
> 
> it is ALWAYS more efficient to do full table scan then to access
> 
> by index.
> 
>  
> 
> Yechiel Adar
> Mehish
> 
> - Original Message - 
> 
> 
> To: Multiple   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 QUERY_REWRITE_ENABLED=TRUE;
> + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> + alter session set optimizer_mode=FIRST_ROWS;
> 
>  
> 
> And 
> 
> + can't use IS NULL & IS NOT NULL clause.
> 
> + can't use Like operator.
> 
>  
> 
> Regards,
> 
> Marul.
> 
>  
> 
>  
> 
>  
> 
> - Original Message - 
> 
> 
> To: Multiple   recipients of list
> ORACLE-L 
> 
> Sent: Saturday, August 31, 2002 6:33 PM
> 
> 
>  
> 
> 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 statistics :-
> 
>  
> 
> + alter session set QUERY_REWRITE_ENABLED=TRUE;
> + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> + alter session set optimizer_mode=FIRST_ROWS;
> + alter session set DB_FILE_MULTIBLOCK_READ_COUNT=1;
> 
>  
> 
> This procedure writes 180,000 records in employeees table
> 
> + execute bulk_insert 
> 
>  
> 
> Analyzing table and rebuilding index (though its not necessary)
> 
> + analyze table employees compute statistics;
> 
> + alter index upper_ix rebuild;
> 
> Making autotrace on
> 
> + set autotrace traceonly explain
> 
>  
> 
> Fired the query:
> 
> SELECT last_name FROM employees WHERE UPPER(last_name) IS NOT NULL
> ORDER BY UPPER(last_name);
> Elapsed: 00:00:00.00
> 
>  
> 
> Execution Plan
> --
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=4001
> Bytes=2
>   0005)
> 
>  
> 
>10   SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005)
>21 TABLE ACCESS (FULL)

RE: Function-Based Index not working

[Cary Millsap]

...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.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11
Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
- Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark



-Original Message-
Carmichael
Sent: Thursday, September 05, 2002 3:28 PM
To: Multiple recipients of list ORACLE-L

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 want to hard_code them into the view). SO I guess I'll
just create it as a IOT, combining index and saving space at the same
time

Rachel

--- Cary Millsap <[EMAIL PROTECTED]> wrote:
> Even when the high-water mark thing isn't a problem, it's sometimes
> more
> efficient to read every row in a table through an index than via a
> full-table scan.
> 
>  
> 
> If you're curious, try this. Create a table with two columns, "key"
> and
> "value", and insert one row with key=1, value='x'. Create an index on
> "key". Then.
> 
>  
> 
> alter session set events '10046 trace name context forever, level 8';
> 
> select * from onerow;  /* just to make sure it's cached */
> 
> select * from onerow;
> 
> select * from onerow where key=1;  /* just to make sure it's cached
> */
> 
> select * from onerow where key=1;
> 
> exit;
> 
>  
> 
> Now look at your trace data. You'll find that the full-table scan of
> this table is both cheaper and faster through the index.
> 
>  
> 
> The age-old advice from many SQL tuning "experts" is badly wrong when
> they tell you never to index small tables. For applications that
> execute
> a lot of small-table queries, the performance impact really adds up.
> 
>  
> 
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> 
> Upcoming events:
> - Hotsos Clinic  , Oct 1-3 San
> Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu
> - 2003 Hotsos Symposium   on
> OracleR System Performance, Feb 9-12 Dallas
> - Next event: Miracle Database Forum  , Sep
> 20-22 Middlefart Denmark
> 
> -Original Message-
> Sent: Thursday, September 05, 2002 12:19 PM
> To: Multiple recipients of list ORACLE-L
> 
>  
> 
> Not necessarily... Cary's IOUG-A presentation covers this very well.
> One
> scenario is where the high water mark is set artificially high, and
> there are far more blocks allocated than actually contain data. In
> this
> case, a FTS will be reading far too many empty blocks.
> 
> -Original Message-
> Sent: Thursday, September 05, 2002 10:19 AM
> To: Multiple recipients of list ORACLE-L
> 
> Hello
> 
>  
> 
> I think that the amount of records you read is also taken into
> account.
> 
> If you run a query that selects ALL the records in the tables
> 
> it is ALWAYS more efficient to do full table scan then to access
> 
> by index.
> 
>  
> 
> Yechiel Adar
> Mehish
> 
> - Original Message - 
> 
> 
> To: Multiple   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 QUERY_REWRITE_ENABLED=TRUE;
> + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> + alter session set optimizer_mode=FIRST_ROWS;
> 
>  
> 
> And 
> 
> + can't use IS NULL & IS NOT NULL clause.
> 
> + can't use Like operator.
> 
>  
> 
> Regards,
> 
> Marul.
> 
>  
> 
>  
> 
>  
> 
> - Original Message - 
> 
> 
> To: Multiple   recipients of list
> ORACLE-L 
> 
> Sent: Saturday, August 31, 2002 6:33 PM
> 
> 
>  
> 
> 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 statistics :-
> 
>  
> 
> + alter session set QUERY_REWRITE_ENABLED=TRUE;
> + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> + alter session set optimizer_mode=FIRST_ROWS;
> + alter session set DB_FILE_MULTIBLOCK_READ_COUNT=1;
> 
>  
> 
> This procedure writes 180,000 records in employeees table
> 
> + execute bulk_insert 
> 
>  
> 
> Analyzing table and rebuilding index (though its not necessary)
> 
> + analyze table employees compute statistics;
> 
> + alter index upper_ix rebuild;
> 
> Making autotrace on
> 
> + set autotrace traceonly 

RE: Function-Based Index not working

[Cary Millsap]

Right.

The index one is easy to explain. It's one LIO for the index root (which
is also the appropriate leaf block for such a small index). Then it's
one more LIO for the data block. In addition to there being only 2 LIOs,
note also that these LIOs are also very inexpensive LIOs. All LIOs do
not cost the same. LIOs that seek straight to a desired row are cheaper
than LIOs motivated by a full-table scan. Each LIO motivated by a
full-table scan has to parse an entire Oracle block.

Why the full-table scan requires five LIOs is a guess. First, remember
that no matter whether my guess is correct or not, you are seeing proof
that the index access is cheaper than the full-table scan. Now, my guess
is grounded in remembering what a full-table scan does: it reads up to
the high-water mark. Well, the high-water mark moves in increments of
_bump_high_water_mark_count (sp?), which by default is 5. (If it
defaulted to 1, people would see lots more waits for "buffer busy waits"
events.) Since "select * from onerow" is a full-table scan, it reads
five blocks. If you really want to dig into it, restart your test
instance and run the experiment again. In the "db_file_%_read" event p1
and p2 values, you can see exactly which blocks are getting read.

Fun stuff. Establishing the habit of tracing things instead of just
blindly believing what the so-called "experts" say is, in my estimation,
the *most* important critical success factor for an Oracle performance
analyst.


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11
Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
- Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark



-Original Message-
Sent: Thursday, September 05, 2002 2:16 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Importance: High

Cary,

Two buffer gets for an Index Range scan vs. 5 buffer gets for a Full
Table 
Scan.

Why is that?

Jared

FTS
Buffer gets:  5
Time: < 1/100 second
=
PARSING IN CURSOR #3 len=21 dep=0 uid=19 oct=3 lid=19 tim=45692398 
hv=2775880792 ad='5304bbac'
select * from onerow
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692398
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692398
WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
FETCH #3:c=0,e=0,p=0,cr=1,cu=4,mis=0,r=1,dep=0,og=4,tim=45692398
WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1
p3=0
FETCH #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692398
WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1
p3=0
STAT #3 id=1 cnt=1 pid=0 pos=0 obj=4651 op='TABLE ACCESS FULL ONEROW '
=

IRS
Buffer gets: 2
Time < 1/100 second
=
PARSING IN CURSOR #3 len=33 dep=0 uid=19 oct=3 lid=19 tim=45692399 
hv=2516078141 ad='531a22b8'
select * from onerow where key=1
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692399
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692399
WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=45692399
WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1
p3=0
FETCH #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692399
WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1
p3=0
STAT #3 id=1 cnt=1 pid=0 pos=0 obj=4651 op='TABLE ACCESS BY INDEX ROWID 
ONEROW '
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=4652 op='INDEX RANGE SCAN '
WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1
p3=0
=





"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 a problem, it's sometimes more

efficient to read every row in a table through an index than via a 
full-table scan.
 
If you're curious, try this. Create a table with two columns, "key" and 
"value", and insert one row with key=1, value='x'. Create an index on 
"key". Then?
 
alter session set events '10046 trace name context forever, level 8';
select * from onerow;  /* just to make sure it's cached */
select * from onerow;
select * from onerow where key=1;  /*

RE: Function-Based Index not working

[Rachel Carmichael]

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 want to hard_code them into the view). SO I guess I'll
just create it as a IOT, combining index and saving space at the same
time

Rachel

--- Cary Millsap <[EMAIL PROTECTED]> wrote:
> Even when the high-water mark thing isn't a problem, it's sometimes
> more
> efficient to read every row in a table through an index than via a
> full-table scan.
> 
>  
> 
> If you're curious, try this. Create a table with two columns, "key"
> and
> "value", and insert one row with key=1, value='x'. Create an index on
> "key". Then.
> 
>  
> 
> alter session set events '10046 trace name context forever, level 8';
> 
> select * from onerow;  /* just to make sure it's cached */
> 
> select * from onerow;
> 
> select * from onerow where key=1;  /* just to make sure it's cached
> */
> 
> select * from onerow where key=1;
> 
> exit;
> 
>  
> 
> Now look at your trace data. You'll find that the full-table scan of
> this table is both cheaper and faster through the index.
> 
>  
> 
> The age-old advice from many SQL tuning "experts" is badly wrong when
> they tell you never to index small tables. For applications that
> execute
> a lot of small-table queries, the performance impact really adds up.
> 
>  
> 
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> 
> Upcoming events:
> - Hotsos Clinic  , Oct 1-3 San
> Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu
> - 2003 Hotsos Symposium   on
> OracleR System Performance, Feb 9-12 Dallas
> - Next event: Miracle Database Forum  , Sep
> 20-22 Middlefart Denmark
> 
> -Original Message-
> Sent: Thursday, September 05, 2002 12:19 PM
> To: Multiple recipients of list ORACLE-L
> 
>  
> 
> Not necessarily... Cary's IOUG-A presentation covers this very well.
> One
> scenario is where the high water mark is set artificially high, and
> there are far more blocks allocated than actually contain data. In
> this
> case, a FTS will be reading far too many empty blocks.
> 
> -Original Message-
> Sent: Thursday, September 05, 2002 10:19 AM
> To: Multiple recipients of list ORACLE-L
> 
> Hello
> 
>  
> 
> I think that the amount of records you read is also taken into
> account.
> 
> If you run a query that selects ALL the records in the tables
> 
> it is ALWAYS more efficient to do full table scan then to access
> 
> by index.
> 
>  
> 
> Yechiel Adar
> Mehish
> 
> - Original Message - 
> 
> 
> To: Multiple   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 QUERY_REWRITE_ENABLED=TRUE;
> + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> + alter session set optimizer_mode=FIRST_ROWS;
> 
>  
> 
> And 
> 
> + can't use IS NULL & IS NOT NULL clause.
> 
> + can't use Like operator.
> 
>  
> 
> Regards,
> 
> Marul.
> 
>  
> 
>  
> 
>  
> 
> - Original Message - 
> 
> 
> To: Multiple   recipients of list
> ORACLE-L 
> 
> Sent: Saturday, August 31, 2002 6:33 PM
> 
> 
>  
> 
> 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 statistics :-
> 
>  
> 
> + alter session set QUERY_REWRITE_ENABLED=TRUE;
> + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> + alter session set optimizer_mode=FIRST_ROWS;
> + alter session set DB_FILE_MULTIBLOCK_READ_COUNT=1;
> 
>  
> 
> This procedure writes 180,000 records in employeees table
> 
> + execute bulk_insert 
> 
>  
> 
> Analyzing table and rebuilding index (though its not necessary)
> 
> + analyze table employees compute statistics;
> 
> + alter index upper_ix rebuild;
> 
> Making autotrace on
> 
> + set autotrace traceonly explain
> 
>  
> 
> Fired the query:
> 
> SELECT last_name FROM employees WHERE UPPER(last_name) IS NOT NULL
> ORDER BY UPPER(last_name);
> Elapsed: 00:00:00.00
> 
>  
> 
> Execution Plan
> --
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=4001
> Bytes=2
>   0005)
> 
>  
> 
>10   SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005)
>21 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=38 Card=4001
> By
> 
=== message truncated ===


__
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com
-- 
Pleas

RE: Function-Based Index not working

[Jared . Still]

Cary,

Two buffer gets for an Index Range scan vs. 5 buffer gets for a Full Table 
Scan.

Why is that?

Jared

FTS
Buffer gets:  5
Time: < 1/100 second
=
PARSING IN CURSOR #3 len=21 dep=0 uid=19 oct=3 lid=19 tim=45692398 
hv=2775880792 ad='5304bbac'
select * from onerow
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692398
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692398
WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
FETCH #3:c=0,e=0,p=0,cr=1,cu=4,mis=0,r=1,dep=0,og=4,tim=45692398
WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
FETCH #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692398
WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
STAT #3 id=1 cnt=1 pid=0 pos=0 obj=4651 op='TABLE ACCESS FULL ONEROW '
=

IRS
Buffer gets: 2
Time < 1/100 second
=
PARSING IN CURSOR #3 len=33 dep=0 uid=19 oct=3 lid=19 tim=45692399 
hv=2516078141 ad='531a22b8'
select * from onerow where key=1
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692399
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692399
WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=4,tim=45692399
WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
FETCH #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45692399
WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
STAT #3 id=1 cnt=1 pid=0 pos=0 obj=4651 op='TABLE ACCESS BY INDEX ROWID 
ONEROW '
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=4652 op='INDEX RANGE SCAN '
WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
=





"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 a problem, it's sometimes more 
efficient to read every row in a table through an index than via a 
full-table scan.
 
If you're curious, try this. Create a table with two columns, "key" and 
"value", and insert one row with key=1, value='x'. Create an index on 
"key". Then?
 
alter session set events '10046 trace name context forever, level 8';
select * from onerow;  /* just to make sure it's cached */
select * from onerow;
select * from onerow where key=1;  /* just to make sure it's cached */
select * from onerow where key=1;
exit;
 
Now look at your trace data. You'll find that the full-table scan of this 
table is both cheaper and faster through the index.
 
The age-old advice from many SQL tuning "experts" is badly wrong when they 
tell you never to index small tables. For applications that execute a lot 
of small-table queries, the performance impact really adds up.
 
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic, Oct 1?3 San Francisco, Oct 15?17 Dallas, Dec 9?11 Honolulu
- 2003 Hotsos Symposium on Oracle® System Performance, Feb 9?12 Dallas
- Next event: Miracle Database Forum, Sep 20?22 Middlefart Denmark
-Original Message-
Sent: Thursday, September 05, 2002 12:19 PM
To: Multiple recipients of list ORACLE-L
 
Not necessarily... Cary's IOUG-A presentation covers this very well. One 
scenario is where the high water mark is set artificially high, and there 
are far more blocks allocated than actually contain data. In this case, a 
FTS will be reading far too many empty blocks.
-Original Message-
Sent: Thursday, September 05, 2002 10:19 AM
To: Multiple recipients of list ORACLE-L
Hello
 
I think that the amount of records you read is also taken into account.
If you run a query that selects ALL the records in the tables
it is ALWAYS more efficient to do full table scan then to access
by index.
 
Yechiel Adar
Mehish
- Original Message - 
To: Multiple 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 QUERY_REWRITE_ENABLED=TRUE;
+ alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
+ alter session set optimizer_mode=FIRST_ROWS;
 
And 
+ can't use IS NULL & IS NOT NULL clause.
+ can'

Re: Function-Based Index not working

[Jan Benjamins]

Hi,

Maybe: NULL values are not indexed, so only way to verify the query
condition is to do a full table scan and filter, however for the same reason
the use of the index would be more logically as that's were all the not null
colums are ??? Beats me


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, September 03, 2002 11:43 PM


>
> There are quite a few restrictions on function-based indexes.
>
> The Oracle SQL guide lists them all.  Have you checked to
> ensure that you're following all the rules?
>
> Jared
>
> On Saturday 31 August 2002 07:53, 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
> >   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:[EMAIL PROTECTED]]
> > Sent: Saturday, August 31, 2002 6:33 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: 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 statistics :-
> >
> > + alter session set QUERY_REWRITE_ENABLED=TRUE;
> > + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> > + alter session set optimizer_mode=FIRST_ROWS;
> > + alter session set DB_FILE_MULTIBLOCK_READ_COUNT=1;
> >
> > This procedure writes 180,000 records in employeees table
> > + execute bulk_insert
> >
> > Analyzing table and rebuilding index (though its not necessary)
> > + analyze table employees compute statistics;
> > + alter index upper_ix rebuild;
> >
> > Making autotrace on
> > + set autotrace traceonly explain
> >
> > Fired the query:
> > SELECT last_name FROM employees WHERE UPPER(last_name) IS NOT NULL
> > ORDER BY UPPER(last_name); Elapsed: 00:00:00.00
> >
> > Execution Plan
> > --
> >0  SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=4001
Bytes=2
> >   0005)
> >
> >10   SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005)
> >21 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=38 Card=4001
By
> >   tes=20005)
> >
> > Any clues what is happening? Should I insert more records in the
table.
> >
> > TIA,
> > Marul.
> >
> >
> >
> >
> >
> >
> >   - 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
> > of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use index.
> >
> >   Set the value of DB_FILE_MULTIBLOCK_READ_COUNT to one.
> >
> >   Insert lots of values in the table. You can make a procedure to
> > insert random characters 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 PM
> >   To: Multiple recipients of list ORACLE-L
> >   Subject: Re: Function-Based Index not working
> >
> >
> > Thanks a lot Naveen,
> >
> > Even after executing the following the execution plan shows full
> > table scan :-
> >
> > + alter session set QUERY_REWRITE_ENABLED=TRUE;
> > + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> > + alter session set optimizer_mode=FIRST_ROWS;
> > + Insert into employees values('A');
> > + Insert into 

RE: Function-Based Index not working

[Cary Millsap]

Even when the high-water mark thing isn’t
a problem, it’s sometimes more efficient to read every row in a table
through an index than via a full-table scan.

 

If you’re curious, try this. Create
a table with two columns, “key” and “value”, and insert
one row with key=1, value=’x’. Create an index on “key”.
Then…

 

alter session set events ‘10046
trace name context forever, level 8’;

select * from onerow;  /* just to
make sure it’s cached */

select * from onerow;

select * from onerow where key=1;  /*
just to make sure it’s cached */

select * from onerow where key=1;

exit;

 

Now look at your trace data. You’ll
find that the full-table scan of this table is both cheaper and faster through
the index.

 

The age-old advice from many SQL tuning “experts”
is badly wrong when they tell you never to index small tables. For applications
that execute a lot of small-table queries, the performance impact really adds
up.

 



Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic, Oct
1–3 San Francisco, Oct 15–17 Dallas, Dec 9–11 Honolulu
- 2003 Hotsos Symposium on
Oracle® System Performance, Feb 9–12 Dallas
- Next event: Miracle Database Forum, Sep
20–22 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 covers this very well. One scenario is where the high water mark
is set artificially high, and there are far more blocks allocated than actually
contain data. In this case, a FTS will be reading far too many empty blocks.





-Original Message-
From: Yechiel Adar
[mailto:[EMAIL PROTECTED]]
Sent: Thursday,
 September 05, 2002 10:19 AM
To: Multiple recipients of list ORACLE-L
Subject: 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 that selects ALL the records in the
tables





it is ALWAYS more efficient to do full table scan then to
access





by index.





 





Yechiel Adar
Mehish







- Original Message - 





From: Marul Mehta 





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 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;





 





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





 





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 statistics :-





 





+ alter session set QUERY_REWRITE_ENABLED=TRUE;
+ alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
+ alter session set optimizer_mode=FIRST_ROWS;
+ alter session set DB_FILE_MULTIBLOCK_READ_COUNT=1;





 





This procedure writes 180,000 records in employeees table





+ execute bulk_insert 





 





Analyzing table and rebuilding index (though its not
necessary)





+ analyze table employees compute statistics;





+ alter index upper_ix rebuild;





Making autotrace on





+ set autotrace traceonly explain





 





Fired the query:





SELECT last_name FROM employees WHERE UPPER(last_name) IS
NOT NULL  ORDER BY UPPER(last_name);
Elapsed: 00:00:00.00





 





Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE
(Cost=57 Card=4001 Bytes=2
  0005)





 





   1    0   SORT (ORDER
BY) (Cost=57 Card=4001 Bytes=20005)
   2    1 TABLE ACCESS (FULL)
OF 'EMPLOYEES' (Cost=38 Card=4001 By
  tes=20005)





 





Any clues what is happening? Should I insert more records in
the table.





 





TIA,





Marul.





 





 





 





 





 





 







- 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 of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT
it will not use index.





 





Set the value of DB_FILE_MULTIBLOCK_REA

RE: Function-Based Index not working

[Fink, Dan]

Not 
necessarily... Cary's IOUG-A presentation covers this very well. One scenario is 
where the high water mark is set artificially high, and there are far more 
blocks allocated than actually contain data. In this case, a FTS will be reading 
far too many empty blocks.

  -Original 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 that selects ALL the records in the 
  tables
  it is ALWAYS more efficient to do full table scan then 
  to access
  by index.
   
  Yechiel AdarMehish
  
- Original Message - 
From: 
Marul Mehta 

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 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;
 
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
  
  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 
  statistics :-
   
  + alter session set 
  QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
  QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
  optimizer_mode=FIRST_ROWS;+ alter session set 
  DB_FILE_MULTIBLOCK_READ_COUNT=1;
   
  This procedure writes 180,000 records in 
  employeees table
  + execute bulk_insert 
   
  Analyzing table and rebuilding index (though 
  its not necessary)
  + analyze table employees compute 
  statistics;
  + alter index upper_ix 
  rebuild;
  Making autotrace on
  + set autotrace traceonly 
explain
   
  Fired the 
query:
  SELECT last_name FROM employees WHERE 
  UPPER(last_name) IS NOT NULL  ORDER BY UPPER(last_name);Elapsed: 
  00:00:00.00
   
  Execution 
  Plan--   
  0  SELECT STATEMENT Optimizer=CHOOSE (Cost=57 
  Card=4001 
  Bytes=2  
  0005)
   
     1    
  0   SORT (ORDER BY) (Cost=57 Card=4001 
  Bytes=20005)   2    1 
  TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=38 Card=4001 
  By  
  tes=20005)
   
  Any clues what is happening? Should I insert 
  more records in the table.
   
  TIA,
  Marul.
   
   
   
   
   
   
  
- 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 of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use 
index.
 
Set the value of DB_FILE_MULTIBLOCK_READ_COUNT to 
one.
 
Insert 
lots of values in the table. You can make a procedure to insert random 
characters 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 not working

  Thanks a lot Naveen,
   
  Even after executing the following the 
  execution plan shows full table scan :-
   
  + alter session set 
  QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
  QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
  optimizer_mode=FIRST_ROWS;
  + Insert into employees 
  values('A');
  
  + Insert into employees 
  values('B');
  + analyze table employees compute statistics;
  + 
      select last_name

Re: Function-Based Index not working

[Yechiel Adar]

Hello
 
I think that the amount of records you read is also taken 
into account.
If you run a query that selects ALL the records in the 
tables
it is ALWAYS more efficient to do full table scan then to 
access
by index.
 
Yechiel AdarMehish

  - Original Message - 
  From: 
  Marul Mehta 
  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 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;
   
  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

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 
statistics :-
 
+ alter session set 
QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
optimizer_mode=FIRST_ROWS;+ alter session set 
DB_FILE_MULTIBLOCK_READ_COUNT=1;
 
This procedure writes 180,000 records in 
employeees table
+ execute bulk_insert 
 
Analyzing table and rebuilding index (though 
its not necessary)
+ analyze table employees compute 
statistics;
+ alter index upper_ix 
rebuild;
Making autotrace on
+ set autotrace traceonly explain
 
Fired the query:
SELECT last_name FROM employees WHERE 
UPPER(last_name) IS NOT NULL  ORDER BY UPPER(last_name);Elapsed: 
00:00:00.00
 
Execution 
Plan--   
0  SELECT STATEMENT Optimizer=CHOOSE (Cost=57 
Card=4001 Bytes=2  
0005)
 
   1    0   
SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005)   
2    1 TABLE ACCESS (FULL) OF 
'EMPLOYEES' (Cost=38 Card=4001 
By  
tes=20005)
 
Any clues what is happening? Should I insert 
more records in the table.
 
TIA,
Marul.
 
 
 
 
 
 

  - 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 of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use 
  index.
   
  Set the value of DB_FILE_MULTIBLOCK_READ_COUNT to 
  one.
   
  Insert lots 
  of values in the table. You can make a procedure to insert random 
  characters 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 not working
  
Thanks a lot Naveen,
 
Even after executing the following the 
execution plan shows full table scan :-
 
+ alter session set 
QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
optimizer_mode=FIRST_ROWS;
+ Insert into employees 
values('A');

+ Insert into employees 
values('B');
+ analyze table employees compute statistics;
+ 
    select last_name   FROM employees 
WHERE UPPER(last_name) IS NOT NULL   ORDER BY 
UPPER(last_name);  2    3Elapsed: 
00:00:00.00
 
Execution 
Plan--   
0  SELECT STATEMENT Optimizer=FIRST_ROWS 
(Cost=3 Card=2 
Bytes=2  
)
 
   1    0   SORT (ORDER BY) 
(Cost=3 Card=2 Bytes=2)   2    
1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 
Card=2 Bytes=  
2)
 
Even after using the hint no change in the plan :-
+ select /* INDEX employees(upper_ix) */ last_name FROM 
employees WHERE UPPER(last_name) IS NOT NULL;
 
Please tell me what else should I do to make this query use the 
 

RE: Function-Based Index not working

[Jamadagni, Rajendra]

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 facts, but having an opinion is an art!


*This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*2

Re: Function-Based Index not working

[Jared Still]

There are quite a few restrictions on function-based indexes.

The Oracle SQL guide lists them all.  Have you checked to
ensure that you're following all the rules?

Jared

On Saturday 31 August 2002 07:53, 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
>   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:[EMAIL PROTECTED]]
> Sent: Saturday, August 31, 2002 6:33 PM
>     To: Multiple recipients of list ORACLE-L
> Subject: 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 statistics :-
>
> + alter session set QUERY_REWRITE_ENABLED=TRUE;
> + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> + alter session set optimizer_mode=FIRST_ROWS;
> + alter session set DB_FILE_MULTIBLOCK_READ_COUNT=1;
>
> This procedure writes 180,000 records in employeees table
> + execute bulk_insert
>
> Analyzing table and rebuilding index (though its not necessary)
> + analyze table employees compute statistics;
> + alter index upper_ix rebuild;
>
> Making autotrace on
> + set autotrace traceonly explain
>
> Fired the query:
> SELECT last_name FROM employees WHERE UPPER(last_name) IS NOT NULL 
> ORDER BY UPPER(last_name); Elapsed: 00:00:00.00
>
> Execution Plan
> --
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=4001 Bytes=2
>   0005)
>
>10   SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005)
>21 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=38 Card=4001 By
>   tes=20005)
>
> Any clues what is happening? Should I insert more records in the table.
>
> TIA,
> Marul.
>
>
>
>
>
>
>   - 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
> of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use index.
>
>   Set the value of DB_FILE_MULTIBLOCK_READ_COUNT to one.
>
>   Insert lots of values in the table. You can make a procedure to
> insert random characters 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 PM
>   To: Multiple recipients of list ORACLE-L
>   Subject: Re: Function-Based Index not working
>
>
> Thanks a lot Naveen,
>
> Even after executing the following the execution plan shows full
> table scan :-
>
> + alter session set QUERY_REWRITE_ENABLED=TRUE;
> + alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
> + alter session set optimizer_mode=FIRST_ROWS;
> + Insert into employees values('A');
> + Insert into employees values('B');
> + analyze table employees compute statistics;
> +
> select last_name
>FROM employees WHERE UPPER(last_name) IS NOT NULL
>ORDER BY UPPER(last_name);  23
> Elapsed: 00:00:00.00
>
> Execution Plan
> --
>0  SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=2
> Bytes=2 )
>
>10   SORT (ORDER BY) (Cost=3 Card=2 Bytes=2)
>21 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=2
> Bytes= 2)
>
> Even after using the hint no change in the plan :-
> + select /* INDEX employees(upper_ix) */ last_name FROM employees
> WHERE UPPER(last_name) IS NOT NULL;
>
>
> Please tell me what else should I do to make this query use the
> index which is created.
>
>

RE: Function-Based Index not working

[Andrey Bronfin]

Re: Function-Based Index not working

[Steve Perry]

I think Oracle's optimizer 
 assumes columns have values and are not null.
Other than bitmap indexes, nulls are 
not stored in the index.
the optimizer probably decided it's 
quicker to do a full table scan to check for not null than it is 
to use the index.
or the table small enough that the 
cost of a FTS is low.
besides the "not null" kind of 
invalidates the "upper" function doesn't it???
 
try 
changing the statement to force a 
hard parse (changing case) 
alter session set events '10053 trace 
name context forever, level 1'/
run the sql again and check the 
trace file.
 
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 such a simple problem, but unfortunately, for me all the tips and 
  tricks are not solving the problem.
  Now these are my current 
  statistics :-
   
  + alter session set 
  QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
  QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
  optimizer_mode=FIRST_ROWS;+ alter session set 
  DB_FILE_MULTIBLOCK_READ_COUNT=1;
   
  This procedure writes 180,000 records in 
  employeees table
  + execute bulk_insert 
   
  Analyzing table and rebuilding index (though its 
  not necessary)
  + analyze table employees compute 
  statistics;
  + alter index upper_ix rebuild;
  Making autotrace on
  + set autotrace traceonly explain
   
  Fired the query:
  SELECT last_name FROM employees WHERE 
  UPPER(last_name) IS NOT NULL  ORDER BY UPPER(last_name);Elapsed: 
  00:00:00.00
   
  Execution 
  Plan--   
  0  SELECT STATEMENT Optimizer=CHOOSE (Cost=57 
  Card=4001 Bytes=2  
  0005)
   
     1    0   
  SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005)   
  2    1 TABLE ACCESS (FULL) OF 
  'EMPLOYEES' (Cost=38 Card=4001 
  By  
  tes=20005)
   
  Any clues what is happening? Should I insert more 
  records in the table.
   
  TIA,
  Marul.
   
   
   
   
   
   
  
- 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 
of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use 
index.
 
Set the value of DB_FILE_MULTIBLOCK_READ_COUNT to 
one.
 
Insert lots 
of values in the table. You can make a procedure to insert random characters 
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 not working

  Thanks a lot Naveen,
   
  Even after executing the following the 
  execution plan shows full table scan :-
   
  + alter session set 
  QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
  QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
  optimizer_mode=FIRST_ROWS;
  + Insert into employees 
  values('A');
  
  + Insert into employees 
  values('B');
  + analyze table employees compute statistics;
  + 
      select last_name   FROM employees 
  WHERE UPPER(last_name) IS NOT NULL   ORDER BY 
  UPPER(last_name);  2    3Elapsed: 
00:00:00.00
   
  Execution 
  Plan--   
  0  SELECT STATEMENT Optimizer=FIRST_ROWS 
  (Cost=3 Card=2 
  Bytes=2  )
   
     1    0   SORT (ORDER BY) 
  (Cost=3 Card=2 Bytes=2)   2    
  1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 
  Card=2 Bytes=  
  2)
   
  Even after using the hint no change in the plan :-
  + select /* INDEX employees(upper_ix) */ last_name FROM 
  employees WHERE UPPER(last_name) 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 Index 
not working

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
 

Re: Function-Based Index not working

[Steve Perry]

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 ORACLE-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 me.
   
  1. SQL> create table employees  
  (last_name varchar2(20));
      Table created.
   
  2. SQL> CREATE INDEX upper_ix ON employees 
  (UPPER(last_name));
      Index created.
   
  Made the autotrace on and than:-
   
  3. SELECT last_name FROM employees WHERE 
  UPPER(last_name) IS NOT NULL  ORDER BY UPPER(last_name);
      no rows selected.
   
  Execution 
  Plan--   
  0  SELECT STATEMENT 
  Optimizer=CHOOSE   1    0   SORT (ORDER 
  BY)   2    1 TABLE ACCESS 
  (FULL) OF 'EMPLOYEES'
   
   
  I fired without order by clause also but no 
  use.
   
  Now can any body please let tell me why this 
  Oracle is having a full scan of the employee table.
   
  TIA,
  Marul.
   
      
   
  

Re: Function-Based Index not working

[Marul Mehta]

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: 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:[EMAIL 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 and 
tricks are not solving the problem.
Now these are my current 
statistics :-
 
+ alter session set 
QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
optimizer_mode=FIRST_ROWS;+ alter session set 
DB_FILE_MULTIBLOCK_READ_COUNT=1;
 
This procedure writes 180,000 records in 
employeees table
+ execute bulk_insert 
 
Analyzing table and rebuilding index (though 
its not necessary)
+ analyze table employees compute 
statistics;
+ alter index upper_ix 
rebuild;
Making autotrace on
+ set autotrace traceonly explain
 
Fired the query:
SELECT last_name FROM employees WHERE 
UPPER(last_name) IS NOT NULL  ORDER BY UPPER(last_name);Elapsed: 
00:00:00.00
 
Execution 
Plan--   
0  SELECT STATEMENT Optimizer=CHOOSE (Cost=57 
Card=4001 Bytes=2  
0005)
 
   1    0   
SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005)   
2    1 TABLE ACCESS (FULL) OF 
'EMPLOYEES' (Cost=38 Card=4001 
By  
tes=20005)
 
Any clues what is happening? Should I insert 
more records in the table.
 
TIA,
Marul.
 
 
 
 
 
 

  - 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 of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use 
  index.
   
  Set the value of DB_FILE_MULTIBLOCK_READ_COUNT to 
  one.
   
  Insert lots 
  of values in the table. You can make a procedure to insert random 
  characters 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 not working
  
Thanks a lot Naveen,
 
Even after executing the following the 
execution plan shows full table scan :-
 
+ alter session set 
QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
optimizer_mode=FIRST_ROWS;
+ Insert into employees 
values('A');

+ Insert into employees 
values('B');
+ analyze table employees compute statistics;
+ 
    select last_name   FROM employees 
WHERE UPPER(last_name) IS NOT NULL   ORDER BY 
UPPER(last_name);  2    3Elapsed: 
00:00:00.00
 
Execution 
Plan--   
0  SELECT STATEMENT Optimizer=FIRST_ROWS 
(Cost=3 Card=2 
Bytes=2  
)
 
   1    0   SORT (ORDER BY) 
(Cost=3 Card=2 Bytes=2)   2    
1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 
Card=2 Bytes=  
2)
 
Even after using the hint no change in the plan :-
+ select /* INDEX employees(upper_ix) */ last_name FROM 
employees WHERE UPPER(last_name) 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 Index 
  not working
  
 

Re: Function-Based Index not working

[Marul Mehta]

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 
optimizer_mode=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
  
  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 
  statistics :-
   
  + alter session set 
  QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
  QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
  optimizer_mode=FIRST_ROWS;+ alter session set 
  DB_FILE_MULTIBLOCK_READ_COUNT=1;
   
  This procedure writes 180,000 records in 
  employeees table
  + execute bulk_insert 
   
  Analyzing table and rebuilding index (though its 
  not necessary)
  + analyze table employees compute 
  statistics;
  + alter index upper_ix rebuild;
  Making autotrace on
  + set autotrace traceonly explain
   
  Fired the query:
  SELECT last_name FROM employees WHERE 
  UPPER(last_name) IS NOT NULL  ORDER BY UPPER(last_name);Elapsed: 
  00:00:00.00
   
  Execution 
  Plan--   
  0  SELECT STATEMENT Optimizer=CHOOSE (Cost=57 
  Card=4001 Bytes=2  
  0005)
   
     1    0   
  SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005)   
  2    1 TABLE ACCESS (FULL) OF 
  'EMPLOYEES' (Cost=38 Card=4001 
  By  
  tes=20005)
   
  Any clues what is happening? Should I insert more 
  records in the table.
   
  TIA,
  Marul.
   
   
   
   
   
   
  
- 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 
of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use 
index.
 
Set the value of DB_FILE_MULTIBLOCK_READ_COUNT to 
one.
 
Insert lots 
of values in the table. You can make a procedure to insert random characters 
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 not working

  Thanks a lot Naveen,
   
  Even after executing the following the 
  execution plan shows full table scan :-
   
  + alter session set 
  QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
  QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
  optimizer_mode=FIRST_ROWS;
  + Insert into employees 
  values('A');
  
  + Insert into employees 
  values('B');
  + analyze table employees compute statistics;
  + 
      select last_name   FROM employees 
  WHERE UPPER(last_name) IS NOT NULL   ORDER BY 
  UPPER(last_name);  2    3Elapsed: 
00:00:00.00
   
  Execution 
  Plan--   
  0  SELECT STATEMENT Optimizer=FIRST_ROWS 
  (Cost=3 Card=2 
  Bytes=2  )
   
     1    0   SORT (ORDER BY) 
  (Cost=3 Card=2 Bytes=2)   2    
  1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 
  Card=2 Bytes=  
  2)
   
  Even after using the hint no change in the plan :-
  + select /* INDEX employees(upper_ix) */ last_name FROM 
  employees WHERE UPPER(last_name) 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 Index 
not working

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 
scan. e.g. You will not use the INDEX if your book has only one 

RE: Function-Based Index not working

[Naveen Nahata]

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:[EMAIL 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 and 
  tricks are not solving the problem.
  Now these are my current 
  statistics :-
   
  + alter session set 
  QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
  QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
  optimizer_mode=FIRST_ROWS;+ alter session set 
  DB_FILE_MULTIBLOCK_READ_COUNT=1;
   
  This procedure writes 180,000 records in 
  employeees table
  + execute bulk_insert 
   
  Analyzing table and rebuilding index (though its 
  not necessary)
  + analyze table employees compute 
  statistics;
  + alter index upper_ix rebuild;
  Making autotrace on
  + set autotrace traceonly explain
   
  Fired the query:
  SELECT last_name FROM employees WHERE 
  UPPER(last_name) IS NOT NULL  ORDER BY UPPER(last_name);Elapsed: 
  00:00:00.00
   
  Execution 
  Plan--   
  0  SELECT STATEMENT Optimizer=CHOOSE (Cost=57 
  Card=4001 Bytes=2  
  0005)
   
     1    0   
  SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005)   
  2    1 TABLE ACCESS (FULL) OF 
  'EMPLOYEES' (Cost=38 Card=4001 
  By  
  tes=20005)
   
  Any clues what is happening? Should I insert more 
  records in the table.
   
  TIA,
  Marul.
   
   
   
   
   
   
  
- 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 
of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use 
index.
 
Set the value of DB_FILE_MULTIBLOCK_READ_COUNT to 
one.
 
Insert lots 
of values in the table. You can make a procedure to insert random characters 
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 not working

  Thanks a lot Naveen,
   
  Even after executing the following the 
  execution plan shows full table scan :-
   
  + alter session set 
  QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
  QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
  optimizer_mode=FIRST_ROWS;
  + Insert into employees 
  values('A');
  
  + Insert into employees 
  values('B');
  + analyze table employees compute statistics;
  + 
      select last_name   FROM employees 
  WHERE UPPER(last_name) IS NOT NULL   ORDER BY 
  UPPER(last_name);  2    3Elapsed: 
00:00:00.00
   
  Execution 
  Plan--   
  0  SELECT STATEMENT Optimizer=FIRST_ROWS 
  (Cost=3 Card=2 
  Bytes=2  )
   
     1    0   SORT (ORDER BY) 
  (Cost=3 Card=2 Bytes=2)   2    
  1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 
  Card=2 Bytes=  
  2)
   
  Even after using the hint no change in the plan :-
  + select /* INDEX employees(upper_ix) */ last_name FROM 
  employees WHERE UPPER(last_name) 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 Index 
not working

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 
scan. e.g. You will not use the INDEX if your book has only one 
page.
 
The decision of going for a full tablescan is based on 
DB_BLOCK_SIZE * DB_FILE_MULTI_BLOCK_READCOUNT, which tells how much data 
Oracle fetches at one time. If your entire table c

RE: Function-Based Index not working

[Seefelt, Beth]

 
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.
 
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 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 employees 
(UPPER(last_name));
    Index created.
 
Made the autotrace on and than:-
 
3. SELECT last_name FROM employees WHERE 
UPPER(last_name) IS NOT NULL  ORDER BY UPPER(last_name);
    no rows selected.
 
Execution 
Plan--   
0  SELECT STATEMENT 
Optimizer=CHOOSE   1    0   SORT (ORDER 
BY)   2    1 TABLE ACCESS 
(FULL) OF 'EMPLOYEES'
 
 
I fired without order by clause also but no 
use.
 
Now can any body please let tell me why this Oracle 
is having a full scan of the employee table.
 
TIA,
Marul.
 
    
 

Re: Function-Based Index not working

[Marul Mehta]

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 
statistics :-
 
+ alter session set 
QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
optimizer_mode=FIRST_ROWS;+ alter session set 
DB_FILE_MULTIBLOCK_READ_COUNT=1;
 
This procedure writes 180,000 records in employeees 
table
+ execute bulk_insert 
 
Analyzing table and rebuilding index (though its 
not necessary)
+ analyze table employees compute 
statistics;
+ alter index upper_ix rebuild;
Making autotrace on
+ set autotrace traceonly explain
 
Fired the query:
SELECT last_name FROM employees WHERE 
UPPER(last_name) IS NOT NULL  ORDER BY UPPER(last_name);Elapsed: 
00:00:00.00
 
Execution 
Plan--   
0  SELECT STATEMENT Optimizer=CHOOSE (Cost=57 
Card=4001 Bytes=2  
0005)
 
   1    0   SORT 
(ORDER BY) (Cost=57 Card=4001 Bytes=20005)   2    
1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=38 Card=4001 
By  
tes=20005)
 
Any clues what is happening? Should I insert more 
records in the table.
 
TIA,
Marul.
 
 
 
 
 
 

  - 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 of 
  DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use 
  index.
   
  Set 
  the value of DB_FILE_MULTIBLOCK_READ_COUNT to one.
   
  Insert lots of values in 
  the table. You can make a procedure to insert random characters 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 not working
  
Thanks a lot Naveen,
 
Even after executing the following the 
execution plan shows full table scan :-
 
+ alter session set 
QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
optimizer_mode=FIRST_ROWS;
+ Insert into employees 
values('A');

+ Insert into employees 
values('B');
+ analyze table employees compute statistics;
+ 
    select last_name   FROM employees 
WHERE UPPER(last_name) IS NOT NULL   ORDER BY 
UPPER(last_name);  2    3Elapsed: 00:00:00.00
 
Execution 
Plan--   
0  SELECT STATEMENT Optimizer=FIRST_ROWS 
(Cost=3 Card=2 
Bytes=2  )
 
   1    0   SORT (ORDER BY) (Cost=3 
Card=2 Bytes=2)   2    
1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=2 
Bytes=  2)
 
Even after using the hint no change in the plan :-
+ select /* INDEX employees(upper_ix) */ last_name FROM employees 
WHERE UPPER(last_name) 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 Index not 
  working
  
  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 
  scan. e.g. You will not use the INDEX if your book has only one 
  page.
   
  The decision of going for a full tablescan is based on 
  DB_BLOCK_SIZE * DB_FILE_MULTI_BLOCK_READCOUNT, which tells how much data 
  Oracle fetches at one time. If your entire table can be fetched in atleast 
  2 fetches, full table 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 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 

RE: Function-Based Index not working

[Naveen Nahata]

See 
the table's size is very small. Till it atleast 2 times the value of 
DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use 
index.
 
Set 
the value of DB_FILE_MULTIBLOCK_READ_COUNT to one.
 
Insert lots of values in the 
table. You can make a procedure to insert random characters 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 not working

  Thanks a lot Naveen,
   
  Even after executing the following the execution 
  plan shows full table scan :-
   
  + alter session set 
  QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
  QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
  optimizer_mode=FIRST_ROWS;
  + Insert into employees values('A');
  
  + Insert into employees values('B');
  + analyze table employees compute statistics;
  + 
      select last_name   FROM employees WHERE 
  UPPER(last_name) IS NOT NULL   ORDER BY UPPER(last_name);  
  2    3Elapsed: 00:00:00.00
   
  Execution 
  Plan--   
  0  SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 
  Card=2 Bytes=2  
  )
   
     1    0   SORT (ORDER BY) (Cost=3 
  Card=2 Bytes=2)   2    1 
  TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=2 
  Bytes=  2)
   
  Even after using the hint no change in the plan :-
  + select /* INDEX employees(upper_ix) */ last_name FROM employees 
  WHERE UPPER(last_name) 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 Index not 
working

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 scan. e.g. You will 
not use the INDEX if your book has only one page.
 
The decision of going for a full tablescan is based on DB_BLOCK_SIZE 
* DB_FILE_MULTI_BLOCK_READCOUNT, which tells how much data Oracle fetches at 
one time. If your entire table can be fetched in atleast 2 fetches, full 
table 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 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 employees 
  (UPPER(last_name));
      Index 
created.
   
  Made the autotrace on and than:-
   
  3. SELECT last_name FROM employees WHERE 
  UPPER(last_name) IS NOT NULL  ORDER BY UPPER(last_name);
      no rows 
  selected.
   
  Execution 
  Plan--   
  0  SELECT STATEMENT 
  Optimizer=CHOOSE   1    0   SORT 
  (ORDER BY)   2    1 
  TABLE ACCESS (FULL) OF 'EMPLOYEES'
   
   
  I fired without order by clause also but no 
  use.
   
  Now can any body please let tell me why this 
  Oracle is having a full scan of the employee table.
   
  TIA,
  Marul.
   
      
   
  

Re: Function-Based Index not working

[Marul Mehta]

Thanks a lot Naveen,
 
Even after executing the following the execution 
plan shows full table scan :-
 
+ alter session set 
QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
optimizer_mode=FIRST_ROWS;
+ Insert into employees values('A');

+ Insert into employees values('B');
+ analyze table employees compute statistics;
+ 
    select last_name   FROM employees WHERE 
UPPER(last_name) IS NOT NULL   ORDER BY UPPER(last_name);  
2    3Elapsed: 00:00:00.00
 
Execution 
Plan--   
0  SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 
Card=2 Bytes=2  )
 
   1    0   SORT (ORDER BY) (Cost=3 
Card=2 Bytes=2)   2    1 
TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=2 
Bytes=  2)
 
Even after using the hint no change in the plan :-
+ select /* INDEX employees(upper_ix) */ last_name FROM employees 
WHERE UPPER(last_name) 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 Index not 
  working
  
  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 scan. e.g. You will 
  not use the INDEX if your book has only one page.
   
  The 
  decision of going for a full tablescan is based on DB_BLOCK_SIZE * 
  DB_FILE_MULTI_BLOCK_READCOUNT, which tells how much data Oracle fetches at one 
  time. If your entire table can be fetched in atleast 2 fetches, full table 
  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 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 employees 
(UPPER(last_name));
    Index created.
 
Made the autotrace on and than:-
 
3. SELECT last_name FROM employees WHERE 
UPPER(last_name) IS NOT NULL  ORDER BY UPPER(last_name);
    no rows 
selected.
 
Execution 
Plan--   
0  SELECT STATEMENT 
Optimizer=CHOOSE   1    0   SORT 
(ORDER BY)   2    1 
TABLE ACCESS (FULL) OF 'EMPLOYEES'
 
 
I fired without order by clause also but no 
use.
 
Now can any body please let tell me why this 
Oracle is having a full scan of the employee table.
 
TIA,
Marul.
 
    
 

RE: Function-Based Index not working

[Sandeep Kurliye]

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

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 employees (UPPER(last_name));
Index created.

Made the autotrace on and than:-

3. SELECT last_name FROM employees WHERE UPPER(last_name) IS NOT NULL  ORDER BY 
UPPER(last_name);
no rows selected.

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE
   10   SORT (ORDER BY)
   21 TABLE ACCESS (FULL) OF 'EMPLOYEES'


I fired without order by clause also but no use.
 
Now can any body please let tell me why this Oracle is having a full scan of the 
employee table.

TIA,
Marul.










<>

RE: Function-Based Index not working

[Naveen Nahata]

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 scan. e.g. You will not use 
the INDEX if your book has only one page.
 
The 
decision of going for a full tablescan is based on DB_BLOCK_SIZE * 
DB_FILE_MULTI_BLOCK_READCOUNT, which tells how much data Oracle fetches at one 
time. If your entire table can be fetched in atleast 2 fetches, full table 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 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 employees 
  (UPPER(last_name));
      Index created.
   
  Made the autotrace on and than:-
   
  3. SELECT last_name FROM employees WHERE 
  UPPER(last_name) IS NOT NULL  ORDER BY UPPER(last_name);
      no rows selected.
   
  Execution 
  Plan--   
  0  SELECT STATEMENT 
  Optimizer=CHOOSE   1    0   SORT (ORDER 
  BY)   2    1 TABLE ACCESS 
  (FULL) OF 'EMPLOYEES'
   
   
  I fired without order by clause also but no 
  use.
   
  Now can any body please let tell me why this 
  Oracle is having a full scan of the employee table.
   
  TIA,
  Marul.