{"id":2708,"date":"2026-08-19T16:09:58","date_gmt":"2026-08-19T14:09:58","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=2708"},"modified":"2026-08-19T16:10:04","modified_gmt":"2026-08-19T14:10:04","slug":"oracle-26ai-a-closer-look-at-join-to-one","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/oracle-26ai-a-closer-look-at-join-to-one\/","title":{"rendered":"Oracle 26ai: A Closer Look at JOIN TO ONE"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">With Oracle AI Database 23.26.2, Oracle introduced the new <code>JOIN TO ONE<\/code> syntax. As it happened, just a few weeks later, I was teaching an Oracle 26ai course for data warehouse developers, and we actually managed to take a first look at this brand-new feature. We found it interesting and promising &#8211; but it also immediately raised some critical questions. Our conclusion was clear: this deserves a closer look. And that is exactly what this blog post is about.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p class=\"wp-block-paragraph\"><strong>TL;DR:<\/strong> <code>JOIN TO ONE<\/code> addresses a real problem: joins that are supposed to widen a row should never multiply it. I like the explicit expression of this intent, although I would prefer to keep join types and conditions explicit in production code. The runtime guarantee comes with some interesting optimizer implications, strongly influenced by the state of the dimension-side primary or unique key. The feature is still very new, and there is clearly room for its optimizer support to evolve.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Before diving deeper, it is worth mentioning that there is already some excellent material available on this very new feature. In particular, Oracle&#8217;s documentation deserves praise here: the Database Development Guide contains a surprisingly detailed chapter that goes far beyond simply documenting the syntax and explains the ideas behind <code>JOIN TO ONE<\/code>, including join graphs, Row-Widened Tables, Row Widening Only Queries, and chasm traps. Chris Saxon provides a very accessible introduction with practical examples, while Dani Schnider has already looked at the feature specifically from a data warehouse perspective, including the important topic of constraints.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Further reading:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/26\/adfns\/building-queries-correct-joins-more-easily.html\">Oracle: Building Queries with Correct JOINS More Easily<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/blogs.oracle.com\/sql\/avoid-join-duplicates-with-modern-join-syntax-in-oracle-ai-database\" target=\"_blank\" rel=\"noreferrer noopener\">Chris Saxon: Avoid join duplicates with modern join syntax in Oracle AI Database<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/danischnider.wordpress.com\/2026\/06\/30\/join-to-one-and-constraints\" target=\"_blank\" rel=\"noreferrer noopener\">Dani Schnider: JOIN TO ONE and Constraints<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/danischnider.wordpress.com\/2026\/07\/18\/join-to-one-and-star-schemas\" target=\"_blank\" rel=\"noreferrer noopener\">Dani Schnider: JOIN TO ONE and Star Schemas<\/a><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">So, rather than repeating what is already well explained elsewhere, I will keep the introduction to the feature brief and focus on the aspects and questions I wanted to explore in more detail.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Introduction<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The new <code>JOIN TO ONE<\/code> syntax has two main goals. First, it makes join syntax simpler and, at the same time, expresses the intention of the query more clearly: these joins are meant to widen a row, <strong>never multiply it<\/strong>. Second, it helps ensure correct results by detecting situations where this expectation is violated instead of silently producing additional rows.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For the examples in this post, I will use Sales History (SH) sample schema provided by Oracle. It is a star schema and therefore almost a textbook example of what Oracle calls a <strong>Row Widening Only Query (RWOQ)<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The <code>SALES<\/code> table is the <strong>Row-Widened Table (RWT)<\/strong>. It defines the rows and the grain of the result. Tables such as <code>CUSTOMERS<\/code>, <code>PRODUCTS<\/code>, <code>TIMES<\/code>, <code>CHANNELS<\/code>, and <code>PROMOTIONS<\/code> are joined to add attributes to these rows, but they are not supposed to increase their number.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s go through the first simple example in <strong>Listing 1<\/strong>. Let&#8217;s say I want to know the total amount for items sold without a promotion. In a star schema, it is a common best practice not to leave dimension keys <code>NULL<\/code>. Instead, dimension tables typically contain special records representing cases such as unknown, missing, or not applicable values. These are often called ghost records, singletons, or simply dummy records.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">I don&#8217;t know which <code>PROMO_ID<\/code> represents this special case, but I do know that the corresponding promotion is named something like <code>'NO PROMOTION'<\/code>. So I need to join the <code>PROMOTIONS<\/code> dimension to filter the fact rows in <code>SALES<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Note that this is still an RWOQ. I don&#8217;t need to select any attributes from the joined table. Using them only for filtering does not change the basic idea: the join should restrict the rows, but never multiply them.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">So I constructed the query in lines 1-4 and got my answer: the total amount is around 94 million. But what happens if we have a duplicate promotion with <code>PROMO_ID=999<\/code>? I have to admit that, to make this possible, I had to manipulate the constraints a little, but more on that later. I added the duplicate (lines 11-13) and ran the query again. And suddenly, the result was wrong by a factor of two: 189 million (line 23)!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Time to introduce our first query using the new <code>JOIN TO ONE<\/code> syntax (lines 27-30)! I intentionally chose the simplest form of the query, which, strictly speaking, is not semantically equivalent to the previous one (I&#8217;ll explain why in a minute). But we can see an <strong>immediate benefit<\/strong>: instead of silently returning a wrong result, Oracle now stops the query with a clear error message.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [32,33,34,35]; title: ; notranslate\" title=\"\">\nSQL&gt; select sum(s.amount_sold) \nfrom   sales s\n       join  promotions p on  p.promo_id = s.promo_id\nwhere  p.promo_name like &#039;NO PROMOTION%&#039;\n\nSUM(S.AMOUNT_SOLD)\n------------------\n        94504520.8\n1 row selected.\n\nSQL&gt; insert into promotions \nselect * from promotions \nwhere promo_id = 999;\n\n1 row created.\n\nSQL&gt; select sum(s.amount_sold) \nfrom   sales s\n       join   promotions p on  p.promo_id = s.promo_id\nwhere  p.promo_name like &#039;NO PROMOTION%&#039;\n\nSUM(S.AMOUNT_SOLD)\n------------------\n         189009042\n1 row selected.\n\nSQL&gt; select sum(s.amount_sold) \nfrom   sales s\n       join to one (promotions p)\nwhere  p.promo_name like &#039;NO PROMOTION%&#039;\n                       *\nError at line 18\nORA-18640: JOIN TO ONE reached multiple rows joining to &quot;P&quot;, \nresulting in a non-unique join\nHelp: https:\/\/docs.oracle.com\/error-help\/db\/ora-18640\/\n\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><em><strong>Listing 1: JOIN TO ONE query prevents a wrong result<\/strong><\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s briefly look at the new syntax. To use it, you start your <code>FROM<\/code> clause with a <strong>Row-Widened Table (RWT)<\/strong> &#8211; in our case it is the <code>SALES<\/code> table &#8211;  which is followed by the new <code>JOIN TO ONE<\/code> clause. Within the parentheses, you specify the tables that should widen the RWT without multiplying its rows.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Listing 2<\/strong> shows several variations of the syntax. By default, <code>JOIN TO ONE<\/code> uses a <strong>LEFT OUTER JOIN<\/strong>, but you can explicitly request an <code>INNER JOIN<\/code>. The join condition can either be derived automatically from existing PK-FK constraints or specified explicitly with an <code>ON<\/code> clause. Both choices are independent, so the examples show the different combinations.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Our first query uses the shortest form: both the join type and the join condition are implicit. In this particular example, the <code>WHERE<\/code> condition on <code>PROMOTIONS<\/code> effectively turns the outer join into an inner join anyway.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n-- implicit LEFT join effectively disabled by a WHERE condition\n-- implicit JOIN condition derived from existing FK-PK constraints\nselect sum(s.amount_sold) \nfrom   sales s\n       join to one (promotions p)\nwhere  p.promo_name like &#039;NO PROMOTION%&#039;;\n\n-- explicit INNER join \n-- implicit JOIN condition derived from existing FK-PK constraints\nselect sum(s.amount_sold) \nfrom   sales s\n       join to one (inner join promotions p)\nwhere  p.promo_name like &#039;NO PROMOTION%&#039;;\n\n-- explicit INNER join \n-- explicit JOIN condition \nselect sum(s.amount_sold) \nfrom   sales s\n       join to one (inner join promotions p on p.promo_id = s.promo_id)\nwhere  p.promo_name like &#039;NO PROMOTION%&#039;;\n\n-- implicit LEFT join \n-- explicit JOIN condition\nselect sum(s.amount_sold) \nfrom   sales s\n       join to one (promotions p on p.promo_id = s.promo_id \n                                and p.promo_name like &#039;NO PROMOTION%&#039;);\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong><em>Listing 2: JOIN TO ONE &#8211; Implicit and Explicit Syntax<\/em><\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How Much Syntax Should We Leave Implicit?<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Join Conditions<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Having shown these implicit options, I have to say that I&#8217;m not a great fan of leaving definitions implicit. Let&#8217;s start with implicit join conditions. They are derived from existing foreign key &#8211; primary\/unique key constraint pairs. The constraints don&#8217;t have to be <code>ENABLED<\/code> or <code>VALIDATED<\/code> (although their state can affect execution plans, but more on that later).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If there is more than one possible join path between the tables, you have to specify the join condition explicitly. You will notice that immediately: Oracle raises <code>ORA-18642: multiple join keys found<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">But what about schema evolution? Today there is only one valid join path. A month later, someone adds another column with another foreign key to the same dimension table (a role-playing dimension). For example, we might distinguish between general and personalized promotions, both of which can be activated for the same sale. So they add a <code>PERS_PROMO_ID<\/code> to the fact table, also referencing <code>PROMOTIONS<\/code> (Listing 3).<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [20,21,22]; title: ; notranslate\" title=\"\">\nSQL&gt; select sum(s.amount_sold) \nfrom   sales s\n       join to one (promotions p)\nwhere  p.promo_name like &#039;NO PROMOTION%&#039;\n\nSUM(S.AMOUNT_SOLD)\n------------------\n        94504520.8\n1 row selected.\n\nSQL&gt; alter table sales add (pers_promo_id number \n                            references promotions(promo_id) disable novalidate);\nTable altered.\n\nSQL&gt; select sum(s.amount_sold) \nfrom   sales s\n       join to one (promotions p)\nwhere  p.promo_name like &#039;NO PROMOTION%&#039;\n                       *\nError at line 12\nORA-18642: Multiple join keys found for &quot;PROMOTIONS&quot;\nHelp: https:\/\/docs.oracle.com\/error-help\/db\/ora-18642\/\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong><em>Listing 3: the same query stops working after the definition of the second foreign key<\/em><\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Suddenly, your <strong>working<\/strong> queries stop working with <code>ORA-18642<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Of course you test for that. Really? What about a &#8220;self-service&#8221; report that runs only once a month? Are you even aware of all such queries? <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">That reminds me of a similar risk to that associated with <a href=\"https:\/\/blog.jooq.org\/why-join-using-can-lead-to-errors-in-sql\" target=\"_blank\" rel=\"noreferrer noopener\">NATURAL JOIN or USING clauses<\/a>. I might consider implicit joins for some ad-hoc queries, but not as part of my production code.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Join Types<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">I have similar concerns about the other implicit choice: <code>JOIN TO ONE<\/code> uses a <code>LEFT OUTER JOIN<\/code> by default. In a data warehouse, we often go to some effort to avoid exactly that. One of the reasons for introducing ghost records or dummy dimension members instead of leaving foreign keys <code>NULL<\/code> is to make sure that every fact row has a matching dimension row. This allows us to use <code>INNER JOIN<\/code>s consistently, even for unknown, missing, or not applicable dimension values.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">And there are good reasons for that. An <code>INNER JOIN<\/code> generally gives the optimizer more freedom. Join order is less constrained, and some query transformations that would change the semantics of an outer join are perfectly valid for an inner join. An outer join has to preserve rows from one side, and this requirement can restrict transformations and possible execution plans.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Of course, Oracle&#8217;s optimizer can recognize situations where an outer join is effectively an inner join. For example, with a <code>NOT NULL<\/code> foreign key and an enforced referential constraint, every fact row is guaranteed to have a matching dimension row. Oracle can use this information and treat the join accordingly.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">But that brings us back to constraints. In data warehouses, foreign keys are often deliberately left disabled, as enabled foreign keys can prevent important bulk loading optimizations such as direct-path inserts and parallel DML. If the optimizer cannot prove that every fact row has a matching dimension row, the outer join really remains an outer join from its perspective.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">So why make <code>LEFT OUTER JOIN<\/code> the implicit choice when my data model and ETL process are deliberately designed for <code>INNER JOIN<\/code>s? Even if Oracle can optimize some of these cases equally well, I prefer to state the intended join type explicitly rather than rely on the optimizer to prove that an implicit outer join is effectively an inner one.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For me, this is another case where the shortest <code>JOIN TO ONE<\/code> syntax is not necessarily the syntax I would choose for production code.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How It Works Under The Hood<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">So far, we have looked at the semantics of <code>JOIN TO ONE<\/code>. But how does Oracle actually implement the promise that a join must never multiply a row?<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A good starting point is to look at the transformed SQL. <strong>Listing 4<\/strong> shows the result of expanding our simple <code>JOIN TO ONE<\/code> query. What looked like a compact join has turned into something considerably more interesting. In addition to the regular join, Oracle introduces a correlated scalar subquery against the joined table (line 19-21).<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [19,20,21]; title: ; notranslate\" title=\"\">\nWITH FUNCTION get_sql (p_sql IN CLOB) RETURN CLOB IS\np_sql_out CLOB;\nBEGIN\n  DBMS_UTILITY.EXPAND_SQL_TEXT (p_sql, p_sql_out);\n  RETURN p_sql_out;\nEND;\nSELECT get_sql(q&#039;&#x5B;\nselect sum(s.amount_sold) \nfrom   sales s\n       join to one (promotions p)\nwhere  p.promo_name like &#039;NO PROMOTION%&#039;]&#039;);\n\n\nSELECT SUM (&quot;A1&quot;.&quot;AMOUNT_SOLD_0&quot;)     &quot;SUM(S.AMOUNT_SOLD)&quot;\nFROM   (SELECT &quot;A3&quot;.&quot;AMOUNT_SOLD&quot;     &quot;AMOUNT_SOLD_0&quot;,\n               &quot;A2&quot;.&quot;PROMO_NAME&quot;      &quot;PROMO_NAME_1&quot;\n        FROM   &quot;ONF&quot;.&quot;SALES&quot; &quot;A3&quot;, &quot;ONF&quot;.&quot;PROMOTIONS&quot; &quot;A2&quot;\n        WHERE  &quot;A3&quot;.&quot;PROMO_ID&quot; = &quot;A2&quot;.&quot;PROMO_ID&quot;\n        AND    &#039;P&#039; = (SELECT &#039;P&#039;  &quot;C&quot;\n                      FROM   &quot;ONF&quot;.&quot;PROMOTIONS&quot; &quot;A4&quot;\n                      WHERE  &quot;A3&quot;.&quot;PROMO_ID&quot; = &quot;A4&quot;.&quot;PROMO_ID&quot;)) &quot;A1&quot;\nWHERE  &quot;A1&quot;.&quot;PROMO_NAME_1&quot; LIKE &#039;NO PROMOTION%&#039;;\n\n\n--------------------------------------------\n| Id  | Operation            | Name       | \n--------------------------------------------\n|   0 | SELECT STATEMENT     |            | \n|   1 |  SORT AGGREGATE      |            | \n|*  2 |   FILTER             |            | \n|*  3 |    HASH JOIN         |            | \n|*  4 |     TABLE ACCESS FULL| PROMOTIONS | \n|   5 |     TABLE ACCESS FULL| SALES      | \n|*  6 |    TABLE ACCESS FULL | PROMOTIONS | \n--------------------------------------------\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Listing 4: One of the possible query transformations behind the JOIN TO ONE<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This scalar subquery is important. It does not merely check whether a matching row exists. By definition, a scalar subquery may return at most one row. If a second matching row is found, Oracle can stop and raise an error instead of allowing the join to multiply the row. This is how the <code>TO ONE<\/code> guarantee can be enforced at runtime.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Constraints Matter &#8211; But Which Ones and in Which State?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Whether Oracle needs an additional runtime check depends on the constraints and their state. Interestingly, the requirements are different for the two sides of the relationship.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For the primary or unique key on the joined table, Oracle can avoid the additional runtime check only if the constraint is <code>ENABLE VALIDATE<\/code>. In all other states, including <code>ENABLE NOVALIDATE<\/code>, the expanded SQL contains an additional correlated scalar subquery that enforces the <code>TO ONE<\/code> semantics at runtime.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The state of the foreign key on the Row-Widened Table is a different story. It does not affect this runtime check and can remain <code>DISABLE NOVALIDATE<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This distinction is particularly relevant in a data warehouse. Foreign keys on large fact tables are often deliberately left disabled, as enabled foreign keys can get in the way of efficient bulk loading, preventing direct-path inserts and parallel DML. <code>DISABLE NOVALIDATE<\/code> can also simplify partition exchange operations. Keeping primary and unique key constraints on the usually much smaller dimension tables <code>ENABLE VALIDATE<\/code> is generally far less problematic.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Listing 5<\/strong> demonstrates the difference by changing the state of the primary key while leaving the foreign key unchanged.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSQL&gt; -- Baseline: PK is RELY ENABLE VALIDATE\nSQL&gt; ALTER TABLE promotions MODIFY CONSTRAINT promo_pk RELY ENABLE VALIDATE\nTable altered.\nSQL&gt; ALTER TABLE sales MODIFY CONSTRAINT promo_fk RELY DISABLE NOVALIDATE\nTable altered.\nSQL&gt; WITH FUNCTION get_sql (p_sql IN CLOB) RETURN CLOB IS\np_sql_out CLOB;\nBEGIN\n  DBMS_UTILITY.EXPAND_SQL_TEXT (p_sql, p_sql_out);\n  RETURN p_sql_out;\nEND;\nSELECT get_sql(q&#039;&#x5B;\nselect sum(s.amount_sold) \nfrom   sales s\n       join to one (promotions p)\nwhere  p.promo_name like &#039;NO PROMOTION%&#039;]&#039;) AS transformation;\n\nTRANSFORMATION\n--------------------------------------------------------------------------------\n\nSELECT SUM (&quot;A1&quot;.&quot;AMOUNT_SOLD_0&quot;)     &quot;SUM(S.AMOUNT_SOLD)&quot;\n  FROM (SELECT &quot;A3&quot;.&quot;AMOUNT_SOLD&quot; &quot;AMOUNT_SOLD_0&quot;, &quot;A2&quot;.&quot;PROMO_NAME&quot; &quot;PROMO_NAME_1&quot;\n          FROM &quot;ONF&quot;.&quot;SALES&quot; &quot;A3&quot;, &quot;ONF&quot;.&quot;PROMOTIONS&quot; &quot;A2&quot;\n         WHERE &quot;A3&quot;.&quot;PROMO_ID&quot; = &quot;A2&quot;.&quot;PROMO_ID&quot;) &quot;A1&quot;\n WHERE &quot;A1&quot;.&quot;PROMO_NAME_1&quot; LIKE &#039;NO PROMOTION%&#039;                                       \n1 row selected.\n\nSQL&gt; ALTER TABLE promotions MODIFY CONSTRAINT promo_pk RELY ENABLE NOVALIDATE\nTable altered.\n\nSQL&gt; ...\n\nTRANSFORMATION\n--------------------------------------------------------------------------------\n\nSELECT SUM (&quot;A1&quot;.&quot;AMOUNT_SOLD_0&quot;)     &quot;SUM(S.AMOUNT_SOLD)&quot;\n  FROM (SELECT &quot;A3&quot;.&quot;AMOUNT_SOLD&quot; &quot;AMOUNT_SOLD_0&quot;, &quot;A2&quot;.&quot;PROMO_NAME&quot; &quot;PROMO_NAME_1&quot;\n          FROM &quot;ONF&quot;.&quot;SALES&quot; &quot;A3&quot;, &quot;ONF&quot;.&quot;PROMOTIONS&quot; &quot;A2&quot;\n         WHERE     &quot;A3&quot;.&quot;PROMO_ID&quot; = &quot;A2&quot;.&quot;PROMO_ID&quot;\n               AND &#039;P&#039; = (SELECT &#039;P&#039;     &quot;C&quot;\n                            FROM &quot;ONF&quot;.&quot;PROMOTIONS&quot; &quot;A4&quot;\n                           WHERE &quot;A3&quot;.&quot;PROMO_ID&quot; = &quot;A4&quot;.&quot;PROMO_ID&quot;)) &quot;A1&quot;\n WHERE &quot;A1&quot;.&quot;PROMO_NAME_1&quot; LIKE &#039;NO PROMOTION%&#039;                                       \n1 row selected.\n\nSQL&gt; ALTER TABLE promotions MODIFY CONSTRAINT promo_pk RELY DISABLE VALIDATE\nTable altered.\nSQL&gt; ...\n\nTRANSFORMATION\n--------------------------------------------------------------------------------\n\nSELECT SUM (&quot;A1&quot;.&quot;AMOUNT_SOLD_0&quot;)     &quot;SUM(S.AMOUNT_SOLD)&quot;\n  FROM (SELECT &quot;A3&quot;.&quot;AMOUNT_SOLD&quot; &quot;AMOUNT_SOLD_0&quot;, &quot;A2&quot;.&quot;PROMO_NAME&quot; &quot;PROMO_NAME_1&quot;\n          FROM &quot;ONF&quot;.&quot;SALES&quot; &quot;A3&quot;, &quot;ONF&quot;.&quot;PROMOTIONS&quot; &quot;A2&quot;\n         WHERE     &quot;A3&quot;.&quot;PROMO_ID&quot; = &quot;A2&quot;.&quot;PROMO_ID&quot;\n               AND &#039;P&#039; = (SELECT &#039;P&#039;     &quot;C&quot;\n                            FROM &quot;ONF&quot;.&quot;PROMOTIONS&quot; &quot;A4&quot;\n                           WHERE &quot;A3&quot;.&quot;PROMO_ID&quot; = &quot;A4&quot;.&quot;PROMO_ID&quot;)) &quot;A1&quot;\n WHERE &quot;A1&quot;.&quot;PROMO_NAME_1&quot; LIKE &#039;NO PROMOTION%&#039;                                       \n1 row selected.\n\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong><em>Listing 5: Impact of PK-constraint on query transformations<\/em><\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">And What About Performance?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This is where things become less attractive. The runtime check is implemented using a correlated scalar subquery. Such a subquery would normally be a candidate for transformations such as subquery unnesting, potentially allowing Oracle to use a more efficient join-based execution.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">But here the scalar-subquery semantics are part of the feature. Oracle must detect the case where more than one matching row exists and raise an error. Simply unnesting the subquery into a conventional join would lose exactly that property: a normal join happily returns two matches.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Listing 6<\/strong> shows the consequence in the execution plan. Instead of being fully integrated into the join processing, the additional check remains visible as a <code>FILTER<\/code> with the correlated lookup underneath it (lines 49-50). Depending on the data volumes and access paths, this can be significantly less efficient than the join strategies we would normally expect for a large data warehouse query.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [49,50]; title: ; notranslate\" title=\"\">\nSELECT SUM (&quot;A1&quot;.&quot;AMOUNT_SOLD_0&quot;)     &quot;SUM(S.AMOUNT_SOLD)&quot;\n  FROM (SELECT &quot;A3&quot;.&quot;AMOUNT_SOLD&quot; &quot;AMOUNT_SOLD_0&quot;\n             , &quot;A2&quot;.&quot;PROMO_NAME&quot; &quot;PROMO_NAME_1&quot;\n          FROM &quot;ONF&quot;.&quot;SALES&quot; &quot;A3&quot;, &quot;ONF&quot;.&quot;PROMOTIONS&quot; &quot;A2&quot;\n         WHERE &quot;A3&quot;.&quot;PROMO_ID&quot; = &quot;A2&quot;.&quot;PROMO_ID&quot;) &quot;A1&quot;\n WHERE &quot;A1&quot;.&quot;PROMO_NAME_1&quot; LIKE &#039;NO PROMOTION%&#039; \n\n------------------------------------------\n| Id  | Operation           | Name       |\n------------------------------------------\n|   0 | SELECT STATEMENT    |            |\n|   1 |  SORT AGGREGATE     |            |\n|*  2 |   HASH JOIN         |            |\n|*  3 |    TABLE ACCESS FULL| PROMOTIONS |\n|   4 |    TABLE ACCESS FULL| SALES      |\n------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   2 - access(&quot;A3&quot;.&quot;PROMO_ID&quot;=&quot;A2&quot;.&quot;PROMO_ID&quot;)\n   3 - filter(&quot;A2&quot;.&quot;PROMO_NAME&quot; LIKE &#039;NO PROMOTION%&#039;)\n \n      \nSELECT SUM (&quot;A1&quot;.&quot;AMOUNT_SOLD_0&quot;)     &quot;SUM(S.AMOUNT_SOLD)&quot;\n  FROM (SELECT &quot;A3&quot;.&quot;AMOUNT_SOLD&quot; &quot;AMOUNT_SOLD_0&quot;\n             , &quot;A2&quot;.&quot;PROMO_NAME&quot; &quot;PROMO_NAME_1&quot;\n          FROM &quot;ONF&quot;.&quot;SALES&quot; &quot;A3&quot;, &quot;ONF&quot;.&quot;PROMOTIONS&quot; &quot;A2&quot;\n         WHERE     &quot;A3&quot;.&quot;PROMO_ID&quot; = &quot;A2&quot;.&quot;PROMO_ID&quot;\n               AND &#039;P&#039; = (SELECT &#039;P&#039;     &quot;C&quot;\n                            FROM &quot;ONF&quot;.&quot;PROMOTIONS&quot; &quot;A4&quot;\n                           WHERE &quot;A3&quot;.&quot;PROMO_ID&quot; = &quot;A4&quot;.&quot;PROMO_ID&quot;)) &quot;A1&quot;\n WHERE &quot;A1&quot;.&quot;PROMO_NAME_1&quot; LIKE &#039;NO PROMOTION%&#039;        \n-------------------------------------------\n| Id  | Operation            | Name       |\n-------------------------------------------\n|   0 | SELECT STATEMENT     |            |\n|   1 |  SORT AGGREGATE      |            |\n|*  2 |   FILTER             |            |\n|*  3 |    HASH JOIN         |            |\n|*  4 |     TABLE ACCESS FULL| PROMOTIONS |\n|   5 |     TABLE ACCESS FULL| SALES      |\n|*  6 |    INDEX UNIQUE SCAN | PROMO_PK   |\n-------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   2 - filter( (SELECT &#039;P&#039; FROM &quot;ONF&quot;.&quot;PROMOTIONS&quot; &quot;A4&quot; WHERE \n              &quot;A4&quot;.&quot;PROMO_ID&quot;=:B1)=&#039;P&#039;)\n   3 - access(&quot;A3&quot;.&quot;PROMO_ID&quot;=&quot;A2&quot;.&quot;PROMO_ID&quot;)\n   4 - filter(&quot;A2&quot;.&quot;PROMO_NAME&quot; LIKE &#039;NO PROMOTION%&#039;)\n   6 - access(&quot;A4&quot;.&quot;PROMO_ID&quot;=:B1)\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong><em>Listing 6: Execution plans after query transformations<\/em><\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Could this be done differently? This part is purely my speculation, but perhaps efficiently implementing <code>JOIN TO ONE<\/code> ultimately requires something beyond the existing query transformations. Oracle already has specialized join operations such as <code>NESTED LOOPS SEMI<\/code> and <code>HASH JOIN SEMI<\/code> that implement semantics different from a conventional join. One could imagine a similar operation for this purpose &#8211; let&#8217;s call it <code>HASH JOIN TO ONE<\/code> &#8211; that builds or probes the join structure while detecting a second match for the same key.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Such an operator could combine joining and cardinality validation in one operation instead of expressing the latter through a correlated scalar subquery. Whether Oracle will ever implement it this way is, of course, another question. But such an implementation would make <code>JOIN TO ONE<\/code> particularly interesting in cases where uniqueness is not already guaranteed by an <code>ENABLE VALIDATE<\/code> primary or unique key. In exactly those cases, the protection offered by <code>JOIN TO ONE<\/code> has the greatest value &#8211; and this is where an efficient native implementation would matter most.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For now, the important point is that <code>JOIN TO ONE<\/code> is not just syntactic sugar. Its additional semantic guarantee requires additional work, and with the current implementation this can affect the execution plan. I hope Oracle is already working on improving these plans, so this is an area worth watching.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Star Transformation<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">There is one more optimizer aspect worth mentioning in a data warehouse context: star transformation. The SH schema is a star schema, after all, and <code>JOIN TO ONE<\/code> looks like a natural fit for joining a fact table to its dimensions.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For this test, I will extend our previous examples to two dimension joins, using <code>PRODUCTS<\/code> and <code>CUSTOMERS<\/code>, so that we can see how star transformation interacts with multiple <code>JOIN TO ONE<\/code> runtime checks.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">My initial tests suggested that star transformation might not work with <code>JOIN TO ONE<\/code>. Further testing, however, showed a more nuanced picture. With <code>ENABLE VALIDATE<\/code> primary keys on both joined dimension tables, Oracle successfully applies star transformation to the <code>JOIN TO ONE<\/code> query.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Interestingly, this can still work when an additional runtime uniqueness check is required for one of the dimensions. In <strong>Listing 7<\/strong>, the primary key on <code>CUSTOMERS<\/code> is <code>ENABLE NOVALIDATE<\/code>, while the primary key on <code>PRODUCTS<\/code> is <code>ENABLE VALIDATE<\/code>. The resulting execution plan contains the additional <code>FILTER<\/code> and correlated lookup for <code>CUSTOMERS<\/code>, but Oracle still applies star transformation.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Things change when both primary keys are <code>ENABLE NOVALIDATE<\/code>. In this case, <code>JOIN TO ONE<\/code> requires additional runtime checks for both dimension joins, and star transformation is no longer applied in my test. The hint report gives the reason <code>star_transformation \/ not enough tables<\/code>.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n\n--=========================================================================\n-- no validated primary keys -&gt; no star transformation  \n--========================================================================= \nalter table customers modify constraint cust_pk novalidate; \nalter table products modify constraint prod_pk novalidate; \n\nSELECT \/*+ star_transformation fact (s) *\/ p.prod_desc\n     , c.cust_gender\n     , sum(s.amount_sold)\n  FROM sales s\n  JOIN TO ONE (INNER JOIN products p ON (s.prod_id = p.prod_id) \n               INNER JOIN customers c   ON (s.cust_id = c.cust_id)) \n WHERE p.prod_name = &#039;Shorts&#039;\n   AND c.country_id = &#039;DE&#039;\n GROUP BY p.prod_desc, c.cust_gender;\n \n--------------------------------------------------- \n| Id  | Operation                     | Name      |\n---------------------------------------------------\n|   0 | SELECT STATEMENT              |           |\n|   1 |  HASH GROUP BY                |           |\n|   2 |   NESTED LOOPS                |           |\n|   3 |    NESTED LOOPS               |           |\n|   4 |     VIEW                      | VW_GBC_10 |\n|   5 |      HASH GROUP BY            |           |\n|*  6 |       FILTER                  |           |\n|*  7 |        HASH JOIN              |           |\n|*  8 |         TABLE ACCESS FULL     | PRODUCTS  |\n|   9 |         TABLE ACCESS FULL     | SALES     |\n|* 10 |        INDEX UNIQUE SCAN      | PROD_PK   |\n|* 11 |        INDEX UNIQUE SCAN      | CUST_PK   |\n|* 12 |     INDEX UNIQUE SCAN         | CUST_PK   |\n|* 13 |    TABLE ACCESS BY INDEX ROWID| CUSTOMERS |\n---------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   6 - filter( (SELECT &#039;P&#039; FROM &quot;PRODUCTS&quot; &quot;P&quot; WHERE &quot;P&quot;.&quot;PROD_ID&quot;=:B1)=&#039;P&#039; AND  \n              (SELECT &#039;C&#039; FROM &quot;CUSTOMERS&quot; &quot;C&quot; WHERE &quot;C&quot;.&quot;CUST_ID&quot;=:B2)=&#039;C&#039;)\n              \nHint Report (identified by operation id \/ Query Block Name \/ Object Alias):\nTotal hints for statement: 2 (U - Unused (2))\n---------------------------------------------------------------------------\n \n   1 -  SEL$9C8C05BA\n         U -  star_transformation \/ not enough tables \n\n--=========================================================================\n-- One PK in VALIDATE, other NOVALIDATE -&gt; star transformation possible\n--=========================================================================\n\nalter table customers modify constraint cust_pk novalidate;     \nalter table products modify constraint prod_pk validate;           \n   \nSELECT \/*+ star_transformation fact (s) *\/ p.prod_desc\n     , c.cust_gender\n     , sum(s.amount_sold)\n  FROM sales s\n  JOIN TO ONE (INNER JOIN products p ON (s.prod_id = p.prod_id) \n               INNER JOIN customers c   ON (s.cust_id = c.cust_id)) \n WHERE p.prod_name = &#039;Shorts&#039;\n   AND c.country_id = &#039;DE&#039;\n GROUP BY p.prod_desc, c.cust_gender;\n \n -----------------------------------------------------------\n| Id  | Operation                              | Name      |\n------------------------------------------------------------\n|   0 | SELECT STATEMENT                       |           |\n|   1 |  HASH GROUP BY                         |           |\n|*  2 |   FILTER                               |           |\n|*  3 |    HASH JOIN                           |           |\n|   4 |     MERGE JOIN CARTESIAN               |           |\n|*  5 |      TABLE ACCESS FULL                 | PRODUCTS  |\n|   6 |      BUFFER SORT                       |           |\n|   7 |       VIEW                             | VW_GBF_10 |\n|*  8 |        TABLE ACCESS FULL               | CUSTOMERS |\n|   9 |     TABLE ACCESS BY INDEX ROWID BATCHED| SALES     |\n|  10 |      BITMAP CONVERSION TO ROWIDS       |           |\n|  11 |       BITMAP AND                       |           |\n|  12 |        BITMAP MERGE                    |           |\n|  13 |         BITMAP KEY ITERATION           |           |\n|* 14 |          TABLE ACCESS FULL             | PRODUCTS  |\n|* 15 |          BITMAP INDEX RANGE SCAN       | PROD_BI   |\n|  16 |        BITMAP MERGE                    |           |\n|  17 |         BITMAP KEY ITERATION           |           |\n|  18 |          VIEW                          | VW_GBF_10 |\n|* 19 |           TABLE ACCESS FULL            | CUSTOMERS |\n|* 20 |          BITMAP INDEX RANGE SCAN       | CUST_BI   |\n|* 21 |    INDEX UNIQUE SCAN                   | CUST_PK   |\n------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   2 - filter( (SELECT &#039;C&#039; FROM &quot;CUSTOMERS&quot; &quot;C&quot; WHERE &quot;C&quot;.&quot;CUST_ID&quot;=:B1)=&#039;C&#039;)\n...\n Note\n-----\n   - star transformation used for this statement\n\n--=========================================================================\n-- all primary keys are ENABLE VALIDATE -&gt; star transformation possible\n--=========================================================================\n\nalter table customers modify constraint cust_pk validate;     \nalter table products modify constraint prod_pk validate;           \n\nSELECT \/*+ star_transformation fact (s) *\/ p.prod_desc\n     , c.cust_gender\n     , sum(s.amount_sold)\n  FROM sales s\n  JOIN TO ONE (INNER JOIN products p ON (s.prod_id = p.prod_id) \n               INNER JOIN customers c   ON (s.cust_id = c.cust_id)) \n WHERE p.prod_name = &#039;Shorts&#039;\n   AND c.country_id = &#039;DE&#039;\n GROUP BY p.prod_desc, c.cust_gender;\n \n----------------------------------------------------------------------------------------\n| Id  | Operation                                | Name                        | Rows  |\n----------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                         |                             |     2 |\n|   1 |  TEMP TABLE TRANSFORMATION               |                             |       |\n|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_7FD9EA8EE_26434BE3 |       |\n|*  3 |    TABLE ACCESS FULL                     | CUSTOMERS                   |  2921 |\n|   4 |   HASH GROUP BY                          |                             |     2 |\n|*  5 |    HASH JOIN                             |                             |  2658 |\n|   6 |     MERGE JOIN CARTESIAN                 |                             |  1461 |\n|*  7 |      TABLE ACCESS FULL                   | PRODUCTS                    |     1 |\n|   8 |      BUFFER SORT                         |                             |  2921 |\n|   9 |       TABLE ACCESS FULL                  | SYS_TEMP_7FD9EA8EE_26434BE3 |  2921 |\n|  10 |     VIEW                                 | VW_ST_97ECF932              |  5315 |\n|  11 |      NESTED LOOPS                        |                             |  5315 |\n|  12 |       BITMAP CONVERSION TO ROWIDS        |                             |  5314 |\n|  13 |        BITMAP AND                        |                             |       |\n|  14 |         BITMAP MERGE                     |                             |       |\n|  15 |          BITMAP KEY ITERATION            |                             |       |\n|* 16 |           TABLE ACCESS FULL              | PRODUCTS                    |     1 |\n|* 17 |           BITMAP INDEX RANGE SCAN        | PROD_BI                     |       |\n|  18 |         BITMAP MERGE                     |                             |       |\n|  19 |          BITMAP KEY ITERATION            |                             |       |\n|  20 |           TABLE ACCESS FULL              | SYS_TEMP_7FD9EA8EE_26434BE3 |  2921 |\n|* 21 |           BITMAP INDEX RANGE SCAN        | CUST_BI                     |       |\n|  22 |       TABLE ACCESS BY USER ROWID         | SALES                       |     1 |\n----------------------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   3 - filter(&quot;C&quot;.&quot;COUNTRY_ID&quot;=TO_NUMBER(&#039;DE&#039;))\n   5 - access(&quot;ITEM_2&quot;=&quot;C0&quot; AND &quot;ITEM_1&quot;=&quot;P&quot;.&quot;PROD_ID&quot;)\n   7 - filter(&quot;P&quot;.&quot;PROD_NAME&quot;=&#039;Shorts&#039;)\n  16 - filter(&quot;P&quot;.&quot;PROD_NAME&quot;=&#039;Shorts&#039;)\n  17 - access(&quot;S&quot;.&quot;PROD_ID&quot;=&quot;P&quot;.&quot;PROD_ID&quot;)\n  21 - access(&quot;S&quot;.&quot;CUST_ID&quot;=&quot;C0&quot;)\n\nNote\n-----\n   - star transformation used for this statement\n\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\"><strong><em>Listing 7: Star transformation and JOIN TO ONE<\/em><\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">So the additional runtime checks introduced by <code>JOIN TO ONE<\/code> do not by themselves prevent star transformation, but they clearly interact with it. I have not investigated the exact optimizer rules behind this behavior yet, so I would not derive a general rule from these examples.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The good news is that <code>JOIN TO ONE<\/code> and star transformation are certainly not mutually exclusive. For a feature that looks particularly interesting for star schemas, that is an important result.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">There are more questions worth exploring from a data warehouse perspective. For example, it would be interesting to see how <code>JOIN TO ONE<\/code> interacts with vector transformation, or whether execution plans involving the additional runtime checks can still make effective use of Bloom filters and their offloading with Exadata Smart Scan. I have not tested these scenarios yet, so perhaps they are topics for another day.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Final Thoughts<\/h1>\n\n\n\n<p class=\"wp-block-paragraph\">So, where does this leave <code>JOIN TO ONE<\/code>?<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">I think the idea behind the feature is compelling, especially in a data warehouse. Being able to express explicitly that a join is supposed to widen a row, never multiply it, makes the intention of a query very clear. And where this expectation cannot be guaranteed by constraints, having the database detect a violation instead of silently returning wrong results is certainly valuable.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">There is an interesting trade-off, though. If the primary or unique key on the dimension side is <code>ENABLE VALIDATE<\/code>, Oracle can avoid the additional runtime uniqueness check. But in exactly this case, the database already guarantees the uniqueness that <code>JOIN TO ONE<\/code> is supposed to protect us against. Where this guarantee cannot be derived from the constraint, the additional runtime check remains, introducing <code>FILTER<\/code> operations and correlated lookups that I would rather not see in large data warehouse queries. This makes it even more important to define the constraints in a star schema carefully and choose their states in accordance with the ETL strategy. If this is done properly, <code>JOIN TO ONE<\/code> can provide its additional semantics without introducing this runtime overhead.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This leaves the clearer expression of intent as an important benefit in its own right. As discussed earlier, I would probably not make much use of the syntactic simplifications in production code and would still spell out the join type and join condition explicitly.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">After all, <code>JOIN TO ONE<\/code> was introduced only recently. The concept addresses a real problem, the intent is clear, and Oracle has already provided a remarkably comprehensive foundation around it. I am curious to see how both the syntax and, especially, its optimizer support evolve in future releases.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>With Oracle AI Database 23.26.2, Oracle introduced the new JOIN TO ONE syntax. As it happened, just a few weeks later, I was teaching an Oracle 26ai course for data warehouse developers, and we actually managed to take a first look at this brand-new feature. We found it interesting and promising &#8211; but it also [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":2766,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[162,50,2],"tags":[167,164,165,51],"class_list":["post-2708","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-26ai","category-data-warehouse","category-oracle","tag-data-warehouse","tag-oracle-26ai","tag-query-optimizer","tag-sql"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/2708","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/comments?post=2708"}],"version-history":[{"count":53,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/2708\/revisions"}],"predecessor-version":[{"id":2773,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/2708\/revisions\/2773"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media\/2766"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media?parent=2708"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/categories?post=2708"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/tags?post=2708"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}