Giter Club home page Giter Club logo

Comments (4)

laurenz avatar laurenz commented on August 14, 2024

I have had complaints about slow Oracle dictionary queries before, see #584. But you figured that problem out.

In principle, the query in Oracle could be restricted like you suggest. But there is a difficulty that is caused by the fact that Oracle folds identifiers to upper case, while PostgreSQL folds them to lower case. By default, oracle_fdw tries to be smart about converting names (see the case option to IMPORT FOREIGN SCHEMA). I cannot think of a good way to translate this to Oracle - or at least in the default case of case = 'smart'. So I don't restrict the Oracle query and let PostgreSQL filter the translated table names.

from oracle_fdw.

joaostorrer avatar joaostorrer commented on August 14, 2024

Perhaps concatenating the tables listed in the limit to clause and passing as a bind variable (tables_filters) like 'table_a, table_b' to a query like this could work:

SELECT col.table_name, col.column_name, col.data_type, col.data_type_owner,
       col.char_length, col.data_precision, col.data_scale, col.nullable,
       CASE WHEN primkey_col.position IS NOT NULL THEN 1 ELSE 0 END AS primary_key
FROM all_tab_columns col,
     (SELECT con.table_name, cons_col.column_name, cons_col.position
      FROM all_constraints con, all_cons_columns cons_col
      WHERE con.owner = cons_col.owner AND con.table_name = cons_col.table_name
        AND con.constraint_name = cons_col.constraint_name
        AND con.constraint_type = 'P' AND con.owner = :nsp) primkey_col
WHERE col.table_name = primkey_col.table_name(+) AND col.column_name = primkey_col.column_name(+)
  AND col.owner = :nsp
  AND UPPER(col.table_name) in (SELECT  regexp_substr(UPPER(:table_filters), '[^,]+', 1, LEVEL)
                                FROM    dual
                                CONNECT BY regexp_substr(UPPER(:table_filters), '[^,]+', 1, LEVEL) IS NOT NULL)
ORDER BY col.table_name, col.column_id

from oracle_fdw.

laurenz avatar laurenz commented on August 14, 2024

I implemented support for this.
Could you test if the latest code works for you?

from oracle_fdw.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.