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.
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
> 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
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;
On Wed, 2023-09-13 at 01:58 -0400, Pete O'Such wrote:
> For a view, how does one show what schema was used to qualify a relation, when
> the query used to create the view originally left the relation unqualified?
>
> The qualification of the view query seems static in all uses of the view.
>
