-
It seems intuitively correct that if you have a large table having a highly-skewed, indexed, histogram-analyzed column of values "A" and "B", like ...
Code:
select my_col,count(*)
from my_table
group by my_col;
MY_COL COUNT(*)
------ --------
A 99999999
B 1
.. then Oracle would use the index for predicates like ...
MY_COL='B'
... or ...
MY_COL!='A'
... or ...
MY_COL='C'
... and would use FTS for predicates like ...
MY_COL='A'
... or ...
MY_COL!='B'
Unfortunately, I haven't been able to put together a test case to demonstrate it, so maybe theory don't apply here.
-
Actually, rethinking it i bet that the != would be much more likely to use an index in a star transformation, involving bitamp index merges etc.
-
Originally posted by slimdave
Code:
select my_col,count(*)
from my_table
group by my_col;
MY_COL COUNT(*)
------ --------
A 99999999
B 1
.. then Oracle would use the index for predicates like ...
MY_COL='B'
... or ...
MY_COL!='A'
... or ...
MY_COL='C'
... and would use FTS for predicates like ...
MY_COL='A'
... or ...
MY_COL!='B'
I doubt only for that highlighted part...
PS
Code:
TEST:ABHAY> Set trimspool on
TEST:ABHAY> analyze table test compute statistics;
Table analyzed.
TEST:ABHAY> ed
Wrote file afiedt.buf
1 select
2 INDEX_OWNER ,
3 INDEX_NAME ,
4 TABLE_OWNER ,
5 TABLE_NAME ,
6 COLUMN_NAME ,
7 COLUMN_POSITION
8* from dba_ind_columns where index_name='AK_TEST'
TEST:ABHAY> /
INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
COLUMN_NAME
-----------------------------------------------
COLUMN_POSITION
--------------------
ABHAY AK_TEST ABHAY TEST
ID4
1
ABHAY AK_TEST ABHAY TEST
ID2
2
TEST:ABHAY> set autotrace on explain
TEST:ABHAY> alter session set optimizer_mode=ALL_ROWS;
Session altered.
TEST:ABHAY> select ID4, Count(*) from TEST group by ID4;
ID4 COUNT(*)
-------------------- --------------------
1 1099799
2 13
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1673 Card=2 Bytes=
4)
1 0 SORT (GROUP BY) (Cost=1673 Card=2 Bytes=4)
2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=359 Card=1099812 Byt
es=2199624)
TEST:ABHAY> select * from test where id4=2 and id2=1999991;
ID ID1 ID2 ID4 ID5
-------------------- -------------------- -------------------- -------------------- --------------------
1999989 1999990 1999991 2 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=18)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3 Card=1 Byt
es=18)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'AK_TEST'
TEST:ABHAY> select * from test where id4!=1 and id2=1999991;
ID ID1 ID2 ID4 ID5
-------------------- -------------------- -------------------- -------------------- --------------------
1999989 1999990 1999991 2 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=359 Card=1 Bytes=1
8)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=359 Card=1 Bytes=18)
You can see its going for FTS with ID4!=1
TEST:ABHAY> select /*+ INDEX(TEST AK_TEST) */ * from test where id4!=1 and id2=1999991;
ID ID1 ID2 ID4 ID5
-------------------- -------------------- -------------------- -------------------- --------------------
1999989 1999990 1999991 2 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9734 Card=1 Bytes=
18)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=9734 Card=1
Bytes=18)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (FULL SCAN) OF 'AK_TEST'
compare the costs with index scan and without for != condition
!= condition will always call for FULL scans (Table/Index), and oracle feels FULL Table scan is wiser than FULL Index scan, as you know FTS can fetch DFMC blocks in one I/O which FULL index scan dosent..
Ok, only exception will be if Index blocks is much less than Table blocks consumed. I would guess a Table with high number COLS present and indexed on only few COLS, than Index Scan would be wiser.
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
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
|