SQL Query Guidelines

You should use the SQL queries that are used to extract the data carefully.

Follow these guidelines when writing SQL queries for extracting log data:

  • Use read-only queries only.

  • The credentials provided to execute the queries should have only the required privileges to extract the necessary data.

  • The query performance is also an important consideration, because it can affect both the target database and other software running on the same host.

  • The query should include at least one column that can be used to order the database records. This can be either some kind of a sequence number or a time-stamp column. Every new entry should have a value for this column that’s equal to or greater than the one in older records. The SQL query will be run at regular intervals to extract new data. Oracle Logging Analytics will use this column to identify the new records that have been introduced since the previous collection. It’s recommended that this column should have an index to avoid full table scans.

  • Do not use ORDER BY or WHERE clauses on the time or your sequence field, because Oracle Logging Analytics does that for you. The necessary filtering and sorting is applied by Oracle Logging Analytics on the query that you provide. However, you can use the WHERE clause for other fields.