Simple Plans

CommentsProcedural SQL (PSQL) Statements

Simple Plans

The simplest plans consist of a relation name followed by a retrieval method.For example, for an unsorted single-table select without a WHERE clause:

select * from students
  plan (students natural);

Advanced plan:

Select Expression
  -> Table "STUDENTS" Full Scan

If there’s a WHERE or a HAVING clause, you can specify the index to be used for finding matches:

select * from students
  where class = '3C'
  plan (students index (ix_stud_class));

Advanced plan:

Select Expression
  -> Filter
    -> Table "STUDENTS" Access By ID
      -> Bitmap
        -> Index "IX_STUD_CLASS" Range Scan (full match)

The INDEX directive is also used for join conditions (to be discussed a little later).It can contain a list of indexes, separated by commas.

ORDER specifies the index for sorting the set if an ORDER BY or GROUP BY clause is present:

select * from students
  plan (students order pk_students)
  order by id;

Advanced plan:

Select Expression
  -> Table "STUDENTS" Access By ID
    -> Index "PK_STUDENTS" Full Scan

ORDER and INDEX can be combined:

select * from students
  where class >= '3'
  plan (students order pk_students index (ix_stud_class))
  order by id;

Advanced plan:

Select Expression
  -> Filter
    -> Table "STUDENTS" Access By ID
      -> Index "PK_STUDENTS" Full Scan
        -> Bitmap
          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

It is perfectly OK if ORDER and INDEX specify the same index:

select * from students
  where class >= '3'
  plan (students order ix_stud_class index (ix_stud_class))
  order by class;

Advanced plan:

Select Expression
  -> Filter
    -> Table "STUDENTS" Access By ID
      -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)
        -> Bitmap
          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

For sorting sets when there’s no usable index available (or if you want to suppress its use), leave out ORDER and prepend the plan expression with SORT:

select * from students
  plan sort (students natural)
  order by name;

Advanced plan:

Select Expression
  -> Sort (record length: 128, key length: 56)
    -> Table "STUDENTS" Full Scan

Or when an index is used for the search:

select * from students
  where class >= '3'
  plan sort (students index (ix_stud_class))
  order by name;

Advanced plan:

elect Expression
  -> Sort (record length: 136, key length: 56)
    -> Filter
      -> Table "STUDENTS" Access By ID
        -> Bitmap
          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Notice that SORT, unlike ORDER, is outside the parentheses.This reflects the fact that the data rows are retrieved unordered and sorted afterward by the engine.

When selecting from a view, specify the view and the table involved.For instance, if you have a view FRESHMEN that selects the first-year students:

select * from freshmen
  plan (freshmen students natural);

Advanced plan:

Select Expression
  -> Table "STUDENTS" as "FRESHMEN" Full Scan

Or, for instance:

select * from freshmen
  where id > 10
  plan sort (freshmen students index (pk_students))
  order by name desc;

Advanced plan:

Select Expression
  -> Sort (record length: 144, key length: 24)
    -> Filter
      -> Table "STUDENTS" as "FRESHMEN" Access By ID
        -> Bitmap
          -> Index "PK_STUDENTS" Range Scan (lower bound: 1/1)

Important

If a table or view has been aliased, it is the alias, not the original name, that must be used in the PLAN clause.

Composite Plans

When a join is made, you can specify the index which is to be used for matching.You must also use the JOIN directive on the two streams in the plan:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  plan join (s natural, c index (pk_classes));

Advanced plan:

Select Expression
  -> Nested Loop Join (inner)
    -> Table "STUDENTS" as "S" Full Scan
    -> Filter
      -> Table "CLASSES" as "C" Access By ID
        -> Bitmap
          -> Index "PK_CLASSES" Unique Scan

The same join, sorted on an indexed column:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  plan join (s order pk_students, c index (pk_classes))
  order by s.id;

Advanced plan:

Select Expression
  -> Nested Loop Join (inner)
    -> Table "STUDENTS" as "S" Access By ID
      -> Index "PK_STUDENTS" Full Scan
    -> Filter
      -> Table "CLASSES" as "C" Access By ID
        -> Bitmap
          -> Index "PK_CLASSES" Unique Scan

And on a non-indexed column:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  plan sort (join (s natural, c index (pk_classes)))
  order by s.name;

Advanced plan:

Select Expression
  -> Sort (record length: 152, key length: 12)
    -> Nested Loop Join (inner)
      -> Table "STUDENTS" as "S" Full Scan
      -> Filter
        -> Table "CLASSES" as "C" Access By ID
          -> Bitmap
            -> Index "PK_CLASSES" Unique Scan

With a search condition added:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  where s.class <= '2'
  plan sort (join (s index (fk_student_class), c index (pk_classes)))
  order by s.name;

Advanced plan:

Select Expression
  -> Sort (record length: 152, key length: 12)
    -> Nested Loop Join (inner)
      -> Filter
        -> Table "STUDENTS" as "S" Access By ID
          -> Bitmap
            -> Index "FK_STUDENT_CLASS" Range Scan (lower bound: 1/1)
      -> Filter
        -> Table "CLASSES" as "C" Access By ID
          -> Bitmap
            -> Index "PK_CLASSES" Unique Scan

As a left outer join:

select s.id, s.name, s.class, c.mentor
  from classes c
  left join students s on c.name = s.class
  where s.class <= '2'
  plan sort (join (c natural, s index (fk_student_class)))
  order by s.name;

Advanced plan:

Select Expression
  -> Sort (record length: 192, key length: 56)
    -> Filter
      -> Nested Loop Join (outer)
        -> Table "CLASSES" as "C" Full Scan
        -> Filter
          -> Table "STUDENTS" as "S" Access By ID
            -> Bitmap
              -> Index "FK_STUDENT_CLASS" Range Scan (full match)

If there are no indices available to match the join condition (or if you don’t want to use it), then it is possible connect the streams using HASH or MERGE method.

To connect using the HASH method in the plan, the HASH directive is used instead of the JOIN directive.In this case, the smaller (secondary) stream is materialized completely into an internal buffer.While reading this secondary stream, a hash function is applied and a pair {hash, pointer to buffer} is written to a hash table.Then the primary stream is read and its hash key is tested against the hash table.

select *
  from students s
  join classes c on c.cookie = s.cookie
  plan hash (c natural, s natural)

Advanced plan:

Select Expression
  -> Filter
    -> Hash Join (inner)
      -> Table "STUDENTS" as "S" Full Scan
      -> Record Buffer (record length: 145)
        -> Table "CLASSES" as "C" Full Scan

For a MERGE join, the plan must first sort both streams on their join column(s) and then merge.This is achieved with the SORT directive (which we’ve already seen) and MERGE instead of JOIN:

select * from students s
  join classes c on c.cookie = s.cookie
  plan merge (sort (c natural), sort (s natural));

Adding an ORDER BY clause means the result of the merge must also be sorted:

select * from students s
  join classes c on c.cookie = s.cookie
  plan sort (merge (sort (c natural), sort (s natural)))
  order by c.name, s.id;

Finally, we add a search condition on two indexable columns of table STUDENTS:

select * from students s
  join classes c on c.cookie = s.cookie
  where s.id < 10 and s.class <= '2'
  plan sort (merge (sort (c natural),
                    sort (s index (pk_students, fk_student_class))))
  order by c.name, s.id;

As follows from the formal syntax definition, JOINs and MERGEs in the plan may combine more than two streams.Also, every plan expression may be used as a plan item in an encompassing plan.This means that plans of certain complicated queries may have various nesting levels.

Finally, instead of MERGE you may also write SORT MERGE.As this makes no difference and may create confusion with “real” SORT directives (the ones that do make a difference), it’s best to stick to plain MERGE.

In addition to the plan for the main query, you can specify a plan for each subquery.For example, the following query with multiple plans will work:

select *
from color
where exists (
  select *
  from hors
  where horse.code_color = color.code_color
  plan (horse index (fk_horse_color)))
plan (color natural)

Warning

Occasionally, the optimizer will accept a plan and then not follow it, even though it does not reject it as invalid.One such example was

MERGE (unsorted stream, unsorted stream)

It is advisable to treat such as plan as “deprecated”.