issue without the key field in select and alternative

[Former Member]

experts

1)in my ztable i have following fields

company code

sales organization

date

time

customer number

po reference

all this are key fields.

I have order number as non key field.

This ztable is used to store the orders , sometimes it stores the records without order number.

Now in my program i want to fetch all the records that dont have the order number.

select * from ztable into table itab

where vbeln eq space.

please tell me how to improve the performance of this query.

2) In another ztable i have two key fields

sales order and date.

But i dont know the date from when to when i need to provide to the select query.

so i am using the sales order number only in the query. Do this effect the performance if i dont give the date? hOW to avoid this

[Former Member]

Hi,

1) use secondary index to improve the performance....

2) just using sales order will not affect the performace in the ztable.....

Regards,

Siddarth

[former_member156446]

try using a join on these two ztable.. and join using vbeln = vbeln

[Former Member]

hi govaabaper,

for your first query:

- try creating a secondary index on the VBELN field.

- or one more thing is "a selection without a where condition fetches records faster than select with a where clause with non key fields."-->so removing the vblen from the where will also help you with performance.

select * from ztable into table gt_table.
if sy_subrc = 0.
  delete table gt_table where vbeln is initial.
endif.

,

for your second query,its ok to have one key field in select.

[Former Member]

thank you all for the reply.

Is it suggestable to create the secondary index on the non key field?

If i create the secondary index , do i need to include some other fields with vbeln or simply one field ie vbeln in secondary index?

here vbeln some records will be filled and some records will be blank,

suppose say in 20 records , 10 will be filled and 10 will be blank vbeln filed.

So do you still suggest me to create a secondary index for vbeln?.

[Former Member]

You can create secondary index for non key fields also... there is no harm in it.... it basically depends on the requirement you want.... on which fields you would be hitting database mostly for....

but for your scenario use one of the key field, then vbeln and date....

i feel that would give you the best performance...

[former_member194613]

> So do you still suggest me to create a secondary index for vbeln?.

no, in all cases where about 10% or more are not filled, you don't need a seondary index, because the database

will anyway use a full table scan.

otherwise secondary keys can have only field.

Siegfried

[former_member182566]

>

> no, in all cases where about 10% or more are not filled, you don't need a seondary index, because the database
> will anyway use a full table scan.

what??

>

> otherwise secondary keys can have only field.

what??

is this some kind of monday morning bug?

[former_member194613]

no, there is no bug.

Every database decides if too many records are expected to use no index but a full table scan,

the percentage is probably determined by a more complicated algorithm, but in general you can

expect, if more that 10% of all records fulfill the condition, then no index is used.

Which means if this is the only use case then the index is not necessary.

> If i create the secondary index , do i need to include some other fields with vbeln or simply one field ie vbeln in secondary index?

Second line is the answer to this question and also correct.

Siegfried

[former_member182566]

>

> Every database decides if too many records are expected to use no index but a full table scan, 
> the percentage is probably determined by a more complicated algorithm, but in general you can
> expect, if more that 10% of all records fulfill the condition, then no index is used.
> Siegfried

Hi Siegfried,

Suppose the table has 1,000 records; 500 of them are for 500 different orders, and 500 of them are empty. Then there are 501 different values for this field, which means it's very selective: the database will estimate 2 rows returned (in general, ie, without histograms and without passing values). Even if the query is "= space", the database will not know the value you are looking for, and will estimate 2 rows returned.

Right?

(about the second answer, I think I misread "can have only one" as "can only have one")

[former_member194613]

yes, but the faster access for the task is the full table scan ... without index.

With index you are right, so the creation of the index is counterproductive in this case.

If different accesses are there and one access is much less selective then the others, then you need the histogram information, but this seems far beyond the original question.

Siegfried

[former_member182566]

yep, ok, good.

I just thought that leaving the answer as "the database will anyway use a full table scan" was not correct and could mislead the original poster and other future readers. In fact, as you say, the index will probably be used, but will probably result in even worse performance.