Database SQL CTE tables: Why are they good for you

A common table expression (CTE), known also as WITH clause, is not a newbie to both SQL Server and Oracle relational databases and is part of ANSI SQL 99.

Microsoft MSDN has a nice definition for CTE:  a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

CTE has many neat usages: Creating recursive queries very easily, Replacing views and temporary tables when the execution scope is limited to one statement, Referencing the resulting table multiple times in the same statement (thus saving expensive table access), Improved Readability and more.

Despite all that, many SQL developers and DBAs do not use it often. One possible explanation is that there are many alternatives to achieve similar functionalities: views, inline views, temporary tables, derived tables, procedural language etc.  Most alternatives will have a similar performance or even execution plan. So why use it?

In the below example, I will try to demonstrate a simple use case of CTE that serves two goals:

Readability of the SQL statement and Reusing the same result set more than one time for enhancing I/O performance.

 

CTE for Elegancy

Query description:

We have a table with generic structure to hold some software module UI configuration in a form of entity list. Each row contains id identifying the entity, String columns: Key and Value to hold attributes of the entities and their values and a String column holding additional information. Each entity id appears one or more times in the table depending on the amount of attributes inserted per each.

 

This is a typical Result set:

CTE post.png

 

The administrator managing the module configuration would like to display configuration data sorted and filtered by entities.

For example, to produce the above result set, he would like to get all UI entities that meet these conditions:

  • Type “report field”, of data type String, with field size smaller than 1000 and only those which have no attributes of “dimension” Or “persistent” set to true.
  • Type “report text box”, of data type String, with field size larger than 1000 and only those which have no attributes of “dimension” Or “persistent” set to true.

The following are two possible ways to populate the query for satisfying the administrator filter:

 

The query in traditional SQL statement with semi joins for each filter:

select * from entity_tree where en_id in
(
	SELECT en_id FROM entity_tree M 
	where m.en_key='type' and m.en_value='report field'
	and exists
(select 1 from entity_tree m1 where m.en_id=m1.en_id and m1.en_key='name' and m1.en_type='java.lang.String')
	and exists 
(select 1 from entity_tree m3 where m.en_id=m3.en_id and m3.en_key='field_precision' and cast(m3.en_value as int)<1000)
	and not exists 
(select 1 from entity_tree m4 where m.en_id=m4.en_id and m4.en_key='dimension' and m4.en_value='true')
	and not exists 
(select 1 from entity_tree m5 where m.en_id=m5.en_id and m5.en_key='persistent' and m5.en_value='true')
	union all
	SELECT en_id FROM entity_tree M 
	where m.en_key='type' and m.en_value='report text box'
	and exists
(select 1 from entity_tree m1 where m.en_id=m1.en_id and m1.en_key='name' and m1.en_type='java.lang.String')
	and exists 
(select 1 from entity_tree m3 where m.en_id=m3.en_id and m3.en_key='field_precision' and cast(m3.en_value as int) >1000 )
	and not exists 
(select 1 from entity_tree m4 where m.en_id=m4.en_id and m4.en_key='dimension' and m4.en_value='true')
	and not exists 
(select 1 from entity_tree m5 where m.en_id=m5.en_id and m5.en_key='persistent' and m5.en_value='true')
 )
order by en_id

 As you can see the query is implemented as a set of semi joins and anti semi joins per each of attributes participating in the filter. Since each attribute of an entity is a separate row there is no escape from several accesses to the same table/index. I tried to make the query more readable and also benefit from reusing the same result set, in this example all CTE tables are accessed twice in the query.

 

The query using CTE for filtering multiple times:

The CTE declaration:

 

WITH 
  m1 
  AS
  (
    SELECT en_id
    FROM entity_tree
    WHERE en_key='name' and en_type='java.lang.String'
      ),
m2 
  AS
  (
    SELECT en_id, en_value
    FROM entity_tree
    WHERE en_key='field_precision' 
      ),
m3 
  AS
  (
    SELECT en_id
    FROM entity_tree
    WHERE en_key='dimension' and en_value='true'
      ),
m4 
  AS
  (
    SELECT en_id
    FROM entity_tree
    WHERE en_key='persistent' and en_value='true' )

The Query declaration:

 

select * from entity_tree where entity_tree.en_id in
(
  SELECT m.en_id FROM entity_tree M join m1 on m.en_id=m1.en_id
  join m2 on m.en_id=m2.en_id and  cast(m2.en_value as int)<1000
  where m.en_key='type' and m.en_value='report field'
  and not exists 
  (select 1 from m3 where m.en_id=m3.en_id)
  and not exists 
  (select 1 from m4 where m.en_id=m4.en_id)
  union all
  SELECT m.en_id FROM entity_tree M join m1 on m.en_id=m1.en_id
  join m2 on m.en_id=m2.en_id and  cast(m2.en_value as int)>1000
  where m.en_key='type' and m.en_value='report text box'
  and not exists
  (select 1 from m3 where m.en_id=m3.en_id)
  and not exists 
  (select 1 from m4 where m.en_id=m4.en_id)
 )
order by entity_tree.en_id

 

The query using the CTE tables is much leaner and readable. All 4 CTE result sets can be accessed once thus saving redundant additional table access.

 

Benefit will grow when more repeating conditions are added; requiring long joins and semi joins in the traditional query and causing redundant I/O operations.

 

The post is written by Sharon Dashet (Expert Oracle DBA).

 

 

 

 

Labels: Databases
Comments
eli revach(anon) | ‎07-03-2013 07:08 AM

Very impressive ! .

The issue  was presents  in a very professional   way .

Leave a Comment

We encourage you to share your comments on this post. Comments are moderated and will be reviewed
and posted as promptly as possible during regular business hours

To ensure your comment is published, be sure to follow the Community Guidelines.

Be sure to enter a unique name. You can't reuse a name that's already in use.
Be sure to enter a unique email address. You can't reuse an email address that's already in use.
Type the characters you see in the picture above.Type the words you hear.
Search
Showing results for 
Search instead for 
Do you mean 
About the Author
Seasoned architect with over 12 years of experience in the enterprise software business, contributing to setting the roadmap / vision, high ...
Featured


Follow Us
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.