LogMiner Query performance could be improved with better table matching [DBZ-7544]

Migrated from [DBZ-7544](https://issues.redhat.com/browse/DBZ-7544)

In order to make your issue reports as actionable as possible, please provide the following information, depending on the issue type.
h1. Bug report

For bug reports, provide this information, please:
h2. What Debezium connector do you use and what version?

Oracle Debezium Connector version 2.4
h2. What is the connector configuration?

connector.class: io.debezium.connector.oracle.OracleConnector
database.url: jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=broken)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=IP_1)(PORT=PORT_1))(ADDRESS=(PROTOCOL=TCP)(HOST=IP_2)(PORT=PORT_2)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SERVICE_NAME)))
database.user: dbzuser
database.password: password
database.dbname: SERVICE_NAME
topic.prefix: staging
tasks.max: 1
schema.include.list: SCHEMA_NAME
table.include.list: SCHEMA_NAME.TABLE_NAME
log.mining.strategy: online_catalog
snapshot.mode: schema_only
snapshot.include.collection.list: SERVICE_NAME.SCHEMA_NAME.TABLE_NAME
key.converter.schemas.enable: false
value.converter.schemas.enable: false
schema.history.internal.store.only.captured.tables.ddl: true
schema.history.internal.store.only.captured.databases.ddl: true
schema.history.internal.kafka.topic: staging.history.ddl
schema.history.internal.kafka.recovery.poll.interval.ms: 60000
schema.history.internal.kafka.query.timeout.ms: 60000
schema.history.internal.kafka.create.timeout.ms: 60000
schema.history.internal.kafka.bootstrap.servers: server:port
schema.history.internal.consumer.bootstrap.servers: server:port
schema.history.internal.consumer.security.protocol: SASL_SSL
schema.history.internal.consumer.sasl.mechanism: PLAIN
schema.history.internal.consumer.ssl.endpoint.identification.algorithm: https
schema.history.internal.consumer.sasl.jaas.config: org.apache.kafka.common.security.plain.PlainLoginModule required username="usr" password="pwd";
schema.history.internal.producer.security.protocol: SASL_SSL
schema.history.internal.producer.bootstrap.servers: server:port
schema.history.internal.producer.sasl.mechanism: PLAIN
schema.history.internal.producer.ssl.endpoint.identification.algorithm: https
schema.history.internal.producer.sasl.jaas.config: org.apache.kafka.common.security.plain.PlainLoginModule required username="usr" password="pwd";
errors.log.enable: true
errors.log.include.messages: true
event.processing.failure.handling.mode: warn
log.mining.transaction.retention.ms: 600000
log.mining.query.filter.mode: in
log.mining.archive.log.hours: 20
h2. What is the captured database version and mode of depoyment?

(E.g. on-premises, with a specific cloud provider, etc.)

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

Kafka Connect 3.6.0 installed as standalone on on-premise VM

Debezium Oracle Connector version 2.4
h2. What behaviour do you expect?

When the property "log.mining.query.filter.mode" is set to "in", we expect the Debezium Connector to retrieve from the REDOLOG (V$LOGMNR_CONTENTS view) filtered on the specific tables that we are monitoring, but instead this is not happening and a lot of useless data is retrieved from the logs. More info below.
h2. What behaviour do you see?

Basically we noticed that the query that the connector does on the Oracle REDOLOG table retrieves a lot more data that actually concerns the table that we are listening on.
We extracted the query from the database logs:
{code:java}
SELECT
SCN,
SQL_REDO,
OPERATION_CODE,
TIMESTAMP,
XID,
CSF,
TABLE_NAME,
SEG_OWNER,
OPERATION,
USERNAME,
ROW_ID,
ROLLBACK,
RS_ID,
STATUS,
INFO,
SSN,
THREAD#
FROM V$LOGMNR_CONTENTS
WHERE SCN > :1 AND SCN <= :2
AND (OPERATION_CODE IN (1,2,3,6,7,34,36,255) OR (OPERATION_CODE = 5 AND INFO NOT LIKE 'INTERNAL DDL%'))
AND (SEG_OWNER IS NULL OR SEG_OWNER NOT IN ('APPQOSSYS','AUDSYS','CTXSYS','DVSYS','DBSFWUSER','DBSNMP','GSMADMIN_INTERNAL','LBACSYS','MDSYS','OJVMSYS','OLAPSYS','ORDDATA','ORDSYS','OUTLN','SYS','SYSTEM','WMSYS','XDB')); {code}
And noticed that there is no filter applied regarding the table name. So we did some research on your documentation and we found this connector property: log.mining.query.filter.mode
It's default value is "none", which results in no filtering, so we valued instead with "in".
After doing so, the above query changed like this:
{code:java}
SELECT
SCN,
SQL_REDO,
OPERATION_CODE,
TIMESTAMP,
XID,
CSF,
TABLE_NAME,
SEG_OWNER,
OPERATION,
USERNAME,
ROW_ID,
ROLLBACK,
RS_ID,
STATUS,
INFO,
SSN,
THREAD#
FROM V$LOGMNR_CONTENTS
WHERE SCN > :1 AND SCN <= :2
AND (OPERATION_CODE IN (1,2,3,6,7,34,36,255) OR (OPERATION_CODE = 5 AND INFO NOT LIKE 'INTERNAL DDL%'))
AND (SEG_OWNER IS NULL OR UPPER(SEG_OWNER) IN ('UNKNOWN','SCHEMA_NAME'))
AND (TABLE_NAME IS NULL OR TABLE_NAME LIKE 'OBJ#%' OR UPPER(SEG_OWNER || '.' || TABLE_NAME) IN ('SCHEMA_NAME.TABLE_NAME')) {code}
Where "SCHEMA_NAME.TABLE_NAME" is the name of the table we are monitoring.
This query unfortunately has 2 issues that make it useless:
# The column "TABLE_NAME" of the V$LOGMNR_CONTENTS does not contain the names of the tables, it contains instead a codification of the table in the form "OBJ# number". Because of this, the IN clause at the end of the above query will never be true. We found out that the actual name of the table can be decodified using the "DBA_OBJECTS" table in JOIN with the V$LOGMNR_CONTENTS view.
# In the last AND condition of the query (last line above), before the "UPPER(SEG_OWNER || '.' || TABLE_NAME) IN ('SCHEMA_NAME.TABLE_NAME')" part, there are other 2 conditions in OR, among which there is "TABLE_NAME LIKE 'OBJ#%' ". This makes completely useless any following filter on the tablename, as the condition will be always true, since any table is identified on the V$LOGMNR_CONTENTS view with TABLENAME LIKE "OBJ# number".

Because of this, even with the log.mining.query.filter.mode set to "in", the query is downloading a lot of useless data from the REDOLOGS.
We did some test to put an actual number on it, and on our database the query is downloading around 3 billion records where actually the records related to our monitored table are only in the order of the hundreds.
This caused a big load on our infrastructure in terms of network usage and DB CPU usage.

We even went as far as talking to Confluent support about this, and they conveyed this looks like a bug on Debezium side.
h2. Do you see the same behaviour using the latest relesead Debezium version?

(Ideally, also verify with latest Alpha/Beta/CR version)

Not tested
h2. Do you have the connector logs, ideally from start till finish?

(You might be asked later to provide [DEBUG/TRACE|https://debezium.io/documentation/reference/stable/operations/logging.html] level log)

We can retrieve it if needed
h2. How to reproduce the issue using our [tutorial|https://github.com/debezium/debezium-examples/tree/main/tutorial] deployment?

 
h1. Feature request or enhancement

For feature requests or enhancements, provide this information, please:
h2. Which use case/requirement will be addressed by the proposed feature?

h2. Implementation ideas (optional)