Hint - Cardinality

Synopsis

CARDINALITY (object_name, number) A hint to specify the expected record set size in a specific operation. OBJECT_NAME can be:

A specific table. Contains the expected number of rows in the table. For example:

SELECT /*++ CARDINALITY (o, 100) */ s.name, o.id
FROM staff s JOIN org o
ON s.deptno = o.deptno
WHERE o.division = 'EASTERN';

Here the hint indicates that there are 100 rows in the org table (for the purpose of query optimization). It doesn’t say anything about how many of them have division = ’EASTERN’.

An alias given to a sub-query result. Contains the expected results from it as if it was done without push down/up. The sub-query must be aliased, and the alias name passed as the argument of the hint.

For example:

SELECT /*++ CARDINALITY (east_deptnumb, 10000) */
s.name, east_deptnumb.id
FROM staff s JOIN (SELECT o.deptno ,id
                   FROM org o
                   WHERE o.division = 'EASTERN') AS east_deptnumb
ON s.deptno = east_deptnumb.deptno;

Specifying multiple hints with the same object_name and different numbers is contradictory and is not allowed.

Hint - INDEX CREATE USER

⌘I

SQL Specification

SQL Syntax Conventions

Data Types

SQL Functions and Operators

Data Definition Language (DDL) Commands

Data Manipulation Language Commands

Transaction Control Language Commands

Data Query Language

Hints

Managing Users

System Monitoring

System Management

Appendices

Hint - Cardinality

Synopsis

​Synopsis

Synopsis