Index on Table Conversion

andres

Active Member
Hi,

If you create a Table Conversion for copying data from one table (F0911) to another (F55911) and with a data selection, for example GLPOST='P', my question is, will the database use the appropriate index by GLPOST although you are not pointing to it becase you only selected F0911 as Input Table? Does the database have the intelligence to use the index for performance or it will do a full scan?

Regards,
Andrés.
 

Tom_Davidson

VIP Member
Andres,

You need to post your configuration, I'm sure the answer varies by database. For the IBM i the answer is a definite 'maybe'. The SQL optimizer may decide to do something else depending on your memory, CPU, and data. For example if you are selecting over 20% of the records, it will probably do a table scan because the I/O cost will be less than using an index.

Tom
 

jdelisths

Reputable Poster
Hi Andrés,

There should be no difference in running a SQL statement using GLPOST= 'P' and doing the same through JDE. The index in JDE is just a logical representation of the physical index. It has nothing to do with what the database does (others - please correct me if you know otherwise).
 

andres

Active Member
Hi,

Thank you for your answers.

You are right, Tom, I didn't mention in which database. I'm talking in an Oracle database. And about JDE, it's E9.1

About your post, Hari, I don't understand you or I didn't explain myself very well.
I'm doing a Table Conversion UBE that will read records from an Input Table F0911 with data selection GLPOST='P' and will insert them into an Output Table F55911.
My question is, when inherent sql: select * from F0911 where GLPOST='P' runs, does Oracle database optimizer will do a full scan on F0911 or will it use a GLPOST index for better performance?

Regards,
Andrés.
 

jdelisths

Reputable Poster
Hi Andrés,


Sorry, I misunderstood your question. You can use EXPLAIN PLAN to do this yourself:


explain plan for
select * from proddta.f0911 where glpost = 'P';


select plan_table_output
from table(dbms_xplan.display('plan_table',null,'basic'));


On our database, we get:

-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| F0911 |
| 2 | INDEX RANGE SCAN | F0911_6 |
-----------------------------------------------



So, it will use the index for this SQL statement. Hope this helps.
 

andres

Active Member
Hi Hari,

Oh, I can see it, thank you very much. Yes it helps a lot.
So the database optimizer uses the index if it considers that when running the sql sentence, perfect.

Regards,
Andrés.
 

Tom_Davidson

VIP Member
Andres,

Just an idle question, does it matter HOW the database performs the table conversion, just so it does it in an efficient manner?

Tom
 

andres

Active Member
Hi Tom,

JDE Table Conversion is going to select all records with data selection GLPOST = 'P' from F0911 and then insert them into F550911.
If database wouldn't use an index by GLPOST, bad performance will happen for sure, isn't it right?

Regards,
Andrés.
 

Larry_Jones

Legendary Poster
Andres,
"If database wouldn't use an index by GLPOST, bad performance will happen for sure, isn't it right?"

No. Particularly since the vast majority of the rows in F0911 will have GLPOST = 'P'. A Full Table scan will always be faster in such a situation than one using an index.
 

andres

Active Member
Hi Larry,

Yes, you are right about that, my case was not exactly the real one, that is GLPOST = 'P' AND GLCFF1 <> 'Y'. I think in this case it is important the use of an index by GLPOST and GLCFF1.

Thanks anyway.
Regards,
Andrés.
 

Tom_Davidson

VIP Member
Andres,

This is why I wondered if you cared what method the optimizer used. After all that's why we have optimizers, and I know the people that write them are a lot smarter at that than I am.

Tom
 
Top