Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17684

CONNECT: How does connection to Oracle database work?

    XMLWordPrintable

Details

    Description

      Good afternoon. I use the Connect Storage Engine to connect the MariaDB database to Oracle using ODBC.
      There was one problem, or it's just a feature of the connection, namely the Oracle database has a lot of tables, and for convenience.
      I created Views that makes a selection of many tables. This view produces about 400 000 rows as a result, and a query without a filter

      SELECT * FROM my_views
      

      is processed for about 7 seconds. If I add additional filter type

      SELECT * FROM MY_VIEWS WHERE TITLE LIKE '%WORD%'
      

      this query would run about 300-500 milliseconds.
      All fast enough.

      Next, I create a connection through the Connect Engine, such as this

      CREATE TABLE my_views_from_oracle (
        id int(10),
        title varchar(255),
        companyname varchar(255),
        contacttitle varchar(255),
        address varchar(255),
        city varchar(255),
        region varchar(255),
        postalcode varchar(255),
        country varchar(255),
        phone varchar(255),
        fax varchar(255)
       
      engine=connect table_type=ODBC tabname='my_views' Connection='DSN="...';
      

      And when I'm on the side already MariaDB trying to make a query

      SELECT * FROM my_views_from_oracle
      

      Then the query is executed about 30 seconds, if I add the same filter, the result is the same

      The feeling that MariaDB connects to Oracle, and at first simply takes absolutely all result, i.e. it does not transfer the Oracle request (i.e. does not ask to filter it), and when to it Oracle gives all result (i.e. all 400 000 lines) only then already MariaDB on the party begins to filter on my requirements.
      The question is, if this is how it works, is it possible to delegate the selection to Oracle? So that MariaDB has already come to the finished result

      It seems in the documentation that something like this was written, but my English is not very good, and there is no doubt

      Attachments

        Activity

          People

            Unassigned Unassigned
            strelkovandreyvalerievich Strelkov Andrey
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.