1. Building Semantic Models in Oracle Analytics Cloud
  2. Create and Build Your Model
  3. Build a Semantic Model's Physical Layer
  4. Use Hints in SQL Statements

Use Hints in SQL Statements

This topic provides information about how to add SQL hints to physical tables and physical joins.

Topics:

About Hints in SQL Statements

Hints are instructions that you add to a SQL statement that tell the data source query optimizer the most efficient way to run the statement.

Hints override the optimizer's processing plan, so you can use hints to improve performance by forcing the optimizer to use a more efficient plan. Hints are only supported for Oracle Database data sources.

You can add hints to a physical table or a join expression. When the object associated with the hint is queried, the Oracle Analytics query engine inserts the hint into the SQL statement.

For physical tables, SQL hints you specify for tables with the source type Table are supported, but SQL hints that you specify for tables with the source type Stored Procedure or Select Statement are ignored. For tables with the source type of Select Statement, you can provide the hint text as part of the SQL statement you enter in the DEFAULT field.

About the Index Hint

An Index hint explains how the optimizer scans a specified index rather than a table.

See Oracle hints in the SQL reference guide for the version of the Oracle Database that you use.

This is the syntax for the Index hint:

index(table_name,index_name)

For example, suppose queries against the ORDER_ITEMS table are slow and you've reviewed the processing plan of the query optimizer and discovered that the FAST_INDEX wasn't used. You can create an Index hint to force the optimizer to scan the FAST_INDEX rather than the ORDER_ITEMS table.

For this example, you add this syntax to the physical table's SQL Hints properties field: 
index(ORDER_ITEMS, FAST_INDEX)

About the Leading Hint

A Leading hint forces the optimizer to build the join order of a query with a specific table.

See Oracle hints in the SQL reference guide for the version of the Oracle Database that you use.

This is the syntax for the Leading hint:

leading(table_name)

For example, suppose you have a join between the Sales Fact table and the Products table and want to force the optimizer to begin the join with the Products table.

For this example, you add this syntax to the physical join's Include Hint field: 
leading(Products)

Performance Considerations for SQL Statement Hints

Well researched and planned SQL statement hints can result in significantly better query performance. However, hints can also negatively affect performance if they result in a suboptimal processing plan.

Follow these guidelines to create hints to optimize query performance:

  • Only add hints to a semantic model after you've tried to improve performance in the following ways:

    • Adding physical indexes or other physical changes to the Oracle Database.

    • Making modeling changes within the server.

  • Avoid creating hints for physical table and join objects that are queried often. If you drop or rename a physical object that's associated with a hint, you must also update the hints accordingly.

Create Physical Table Hints

You can add SQL hints to a physical table. You can't add hints to an alias table, but only to its source table.

Although hints are identified using SQL comment markers (/* or --), don't type SQL comment markers when you type the text of the hint. The Oracle Analytics query engine inserts the comment markers when the hint is run.

For a description of available Oracle hints and hint syntax, see SQL reference for the version of the Oracle Database that you use.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Physical Layer.
  4. In the database pane, browse for and double-click the table where you want to add a SQL hint.
  5. Click the General tab, and in the SQL Hints field enter the SQL hint.
  6. Click Save.

Create Physical Join Hints

You can add SQL hints to a physical join in an alias table.

Although hints are identified using SQL comment markers (/* or --), don't type SQL comment markers when you type the text of the hint. The Oracle Analytics query engine inserts the comment markers when the hint is run.

For a description of available Oracle hints and hint syntax, see SQL reference for the version of the Oracle Database that you use.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Physical Layer.
  4. Hover over a table and right-click. Select Show Physical Diagram and the diagram type you want to work from.
  5. Double-click a join.
  6. In Edit Physical Join, click the Include Hint field and enter the SQL hint.
  7. Click Save.