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

[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

[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

[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

[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 lastI got the 
  function-based index working properly.
  This is whatI noticed :-
  Have to alter session/system for :-
  + alter session set 
  QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
  QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
  optimizer_mode=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_COUNTto 
  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

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 lastI got the 
function-based index working properly.
This is whatI noticed :-
Have to alter session/system for 
:-
+ alter session set 
QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
optimizer_mode=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_COUNTto 
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

RE: Function-Based Index not working

[Cary Millsap]

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



If youre 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 its cached */

select * from onerow;

select * from onerow where key=1; /*
just to make sure its cached */

select * from onerow where key=1;

exit;



Now look at your trace data. Youll
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
13 San Francisco, Oct 1517 Dallas, Dec 911 Honolulu
- 2003 Hotsos Symposium on
Oracle System Performance, Feb 912 Dallas
- Next event: Miracle Database Forum, Sep
2022 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 lastI got the
function-based index working properly.





This is whatI noticed :-





Have to alter session/system for :-





+ alter session set QUERY_REWRITE_ENABLED=TRUE;
+ alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
+ alter session set optimizer_mode=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_COUNTto
one.











Insert lots of values in the table. You
can make a procedure to insert random characters into the table, and then put

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 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.
 
 
  TIA,
  Marul

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

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 http://www.hotsos.com/training/clinic , Oct 1-3 San
 Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu
 - 2003 Hotsos Symposium http://www.hotsos.com/events/symposium  on
 OracleR System Performance, Feb 9-12 Dallas
 - Next event: Miracle Database Forum http://www.miracleas.dk , 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 mailto:[EMAIL PROTECTED]  recipients of list
 ORACLE-L 
 
 Sent: Saturday, August 31, 2002 4:23 PM
 
 
  
 
 Hi All,
 
  
 
 Thanks a lot to you all. At last I got the function-based index
 working
 properly.
 
 This is what I noticed :-
 
 Have 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 mailto:[EMAIL PROTECTED]  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
-- 
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 

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

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 http://www.hotsos.com/training/clinic , Oct 1-3 San
 Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu
 - 2003 Hotsos Symposium http://www.hotsos.com/events/symposium  on
 OracleR System Performance, Feb 9-12 Dallas
 - Next event: Miracle Database Forum http://www.miracleas.dk , 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 mailto:[EMAIL PROTECTED]  recipients of list
 ORACLE-L 
 
 Sent: Saturday, August 31, 2002 4:23 PM
 
 
  
 
 Hi All,
 
  
 
 Thanks a lot to you all. At last I got the function-based index
 working
 properly.
 
 This is what I noticed :-
 
 Have 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 mailto:[EMAIL PROTECTED]  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
 

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 http://www.hotsos.com/training/clinic , Oct 1-3
San
 Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu
 - 2003 Hotsos Symposium http://www.hotsos.com/events/symposium  on
 OracleR System Performance, Feb 9-12 Dallas
 - Next event: Miracle Database Forum http://www.miracleas.dk , 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 mailto:[EMAIL PROTECTED]  recipients of list
 ORACLE-L 
 
 Sent: Saturday, August 31, 2002 4:23 PM
 
 
  
 
 Hi All,
 
  
 
 Thanks a lot to you all. At last I got the function-based index
 working
 properly.
 
 This is what I noticed :-
 
 Have 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 mailto:[EMAIL PROTECTED]  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 
-- 
Please see the 

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
 http://www.hotsos.com/training/clinic , Oct 1-3
 San
 Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu
 - 2003 Hotsos Symposium
 http://www.hotsos.com/events/symposium  on
 OracleR System Performance, Feb 9-12 Dallas
 - Next event: Miracle Database Forum
 http://www.miracleas.dk , 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 mailto:[EMAIL PROTECTED] 
 recipients of list ORACLE-L 
 
 Sent: Saturday, August 31, 2002 4:23 PM
 
 
  
 
 Hi All,
 
  
 
 Thanks a lot to you all. At last I got the
 function-based index working
 properly.
 
 This is what I noticed :-
 
 Have 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 mailto:[EMAIL PROTECTED] 
 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 mailto:[EMAIL PROTECTED] 
 recipients of list ORACLE-L 
 
 Sent: Saturday, August 31, 2002 4:58 PM
 
 
  
 
 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 

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

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 http://www.hotsos.com/training/clinic , Oct 1-3

 San

  Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu
  - 2003 Hotsos Symposium http://www.hotsos.com/events/symposium  on
  OracleR System Performance, Feb 9-12 Dallas
  - Next event: Miracle Database Forum http://www.miracleas.dk , 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 mailto:[EMAIL PROTECTED]  recipients of list
  ORACLE-L
 
  Sent: Saturday, August 31, 2002 4:23 PM
 
 
 
 
  Hi All,
 
 
 
  Thanks a lot to you all. At last I got the function-based index
  working
  properly.
 
  This is what I noticed :-
 
  Have 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 mailto:[EMAIL PROTECTED]  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) 

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 http://www.hotsos.com/training/clinic , Oct 1-3
 
  San
 
   Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu
   - 2003 Hotsos Symposium http://www.hotsos.com/events/symposium 
 on
   OracleR System Performance, Feb 9-12 Dallas
   - Next event: Miracle Database Forum http://www.miracleas.dk ,
 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 mailto:[EMAIL PROTECTED]  recipients of list
   ORACLE-L
  
   Sent: Saturday, August 31, 2002 4:23 PM
  
  
  
  
   Hi All,
  
  
  
   Thanks a lot to you all. At last I got the function-based index
   working
   properly.
  
   This is what I noticed :-
  
   Have 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 mailto:[EMAIL PROTECTED]  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: [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 

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?11
Honolulu
 - 2003 Hotsos Symposium on Oracle(r) 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

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.


 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

RE: Function-Based Index not working

[Andrey Bronfin]

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_nameFROM 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.










winmail.dat

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

[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_COUNTto 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_nameFROM 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_COUNTto 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

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

[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_COUNTto 
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

Re: Function-Based Index not working

[Marul Mehta]

Hi All,

Thanks a lot to you all. At lastI got the 
function-based index working properly.
This is whatI noticed :-
Have to alter session/system for :-
+ alter session set 
QUERY_REWRITE_ENABLED=TRUE;+ alter session set 
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set 
optimizer_mode=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_COUNTto 
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

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

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

[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 
tousethe 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 
tracefile.

steve

  - Original Message - 
  From: 
  Marul Mehta 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Saturday, August 31, 2002 8:03 
  AM
  Subject: Re: Function-Based Index not 
  working
  
  Hi Naveen,
  Thanks a lot for the efforts you are putting in 
  for me for 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_COUNTto 
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 on