Thanks to all who replied on this issue. I overlooked the difference in the
execution plan between Oracle and Postgres. Oracle in fact does an index range
scan only with no table lookup. When I changed the query to be a count(*)
Postgres went from 332 logical reads to 19. Oracle did 20. Ora
On Wed, 13 Sept 2023 at 17:30, Anthony Apollis
wrote:
> Yes in deed.
> I am trying to make sure that the keys are aligned, but it doesnt update
> or it simply shows NULL in Fact table, meaning its secondary keys.
>
> "-- Step 1: Drop existing foreign key constraint for Entity
> ALTER TABLE system
> PostgreSQL resolves tables and other objects according to the setting of
> "search_path" at CREATE VIEW time. The query itself is stored in parsed
form.
>
> pg_get_viewdef() deparses the query and only prepends a schema name if the
> schema is not on the "search_path". So the solution is to set
Yes in deed.
I am trying to make sure that the keys are aligned, but it doesnt update or
it simply shows NULL in Fact table, meaning its secondary keys.
"-- Step 1: Drop existing foreign key constraint for Entity
ALTER TABLE system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" DROP CONSTRAINT IF EXISTS
fk_entity;
