-
Originally Posted by hrishy
Hi Parag
Oracle cannot index null values and hence queries with is and is not null cannot use an index.
Hrishy - how would you explain the following:
"Indexing Null Values
Bitmap indexes index nulls, whereas all other index types do not.
"
Found in the propaganda at:
http://download-west.oracle.com/docs..._acc.htm#18779
-
Jeff,
Here is the o/p taht you wanted......
IN_FLAG COUNT(*)
======= ==========
N 123731
Y 2503067
-
Well i read the documnet given in.
http://download-west.oracle.com/doc...a_acc.htm#18779
About indexing nulls. But one query In the example given "is null" condition is niot used in where condition. Then in that case will the bitmap index get used.
Rgds
Parag
-
No i tried it.
i created one table parag1 with one column containing null values created .Table has around 2000000 rows .
created a bitmap index on that column and tried to see the explain plan of following statement
select a from parag1 where b is null;
and explian plan is ....
Q_PLAN
-------------------------------
SELECT STATEMENT
TABLE ACCESS FULL PARAG1
TABLE ACCESS FULL PARAG1
SELECT STATEMENT
TABLE ACCESS FULL PARAG1
TABLE ACCESS FULL PARAG1
So i think i have to go by Hrishy's way. bitmap index wont help in null .
-
I am extremely sorry to give so many replies on my own.
But i thought i need to share this with all.
I tried to give hint in the query like..
select /*+ index (parag1,idx_parag1) +*/ a from parag1 where b is null;
And bingo what comes the explain plan.....
here it is....
Q_PLAN
----------------------------------------------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID PARAG1
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE IDX_PARAG1
BITMAP INDEX SINGLE VALUE IDX_PARAG1
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE IDX_PARAG1
BITMAP INDEX SINGLE VALUE IDX_PARAG1
TABLE ACCESS BY INDEX ROWID PARAG1
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE IDX_PARAG1
Q_PLAN
----------------------------------------------
BITMAP INDEX SINGLE VALUE IDX_PARAG1
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE IDX_PARAG1
BITMAP INDEX SINGLE VALUE IDX_PARAG1
"dbtoo" what you have said is correct..
Once again sorry to give so many replies on my own.....
Rgds
Parag
-
Hi
Regular B tree indexes will not store null entries in oracle.
Bit Map indexes on the other hand will indexe null values.
Parag the idea of using Function Based indexes as suggested by Dapi did not occur to me.I think you could try DApis method too.
regards
Hrishy
-
Hi,
Ya hrushi i will try both offcourse on test env first.
Bitmap index is taking around 25MB . I will create function based
index and lets see which one is resource friendly. As we have 2 solutions now We will choose which is best. But still want to know your opinion what do you think Which one is better Function based index or bitmap index.
Rgds
Parag
-
Hrishi,
checked index is taking more space than expected . In test env
it is taking around 60MB for a table having size 40MB. So i think Bitmap is correct solution....
-
Originally Posted by paragp1981
So i think Bitmap is correct solution....
You didn't say much about the environment - Bitmap indexes are *ahem* not advised for OLTP systems.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Hi Dapi,
Ours is a telecom domain. Though this problem is coming in new server for which we installed new application called FMS(Fraud Mgmt System). This procedure is taking around 40-50% of one CPU out of Dual CPU. Most of the time some scheduled procedure will be running in this. And even if bitmap indexes are not advised. One Rejection procedure is running and doing Full tablescan of table having 20,00000 rows so . I dont think major problem. In any case we will se if this works for some days.
and if found not satisfactory We can always come back to solution given by hrishi.
But still help appreciated. Thanks for all help.
Rgds
Parag
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|