Query Fetch Slow..

[shailesh]

Hi,

We use Cost based optimizer, I have created Bitmap Index on Col1, Col2 as well. But still to fetch query takes time on higherside and doesn't use the Index. Out of 15000, I am fetching 10000 records.

Please refer Query, execution plan and trace below.

SELECT Code, SUBSTR(LTRIM(Title||' '||NVL(Name1,'My Name1,')||''||NVL(Name2,'my Name2')||'; ')
||NVL(Str,'my Str')||';'||NVL(PLZ,'my PLZ')||''||NVL(ORT,'my Ort'),1,254), Verborgen, LOWER(Name1)
FROM TABLE
WHERE Col1 = 'Y'
AND NVL( Col2, 'N' ) = 'N'
ORDER BY Name1;

10443 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=105 Bytes=58
80)

1 0 SORT (ORDER BY) (Cost=48 Card=105 Bytes=5880)
2 1 TABLE ACCESS (FULL) OF 'Table' (Cost=46 Card=1
05 Bytes=5880)


Statistics
----------------------------------------------------------
0 recursive calls
24 db block gets
546 consistent gets
369 physical reads
0 redo size
1530635 bytes sent via SQL*Net to client
77680 bytes received via SQL*Net from client
698 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
--------------------------------------------------------------------

When I tried to force index by using Hint then still situation is not different.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=587 Card=105 Bytes=5
880)

1 0 SORT (ORDER BY) (Cost=587 Card=105 Bytes=5880)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'Table' (Cost
=585 Card=105 Bytes=5880)

3 2 BITMAP CONVERSION (TO ROWIDS)
4 3 BITMAP INDEX (RANGE SCAN) OF 'TRY'




Statistics
----------------------------------------------------------
8 recursive calls
19 db block gets
6673 consistent gets
369 physical reads
0 redo size
1529552 bytes sent via SQL*Net to client
77680 bytes received via SQL*Net from client
698 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)

Please help me to solve this performance issue.

Thanks & Regards,

Shailesh

[Orca777]

Hi;
There is a paradigma that index-access is always faster then full-table-access.
This is NOT right.
For example if you would use the index then Oracle would have to read all index-blocks too ( process the indexdata, access each row thru rowid ) , in addition to the blocks of the table.
The reason is that you access 60% of data.
Oracle sometimes sikps Index-scanning when it has more than 5% of Tabledata to access.

The CBO in this case is right!

BitmapIndexes are very small but the technique is to combine some very less selective columns to reach high selectivity and to have fast access to few rows.
But if you fetch 60% of data even Bitmapps have no advantage.
So this is only a raw path, always check the theory with realtime-examples.

Orca

[Edited by Orca777 on 07-10-2002 at 07:00 AM]

[marist89]

Originally posted by Orca777
paradigma

I like that, kind of like a cross between stigma and paradigm.

[Orca777]

Sorry it's funny but i am not a native english-speaker!
Orca

PS.: But iam very happy tha PL/SQL is based on English and not on ...