Oracle query speed after CTAS

Question

I had a large table where I needed to delete millions of rows. It would take an awfully long time, so I created a new one via CTAS without the records I wanted to delete. Over this table I created the same indexes and foreign keys as on the original, then deleted the original table. I had the statistics recalculated. Everything was done quickly and without problems. However, working with the table is strangely slower now. FORALL delete of records on the primary key takes longer (still index fast full scan), even though the execution plan is the same. What to do about it?

Answer 1

Two common ways a CTAS may slow down a table is by missing a crucial table property like compression, or loading the table in a different order that reduces index performance.

Compression

Are you sure you perfectly recreated the new table? Perhaps you forgot an important table property, like compression. The below example demonstrates how a property like compression is not automatically copied over to the new table:

-- Create a compressed table.
create table compressed compress as
select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' columnA
from dual
connect by level <= 100000;

-- CTAS but forgot the COMPRESS keyword.
create table uncompressed /* forgot compress! */ as
select * from compressed;

-- The uncompressed table is 3 times larger, which may slow down future DML.
select segment_name, bytes
from dba_segments
where segment_name in ('COMPRESSED', 'UNCOMPRESSED')
order by 1;


SEGMENT_NAME   BYTES
------------   -------
COMPRESSED     2097152
UNCOMPRESSED   6291456

You can check the table properties with a query like SELECT * FROM DBA_TABLES, or you can get the entire table DDL with a query like select dbms_metadata.get_ddl('TABLE', 'TABLE_NAME') from dual;

Clustering Factor

Even if the data structures and data are identical, the order the data was physically loaded can have a performance impact. For example, indexes can read from the table more efficiently when the relevant columns are inserted in order. This happens because each index entry contains a pointer to a block of data from the table, which is usually 8KB. Since indexes are usually read in order, it's a lot more efficient if ordered values are stored next to each other. If the table values are completely disordered, you may end up with a situation where reading 1% of the values from an index requires pulling 100% of the blocks from the table.

--Create a well-ordered table and index.
create table ordered_data as
select level columnA
from dual
connect by level <= 100000
order by level;

create index ordered_data_idx on ordered_data(columnA);

--Create a randomly ordered table and index.
create table unordered_data as
select level columnA
from dual
connect by level <= 100000
order by dbms_random.value;

create index unordered_data_idx on unordered_data(columnA);

The clustering factor is a measure of how efficient the index is, which is related to how ordered the data is. The below query shows that ordering data can make a huge impact.

select index_name, clustering_factor
from dba_indexes
where index_name in ('ORDERED_DATA_IDX', 'UNORDERED_DATA_IDX');


INDEX_NAME           CLUSTERING_FACTOR
------------------   -----------------
ORDERED_DATA_IDX       153
UNORDERED_DATA_IDX   99357

Fixes

You can either recreate your table or alter it to solve compression:

alter table uncompressed compress;
alter table uncompressed move;

For ordering data, there are some advanced features like clustering, but it might be simpler to just redo the CTAS and add an ORDER BY clause if necessary.