CODEWITHSUNDEEP
sqloracle-databaseperformance
user5992977
user5992977

Reputation:

Two very alike select statements different performance

I've just came across some weird performance differences.

I have two selects:

SELECT s.dwh_end_date,
       t.*,
       '-1' as PROMOTION_DROP_EMP_CODE,
       trunc(sysdate +1) as PROMOTION_END_DATE,
      'K01' as PROMOTION_DROP_REASON,
       -1 as PROMOTION_DROP_WO_NUMBER
FROM STG_PROMO_EXPIRE_DATE t
INNER JOIN fct_customer_services s
 ON(t.dwh_product_key = s.dwh_product_key)

Which takes approximately 20 seconds.

And this one:

SELECT s.dwh_end_date,
       s.dwh_product_key,
       s.promotion_expire_date,
       s.PROMOTION_DROP_EMP_CODE,
       s.PROMOTION_END_DATE,
       s.PROMOTION_DROP_REASON,
       s.PROMOTION_DROP_WO_NUMBER
FROM STG_PROMO_EXPIRE_DATE t
INNER JOIN fct_customer_services s
 ON(t.dwh_product_key = s.dwh_product_key)

That takes approximately 400 seconds

They are basically the same - its just to assure that I've updated my data correct (first select is to update the FCT tables) second select to make sure every thing updated correctly.

The only differences between this two selects, is the columns I select. (STG table has two columns - dwh_p_key and prom_expire_date)

First select explain plan

Second select explain plan

What can cause this weird behaviour?..

The FCT tables is indexed UNIQUE (dwh_product_key, dwh_end_date) and partitioned by dwh_end_date (250 million records), the STG doesn't have any indexes (and its only 15k records)

Thanks in advance.

Upvotes: 0

Views: 70

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

The plans are not exactly the same. The first query uses a fast full scan of the index on fct_customer_services and doesn't need to access any blocks from the actual table, since you only refer to the two indexed columns.

The second query does have to access the table blocks to get the other unidexed column values. It's doing a full table scan - slower and more expensive than a full index scan. The optimiser doesn't see any improvement from using the index and then accessing specific table rows, presumably because the cardinality is too high - it needs to access too many table rows to save any effort by hitting the index first. Doing so would be even slower.

So the second query is slower because it has to read the whole table from disk/cache rather than just the whole index, and the table is much larger than the index. You can look at the segments assigned to both objects (index and table) to see the ratio of their sizes.

Upvotes: 3

Related Questions