secondary index

[Former Member]

Hi Experts,

In my report the SELECT statement taking very huge time,if I created a secondary index for that,How can I use that index in my select statement? in coding how can we use that index?

Thanks&Regards,

Sam.

[Former Member]

Hi,

You cannot specify secondary index in your select query. Database Optimizer automatically opts the best index available to fetch the data from data base.

Regards

Abhijeet

[christine_evans]

>

> Hi,
>   You cannot specify secondary index in your select query. Database Optimizer automatically opts the best index available to fetch the data from data base.
> 
> Regards
> Abhijeet

Well, you can if you use a database hint. But this is not encouraged because it is database specific. And, a lot of the time anyway, if your stats are up to date the database optimiser does get it right.

[Former Member]

Hi,

use the indexed fields in your where clause: for index usage some filter should be applied because the

index read is efficient only if you retieve only a small percentage of rows ( 2- 5%).

The Optimizer will discard the index access if you retrieve lot of rows from the table and will opt for full table scan instead.

Make sure table statistics are up to date.

bye

[rajnarayanan_eangandiyoor]

run the stats and also make sure the order of the fields in the select statement matches as closely as the index order.

rgds

Raj

[Former Member]

Hi Raj,

This is my select statement ,Can you please tellme the order of srcondary index creation.

select * from dfkkop into corresponding fields of table t_dfkkop

where vtref like 'EPC%' and

( ( augbd = '00000000' and

xragl = 'X' )

or

( augbd ge w_clrfr and

augbd le w_clrto ) ) and

augrd ne '03' and

zwage_type in s_wtype.

Thanks&Regards,

sam.

[Former Member]

Hi,

Logically and also technically i can suggest you to create two secondary indexes.

Because you have used two set of fields in your condition. For each of these set of fields the indexes will be reffered.

First secondary index with fields => vtref, augbd, xragl.

Another secondary index with fields => augrd, zwage_type.

Regards,

R.Nagarajan (a) R.Raja.

[christine_evans]

I'd suggest you try to rewrite your SELECT. You've got an OR, NE, LE and GE in there, all of which can be in some situations performance killers. In response to a previous post, you do not need to make the order of fields in the WHERE clause match the order of fields in the index; it won't affect performance.

[Former Member]

Hi,

You can use the secondary index in a select query like below.

TABLES: spfli.

DATA : t_spfli LIKE spfli OCCURS 0 WITH HEADER LINE.

SELECT * FROM spfli

INTO TABLE t_spfli

%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.

LOOP AT t_spfli.

WRITE 😕 t_spfli.

ENDLOOP.

001 is a secondary index for table SPFLI

Thanks,

Naveen Kumar.

[Former Member]

hi,

If u create secondary index for particular field.We cant see the secondary index in table.in where condition in u can use the secondary index field.

Regards,

sankar