Re: SQL performance on index?

On 11 Jun 2006 00:51:28 -0700, "James Yang" <James.YangGang_at_gmail.com> wrote:

>We found an expensive SQL on our SAP R/3 system:
>
> SELECT
> "AUFPL" , "OBJNR" , "APLFL"
> FROM
> "AFVC"
> WHERE
> "MANDT" = :A0 AND "ARBID" IN ( :A1 , :A2 )#
>
>AUFPL is key for table AFVC. ARBID is defined as an index already.
>OBJNR & APLFL are not defined in any index. Field MANDY & ARBID are
>low-cardinality, but AUFPL is high-cardinality.
>
>If I create a new index which include all those fields (MANDT, ARBID,
>AUFPL, OBJNR, APLFL), will it improve the performance?
>
>B-Tree index or bitmap index is better?
>
>Or, any other good suggestion?
>
>P.S. Storage is not a question for us.
>
>TIA
>James

You provide insufficient information to work on, you would always need to post platform and Oracle version, and in cases like this definition of the table and it's indexes.
Then it is unclear what you are trying to improve. You seem to be a victim of the belief 'full table scans are always bad and *every* query *must* be resolved by using *whatever* index'. This is a myth, at least if you are using CBO.
If MANDT and ARBID are low cardinality a full table scan may result in less I/O than any index range scan.
However, creating an index on all columns in the query will probably not improve performance.
If you continue this strategy for all queries on that table you will end up with
- optimizer choosing the wrong index
- bad INSERT and UPDATE performance and extra redo log because all (redundant) indexes need to be maintained.

--
Sybrand Bakker, Senior Oracle DBA