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 -----
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); 2 3
Elapsed:
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 -----
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
Hi,
Can you please help me out in solving
this weird problem of funcation-based index not being used when I
query the table.
This is the comand I fired and the
result it returned me.
1. SQL> create table employees
(last_name varchar2(20));
Table
created.
2. SQL> CREATE INDEX upper_ix ON
employees (UPPER(last_name));
Index
created.
Made the autotrace on and
than:-
3. SELECT last_name FROM employees
WHERE UPPER(last_name) IS NOT NULL ORDER BY
UPPER(last_name);
no rows
selected.
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT
Optimizer=CHOOSE
1 0
SORT (ORDER BY)
2
1 TABLE ACCESS (FULL) OF
'EMPLOYEES'
I fired without order by clause also
but no use.
Now can any body please let tell me why
this Oracle is having a full scan of the employee
table.
TIA,
Marul.
