Hello Sergey.

I'm not sure what you read there. It says the ideal way is "without using Expand Inline".

1. The suggestion is to use a boolean variable and a filter variable to avoid using expand inline. Precisely because expand inline affects indexing (and any optimization).
The alternative to boolean+string would be to use a single string that you would need to escape, expand inline and SQL would need to process. Bad for you, bad for safety, bad for performance.

2. And the boolean+string works in aggregates.

I think you should read again and look at the examples.

Hi Nuno.

>>Precisely because expand inline affects indexing (and any optimization).

Do you even understand how Oracle selects records by index? What is an index in a database and how do I get into it? 

SQL constructs of this type do not use index-based selections, even if such indexes exist and could be used.
FOR EXAMPLE.

table1 contains 10 000 000 records

and field1, field2

once index

CREATE INDEX <name1> ON <table1>("<field1>").

SELECT * FROM <table> WHERE
<table>.<field2> = <foo> AND (@variable = "foo" OR <table>.field1 = <foo>)

Indexed select or not?

It doesn't say anything about optimizing specific examples. Our developers follow these tips and stop the database with full scan samples.

if you add a variable, as advised, then Oracle will no longer use indexes (like uses an index if % is not put at the very beginning)

OR prevents the Oracle from being selected by the index. We have tables with the 30 000 000 - 400 000 000 records. Full scan stopped buisness process on minutes or hours. Only dynamic helps us.

Hi Sergey,

I'm not sure I understand exactly what part of the advice on that page you are having issues with? In general, a database engine will use an index if the column is indexed, unless it's compare with a LIKE and there's a "%" at the start. Both Oracle and MS SQL are pretty good at issuing query plans that make optimal use of indexes.

The only difference between using variables and using hard-coded values is that for hard-coded values, the database engine may have to build a new query plan, whereas it can reuse an existing one if you use variables (though depending on the nature of the variables' contents, that can be benificial or not).

A database engine should only use full table scans if a column isn't indexed, or a LIKE with a starting "%" is used, and there are no other ways to restrict the data. I don't see any advice on that page that would lead to intentionally causing these situations.

I'm really sorry for you if you don't see the problem. :-(

How do I enable the use of indexing and still be able to disable filters? Other than using dynamics, I don't see any other option. But Outsystems advises using OR with variables to disable filters. It's just a bottom of some kind.

Of course, if Outsystems had 100 records in the test spaces in the tables, then why not use it. But in a real enterprise-tables of millions of records and Oracle just really starts to slow down the whole process.

1. How to disable in runtime filter in advanced SQL  in WHERE without variable and OR? 

2. How to disable in runtime filter in aggregate in conditions?

Hello Sergey,

The solution is to use expand inline and use hints to force a specific execution plan, to do that you have to use the Factory configuration forge component and enable Oracle hints.

Please confirm this with your DBA team and let us know if it worked.

Regards,