Why reading from some table in Oracle slower than other table in the same database-CodePudding

I am doing a simple Select col1, col2, col22 from Table1 order by col1 and the same Select statement in Table2. Select col1, col2, col22 from Table2 order by col1.

I use Pentaho ETL tool to replicate data from Oracle 19c to SQL Server. Reading from Table1 is much much slower than reading from Table2. Both have almost the same number for columns and almost the same number for rows. Both exist in the same schema. Table1 is being read at 10 rows per sec while Table2 is being read at 1000 rows a sec.

What can cause this slowness?

CodePudding user response：

Are the indexes the same on the two tables? It's possible Oracle is using a fast full index scan (like a skinny version of the table) if an index covers all the relevant columns in one table, or may be using a full index scan to pre-sort by COL1. Check the execution plans to make sure the statements are using the same access methods:

explain plan for select ...;
select * from table(dbms_xplan.display);

Are the table segment sizes the same? Although the data could be the same, occasionally a table can have a lot of wasted space. For example, if the table used to contain a billion rows, and then 99.9% of the rows were deleted, but the table was never rebuilt. Compare the segment sizes with a query like this:

select segment_name, sum(bytes)/1024/1024 mb
from all_segments
where segment_name in ('TABLE1', 'TABLE2')

CodePudding user response：

It depends on many factors. The first things I would check are the table indexes:

select
   uic.table_name, 
   uic.index_name,
   utc.column_name 
from USER_TAB_COLUMNS UTC, 
     USER_IND_COLUMNS UIC
where utc.table_name  = uic.table_name
  and utc.column_name = uic.column_name
  and utc.table_name in ('TABLE1', 'TABLE2')
order by 1, 2, 3;