Edit Question - KnowledgeHub

Title Body

I often encounter the following situation in my Oracle execution plans: Operation | Object | Order | Rows | Bytes | Projection ----------------------------+---------+-------+------+-------+------------- TABLE ACCESS BY INDEX ROWID | PROD | 7 | 2M | 28M | PROD.VALUE INDEX UNIQUE SCAN | PROD_PK | 6 | 1 | | PROD.ROWID This is an extract from a larger execution plan. Essentially, I'm accessing (joining) a table using the table's primary key. Typically, there is another table ACCO with ACCO.PROD_ID = PROD.ID , where PROD_PK is the primary key on PROD.ID . Obviously, the table can be accessed using a UNIQUE SCAN , but as soon as I have some silly projection on that table, it seems as though the whole table (around 2 million rows) is planned to be read in memory. I get a lot of I/O and buffer gets. When I remove the projection from the greater query, the problem disappears: Operation | Object | Order | Rows | Bytes | Projection ----------------------------+---------+-------+------+-------+------------- TABLE ACCESS BY INDEX ROWID | PROD | 7 | 1 | 8 | PROD.ID INDEX UNIQUE SCAN | PROD_PK | 6 | 1 | | PROD.ROWID I don't understand this behaviour. What could be the reasons for this? Note, I cannot post the complete query. It is rather complex and involves a lot of calculations. The pattern, however, is often the same. UPDATE : I maganged to bring down my rather complex setup to a simple simulation that produces a similar execution plan in both cases (when projecting PROD.VALUE or when leaving it away): Create the following database: -- products have a value create table prod as select level as id, 10 as value from dual connect by level < 100000; alter table prod add constrai

Tags (comma-separated) Cancel