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